July 2016 - discuss - lists.mariadb.org

[Maria-discuss] Finicky syntax with RETURNING?
by Rhys.Campbell＠swisscom.com 27 Jul '16

27 Jul '16

Hello All, I'm having a bit of an issue with the RETURNING feature of DELETE<https://mariadb.com/kb/en/mariadb/delete/>. This was introduced in MariaDB 10.0.5. Specifically there seems to be an issue when using aliases with the RETURNING keyword. It involves a query like this. A user came to me with queries of this form that were running slowly. Here is the explain plan... EXPLAIN DELETE from t1 where id in (select id from t1 where acc_id = 9999) \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4675 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: unique_subquery possible_keys: XXXXXXXXXX key: PRIMARY key_len: 4 ref: func rows: 1 Extra: Using where 2 rows in set (0.00 sec) You can see it's being forced to run a table scan and run the subquery for each row. This was fixed by properly aliasing the query... EXPLAIN EXTENDED DELETE uc from t1 AS uc where uc. id in (select p. id from t2 p where p.acc_id = 9999) \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: p type: ref possible_keys: XXXXXXXXXXXXXXXXXXXXXXXX key: uk_XXXXXXXXXXXXXXXXXXXX key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: PRIMARY table: uc type: ref possible_keys: XXXXXXXXXXXXXXXXXXX key: XXXXXXXXXXXXXX key_len: 4 ref: XXXXXXXXXXXXXX rows: 1 filtered: 100.00 Extra: 2 rows in set (0.00 sec) There now seems to be an issue when attempting to use the RETURNING keyword with this aliases version of the query. I have tried numerous versions of this query... EXPLAIN DELETE uc FROM t1 AS uc WHERE uc.id IN (SELECT p.id FROM t2 AS p WHERE p.account_id = 9999) RETURNING uc.some_id; EXPLAIN DELETE uc FROM t1 uc INNER JOIN t2 p ON uc.id = p.id WHERE p.acc_id = 9999 RETURNING some_id; All of these produce a SQL syntax error. I've tried a range of ways of writing this, using different joins, subqueries, with and without the AS keyword. The issues seems to be with aliases. The only version I can seem to get to work is this (note the removal of the aliases)... EXPLAIN DELETE FROM t1 WHERE id IN (SELECT id FROM t2 WHERE acc_id = 9999) RETURNING some_id; But then that creates the slow table scan problem again. Anyone have any experience of this? Cheers, Rhys

2 3

[Maria-discuss] New Question: Maria DB and Pro*C
by AskMonty KB 25 Jul '16

25 Jul '16

Hello, A new question has been asked in "MariaDB Connector/C" by Koyeli Roy. Please answer it at http://mariadb.com/kb/en/maria-db-and-proc/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, Is there any plugin/code convertor available to perform DB operations on maria DB from Pro*C code? I have a huge application written using C and Pro*C and I want to migrate to maria DB without changing the Pro*C layer or making very few changes in it. Thanks, Koyeli -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/maria-db-and-proc/

1 0

[Maria-discuss] New Question: Cobol and Maria DB
by AskMonty KB 25 Jul '16

25 Jul '16

Hello, A new question has been asked in "MariaDB Connector/C" by Koyeli Roy. Please answer it at http://mariadb.com/kb/en/cobol-and-maria-db/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi, Can I write a routine using C connector provided by Maria DB and call that routine from Cobol code? Will it work? If not , what do I need to do perform database operations on Maria DB from Cobol code. Thanks, Koyeli -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/cobol-and-maria-db/

1 0

[Maria-discuss] New Question: MONyog server configuration via command line
by AskMonty KB 25 Jul '16

25 Jul '16

Hello, A new question has been asked in "MariaDB FAQ" by palash.harchandani93_43595. Please answer it at http://mariadb.com/kb/en/monyog-server-configuration-via-command-line/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- Is it possible to add new servers in monyog via shell commands?? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/monyog-server-configuration-via-command-line/

1 0

[Maria-discuss] New Question: MONyog Audit Log Monitoring
by AskMonty KB 22 Jul '16

22 Jul '16

Hello, A new question has been asked in "MariaDB FAQ" by palash.harchandani93_43595. Please answer it at http://mariadb.com/kb/en/monyog-audit-log-monitoring/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- MariaDB has a plugin server_audit which records all the events happening on the server. Does MONyog monitor these audit log files?? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/monyog-audit-log-monitoring/

1 0

[Maria-discuss] New Question: InnoDB/XtraDB Configuration for optimum performance (MySQL 5.1 to MariaDB 10.1)
by AskMonty KB 22 Jul '16

22 Jul '16

Hello, A new question has been asked in "Moving to MariaDB" by rajeev_joshi_g. Please answer it at http://mariadb.com/kb/en/innodbxtradb-configuration-for-optimum-performance… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, we were using MySQL 5.1 with MyISAM till now and now moving to MariaDB10.1. Instead of taking a Hop of MariaDB5.5, we will be removing MySQL after taking backup of data and then install MariaDB 10.1 & import the Data dump. We will also be changing our engine to InnoDB/XtrDB. May I know what are the configuration we need to take care of with regard to InnoDB/XTraDB for better/faster responses. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/innodbxtradb-configuration-for-optimum-performance…

2 1

[Maria-discuss] New Question: Upgrade (10.1.16+maria-1~jessie) broke system
by AskMonty KB 22 Jul '16

22 Jul '16

