- 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] warning 1292
by Federico Razzoli 12 Dec '13

12 Dec '13

Hi! MySQL 5.6 returns a warning for wrong datatypes, with any SQL_MODE: mysql> CREATE TABLE t (c TIMESTAMP) ENGINE=InnoDB; Query OK, 0 rows affected (0,31 sec) mysql> SELECT c FROM t WHERE c = 1; Empty set, 1 warning (0,00 sec) Warning (Code 1292): Incorrect datetime value: '1' for column 'c' at row 1 Will this be done in MariaDB 10 too? Currently, no warning is issued. Federico

3 5

[Maria-discuss] New Question: Simple cluster two nodes, error.
by AskMonty KB 11 Dec '13

11 Dec '13

Hello, A new question has been asked in "MariaDB FAQ" by maximilianodipietro: -------------------------------- Hi people, i have a two nodes cluster of MariaDb, and i need some help, i have this simple script to do a solve failover, the script works like this: if the master node dies, the script kills the slave and restart it as master, and then the master as slave when the script detects that the process is down, here's the code: ---------------------------------------------------------------------------------------------------------------------------------- #!/bin/bash if [ $1 = "start" ] then PidMaria=$(ps -ef | grep wsrep_cluster | grep -v grep | awk {'print $2'}) Server2=$(nmap -v ipnodo2 | grep 3306 | tail -n1 | awk {'print $2'}) TipoPid=$(ssh ipnodo2 "ps -ef |grep mysql | grep -v grep | cut -d "=" -f 2 | cut -d "/" -f 3 | cut -d "-" -f 1 | cut -d "." -f 1") TipoPidLocal=$(ps -ef |grep mysql | grep -v grep | cut -d "=" -f 2 | cut -d "/" -f 3 | cut -d "-" -f 1 | cut -d "." -f 1) if [ -z $PidMaria ] then #sleep 20 Server2=$(nmap -v ipnodo2 | grep 3306 | tail -n1 | awk {'print $2'}) if [ -z $Server2 ] then if [ -z $TipoPid ] then if [ -z $(ssh ipnodo2 "ps -ef |grep mysql | grep -v grep " | awk {'print $2'}) ] then /usr/sbin/mysqld --wsrep_cluster_address=gcomm:// --user=mysql --wsrep_sst_auth=root:root --wsrep_provider=/usr/lib64/galera/libgalera_smm.so fi else /usr/sbin/mysqld --wsrep_cluster_address=gcomm://ipnodo2 --wsrep_sst_auth=root:root --user=mysql --wsrep_provider=/usr/lib64/galera/libgalera_smm.so fi else if [ -z $TipoPid ] then /usr/sbin/mysqld --wsrep_cluster_address=gcomm://ipnodo2 --user=mysql --wsrep_sst_auth=root:root --wsrep_provider=/usr/lib64/galera/libgalera_smm.so fi fi else if [ -z $Server2 ] then if [ -z $TipoPidLocal ] then echo "hola" else kill -9 $PidMaria /usr/sbin/mysqld --wsrep_cluster_address=gcomm:// --wsrep_sst_auth=root:root --user=mysql --wsrep_provider=/usr/lib64/galera/libgalera_smm.so fi else if [ -z $PidMaria ] then /usr/sbin/mysqld --wsrep_cluster_address=gcomm://ipnodo2 --user=mysql --wsrep_sst_auth=root:root --wsrep_provider=/usr/lib64/galera/libgalera_smm.so fi fi fi fi -------------------------------------------------------------------------------------------------------------------------------- This is the error i get in the slave output when i run the script by hand. --------------------------------------------------------------------------------------------------------------------------- + /usr/sbin/mysqld --wsrep_cluster_address=gcomm://IPMASTER --user=mysql --wsrep_sst_auth=root:root --wsrep_provider=/usr/lib64/galera/libgalera_smm.so 131211 20:04:35 [Note] WSREP: Read nil XID from storage engines, skipping position init 131211 20:04:35 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera/libgalera_smm.so' 131211 20:04:35 [Note] WSREP: wsrep_load(): Galera 23.2.7(r157) by Codership Oy <info(a)codership.com> loaded succesfully. 131211 20:04:35 [Note] WSREP: Found saved state: 1503cc31-6281-11e3-abfc-5bf96ca010d8:-1 131211 20:04:35 [Note] WSREP: Reusing existing '/var/lib/mysql//galera.cache'. 131211 20:04:35 [Note] WSREP: Passing config to GCS: base_host = IPLOCALHOST; base_port = 4567; cert.log_conflicts = no; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 16; gcs.fc_master_slave = NO; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = NO; replicator.causal_read_timeout = PT30S; replicator.commit_order = 3 131211 20:04:35 [Note] WSREP: Assign initial position for certification: -1, protocol version: -1 131211 20:04:35 [Note] WSREP: wsrep_sst_grab() 131211 20:04:35 [Note] WSREP: Start replication 131211 20:04:35 [Note] WSREP: Setting initial position to 00000000-0000-0000-0000-000000000000:-1 131211 20:04:35 [Note] WSREP: protonet asio version 0 131211 20:04:35 [Note] WSREP: backend: asio 131211 20:04:35 [Note] WSREP: GMCast version 0 131211 20:04:35 [Note] WSREP: (74e621f3-629f-11e3-a86f-0adb496b3ff6, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567 131211 20:04:35 [Note] WSREP: (74e621f3-629f-11e3-a86f-0adb496b3ff6, 'tcp://0.0.0.0:4567') multicast: , ttl: 1 131211 20:04:35 [Note] WSREP: EVS version 0 131211 20:04:35 [Note] WSREP: PC version 0 131211 20:04:35 [Note] WSREP: gcomm: connecting to group 'my_wsrep_cluster', peer '162.243.62.104:' 131211 20:04:35 [Note] WSREP: declaring 15031b4b-6281-11e3-9ad7-8a59b1f0cba0 stable 131211 20:04:35 [Note] WSREP: view(view_id(NON_PRIM,15031b4b-6281-11e3-9ad7-8a59b1f0cba0,8) memb { 15031b4b-6281-11e3-9ad7-8a59b1f0cba0, 74e621f3-629f-11e3-a86f-0adb496b3ff6, } joined { } left { } partitioned { 18299990-6281-11e3-a268-975810126780, a6d47aea-6281-11e3-b007-16991d53e685, d52af178-6281-11e3-871d-03c1e15f1ac4, }) 131211 20:05:05 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out) at gcomm/src/pc.cpp:connect():139 131211 20:05:05 [ERROR] WSREP: gcs/src/gcs_core.c:gcs_core_open():195: Failed to open backend connection: -110 (Connection timed out) 131211 20:05:05 [ERROR] WSREP: gcs/src/gcs.c:gcs_open():1289: Failed to open channel 'my_wsrep_cluster' at 'gcomm://IPMASTER': -110 (Connection timed out) 131211 20:05:05 [ERROR] WSREP: gcs connect failed: Connection timed out 131211 20:05:05 [ERROR] WSREP: wsrep::connect() failed: 6 131211 20:05:05 [ERROR] Aborting 131211 20:05:05 [Note] WSREP: Service disconnected. 131211 20:05:06 [Note] WSREP: Some threads may fail to exit. 131211 20:05:06 [Note] /usr/sbin/mysqld: Shutdown complete ------------------------------------------------------------------------------------------------ When the script starts the master from the crontab, the PID is already up, and i can access to the mariadb, but i cant write any database, only list the databases. [root@xxx ~]# mysql -uxxx -pxxxx Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 24 Server version: 5.5.33a-MariaDB Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create database TESTEOKILL2; ERROR 1047 (08S01): Unknown command MariaDB [(none)]> Any idea?. Thanks in advance! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/simple-cluster-two-nodes-error/

