December 2013 - discuss - lists.mariadb.org

[Maria-discuss] explain for insert/update/delete
by MARK CALLAGHAN 08 Jul '25

08 Jul '25

Does MariaDB have any plans to support explain for insert/update/delete/replace/CTAS? There have been feature requests open for a very long time on official MySQL without any progress. I have had a few problems in production from 'bad' delete statements that had a very different plan than the select statement with the same where clause. The only way to check for a bad plan today is to use SHOW INNODB STATUS to determine whether the statement locks all rows in a table. http://bugs.mysql.com/bug.php?id=14745 http://bugs.mysql.com/bug.php?id=35355 -- Mark Callaghan mdcallag(a)gmail.com

3 3

[Maria-discuss] Galera 23.2.7 mariadb 5.5.33a msvc 1600 binaries available
by Yann Nayn 21 May '25

21 May '25

Binaries have been uploaded for both x86 and amd64 platforms. due to github file size limitations , the directories have been uploaded unarchived. for Win33 binaries , please checkout https://github.com/YannNayn/galera-mariadb-win32.git and for Win64(amd64) binaries (being uploaded/pushed): https://github.com/YannNayn/galera-mariadb-win64.git they are not recommended for a production environment. in the my.cnf file: _________ wsrep_provider=galera_smm.dll wsrep_sst_method=msqldump # percona xtrabackup will be tested soon _________ libevent and openssl(the provided binaries use respectively 2.1.3-alpha and 1.1.0-dev versions ) will be added next on github some limitations are stated on openssl website copied here for convenience: ____________ from http://www.openssl.org/ ____________ This software package uses strong cryptography, so even if it is created, maintained and distributed from liberal countries in Europe (where it is legal to do this), it falls under certain export/import and/or use restrictions in some other parts of the world. PLEASE REMEMBER THAT EXPORT/IMPORT AND/OR USE OF STRONG CRYPTOGRAPHY SOFTWARE, PROVIDING CRYPTOGRAPHY HOOKS OR EVEN JUST COMMUNICATING TECHNICAL DETAILS ABOUT CRYPTOGRAPHY SOFTWARE IS ILLEGAL IN SOME PARTS OF THE WORLD. SO, WHEN YOU IMPORT THIS PACKAGE TO YOUR COUNTRY, RE-DISTRIBUTE IT FROM THERE OR EVEN JUST EMAIL TECHNICAL SUGGESTIONS OR EVEN SOURCE PATCHES TO THE AUTHOR OR OTHER PEOPLE YOU ARE STRONGLY ADVISED TO PAY CLOSE ATTENTION TO ANY EXPORT/IMPORT AND/OR USE LAWS WHICH APPLY TO YOU. THE AUTHORS OF OPENSSL ARE NOT LIABLE FOR ANY VIOLATIONS YOU MAKE HERE. SO BE CAREFUL, IT IS YOUR RESPONSIBILITY. ________________________________________________ msvc 1600 redistributables SP1 need to be installed/available on the target machine: vcredist_x86.exe : http://www.microsoft.com/en-us/download/details.aspx?id=8328 vcredist_x64.exe : http://www.microsoft.com/en-us/download/details.aspx?id=13523 MSys binaries are provided and msqld.bat should then be used instead of mysqld.exe (e.g. mysld.bat --console --wsrep_cluster_address=gcomm:// ) in order to have a working sh shell(cygwin has been proved to do it as well) Please note that oqgraph is disabled (boost trunk is used , that is 1.55) Comments and suggestions are welcome.

5 4

[Maria-discuss] question about mariadb threadpools and file descriptors
by Rohan M C 31 Dec '13

31 Dec '13

Hi All, We're considering a switch to mariadb (5.5.33a) from mysql (5.5.31) for our production servers. First, some background info about our system 1) ~2000 tables. 3/4th of them are innodb, the rest are myisam 2) All tables are partitioned with ~100 partitions each 3) our largest tables are partitioned (~200 partitions each) and they're all myisam due to space constraints (and other reasons) 4) all our instances are on amazon ec2 on ephemeral store Some of our config parameters open-files-limit 131072 innodb-open-files 16384 table-open-cache 4096 innodb-buffer-pool-size 256M One of the problems we're facing with mysql, and we're hoping to solve with mariadb, is the blowup of the number of open file descriptors when some of our long-running/heavier scripts run. So far we've been able to keep it in control by having our scripts flush at intervals, as well as making individual threads hit different read slaves so as to not open up mulitple copies of myisam fds on a single machine. I do know that the above parameters could be tweaked to set a cap on the number of file descriptors (innodb seems to be a hard limit, so thats under control), but we've set table-open-cache at a high number for performance reasons. Our ideal solution would be to keep the above parameters and not run into a situation where the number of file descriptors grow uncontrollably beyond 70k-80k. It would be preferable to have mariadb close file descriptors once queries are done. We were interested in the mariadb threadpool option as it launches threads dynamically when needed and closes them once it's done. I'm currently testing on a mariadb instance with the default parameters. One of my assumptions with threadpools was that when additional threads were closed, the file descriptors opened for them would also be closed, but upon testing I've seen that this is not the case, and that mariadb behavior as far as threadpools are concerned is exactly the same as mysql. For example, with thread_pool_size=1 and max_threads at 500, if I run 10 identical concurrent queries from different connections on mariadb on a myisam table, it opens up 10 sets of 200 file descriptors. Threadpool_threads spikes up to 10 and drops back down after some time, but all the file descriptors initially opened are still in the open state (2200 fds ). Is this intended behavior for threadpools? I would also be grateful for any tips, reading material or knowledge regarding how mariadb handles file descriptors and if there are any additional parameters that can be tuned to keep the number of file descriptors under control, while not impacting performance too much Thanks in advance. Rohan

