- discuss - lists.mariadb.org

[Maria-discuss] New Question: Deadlock with foreign key constraints
by AskMonty KB 05 May '16

05 May '16

Hello, A new question has been asked in "MariaDB Community" by sjangra. Please answer it at http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- We are using MariaDB 10.1 and facing some weird deadlock issues. While investigating we ran a sample program to test the foreign keys and found a deadlock error. Can someone please explain this in bit detail so we understand how locking works in galera. We used a 2 node cluster for this testing. Session 1 (Node1): ------------------------ create table dl1(pk int primary key, data varchar(100)); create table dl2(pk int primary key, pk1 int not null, constraint dl2_fk foreign key(pk1) references dl1(pk), data varchar(100)); set autocommit=off; insert into dl1 values(1, 'a'); Session 2 (Node2): ------------------------ set autocommit=off; insert into dl2(pk, pk1, data) values(10, 1, 'a0'); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demoApp`.`dl2`, CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`)) -- as expected select * from dl1; Empty set (0.00 sec) -- as expected Session 1 (Node1): ------------------------ commit; Session 2 (Node2): ------------------------ select * from dl1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction --- strange Once I got this error I ran the 'SHOW ENGINE INNODB STATUS\G' on node2 and here is the truncated output. ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2016-05-04 17:46:34 7ff98ead8b00 Transaction: TRANSACTION 3852, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1 --------------------> (LOOK HERE) MySQL thread id 2507, OS thread handle 0x7ff98ead8b00, query id 73514 localhost root update insert into dl2(pk, pk1, data) values(10, 1, 'a0') Foreign key constraint fails for table `test`.`dl2`: , CONSTRAINT `dl2_fk` FOREIGN KEY (`pk1`) REFERENCES `dl1` (`pk`) Trying to add in child table, in index `dl2_fk` tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000001; asc ;; 1: len 4; hex 8000000a; asc ;; But in parent table `test`.`dl1`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 696e66696d756d00; asc infimum ;; -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 1 RW transactions active inside InnoDB ------------------> (LOOK HERE) 0 RO transactions active inside InnoDB 1 out of 1000 descriptors used Main thread process no. 1242, id 140706194630400, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/deadlock-with-foreign-key-constraints/

1 0

[Maria-discuss] configuration changes to consider in 10.2
by Daniel Black 05 May '16

05 May '16

As major versions are really the only time to consider changing default settings perhaps getting onto the following MDEV soon. https://jira.mariadb.org/browse/MDEV-7635 My main objective is to move the required configuration file down to approximately 0 settings and to work for the majority of people. The sql_mode will cause pain because years of bad practice haven't been fixed, however MySQL-5.7 has gone first and common frameworks are beginning to fix their implementations. They are usually fairly simple fixes to work with the sql_mode of "ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION". Finding/making some documentation to describe how to fix this would be good so the google answer doesn't become set sql_mode=''. Lots of suggestions here model MySQL-5.7 defaults with a few of my additions. Please let the list know which changes you agree with, disagree with (any why).

1 0

[Maria-discuss] New Question: Excessive contexting running MariaDB CE Query
by AskMonty KB 04 May '16

04 May '16

Hello, A new question has been asked in "MariaDB Community" by aferrett. Please answer it at http://mariadb.com/kb/en/excessive-contexting-running-mariadb-ce-query/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm working on a MariaDB vs. Oracle query performance issue. We have about 25 queries that we run on MariaDB 10.1.11 vs. Oracle 11.2.0.4. In all cases Oracle is several times faster than MariaDB. The environments are similar: running RHEL 6.5 on VMware guest. I focused on one representative query to drill down. This query runs in 13 seconds on Oracle and 30 seconds on MariaDB. The access plans look the same using the index with columns in the where clause to access the data. I don't see heavy I/O. The longest running step in the profile is below: | Sending data | 29.728961 | 32.786016 | 1.111831 | 100822 | 67799 | The 100822 is Context_voluntary and the 67799 is context involuntary. A the "cs" field in a vmstat jumps up to around 8,000 as soon as I start the query and goes back to hundreds when it ends: procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 3 89744 74424 103624 6447400 0 0 0 12 2322 9256 56 3 38 4 0 << query running 0 0 89744 74424 103636 6447404 0 0 0 0 152 241 1 0 91 9 0 << query ends My assessment is that the problem is excessive context switches. I tried increasing thread_pool_oversubscribe from 3 to 10, thread_pool_size from 2 to 10, and thread_pool_stall_limit to 1,000 each parameter one at a time and bouncing the instance. In all cases the query runs at a consistent 27-29 seconds. We are also doing heavy parallel insert testing. When we run the job, vmstat cs jumps to the same 8,000 or so. Our Linux SA cannot see any context related configuration differences between the Oracle and MariaDB servers. Any ideas will be appreciated. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/excessive-contexting-running-mariadb-ce-query/

