November 2013 - discuss - lists.mariadb.org

[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

[Maria-discuss] Mroonga storage engine is merged into MariaDB 10.0
by kentoku 01 Dec '13

01 Dec '13

Hi! We just pushed Mroonga storage engine into "lp:~mroonga/maria/mroonga". Mroonga is a fulltext/geo search storage engine. Would you please bundle Mroonga into MariaDB? Mroonga's strong points are - High-speed updating with multiple thread searching. (lock-free model) - Support CJK languages. - Support wrapper mode. Wrapper mode works adding fulltext/geo index for other storage engines. Mroonga is tested on the following 64bit OS. Debian GNU/Linux squeeze Debian GNU/Linux wheezy Debian GNU/Linux jessie Debian GNU/Linux sid Ubuntu 10.04 LTS Lucid Lynx Ubuntu 12.04 Precise Pangolin Ubuntu 12.10 Quantal Quetzal Ubuntu 13.04 Raring Ringtail Ubuntu 13.10 Saucy Salamander CentOS 5 CentOS 6 Fedora 19 Mac OS X Windows 7 Mroonga documents (English & Japanease) are available on http://mroonga.org/docs/index.html Test command is the followings. The command calls mysql-test-run.plinternally. > ./storage/mroonga/test/run-sql-test.sh Thanks, Kentoku

6 6

[Maria-discuss] Tr : mariadb galera msvc
by Yann Nayn 30 Nov '13

30 Nov '13

>Le Mercredi 27 novembre 2013 9h33, Colin Charles <byte(a)mariadb.com> a écrit : >does this also include the galera library? or is this just straight mariadb? >Colin Charles, Chief Evangelist >MariaDB | t: +6-012-204-3201 | Skype: colincharles This is not intended to be left undone half the way ... Right now most of galera tests are passing and galera_smm.dll is produced. a more detailed instruction set can be found at: https://github.com/YannNayn/galera-build-msvc/blob/master/build.bat the C89 features have been rewritten Next a pre production deployment is going to be tested Have a Happy ThanksGiving Day when relevant

2 6

[Maria-discuss] New Question: Mariadb refuses to stay confined to install directory
by AskMonty KB 29 Nov '13

29 Nov '13

Hello, A new question has been asked in "Compiling MariaDB From Source" by openiduser33: -------------------------------- I've been struggling with this off and on for weeks. I need a contained MariaDB install running in the home directory. My most recent attempt (and what would be my preferred method) is compiling from source. No matter what I do though MariaDB insists on trying to create /var/run/mysqld/mysqld.sock on every run and fails. I compiled it from source with --prefix pointing to my home directory. I ran mysql with --basedir and --defaults-file. The defaults-file I used contains the location of my mysql.sock file in the home directory but MariaDB will not use it. I even tried recompiling with --exec-prefix as per the MySQL 5.5 documentation but this screws the entire installation up, causing MariaDB to recreate the entire prefix path from root underneath src/efix=/ where I'm storing the source while I install. Here is my configure options: ##./configure --prefix=/home/user/web/servers/mariadb --enable-assembler \ --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables \ --with-plugin-maria --with-aria-tmp-tables --without-plugin-innodb_plugin --without-plugin=tokudb \ --with-mysqld-ldflags=-static --with-client-ldflags=-static --with-readline \ --with-ssl --with-plugins=max-no-ndb --with-embedded-server --with-libevent \ --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static \ --with-zlib-dir=bundled --enable-local-infile## And here is what happens when I try to launch MariaDB: ##$ bin/mysqld_safe --basedir=/home/user/web/servers/mariadb --defaults-file=/home/user/web/servers/mariadb/my.cnf 131128 16:20:20 mysqld_safe Logging to '/home/user/web/servers/mariadb/log/error.log'. mkdir: cannot create directory ‘/var/run/mysqld’: Permission denied Fatal error Can't create database directory '/var/run/mysqld/mysqld.sock'## My socket file is specific in the given my.cnf file as /home/user/web/servers/mariadb/run/mysql.sock No matter what I do I can't get MariaDB to behave and run in user land. What am I doing wrong? I've tried the Linux binaries with slightly more success but I still felt like I was fighting the server to get it to read files from the correct location. I'd like to have it compiled from source though and it *should* be easier this way since I can specify a prefix. How can I get MariaDB to follow the set prefix for all files? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-refuses-to-stay-confined-to-install-direct…

1 0

[Maria-discuss] plans for MDL
by MARK CALLAGHAN 28 Nov '13

28 Nov '13

Do you have any plans for MDL in MariaDB 10+ that might do something better for workloads that need to do some DDL concurrent with some long-running SELECT statements? https://www.facebook.com/MySQLatFacebook/posts/10151939557456696 -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-discuss] New Question: Build fails at "Linking CXX executable tdb-recover"
by AskMonty KB 26 Nov '13

26 Nov '13

Hello, A new question has been asked in "Compiling MariaDB From Source" by openiduser33: -------------------------------- I'm trying to compile MariaDB on Ubuntu 13.10. I've followed all the directions here on the KB in regards to compiling. I meet all the package requirements listed. This is the output where make fails: Linking CXX executable tdb-recover cd /home/chris/web/servers/mariadb/src/mariadb-5.5.34/storage/tokudb/ft-index/ft && /usr/bin/cmake -E cmake_link_script CMakeFiles/tdb-recover.dir/link.txt --verbose=1 /usr/bin/c++ -std=c++11 -Wall -Werror -Wcast-align -Wshadow -Wmissing-format-attribute -Wpointer-arith -Wmissing-declarations -Wno-missing-noreturn -Wextra -Wno-error=strict-overflow -Wpacked -fno-exceptions -fno-rtti -Wno-error=missing-format-attribute -Wmissing-format-attribute -Wlogical-op -Wtrampolines -Wswitch -Winit-self -Wstrict-null-sentinel -Wno-missing-field-initializers -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -fimplicit-templates -flto -fuse-linker-plugin -O2 -g -DNDEBUG -DDBUG_OFF -g -O3 -UNDEBUG -g -fuse-linker-plugin -Wno-error=strict-overflow CMakeFiles/tdb-recover.dir/tdb-recover.cc.o -o tdb-recover -lpthread libft_static.a ../util/libutil_static.a ../../../../zlib/libzlib.a .././xz/lib/liblzma.a ../portability/libtokuportability_static.a -lpthread -ldl -lrt /home/chris/web/servers/mariadb/src/mariadb-5.5.34/storage/tokudb/ft-index/ft/ft-ops.cc: In function ‘ft_leaf_run_gc’: /home/chris/web/servers/mariadb/src/mariadb-5.5.34/storage/tokudb/ft-index/ft/ft-ops.cc:2261:26: error: ‘leaf_entry’ may be used uninitialized in this function [-Werror=maybe-uninitialized] if (leaf_entry->type != LE_MVCC) { ^ lto1: all warnings being treated as errors lto-wrapper: /usr/bin/g++-4.8.real returned 1 exit status /usr/bin/ld.bfd.real: lto-wrapper failed collect2: error: ld returned 1 exit status Pastebin for easier reading: http://pastebin.com/wGzHpD2p I'm using the default compile option list posted here: https://mariadb.com/kb/en/generic-build-instructions/ The only thing I've changed is the prefix. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/build-fails-at-linking-cxx-executable-tdb-recover/

1 0

[Maria-discuss] New Question: Link MariaDB Client Library for C with specific external zlib and openssl
by AskMonty KB 26 Nov '13

26 Nov '13

Hello, A new question has been asked in "MariaDB Client Library for C" by trsystran: -------------------------------- Hi, I am trying to link the mariadb client library with zlib and openssl which are neither embedded in mariadb (for zlib), nor the one available on the system: I want to force cmake to use zlib and openssl from a given path. How can I do that? Is there another way than creating my own FindZLIB.cmake and force it via CMAKE_MODULE_PATH ? Thank you. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/link-mariadb-client-library-for-c-with-specific-ex…

1 0

[Maria-discuss] New Question: SphinxSE gone from MariaDB 10.0.6?
by AskMonty KB 26 Nov '13

26 Nov '13

Hello, A new question has been asked in "Development" by robertmedeksza: -------------------------------- I recently ran apt-get upgrade and this updated my MariaDB 10.0.4 to 10.0.6. After the update the SphinxSE engine is gone and my Sphinx tables no longer function. How can I get SphinxSE back and can this be fixed in 10.0.7? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/sphinxse-gone-from-mariadb-1006/

1 0

[Maria-discuss] mariadb galera msvc
by Yann Nayn 25 Nov '13

25 Nov '13

Hi this is my first post to this list I just setup a github repository with version 5.5.33.a compiling version using msvc 1600 at: https://github.com/YannNayn/mariadb-galera-msvc any tests and suggestions are welcome

2 4

[Maria-discuss] Table Change notification to all Clients?
by SARTrack Admin 21 Nov '13

21 Nov '13

I develop a Windows application for Search and Rescue (using Delphi and MyDAC components), which gets a stream of "APRS" data packets, up to 10 per second. These are saved in the SQL database, but all Clients which are connected to the database MUST be notified when a Table change has occured, so that they then can update their internal tables. Which in turn update their Maps (GPS location of moving items) and other data, which MUST be up-to-date. Currently I am having all Clients polling the database every 5 seconds, which really sucks, and causes all kinds of other problems. Is there any chance that MariaDB will have some kind of Client Notification system when a Table has changed, as some other SQL databases seem to have? Thanks Bart Kindt SARTrack Limited http://www.sartrack.co.nz/ -- SARTrack Developer and CEO

6 5