- discuss - lists.mariadb.org

[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

[Maria-discuss] Transportable (portable) tablespaces
by Colin Charles 05 Jan '16

05 Jan '16

Hi, I haven’t tested this yet (the joys of vacation that still keeps you coming back to the lists), but thought I’d approach this as a user searching the Knowledgebase :-) This is a feature from 5.6, and it has improved in 5.7. I saw https://mariadb.atlassian.net/browse/MDEV-4299 saying that it works (looks like Serg did the merge — http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3965.1.18 for 10.0.8), but I couldn’t find any documentation suggesting that such a feature exists, eg. https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-exa… One of the “improvements” that I guess we’ll be getting is, the ability to use the CREATE TABLESPACE command — https://dev.mysql.com/doc/refman/5.7/en/create-tablespace.html (vs. https://mariadb.com/kb/en/mariadb/create-tablespace/) Looking forward to more information/discussion, and also if this is a docs bug, I guess I CC’ed Ian cheers, -colin -- Colin Charles, http://bytebot.net/blog/ twitter: @bytebot | skype: colincharles "First they ignore you, then they laugh at you, then they fight you, then you win." -- Mohandas Gandhi

2 1

[Maria-discuss] New Question: start the first cluster node failed with libssl.so.6 problem
by AskMonty KB 04 Jan '16

04 Jan '16

Hello, A new question has been asked in "MariaDB Galera Cluster" by yu.d.sun_32615. Please answer it at http://mariadb.com/kb/en/start-the-first-cluster-node-failed-with-libsslso6… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, All Mariadb galera Version: 10.0.21 OS: Redhat 6 : 2.6.32-504.23.4.el6.x86_64 I installed the software from tarball. When i tried to start the first node with: ./bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf --wsrep-new-cluster & in error log: 160104 11:23:01 [Note] WSREP: wsrep_load(): loading provider library '/apps/opt/mysql/galera-10.0.21/lib/libgalera_smm.so' 160104 11:23:01 [ERROR] WSREP: wsrep_load(): dlopen(): libssl.so.6: cannot open shared object file: No such file or directory 160104 11:23:01 [ERROR] WSREP: wsrep_load(/apps/opt/mysql/galera-10.0.21/lib/libgalera_smm.so) failed: Invalid argument (22). Reverting to no provider. 160104 11:23:01 [Note] WSREP: Read nil XID from storage engines, skipping position init 160104 11:23:01 [Note] WSREP: wsrep_load(): loading provider library 'none' 160104 11:23:01 [ERROR] Aborting I guess this is sth related to openssl version: openssl-1.0.1e-30.el6_6.11.x86_64 I can install and configure 10.0.21 cluster without any problem in CentOS 5.5 Any suggestions on how to work around openssl problem? Downgrade openssl ? or If i compile the source in redhat OS 6,will that help? Anyone has installed any version of Mariadb Galera cluster in any version of Redhat/CentOS linux above 5? Denis -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/start-the-first-cluster-node-failed-with-libsslso6…

1 0

[Maria-discuss] mariadb prefers using indexes despite low cardinality
by dE 02 Jan '16

02 Jan '16

Hi! Taking the example as provided in my last mail: I created a table with 4 text columns and indexed the 4th column with a prefix length of 1. To trigger high no. of index scans I used the following where clause -- f4 like 'klas9dufa9us09df8a8%' Explain shows mariadb still chooses to use the index despite high no. of scans required.

1 0