1 0

[Maria-discuss] ALGORITHM INPLACE for 10.0.24-MariaDB
by Rhys.Campbell＠swisscom.com 04 May '16

04 May '16

Hi All, For the following statement... ALTER ONLINE TABLE dom_audit_event ENGINE=INNODB, ALGORITHM=INPLACE; I can still see that MariaDB is still using the value of tmpdir. We have this set to tmpfs on some of our servers and this causes some ALTER statements on big tables to fail. It was my understanding that if you specified INPLACE then the statement would fail if this was not possible. Am I missing something? Cheers, Rhys

3 3

[Maria-discuss] MariaDB Galera Cluster 10.0.25 now available
by Daniel Bartholomew 03 May '16

03 May '16

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 10.0.25. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 10.0.25 - Release Notes: https://mariadb.com/kb/en/mgc-10025-rn/ - Changelog: https://mariadb.com/kb/en/mgc-10025-cl/ - Downloads: https://downloads.mariadb.org/mariadb-galera/10.0.25 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 - - - - - - - - - - - - - - - - - - - - - - - - - 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: Can't connect to MariaDB through mysql command line client
by AskMonty KB 02 May '16

02 May '16

Hello, A new question has been asked in "MariaDB Community" by chuck_lyon_g. Please answer it at http://mariadb.com/kb/en/cant-connect-to-mariadb-through-mysql-command-line… as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have installed the xampp stack which now includes MariaDB. I have been connecting to it with phpMyAdmin and php scripts through Apache as localhost. This all works with no problem. Today I tried to use the mysql command line tool and got the following error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory") I checked, and indeed that path does not exist. I did, however find the following path: /opt/lampp/var/mysql/mysql.soc Is it possible that the xampp stack installs the database files in a non-standard location and I need to tell mysql about the change? If so, how? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cant-connect-to-mariadb-through-mysql-command-line…

1 0

[Maria-discuss] New Question: JDBC driver configuration error
by AskMonty KB 02 May '16

02 May '16

Hello, A new question has been asked in "MariaDB Community" by Intrinsic. Please answer it at http://mariadb.com/kb/en/jdbc-driver-configuration-error/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I am attempting to connect to MariaDB from a command line Java program. I am getting the "no suitable driver found" error. I am using the mariaDB Type 4 JDBC driver, the JDK 1.8, Windows 10. I have added the maria driver to the lib directory of the JRE, and added this directory to the PATH and CLASSPATH environment variables. I can connect to the mariaDB database using DBVisualizer, when I manually point the configuration of DBVisualizer to the maria driver, so I know that the driver works fine. Here is the Java code: Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword"); and the error C:\Users\Documents\VTK\Dev>java -cp . VTK3 java.sql.SQLException: No suitable driver found for jdbc:mariadb://localhost:3306/DB?user=root&password=myPassword at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at VTK3.main(VTK3.java:38) Thanks -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/jdbc-driver-configuration-error/

2 1

[Maria-discuss] MariaDB 10.1.13 sysbench and Fusion-IO PCIE card lock question ?
by Ljr Yang 02 May '16

02 May '16

