- discuss - lists.mariadb.org

[Maria-discuss] Select optimizations
by Roberto Spadim 20 Jan '16

20 Jan '16

Hi guys! There's a "easy/fast" way to rewrite a query from this: Select datetime,key,value from table where ... To this: Select datetime, value1 as key1, value2 as key2, .... From table where ... I don't remember the right name in R/python (pandas) language but it's something like vector/matrix merge The ideia is, instead of one line of date key vale, use a single date, and "put" values as columns, and the columns name is the key name The table structure is something like: Create table ( Datetime datetime not null, Key varchar(255) not null, Value double not null, Primary key(datetime,key) ) Any idea is wellcome I know there's many solutions, if i have >100 columns, if datetime is sparse (only one key have values per datetime value), etc etc -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

2 3

[Maria-discuss] New Question: Using BitNami WordPress with Amazon RDS MariaDB
by AskMonty KB 19 Jan '16

19 Jan '16

Hello, A new question has been asked in "MariaDB Community" by brasilmorar. Please answer it at http://mariadb.com/kb/en/using-bitnami-wordpress-with-amazon-rds-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Installed BitNami WordPress which is already configured and updated in an instance EC2 Amazon. Launched a database RDS MariaDB, efetuei up the MySQL database that is already inserted in BitNami stack and then sent the backup.sql for instance RDS MaridaDB. I changed the database access settings in wp-config.php file by inserting the data from the database RDS MariaDB created. Everything is working fine, but I have a doubt about the performance of the bank I have not deleted the Mysql the BitNami stack. If I'm using InnoDB tables and not the standard MariaDB tables. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/using-bitnami-wordpress-with-amazon-rds-mariadb/

1 0

[Maria-discuss] New Question: mysql_install_db
by AskMonty KB 14 Jan '16

14 Jan '16

Hello, A new question has been asked in "MariaDB Community" by skuns_35717. Please answer it at http://mariadb.com/kb/en/mariadb-community-mysql_install_db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm new to maria, coming from mysql, and I'm trying to run the mysql_install_db program to create the new databases. This is what i'm executing as root: /usr/bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql --basedir=/usr --force I've tried it with and without the force option. I always get the same error: ERROR: 1005 Can't create table `mysql`.`innodb_table_stats` (errno: 165 "Table is read only") 160114 11:35:10 [ERROR] Aborting Any help will be greatly appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-community-mysql_install_db/

1 0

[Maria-discuss] Are lock/unlock tables replicated in ROW / STATEMENT / MIXED r. mode
by pslawek83 13 Jan '16

13 Jan '16

Hi Guys, quick question about lock tables with replication... I basically use it to achieve atomicity of some database writes, but it seems that queries like LOCK TABLES ___tsc WRITE UNLOCK TABLES are not replicated to slave. Eg. i can lock table on master, for write, but still read from it on the slave, which could lead to issues... We're reading some data from slaves, and writing data to it atomically using LOCK TABLES, it seems that this way will lead to race conditions and inconsistent reads from the slave. Any way of having aria/myisam writes to be atomic on the slaves too? I thought to copy the table to random temporary name, do the changes and then rename... but it seems like not very optimal. Thanks.

3 2

[Maria-discuss] New Question: SQL Server replication to Maria DB
by AskMonty KB 13 Jan '16

13 Jan '16

Hello, A new question has been asked in "MariaDB Community" by zeeshan_amin. Please answer it at http://mariadb.com/kb/en/sql-server-replication-to-maria-db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, I am new to Maria DB and have no past experience with MySQL. I am trying to create replication between SQL Server 2005 and Maria DB 10.0.14 Publication has 2 tables with close to 24 million rows, and taking a long time to load data into Maria DB. There is no other user connected to MariaDB, Looking at the workbench, i see InnoDB Buffer usage close to 87%, key Efficiency 100%, and Traffic 35KB/sec. as per current speed 2million rows an hour, its going to take 12 hours to load 24 million rows. Any help in making this load faster? Regards Zee -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/sql-server-replication-to-maria-db/

2 1

[Maria-discuss] New Question: PDF documentation going to be available anytime soon?
by AskMonty KB 12 Jan '16

12 Jan '16

Hello, A new question has been asked in "MariaDB Documentation" by dominic_baines. Please answer it at http://mariadb.com/kb/en/pdf-documentation-going-to-be-available-anytime-so… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Any update on the answer from a year ago ? Looking for mariadb documentation for offline use. The KB/documentation great IF you have internet access. Dom -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/pdf-documentation-going-to-be-available-anytime-so…

1 0

[Maria-discuss] New Question: Galera cluster fail during dump or optimize
by AskMonty KB 11 Jan '16

11 Jan '16

