- discuss - lists.mariadb.org

[Maria-discuss] New Question: Trigger error
by AskMonty KB 07 May '16

07 May '16

Hello, A new question has been asked in "Triggers" by shadowfoxx_49588. Please answer it at http://mariadb.com/kb/en/trigger-error/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, i'm trying to create check trigger between two columns. But get query error. Structure of columns is: ,,`start_t` (time) , `end_t` (time),, The goal is hours in `end_t` don't be same with `start_t` (e.g. 11:00 > 11:00) Here is query code: ,,CREATE TRIGGER `check_times` BEFORE INSERT ON `schedule` FOR EACH ROW BEGIN DECLARE v_end_time time; SELECT end_t INTO v_end_time FROM `schedule` WHERE fn_stud = NEW.fn_stud; IF NEW.start_t > v_end_time THEN SET NEW.fn_stud = NULL; END IF; END;,, MySQL says: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 Don't understand what is wrong. Can anyone explain? Thanks. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/trigger-error/

1 0

[Maria-discuss] New Question: Which OS to use for MariaDB?
by AskMonty KB 07 May '16

07 May '16

Hello, A new question has been asked in "General FAQ" by dbamaria. Please answer it at http://mariadb.com/kb/en/which-os-to-use-for-mariadb/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, What is the best OS environnement for MariaDB database ?(in term of varity of features, stability, performance..) I've seen that ,for instance, the Galera solution couldn't be installed under Windows..Are there any kind of such limitations and constraints to install it in Linux or Windows platform? Regards -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/which-os-to-use-for-mariadb/

1 0

[Maria-discuss] SST with 10.1.13 + xtrabackup-v2 is failing
by Erik Mathis 06 May '16

06 May '16

I cant seem to get past this problem when using xtrabackup-v2 to preform an SST. Everything works fine when I use rsync method. This is a new cluster. I know the sst user/password is working which I confirmed. 2016-05-06 14:34:31 140258805017344 [Note] WSREP: State transfer required: Group state: b5013090-12e9-11e6-ba99-bf6bb7612fa3:8 Local state: 00000000-0000-0000-0000-000000000000:-1 2016-05-06 14:34:31 140258805017344 [Note] WSREP: New cluster view: global state: b5013090-12e9-11e6-ba99-bf6bb7612fa3:8, view# 43: Primary, number of nodes: 3, my index: 0, protocol version 3 2016-05-06 14:34:31 140258805017344 [Warning] WSREP: Gap in state sequence. Need state transfer. 2016-05-06 14:34:31 140258796631808 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.1.5.42' --datadir '/var/lib/mysql/' --parent '28455' --binlog 'mysql-bin' ' WSREP_SST: [INFO] Streaming with xbstream (20160506 14:34:32.597) WSREP_SST: [INFO] Using socat as streamer (20160506 14:34:32.605) WSREP_SST: [INFO] Evaluating timeout -s9 100 socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160506 14:34:32.740) 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Prepared SST request: xtrabackup-v2|10.1.5.42:4444/xtrabackup_sst//1 2016-05-06 14:34:32 140258805017344 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 2016-05-06 14:34:32 140258805017344 [Note] WSREP: REPL Protocols: 7 (3, 2) 2016-05-06 14:34:32 140258905679616 [Note] WSREP: Service thread queue flushed. 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Assign initial position for certification: 8, protocol version: 3 2016-05-06 14:34:32 140258905679616 [Note] WSREP: Service thread queue flushed. 2016-05-06 14:34:32 140258805017344 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (b5013090-12e9-11e6-ba99-bf6bb7612fa3): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. 2016-05-06 14:34:32 140258832279296 [Note] WSREP: Member 0.0 () requested state transfer from '*any*'. Selected 1.0 ()(SYNCED) as donor. 2016-05-06 14:34:32 140258832279296 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 8) 2016-05-06 14:34:32 140258805017344 [Note] WSREP: Requesting state transfer: success, donor: 1 WSREP_SST: [INFO] Proceeding with SST (20160506 14:34:33.153) WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20160506 14:34:33.152) WSREP_SST: [INFO] Cleaning the existing datadir and innodb-data/log directories (20160506 14:34:33.163) removed `/var/lib/mysql/ib_logfile0' removed `/var/lib/mysql/aria_log_control' removed `/var/lib/mysql/aria_log.00000001' removed `/var/lib/mysql/mysql-bin.index' removed `/var/lib/mysql/ib_logfile1' removed `/var/lib/mysql/ibdata1' removed `/var/lib/mysql/mysql.sock' removed `/var/lib/mysql/mysql-bin.state' removed `/var/lib/mysql/mysql-bin.000001' WSREP_SST: [INFO] Waiting for SST streaming to complete! (20160506 14:34:33.252) 2016-05-06 14:34:34 140258842769152 [Note] WSREP: (a4dbb726, 'tcp://0.0.0.0:4567') turning message relay requesting off 2016-05-06 14:34:44 140258832279296 [Note] WSREP: 1.0 (): State transfer to 0.0 () complete. 2016-05-06 14:34:44 140258832279296 [Note] WSREP: Member 1.0 () synced with group. ls: cannot access mysql-bin.[0-9]*: No such file or directory WSREP_SST: [INFO] Preparing the backup at /var/lib/mysql//.sst (20160506 14:34:45.012) WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --apply-log $rebuildcmd ${DATA} &>${DATA}/innobackup.prepare.log (20160506 14:34:45.021) rm: cannot remove `/var/lib/mysql//innobackup.prepare.log': No such file or directory rm: cannot remove `/var/lib/mysql//innobackup.move.log': No such file or directory WSREP_SST: [INFO] Moving the backup to /var/lib/mysql/ (20160506 14:34:50.126) WSREP_SST: [INFO] Evaluating innobackupex --no-version-check --move-back --force-non-empty-directories ${DATA} &>${DATA}/innobackup.move.log (20160506 14:34:50.135) WSREP_SST: [ERROR] Cleanup after exit with status:2 (20160506 14:34:50.167) 2016-05-06 14:34:50 140258796631808 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.1.5.42' --datadir '/var/lib/mysql/' --parent '28455' --binlog 'mysql-bin' : 2 (No such file or directory) 2016-05-06 14:34:50 140258796631808 [ERROR] WSREP: Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script. 2016-05-06 14:34:50 140260071184416 [ERROR] WSREP: SST failed: 2 (No such file or directory) 2016-05-06 14:34:50 140260071184416 [ERROR] Aborting Error in my_thread_global_end(): 1 threads didn't exit The Joiner innobackup.move.log has this error innobackupex version 2.4.2 based on MySQL server 5.7.11 Linux (x86_64) (revision id: 8e86a84) Error: Move file ib_logfile0 to ./ib_logfile0 failed: Destination file exists 14:34:50 UTC - xtrabackup got signal 11 ; This could be because you hit a bug or data is corrupted. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x10000 innobackupex(my_print_stacktrace+0x35)[0xedd1a5] innobackupex(handle_fatal_signal+0x273)[0xa3fce3] /lib64/libpthread.so.0(+0xf7e0)[0x7f5527d2b7e0] /lib64/libc.so.6(cfree+0x1c)[0x7f552618593c] innobackupex(_Z9copy_backv+0x377)[0x74f437] innobackupex(main+0xc0f)[0x73e00f] /lib64/libc.so.6(__libc_start_main+0xfd)[0x7f5526128d5d] innobackupex[0x72db39]

2 2

[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