Hello, A new question has been asked in "Getting, Installing, and Upgrading MariaDB" by olliblum_59359. Please answer it at http://mariadb.com/kb/en/upgrade-10116maria-1jessie-broke-system/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- sudo apt-get -f install ....... Setting up mariadb-server-10.1 (10.1.16+maria-1~jessie) ... Job for mariadb.service failed. See 'systemctl status mariadb.service' and 'journalctl -xn' for details. invoke-rc.d: initscript mysql, action "start" failed. dpkg: error processing package mariadb-server-10.1 (--configure): subprocess installed post-installation script returned error exit status 1 dpkg: dependency problems prevent configuration of mariadb-server: mariadb-server depends on mariadb-server-10.1 (= 10.1.16+maria-1~jessie); however: Package mariadb-server-10.1 is not configured yet. dpkg: error processing package mariadb-server (--configure): dependency problems - leaving unconfigured Errors were encountered while processing: mariadb-server-10.1 mariadb-server E: Sub-process /usr/bin/dpkg returned an error code (1) ------------- ystemctl status mariadb.service mariadb.service - MariaDB database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: failed (Result: exit-code) since Thu 2016-07-21 22:46:21 UTC; 2min 1s ago Process: 9595 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=227/NO_NEW_PRIVILEGES) Process: 9592 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 9433 ExecStartPre=/bin/sh -c VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS) Process: 9429 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Main PID: 9595 (code=exited, status=227/NO_NEW_PRIVILEGES) Jul 21 22:46:21 systemd[1]: mariadb.service: main process exited, code=exited, status=227/NO_NEW_PRIVILEGES Jul 21 22:46:21 systemd[1]: Failed to start MariaDB database server. Jul 21 22:46:21 systemd[1]: Unit mariadb.service entered failed state. ------- syslog Jul 21 22:49:15 systemd[1]: mariadb.service: main process exited, code=exited, status=227/NO_NEW_PRIVILEGES Jul 21 22:49:15 systemd[1]: Failed to start MariaDB database server. Jul 21 22:49:15 systemd[1]: Unit mariadb.service entered failed state. ------- I have tried so far apt-get clean system restart dpkg --configure -a dpkg --configure -f apt-get is blocked now as it always tries to configure mariadb and then fail Any ideas? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/upgrade-10116maria-1jessie-broke-system/

1 0

[Maria-discuss] New Question: data base issue
by AskMonty KB 21 Jul '16

21 Jul '16

Hello, A new question has been asked in "MariaDB Community" by annamae_moreno_g. Please answer it at http://mariadb.com/kb/en/data-base-issue/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- hello im using xampp because im doing some web programming with php and i also using mariadb with data base . my question how can i use mariadb without using the port of mysql in xampp.. what program do i have to download so i can use this 2 products. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/data-base-issue/

1 0

[Maria-discuss] MariaDB 10.1.14 failure to initiate SST after RSU schema upgrade
by Mark Wadham 21 Jul '16

21 Jul '16

Hi, We have a repeatable failure to initiate IST with MariaDB 10.1.14 after performing a schema upgrade on a single node in RSU mode. The error condition is when there is a delete query in the format: delete from <table> where id >= <n> on the non-RSU cluster nodes while the node is disconnected from the cluster. On rejoining the node determines that it is in sync with the other cluster nodes and no IST is performed, despite the rows that were deleted in the cluster. If we then delete the rows manually from the joining node, mysqld immediately crashes on the other nodes because they can't execute the new write transaction. The process we followed is: 1. Set up a 3-node cluster, nodes 0,1,2 2. Enable RSU on node 0: SET GLOBAL wsrep_OSU_method='RSU'; 3. Isolate node 0 from the cluster: SET GLOBAL wsrep_cluster_address="gcomm://"; 4. Perform a backward-compatible schema change, since this is the point of this process. In our test we added a single column to a table with a default value of null. Additionally we deleted some rows from a table on nodes 1 and 2, with: delete from <table> where id >= 100; which affected around 20 rows. 5. Rejoin the node to the cluster: SET GLOBAL wsrep_cluster_address="<gcomm string from config file>"; At this point the node immediately rejoins without doing IST and believes it is in sync, yet the rows are deleted on nodes 1 and 2 but not node 0. Interestingly if the delete query is: delete from <table> where id = <n>; there is no problem. Also we have not had any issue with syncing INSERT and UPDATE statements. A combination of INSERT, UPDATE and DELETE where id >= resulted in the insert/update statements being synced but the deletes not synced. It is as if the quorum somehow doesn't recognise delete where id >= as an event. Our next test cases are: 1. Switching node 0 back to TOI mode before rejoining the cluster, although I can't really see how this would make a difference. 2. Upgrading to MariaDB 10.1.16 which was released a couple of days ago. 3. Testing whether regular IST is affected, ie IST that should occur normally without switching to RSU mode or dropping a node out of the cluster. This seems like a pretty basic failure and I'm concerned that it may also affect regular IST, i.e. a node falling behind the cluster for normal reasons without any involvement of RSU mode, which would effectively make the whole system useless if it could randomly drop delete statements. If anyone can shed any light on why this may be happening we would be very grateful! Thanks, Mark

2 6

[Maria-discuss] Creating Custom Storage Engine
by Jonathan Ellithorpe 21 Jul '16

21 Jul '16

Hi All, I searched around but couldn't find documentation / tutorials on creating a custom storage backend for MariaDB. Could anyone point me in the right direction? Thanks, Jonathan

5 11