3 2

[Maria-discuss] INFORMATION_SCHEMA.PROCESSLIST values of PROGRESS, STAGE and STAGE_MAX
by Jean Weisbuch 11 Dec '13

11 Dec '13

Hello, Lately, i did modify mytop (https://mariadb.atlassian.net/browse/MDEV-4476) to use INFORMATION_SCHEMA.PROCESSLIST instead of SHOW PROCESSLIST (when available) to gather running processes informations as it's more flexible and has more informations but the value of the PROGRESS column on both is different, so i wondered if the PROGRESS value on SHOW PROCESSLIST was using a simple formula like this one or something more complex : (STAGE-1)/MAX_STAGE + {decimal progress on the actual stage}/MAX_STAGE There seems to be a broken alinea about that on the KB page of the PROCESSLIST : https://mariadb.com/kb/en/show-processlist/ In any case, what would be the best way to go with mytop, add columns with the actual_stage/max_stage or only keep the progress of the actual stage or only the "total" progress using the formula previously shown? Putting an option and keystroke to show/hide these columns could be another solution as these values might be of use on only "rare" cases depending on the workload. Regards.

3 3

[Maria-discuss] New Question: mariadb run failed on Centos 6.4
by AskMonty KB 09 Dec '13

09 Dec '13

Hello, A new question has been asked in "MariaDB community" by kimbg: -------------------------------- my server installed centos 6.4(32bit) i'll source compile maria db 5.1.67/5.5.34 and yum install. but, i looking error message mysql_install_db. how fixed? maria source version : 5.1.67/5.1.62 5.5.34 (https://downloads.mariadb.org/interstitial/mariadb-5.1.67/kvm-tarbake-jaunt…) compile option [root@localhost var]# ./configure --prefix=/usr/local/mysql --with-extra-charsets=all --with-readline mysql_install_db command [root@localhost ~]# /usr/local/mysql/bin/mysql_install_db Installing MariaDB/MySQL system tables... 131209 11:07:26 [ERROR] /usr/local/mysql/libexec/mysqld: ambiguous option '--o' (old-alter-table, optimizer_switch) 131209 11:07:26 [ERROR] Aborting Installation of system tables failed! Examine the logs in /usr/local/mysql/var for more information. You can try to start the mysqld daemon with: shell> /usr/local/mysql/libexec/mysqld --skip-grant & and use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell> /usr/local/mysql/bin/mysql -u root mysql mysql> show tables Try 'mysqld --help' if you have problems with paths. Using --general-log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about mysql_install_db is available at http://kb.askmonty.org/v/installing-system-tables-mysql_install_db. MariaDB is hosted on launchpad; You can find the latest source and email lists at http://launchpad.net/maria Please check all of the above before mailing us! And remember, if you do mail us, you should use the /usr/local/mysql/bin/mysqlbug script! [root@localhost ~]# /usr/local/mysql/libexec/mysqld --skip-grant & [1] 1402 [root@localhost ~]# 131209 11:20:11 [ERROR] /usr/local/mysql/libexec/mysqld: ambiguous option '--o' (old-alter-table, optimizer_switch) 131209 11:20:11 [ERROR] Aborting [1]+ Exit 1 /usr/local/mysql/libexec/mysqld --skip-grant [root@localhost ~]# cat /usr/local/mysql/var/localhost.localdomain.err 131209 11:08:40 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var 131209 11:08:40 [ERROR] /usr/local/mysql/libexec/mysqld: ambiguous option '--o' (old-alter-table, optimizer_switch) 131209 11:08:40 [ERROR] Aborting 131209 11:08:40 mysqld_safe mysqld from pid file /usr/local/mysql/var/localhost.localdomain.pid ended [root@localhost ~]# -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-run-failed-on-centos-64/

1 0

[Maria-discuss] New Question: CMAKE settings to include TokuDB in 5.5.34
by AskMonty KB 02 Dec '13

02 Dec '13

Hello, A new question has been asked in "TokuDB" by markdiener: -------------------------------- I am trying to build 5.5.34 from source on Linux x64 (Fedora 14). I was looking at the readme files and could not find anything about building 5.5.34 from source and including TokuDB (which has the source code in the /storage/TokuDB/ directory) into the build. I was looking for something like: -DWITH_TOKUDB_STORAGE_ENGINE=ON but did not stumble across anything. Anybody got some ideas? Here is my build script: rm -f CMakeCache.txt make clean cmake \ -DCMAKE_BUILD_TYPE=Release \ -DCMAKE_INSTALL_PREFIX=/mysql \ -DENABLED_PROFILING=OFF \ -DENABLE_DEBUG_SYNC=ON \ -DENABLE_GCOV=OFF \ -DINSTALL_LAYOUT=STANDALONE \ -DMYSQL_DATADIR=/mysql/data \ -DINSTALL_INCLUDEDIR=include/mysql \ -DINSTALL_UNIX_ADDRDIR=/mysql/mysql.sock \ -DUSE_ARIA_FOR_TMP_TABLES=ON \ -DWITHOUT_PBXT_STORAGE_ENGINE=ON \ -DWITHOUT_SERVER=OFF \ -DWITH_ARCHIVE_STORAGE_ENGINE=OFF \ -DWITH_EMBEDDED_SERVER=OFF \ -DWITH_FAST_MUTEXES=OFF \ -DWITH_FEDERATEDX_STORAGE_ENGINE=OFF \ -DWITH_FEEDBACK=OFF \ -DWITH_LIBWRAP=OFF \ -DWITH_PIC=ON \ -DWITH_PARTITION_STORAGE_ENGINE=OFF \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=ON \ -DWITH_SPHINX_STORAGE_ENGINE=OFF \ -DWITH_SSL=OFF \ -DWITH_UNIT_TESTS=OFF \ -DWITH_VALGRIND=OFF \ -DWITH_XTRADB_STORAGE_ENGINE=ON \ -DWITHOUT_OQGRAPH=ON \ -DWITH_ZLIB=bundled make (now compiling your program) -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cmake-settings-to-include-tokudb-in-5534/

1 0

[Maria-discuss] Fw: [Website feedback] galera mariadb compiled using msvc
by Yann Nayn 02 Dec '13

02 Dec '13

On Monday, December 2, 2013 11:46 AM, Yann Nayn <yann.nayn(a)yahoo.fr> wrote: >Yes, we're reading mariadb mailing list ... I realized that while reading mailing lists archives . galera is now running using mysqldump as the wsrep sst backup method I didnt dig the details yet and will compile percona xtrabackup next Im tested the deployment with two nodes(...) and still find bootstrapping "tiedeous". I will go with basic sql manipulations in the next coming hours I just tested create database on one node , the database is visible from the other node . I might provide soon msi installation packages to ease testing https://github.com/YannNayn

1 0

[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