April 2016 - discuss - lists.mariadb.org

[Maria-discuss] InnoDB fake changes
by Federico Razzoli 25 Apr '16

25 Apr '16

Hi all I see that 10.2 still has the innodb_fake_changes variable (OFF by default). That feature was stable in Percona Server 5.5, is beta in 5.6, and was removed in 5.7. Is it still there just for compatibility with old configuration files, or is there still a reason to use it? And in that case, is it stable or not? Regards, Federico

4 3

[Maria-discuss] New Question: Mariadb Columnstore on Windows
by AskMonty KB 25 Apr '16

25 Apr '16

Hello, A new question has been asked in "MariaDB Community" by brian_demilia_g. Please answer it at http://mariadb.com/kb/en/mariadb-columnstore-on-windows/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Does anyone know if the upcoming Columnstore storage engine will be usable on Windows? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-columnstore-on-windows/

1 0

[Maria-discuss] New Question: mysqld fills /tmp with file /tmp/#sql_26be_0.MAD
by AskMonty KB 22 Apr '16

22 Apr '16

Hello, A new question has been asked in "MariaDB Documentation" by aferrett. Please answer it at http://mariadb.com/kb/en/mysqld-fills-tmp-with-file-tmpsql_26be_0mad/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We have MariaDB CE 10.1.11 running on RHEL. We were loading data and started getting this error in the mysqld.log: 2016-04-22 9:45:54 139696698296064 [Warning] mysqld: Disk is full writing '/tmp/#sql_26be_0.MAD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) We moved the large file out of /tmp, but had to reboot in order to free up the /tmp. Restarted mysqld and it appears to be OK. Any information on what this might be and how to get to root cause will be appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mysqld-fills-tmp-with-file-tmpsql_26be_0mad/

1 0

[Maria-discuss] GTID replication and SST method
by Craig Bailey 22 Apr '16

22 Apr '16

Hi, I am attempting to get remote replication working from my MariaDB Galera Cluster in the following way: * MariaDB Galera Cluster replicates to a remote MariaDB DR node via standard master slave method using MariaDB GTID o Pick a master node from the cluster o Take a backup with xtrabackup-v2 o Apply backup to slave o Connect the slave to the master and replicate using MariaDB GTID o If master goes down, switch master to another node in the Galera Clutser However I am having problems keeping the GTID in step across all the nodes in the Galera cluster when joining new nodes or re-joining existing nodes. Using the latest version of xtrabackup-v2 the current GTID position is stored in a file as part of the backup, however it does not apply this value to the database when a node is brought into the cluster. A step by step example is below: * Cluster is running fine * Lots of transactions going through, enough that a new joiner would need an SST * Begin joining a new node to the cluster * SST via xtrabackup-v2 happens during this process * The GTID in the donor is 1-1-12345 * SST completes joiner is now part of the cluster * The GTID on the newly joined node is 1-1-1 * We now have an out of sync GTID meaning we cannot simply switch master if our current master goes down. I tried the same scenario using RSYNC as the transfer method and the GTID is replicated across to the joiner without issue. Ideally I would like continue using xtrabackup-V2 as my SST method because it does very little locking on a state transfer. That being the case are there any work arounds for this, such as a way to ensure the GTID is set on the joiner when the SST happens with xtrabackup-v2? On the MariaDB pages it does say "xtrabackup-v2 and xtrabackup SST methods currently do not support GTID" is this up-to-date information? https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_ss… Thanks, Craig Evertz UK cbailey(a)evertz.com<mailto:cbailey@evertz.com> www.evertz.com This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender (as shown above). Kindly do not reproduce, print or forward any material received in error, please delete it immediately. Evertz UK Limited (Company No. 3458137) is incorporated in England and Wales and has its registered office at 100 Berkshire Place, Wharfedale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5RD. Evertz Singapore Pte Limited (Company No. 200817005N) is incorporated in Singapore and has its registered office at One Marina Boulevard, #28-00. Singapore 018989.

3 4

[Maria-discuss] MariaDB 5.5.49 and updated connectors now available
by Daniel Bartholomew 22 Apr '16