1 0

[Maria-discuss] MariaDB 10.0.7 Now Available
by Daniel Bartholomew 27 Dec '13

27 Dec '13

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.7. This is a Beta release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.7 - Release Notes: https://mariadb.com/kb/en/mariadb-1007-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-1007-changelog - Downloads: https://downloads.mariadb.org/mariadb/10.0.7 About MariaDB 10.0: - https://mariadb.com/kb/en/what-is-mariadb-100/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - SkySQL has had several MariaDB focused Webinars recently which are available on an on-demand basis at: - http://www.skysql.com/why-skysql/webinars/ They are also planning several more MariaDB-focused webinars that will be held over the comming months. Details available at the above link. - - New MariaDB Book - - - - - - - - - - - - - - - - - - - - - - - - - A new MariaDB book has been released! Getting Started with MariaDB is a how-to guide for beginners to help them get up to speed quickly with MariaDB, no previous MariaDB or MySQL experience required. It's available from several booksellers in both paperback and DRM-free epub, mobi and pdf formats. See the PacktPub website for details: - http://www.packtpub.com/getting-started-with-mariadb/book - - 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/en/donate/ - http://www.cafepress.com/mariadb We hope you enjoy MariaDB! -- MariaDB: An Enhanced Drop-in Replacement for MySQL 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: Debug files not written by client lib
by AskMonty KB 26 Dec '13

26 Dec '13

Hello, A new question has been asked in "MariaDB Client Library for C" by sruliganor: -------------------------------- Hi, I have a C program that uses mariadbclient.lib. I added this line to generatate a trace file from the lib: mysql_debug("d:t:O,e:\\Temp\\MysqlClient.log"); In addition I set environmant variable MYSQL_DEBUG. However, no debug file was created anywhere. What should I do to see trace files? I tried the client lib 1.0 binary downloaded fron your site, as well as the source code complied in DEBUG. Thanks -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/debug-files-not-written-by-client-lib/

1 0

[Maria-discuss] Change from MySQL to MariaDB - Problem with EVENTS-Table in information_schema
by Frank Röhm 21 Dec '13

21 Dec '13

Hello day before yesterday I updated my MySQL Server 5.5.34 to MariaDB 5.5.34 on my Ubuntu 12.04 Server. All went fine but next day, I noticed that the automatic backup script (automysqlbackup) had errors, I read: mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `EVENTS`': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577) mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577) I searched about it and found the hint to do the command mysql_upgrade (I needed the option --force) and restart the server. This didn't help, no change. When I manually select * from EVENTS from information_schema I get the same error 1577. When I upgraded I made a backup before, so could I just overwrite the existing information_schema with the dump from my backup? Could I destroy something? Thank you. frank

2 8

Re: [Maria-discuss] INSERT IGNORE (and other SQL commands) fail with: Deadlock found when trying to get lock;
by Benoit Panizzon 20 Dec '13

20 Dec '13

Hi > If memory serves me, the first timestamp, will be set > to current universal time. Like db.table.last_changed Ok, I replaced NOW() with a generated timestamp to avoid that NOW() deadlock. sub mysql_now() { my($sec,$min,$hour,$mday,$mon,$year,$wday, $yday, $isdst)=localtime(time); my($result)=sprintf("%4d-%02d-%02d %02d:%02d:%02d",$year+1900,$mon+1, $mday,$hour,$min,$sec); return $result; } [...] my $mysqltimestamp = mysql_now(); my $timestamph = $dbh->prepare("UPDATE user SET lastActive = ". $dbh- >quote($mysqltimestamp) ." WHERE userid = ". $dbh->quote($user) ." and domain = ". $dbh->quote($domain)); $timestamph->execute(); Still in about 1 of 10 updates, this causes that wrenched Deadlock error, causing the email not being processed correctly and in the case the email is not for local but being forwarded, the email is not forwarded to the correct destination. Dec 18 14:12:34 asterix mimedefang-multiplexor[8019]: NOQUEUE: Slave 0 stderr: DBD::mysql::st execute failed: Deadlock found when trying to get lock; try restarting transaction at /etc/mimedefang-filter.mx line 316. Is there any known way to circumvent that Deadlock Problem? Kind regards Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________

3 9

[Maria-discuss] New Question: MySQL 5.6 function
by AskMonty KB 20 Dec '13

20 Dec '13

