- discuss - lists.mariadb.org

[Maria-discuss] Semi-sync replication hangs when changing binlog filename.
by Joseph Glanville 29 Jul '16

29 Jul '16

Hi, I am using semi-sync replication with the `rpl_semi_sync_master_timeout` set to a very high value to ensure commits aren't committed on the master unless they have been replicated to the slave. However whenever the binlog filename changes the replication stalls indefinitely. A trace of this interaction between the master and the semi-sync slave is here: https://gist.github.com/josephglanville/e87ae96b9be9125427b58cd3a43d5c1e The semi-sync settings I have configured on the master are: rpl_semi_sync_master_wait_point = "AFTER_SYNC" rpl_semi_sync_master_timeout = 18446744073709551615 # effectively infinite as we don't want to fallback to async repl rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_wait_no_slave = 1 # don't allow writes if slave isn't connected Up until the binlog filename change everything works as expected. Anyone have any ideas on how to fix the problem or is this a bug in semi-sync replication? Joseph.

3 3

[Maria-discuss] Orphaned TokuDB tables
by Reinis Rozitis 29 Jul '16

29 Jul '16

Hello, tldr - Is there a way (similarly as with the InnoDB workaround of DROP TABLE `#mysql50##sql-..') to delete an orphaned TokuDB table (and/or clean the information_schema.tokudb_file_map)? Or should I address this to Percona/Toku people? The long version: I have noticed on one db instance (currently running 10.0.26) and orphaned table (no idea how it got there): ls -la /data/mysql/db/ -rw-r----- 1 mysql mysql 1114 Jul 29 01:41 #sql-6f2d_9.frm -rw-r----- 1 mysql mysql 908 Jul 29 01:41 #sql-6f2d_9.par Also: MariaDB [(none)]> SELECT * FROM information_schema.tokudb_file_map limit 10; +--------------------------------+-----------------------------------------------------+--------------+------------+-----------------------+ | dictionary_name | internal_file_name | table_schema | table_name | table_dictionary_name | +--------------------------------+-----------------------------------------------------+--------------+------------+-----------------------+ | ./db/#sql-6f2d_9#P#p1-key-aid | ./_db_sql_6f2d_9_P_p1_key_aid_1734_2_1b_B_0.tokudb | db | #sql | 6f2d_9#P#p1-key-aid | | ./db/#sql-6f2d_9#P#p1-main | ./_db_sql_6f2d_9_P_p1_main_1734_2_1b_B_1.tokudb | db | #sql | 6f2d_9#P#p1-main | | ./db/#sql-6f2d_9#P#p1-status | ./_db_sql_6f2d_9_P_p1_status_1609_1_1b.tokudb | db | #sql | 6f2d_9#P#p1-status | .... While I can DROP the table: MariaDB [db]> drop table `#mysql50##sql-6f2d_9`; Query OK, 0 rows affected, 1 warning (3.99 sec) And even physically delete the ./_db_sql_6f2d_9_P* files from the root datadir it never disappears from the information_schema tables. It doesn't seem to currently affect anything but I wanted to know if maybe there is a workaround or other ways to rebuild to Toku directory (without dumping/reimporting the whole database). rr

2 2

[Maria-discuss] Crash when adding a column to a very large table
by Mark Wadham 27 Jul '16

27 Jul '16

Hi, MariaDB 10.1.16 with a 3-node galera cluster. We had 187GB free disk space and had the gcache set to 800GB. We started firing write traffic at the cluster and then began testing the process of performing DDL on a single node in RSU mode. Our command was a single ALTER TABLE statement that added a column to a ~160GB table. While this was occurring we were firing around 20GB/hour of write traffic at the cluster which was being collected in the gcache for catchup after the node completed the DDL. At around 3h 45min into the DDL process the node performing it crashed with an error indicating it was unable to write to a temporary ibdata file. There was still plenty of disk space available. Full crash log is below, if anyone can shed light on this we'd be very grateful. My own research so far suggests that the problem is because the ALTER TABLE statement inserts all of the rows into the newly rebuilt table at once, effectively requiring a rollback to be created for every row in the table and this is just too much for *some limit* that I haven't worked out yet. I saw a suggestion[1] to increase the number of ibdata files but this doesn't really make sense to me given that we have two and the second one is set to autoextend. It's a very slow debug process so I'd like to get a reasonable idea of what happened here before starting another test run. Thanks, Mark [1] http://dba.stackexchange.com/questions/68319/mysql-index-creation-failing-o… -------------------- 2016-07-27 8:45:59 140258863733504 [Note] InnoDB: Online DDL : Start 2016-07-27 8:45:59 140258863733504 [Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files 2016-07-27 9:42:23 140258863733504 [Note] InnoDB: Online DDL : End of reading clustered index of the table and create temporary files 2016-07-27 9:42:23 140258863733504 [Note] InnoDB: Online DDL : Start merge-sorting index `PRIMARY` (1 / 4), estimated cost : 14.7886 2016-07-27 9:42:23 140258863733504 [Note] InnoDB: Online DDL : merge-sorting has estimated 72714 runs 2016-07-27 11:47:05 140258863733504 [Note] InnoDB: Online DDL : End of merge-sorting index `PRIMARY` (1 / 4) 2016-07-27 11:47:05 140258863733504 [Note] InnoDB: Online DDL : Start building index `PRIMARY` (1 / 4), estimated cost : 22.1242 2016-07-27 12:26:52 7f908fbfdb00 InnoDB: Error: Write to file ./db/#sql-ib16588-3044794026.ibd failed at offset 54409560064. InnoDB: 1048576 bytes should have been written, only 991232 were written. InnoDB: Operating system error n2016-07-27 12:26:52 140258863733504 [Note] InnoDB: Online DDL : Completed 2016-07-27 12:26:53 7f908fbfdb00 InnoDB: Assertion failure in thread 140258863733504 in file handler0alter.cc line 137 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 160727 12:26:53 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.1.16-MariaDB key_buffer_size=33554432 read_buffer_size=16777216 max_used_connections=4 max_threads=770 thread_count=11 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 37895560 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x7ebbabfb9008 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 = 0x7f908fbfd180 thread_stack 0x48400 (my_addr_resolve failure: fork) /usr/sbin/mysqld(my_print_stacktrace+0x2b) [0x555e557caaab] /usr/sbin/mysqld(handle_fatal_signal+0x475) [0x555e55327ef5] /lib64/libpthread.so.0(+0xf100) [0x7f90982ce100] /lib64/libc.so.6(gsignal+0x37) [0x7f909663b5f7] /lib64/libc.so.6(abort+0x148) [0x7f909663cce8] /usr/sbin/mysqld(+0x734ff9) [0x555e55491ff9] /usr/sbin/mysqld(+0x736eec) [0x555e55493eec] /usr/sbin/mysqld(+0x3a4c24) [0x555e55101c24] /usr/sbin/mysqld(mysql_alter_table(THD*, char*, char*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool)+0x27f9) [0x555e5523dff9] /usr/sbin/mysqld(Sql_cmd_alter_table::execute(THD*)+0x5d7) [0x555e5527f8b7] /usr/sbin/mysqld(mysql_execute_command(THD*)+0x140f) [0x555e551b533f] /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, Parser_state*)+0x28d) [0x555e551bd1ed] /usr/sbin/mysqld(+0x4602ec) [0x555e551bd2ec] /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x1c63) [0x555e551bf673] /usr/sbin/mysqld(do_command(THD*)+0x16b) [0x555e551c023b] /usr/sbin/mysqld(do_handle_one_connection(THD*)+0x17f) [0x555e5527c74f] /usr/sbin/mysqld(handle_one_connection+0x47) [0x555e5527c8a7] /lib64/libpthread.so.0(+0x7dc5) [0x7f90982c6dc5] /lib64/libc.so.6(clone+0x6d) [0x7f90966fcc9d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7ebb7102f020): is an invalid pointer Connection ID (thread ID): 212 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. We think the query pointer is invalid, but we will try to print it anyway. Query: alter table delivery_person add column boris int(11) null 160727 12:26:58 mysqld_safe Number of processes running now: 0 160727 12:26:58 mysqld_safe WSREP: not restarting wsrep node automatically 160727 12:26:58 mysqld_safe mysqld from pid file /mysql/data/galera0.pid ended

1 0

[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