22 Apr '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.49, MariaDB Connector/J 1.4.2, and MariaDB Connector/ODBC 2.0.10. These are all Stable (GA) releases. See the Release Notes and Changelogs for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 5.5.49 - Release Notes: https://mariadb.com/kb/en/mdb-5549-rn/ - Changelog: https://mariadb.com/kb/en/mdb-5549-cl/ - Downloads: https://downloads.mariadb.org/mariadb/5.5.49 About MariaDB 5.5: - https://mariadb.com/kb/en/what-is-mariadb-55/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ MariaDB Connector/J 1.4.2 - Release Notes: https://mariadb.com/kb/en/mcj-142-rn/ - Changelog: https://mariadb.com/kb/en/mcj-142-cl/ - Downloads: https://downloads.mariadb.org/connector-java/1.4.2/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-j/ MariaDB Connector/ODBC 2.0.10 - Release Notes: https://mariadb.com/kb/en/mco-2010-rn/ - Changelog: https://mariadb.com/kb/en/mco-2010-cl/ - Downloads: https://downloads.mariadb.org/connector-odbc/2.0.10/ About MariaDB Connector/J: - https://mariadb.com/kb/en/about-mariadb-connector-odbc/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - There are several upcoming MariaDB-focused webinars and many previously held ones available to watch on an on-demand basis at: - https://mariadb.com/news-events/webinars - - MariaDB Books - - - - - - - - - - - - - - - - - - - - - - - - - - There is an ever-growing library of MariaDB books available to help you get the most out of MariaDB. See the MariaDB Books page for details and links: - https://mariadb.com/kb/en/mariadb/books/ - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/donate/ We hope you enjoy MariaDB! -- MariaDB Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] New Question: MariaDB Storage Engine
by AskMonty KB 21 Apr '16

21 Apr '16

Hello, A new question has been asked in "MariaDB FAQ" by palash.harchandani93_43595. Please answer it at http://mariadb.com/kb/en/mariadb-storage-engine/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Is Cassandra storage engine available for mariadb-enterprise 10.1.13 tarball or rpm packages..as i was looking into tar.gz packages but couldn't find "ha_cassandra.so" plugin? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-storage-engine/

1 0

[Maria-discuss] Dynamically rewrite query
by Erik Cederstrand 21 Apr '16

21 Apr '16