Hello, A new question has been asked in "Miscellaneous Functions" by ymhuang0808: -------------------------------- Hello! My MariaDB version is 10.0.6. I found it dosen't support some MySQL 5.6 functions, like INET6_ATON(), INET6_NTOA(), IS_IPV4(), etc. Hope new version MaraiDB support those functions. Thank you. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mysql-56-function/

1 0

[Maria-discuss] MariaDB Galera Cluster 5.5.34 Now Available
by Daniel Bartholomew 18 Dec '13

18 Dec '13

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.34. This is a Stable (GA) maintenance (bug fix) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 5.5.34 - Release Notes: https://mariadb.com/kb/en/mariadb-galera-cluster-5534-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-galera-cluster-5534-changelog - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.34 About MariaDB Galera Cluster: - https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - SkySQL has had several MariaDB focused Webinars recently which are available on an on-demand basis at: - http://www.skysql.com/why-skysql/webinars/ They are also planning several more MariaDB-focused webinars that will be held over the comming months. Details available at the above link. - - New MariaDB Book - - - - - - - - - - - - - - - - - - - - - - - - - A new MariaDB book has been released! Getting Started with MariaDB is a how-to guide for beginners to help them get up to speed quickly with MariaDB, no previous MariaDB or MySQL experience required. It's available from several booksellers in both paperback and DRM-free epub, mobi and pdf formats. See the PacktPub website for details: - http://www.packtpub.com/getting-started-with-mariadb/book - - 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/en/donate/ - http://www.cafepress.com/mariadb We hope you enjoy MariaDB! -- MariaDB: An Enhanced Drop-in Replacement for MySQL 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] INSERT IGNORE (and other SQL commands) fail with: Deadlock found when trying to get lock;
by Benoit Panizzon 14 Dec '13

14 Dec '13

Hello We have migrated a mailserver project from MySQL NDBCluster to MariaDB Gallera Cluster because we often had sync problems with NDBCluster. Now we found that various of our tools and also our SOAP API Server do not work anymore because some SQL Statements that used to work on NDBCluster do not work with Gallera anymore. I wonder if we stumbled over bugs or if this is some kind of expected behaviour and if yes, how to work around them. We run the most actual version: 5.5.33a Some examples: We have a skript, that from time to time wants to completely delete some statistics data. We don't know if there is already data in that table, we just want to get rid of it... Just some examples how to reproduce the problem: === DELETE on EMPTY TABLE === MariaDB [maildb]> create table statistics (id int(11) primary key, data varchar(30)); Query OK, 0 rows affected (0.80 sec) MariaDB [maildb]> insert into statistics set id=1,data="FooBar"; Query OK, 1 row affected (0.04 sec) MariaDB [maildb]> delete from statistics; Query OK, 1 row affected (0.26 sec) MariaDB [maildb]> delete from statistics; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Why the heck do we get a severe error when deleting data from an empty table. This causes the perl skipt to die and not continue it's execution. === INSERT IGNORE === We merge information about users from two sources. One source contains the password. One source might contain additional user which were not added from the first source, but do not contain the passwort. But they also contain users already inserted from the first source, so if a unique key exists, it must not be overwroten. Therefore we can not use REPLACE INTO but would use INSERT IGNORE: MariaDB [maildb]> show create table user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(30) DEFAULT NULL, `passwd` varchar(30) DEFAULT NULL, `domain` varchar(30) DEFAULT NULL, `homeserver` varchar(30) DEFAULT NULL, `quotaInMiB` int(11) DEFAULT '100', `password_type` varchar(10) DEFAULT 'plain', `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', `spampoint` decimal(5,1) DEFAULT '5.2', `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', `keepFwdCopy` tinyint(1) DEFAULT '0', `quotaUsed` int(11) DEFAULT '0', `extRef` varchar(12) DEFAULT NULL, `mandate` int(11) DEFAULT '0', `FwdBounceCount` decimal(1,0) DEFAULT '0', `FwdActive` tinyint(1) DEFAULT '0', `lastActive` datetime DEFAULT '0000-00-00 00:00:00', `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', `disabled` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `userid` (`userid`,`domain`) ) ENGINE=InnoDB AUTO_INCREMENT=1808 DEFAULT CHARSET=utf8 First Source: MariaDB [maildb]> insert into user set userid="dummyuser",domain="example.com",passwd="verysecret"; Query OK, 1 row affected (0.00 sec) Second Source: Now we get a user that does not already exist, but for which we do not have a password: MariaDB [maildb]> INSERT IGNORE user set userid="otheruser",domain="example.com",passwd="random1"; Query OK, 1 row affected (0.00 sec) Now that second source provides another user, which already exists in the database, so we do want to ignore that insert: MariaDB [maildb]> INSERT IGNORE user set userid="dummyuser",domain="example.com",passwd="otherrandom1"; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction What happened there? In NDBCluster this works perfectly. Gallera fails, that merge skipt stopps. Google suggested replacing INSERT IGNORE by ON DUPLICATE KEY update id=id to just do a dummy update, but that fails too: MariaDB [maildb]> INSERT user set userid="dummyuser",domain="example.com",passwd="otherrandom1" ON DUPLICATE KEY UPDATE id=id; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Any suggestions? Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________

3 3