Hello, A new question has been asked in "MariaDB Galera Cluster" by gijs. Please answer it at http://mariadb.com/kb/en/galera-cluster-fail-during-dump-or-optimize/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm experiencing an issue with a MariaDB cluster when dumping or optimizing databases. When I run a a "mysqlcheck" or a "mysqldump" on my Mariadb 10.1.10 database server (which runs in a Galera cluster with two other servers) then the tasks stop after a short time and don't show any progress. The entire cluster seems to stall. For example the mysqldump stops after creating an 14,0 MB (14.760.912 bytes) dump file and doesn't proceed, even after several minutes nothing happens. The mysqlcheck to repair and optimize tables also hangs after checking a few tables. In both situations the cluster starts to have issues and the only way to get it to work normally again is by taking the server that executed the job offline and also taking another server offline. I then take them back online on by one and the cluster works normally again. I'm not sure what is causing these problems. I haven't found any errors in the syslog, although during the shutdown of the servers I noticed the following: Jan 10 20:43:46 france mysqld[1015]: 2016-01-10 20:43:46 140096330258176 [Warning] WSREP: TO isolation failed for: 3, schema: mysql, sql: OPTIMIZE TABLE proc. Check wsrep connection state and retry the query. Jan 10 21:58:47 france mysqld[1034]: 2016-01-10 21:58:47 139691511322368 [Warning] WSREP: TO isolation failed for: 3, schema: smf, sql: OPTIMIZE TABLE smf_categories. Check wsrep connection state and retry the query. Jan 10 21:58:47 france mysqld[1034]: 2016-01-10 21:58:47 139691511322368 [Warning] Aborted connection 24 to db: 'smf' user: 'maintenance' host: 'localhost' (Unknown error) Jan 10 21:58:47 france mysqld[1034]: 2016-01-10 21:58:47 139691509827328 [Warning] WSREP: TO isolation failed for: 3, schema: (null), sql: SELECT 1 FROM mysql.user LIMIT 1. Check wsrep connection state and retry the query. Any idea what could be the cause of this? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/galera-cluster-fail-during-dump-or-optimize/

1 0

[Maria-discuss] mariadb still uses indexes with high average value group size.
by dE 09 Jan '16

09 Jan '16

Hi! I was playing around with indexes after we faced problems with mariadb choosing the wrong index because of high average value group size. So I created a table with 4 text columns and indexed the 4th column with a prefix length of 1. Following are the stats in brief -- The index has 35 unique values There are around 1040000 rows in total. this will give an average value group size of 29714 Cardinality is calculated as <no. of records in the index>/<average size of value groups>, thus it's value must be 35 BUT from show indexes, it's value is shown as 72 (innodb_stats_persistent_sample_pages = 9999999).

3 4

[Maria-discuss] New Question: Connect DB2 table via ODBC with CLOB or Text datatype.
by AskMonty KB 08 Jan '16

08 Jan '16

Hello, A new question has been asked in "CONNECT" by mldudding. Please answer it at http://mariadb.com/kb/en/connect-db2-table-via-odbc-with-clob-or-text-datat… as the person asking the question may not be subscribed to the mailing list. -------------------------------- I am attempting to connect a DB2 table using the following command line: create table TASKS engine=connect table_type=ODBC block_size=1000 tabname='ISW.TASKS' connection='DSN=DB2_ODBC;UID=user;PWD=password;MODE=SHARE;DBALIAS=SCODS;'; and I receive the following error: ERROR 1105 (HY000): Unsupported SQL type -99 My belief is that because CONNECT is retrieving the table description using ODBC catalog functions and since the DB2 table contains a column that has a datatype of CLOB that Mariadb is throwing the above error. When I attempt to specify the column definitions for the DB2 table, and define the column as TEXT or BLOB, I receive the following error: SQL Error (1163): Storage engine CONNECT doesn't support BLOB/TEXT columns. Is there a way to CONNECT a DB2 table via ODBC or Direct Connection to a table that contains CLOB or Text defined columns? I do not have any issue connecting other tables from the DB2 database that do not contain CLOB datatypes. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/connect-db2-table-via-odbc-with-clob-or-text-datat…

1 0

[Maria-discuss] New Question: MariaDb vs Oracle
by AskMonty KB 06 Jan '16

06 Jan '16

Hello, A new question has been asked in "MariaDB Community" by dbamaria. Please answer it at http://mariadb.com/kb/en/mariadb-vs-oracle/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, I have to find out in MariaDb how to implement some features used in Oracle . I have : - Load a file: in Oracle I use the external table. Is there a way (fast and efficient one ) to load a file into a table . Has MariaDb a plugin which allows to load well a specific format of files? - In my existing Oracle code I used to developp a java wrap functions which allow those feature (is there a way in MariaDb to do this?), specifically : 1- Searching a files in an OS directory and insert them in a table, 2- send an SNMP trap 3- Send a mail via SMTP - Is there an equivalent to an Oracle job in Mariadb? - Is there an equivalent to Oracle TDE (Transparent data encryption) ? -Is there an equivalent to the VPD (virtual private policy)? Many Thanks and Best Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-vs-oracle/

1 0