Hello list, I'm administering a MariaDB (10.0.23) backend used by some proprietary software. I'm having trouble with one specific slow query. The tables look like this (simplified for clarity): CREATE TABLE `t1` ( `i` int(11) NOT NULL, `j` int(11) NOT NULL, `l` int(11) NOT NULL, `m` int(11) DEFAULT NULL, PRIMARY KEY (`i`,`j`,`l`) ); CREATE TABLE `t2` ( `i` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; The problematic query is this: SELECT DISTINCT t2.i, t2.k, t1.j, t1.l, t1.m FROM t2 LEFT JOIN t1 ON t2.i=t1.i WHERE t2.k < 123; The query is slow because the DISTINCT clause forces the query to use a temporary table. Removing DISTINCT makes the query 100x faster. Now, since (t1.i, t1.j, t1.l, t1.m) are guaranteed to be unique (and also (t2.i, t2.k) on their own, if the left join doesn't match), I cannot see how "SELECT ..." and "SELECT DISTINCT ..." can possibly produce different output. So I mention that to the developers of said software, and they reply that they forgot why they added the DISTINCT but that they decided it's too risky to remove it (thereby admitting that they don't comment their code, and don't write unit tests for bugs they find, but I knew that already). I'm aware of "Garbage in, garbage out", but it could also be argued that MariaDB should be smart enough to detect that the DISTINCT is unnecessary and ignore it (should I create a bug report for that?). In the meantime, is there any way I can rewrite queries like this dynamically to remove the DISTINCT clause? MySQL 5.7 documentation mentions query rewrite plugins, but I don't see that mentioned in MariaDB documentation. Any other suggestions are welcome. Thanks, Erik

4 5

[Maria-discuss] New Question: Storage engine support and compatibility constraints
by AskMonty KB 20 Apr '16

20 Apr '16

Hello, A new question has been asked in "MariaDB FAQ" by dbamaria. Please answer it at http://mariadb.com/kb/en/storage-engine-support-and-compatibility-constrain… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, 1- As the storage engine are plugins: - Are there any compatibility constraints between them when they are implemented in the same instance ? - Is there a constraint compatibility with those plugins and the MariaDb core? (any kind of compatibility matrix is appreciated) 2-Who support the MariaDb plugins (storage engines, UDF libraries...)? Thanks and Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/storage-engine-support-and-compatibility-constrain…

1 0

Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures
by Federico Razzoli 19 Apr '16

19 Apr '16

I don't think that this is a procedure performance problem. The problem seems to be character set conversion. 1) set correctly your server and databases default character set and collation (show variables like '%char', alter database) 2) if your connection uses utf8, and your query/procedures contains a 'string', MariaDB considers 'string' as a utf8 string. Make sure you're using latin1 (with the commant-line client it's the default) To check if the character set is latin1, you can query information_schema.ROUTINES table and check the fields CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION (or SHOW CREATE PROCEDURE). Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson(a)nepean.com> ha scritto: Oggetto: Re: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Martedì 19 Aprile 2016, 10:21 #yiv4011784495 #yiv4011784495 -- _filtered #yiv4011784495 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv4011784495 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} #yiv4011784495 #yiv4011784495 p.yiv4011784495MsoNormal, #yiv4011784495 li.yiv4011784495MsoNormal, #yiv4011784495 div.yiv4011784495MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;} #yiv4011784495 a:link, #yiv4011784495 span.yiv4011784495MsoHyperlink {color:#0563C1;text-decoration:underline;} #yiv4011784495 a:visited, #yiv4011784495 span.yiv4011784495MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;} #yiv4011784495 p.yiv4011784495MsoPlainText, #yiv4011784495 li.yiv4011784495MsoPlainText, #yiv4011784495 div.yiv4011784495MsoPlainText {margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;} #yiv4011784495 span.yiv4011784495PlainTextChar {} #yiv4011784495 .yiv4011784495MsoChpDefault {} _filtered #yiv4011784495 {margin:72.0pt 72.0pt 72.0pt 72.0pt;} #yiv4011784495 div.yiv4011784495WordSection1 {} #yiv4011784495 Thanks for that Federico, I read a little more on the net including the bug report you pointed me to however I did a little more digging and found the following:- Using one of the 476 stored procedures I have in my database, when the procedure passes the command string to the database it adds the following highlighted bits:- SELECT SUM(tblsub.fieldX-tblsub.fieldy) AS Bal FROM tblsub join tblmain on tblsub.keyfield=tblmain.keyfield WHERE tblsub.criteria1= NAME_CONST('refnum',_utf8'S000730128000' COLLATE 'utf8_general_ci') and tblmain.criteria2<5; ‘refnum’ is a passed in parameter. The procedeure is a function and returns a double. When I run this in a client (heidiSQL) it takes between 1 minute and 13 seconds (slowest) to 40.451 seconds (fastest) When I change the where clause to read:- WHERE tblsub.criteria1= NAME_CONST('refnum',_latin1'S000730128000' COLLATE 'latin1_swedish_ci') and tblmain.criteria2<5; The query takes 0.078 seconds (slowest) 0.045 (fastest) The following system character set variables are set to “latin1” but stored procedures still try and convert where criteria or passed in parameters to ‘utf8’ Character_set_client Character_set_connection Character_set_database Character_set_results Character_set_server All local tables are InnoDB with latin1_swedish_ci default collation therefore I assume they are stored using the latin1 character set. Is there any way to force MariaDB to use latin1 in stored procedures? Also I must point out that this is happening in MariaDB 10.1.7 and 10.1.13 that I’ve tested but does not happen in MariaDB 5.3.4 which has been my production version since 5.3.4 was released and I must add it has never failed. I’m trying to upgrade to 10.1.xxx to take advantage of the connect engine which I have tested for several months now and found to be extremely useful. However I can not put version 10.1.xxx into service until this issue is sorted. Kind Regards, Bruce Carlson -----Original Message----- From: Federico Razzoli [mailto:federico_raz@yahoo.it] Sent: Tuesday, 19 April 2016 10:41 AM To: maria-discuss(a)lists.launchpad.net; Bruce Carlson Subject: R: [Maria-discuss] Mariadb 10.1.13 slow stored procedures Stored procedures are slow in MariaDB and MySQL. I reported a bug for this, and Elena Stepanova's comment explains the reason, or maybe one of the reasons. The bug is: https://jira.mariadb.org/browse/MDEV-8254 MariaDB 10.1 is not slower than other versions. From my tests, MySQL 5.7 is almost thrice slower (wtf...), and in one case it caused the OOM to kill mysqld (wtf!?!?). Suggestion: try to avoid using @user_variables. Replace them with local_variables. NEW. and OLD. variables in triggers are ok. Queries are ok. I think that the only problem is access to variables. Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson(a)nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Martedì 19 Aprile 2016, 01:24 HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] R: Mariadb 10.1.13 slow stored procedures
by Federico Razzoli 19 Apr '16

19 Apr '16

Stored procedures are slow in MariaDB and MySQL. I reported a bug for this, and Elena Stepanova's comment explains the reason, or maybe one of the reasons. The bug is: https://jira.mariadb.org/browse/MDEV-8254 MariaDB 10.1 is not slower than other versions. From my tests, MySQL 5.7 is almost thrice slower (wtf...), and in one case it caused the OOM to kill mysqld (wtf!?!?). Suggestion: try to avoid using @user_variables. Replace them with local_variables. NEW. and OLD. variables in triggers are ok. Queries are ok. I think that the only problem is access to variables. Regards, Federico -------------------------------------------- Mar 19/4/16, Bruce Carlson <Bruce.Carlson(a)nepean.com> ha scritto: Oggetto: [Maria-discuss] Mariadb 10.1.13 slow stored procedures A: "maria-discuss(a)lists.launchpad.net" <maria-discuss(a)lists.launchpad.net> Data: Martedì 19 Aprile 2016, 01:24 HI All, Just a quick question to start with, does anybody know why MariaDB 10.1.13 is extremely slow running stored procedures. I have searched for as much as I can find and have made sure the database and table character sets and compilation seta are identical but still stored procedures with lots of joins and internal variables is running hundreds of times slower than the old version 5.3. I have checked all system variables and they are identical in both systems (where they exist in both systems) Any assistance or advice would be greatly appreciated. If I need to supply more information please let me know. Both versions are running on windows server 2008 R2 Kind Regards, Bruce Carlson -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

2 1