Hi, All Recently, We use Sysbench Test MariaDB 10.1.13. In Test ,We found server lock and can't write . Testing environment: server1: 10.2.1.242 (HP OEM Fusion IO PCIE Card 3.2TB ) server2: 10.2.1.239 (ScanDisk Fusion IO PCIE Card 3.2TB) sysbench test script: sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --num-threads=16 --mysql-host=localhost --mysql-port=3306 --mysql-socket=/data/mysql2/data/mysql.sock --mysql-user=root --mysql-password=123456 --mysql-db=test --max-time=0 --max-requests=0 --report-interval=1 --oltp-tables-count=16 run when *wsrep_on = on *, after run a few minutes or a few hours, both server1 and server2 were stucked, all write stoped ,only select statement run normal : ................ [ 815s] threads: 16, tps: 6176.00, reads: 86431.00, writes: 24699.00, response time: 3.37ms (95%), errors: 0.00, reconnects: 0.00 [ 816s] threads: 16, tps: 6064.99, reads: 84907.86, writes: 24256.96, response time: 3.66ms (95%), errors: 0.00, reconnects: 0.00 [ 817s] threads: 16, tps: 6097.00, reads: 85394.05, writes: 24394.02, response time: 3.54ms (95%), errors: 0.00, reconnects: 0.00 [ 818s] threads: 16, tps: 6131.97, reads: 85819.52, writes: 24529.86, response time: 3.65ms (95%), errors: 0.00, reconnects: 0.00 [ 819s] threads: 16, tps: 6143.04, reads: 86036.63, writes: 24575.18, response time: 3.44ms (95%), errors: 0.00, reconnects: 0.00 [ 820s] threads: 16, tps: 5819.80, reads: 81532.22, writes: 23332.20, response time: 3.60ms (95%), errors: 0.00, reconnects: 0.00 [ 821s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 822s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 823s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 824s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 825s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 [ 826s] threads: 16, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects: 0.00 ................ In error log file not error info , after execute* pstack `pidof mysqld` > pstack.log*, sysbench run normal again , and start write data. Every time have this problem, in pstack.log file contains a few lock: #0 0x00007f2b04fe0334 in __lll_lock_wait () from /lib64/libpthread.so.0 #1 0x00007f2b04fdb5d8 in _L_lock_854 () from /lib64/libpthread.so.0 #2 0x00007f2b04fdb4a7 in pthread_mutex_lock () from /lib64/libpthread.so.0 .................... When *set wsrep_on = off*, Sysbench test MariaDB 10.1.13 normal. As contrast ,We download Percona-XtraDB-Cluster-5.6.28-rel76.1-25.14.1.Linux.x86_64.ssl101.tar.gz and run sysbench test, found same problem too. We can't confirm , is it hardware or galera question ? please help analyze ,thank you. Reference link: https://en.wikibooks.org/wiki/Linux_Applications_Debugging_Techniques/Deadl… http://www.linuxprogrammingblog.com/threads-and-fork-think-twice-before-usi… -- 杨建军

2 1

[Maria-discuss] MariaDB 10.0.25 now available
by Daniel Bartholomew 30 Apr '16

30 Apr '16

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.25. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 10.0.25 - Release Notes: https://mariadb.com/kb/en/mdb-10025-rn/ - Changelog: https://mariadb.com/kb/en/mdb-10025-cl/ - Downloads: https://downloads.mariadb.org/mariadb/10.0.25 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 - - - - - - - - - - - - - - - - - - - - - - - - - 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] ERROR 1242 (21000): Subquery returns more than 1 row
by Tristan AURIOL 29 Apr '16

29 Apr '16

Hi everyone, I have a big problem with my application. My application execute this request : INSERT INTO `mydb`.`service_request` ( `id_z`, `id_app`, `request_time`, `priority` ) ( SELECT sc.`id_z`, sc.`id_app`, NOW(), 0 FROM `mydb`.`service_connection` sc JOIN `mydb`.`service` s ON ( s.`id_service` = sc.`id_service` ) WHERE 1=1 AND sc.`id_app` = _utf8'afr' AND s.`status` = 'available' AND ( sc.`last_synchronized_time` < SUBDATE( NOW(), INTERVAL 600 SECOND ) OR sc.`last_synchronized_time` IS NULL ) AND sc.`nb_errors` < 2 AND sc.`id_z` NOT IN ( SELECT sr.`id_z` FROM `mydb`.`service_request` sr WHERE sr.`id_app` = _utf8'afr' AND sr.`status` IN ( 'waiting thu', 'waiting sez', 'in progress' ) ) ) ; This request works well into MySQL 5.6.25-1~dotdeb+7.1 but when I run into 10.1.11-MariaDB-1~jessie-log I have this error: ERROR 1242 (21000): Subquery returns more than 1 row Do you have an idea? Thanks -- *Tristan AURIOL*

3 2