- discuss - lists.mariadb.org

Re: [Maria-discuss] Unable to DROP table, Data Dict problem
by Martin Langhoff 27 Jan '15

27 Jan '15

Hi Jan, thanks for your notes. I have gone through the instructions in that doc link, but they do not help. In the prior emails I have included stack dumps and straces of what happens when I follow those instructions. There is something else going on, or perhaps a regression that prevents the procedure you mention from working right. We are definitely NOT hitting ENOSPC, nor any FS or storage side issue. I have double checked on that. cheers, m On Tue, Jan 27, 2015 at 12:37 AM, Jan Lindström <jan.lindstrom(a)mariadb.com> wrote: > Hi, > > Failure you see on > > InnoDB: Error: tablespace id is 47073 in the data dictionary InnoDB: but in > file ./moodle_sand/mdl_tag_correlation.ibd it is 48463! > 150119 17:14:45 InnoDB: Assertion failure in thread 140045886629632 in file > fil0fil.c line 778 > > indicates database corruption that is caused most likely disk failure or a > bug on a software. With this information not easy to say which one. If drop > table crashes while the operation is done, deleting .ibd file is not enough > to re-create the table because table definition is on innodb data dictionary > while it is not on MySQL/MariaDB data dictionary. Please refer to > http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html > how to resolve the issue. > > R: Jan > > On Tue, Jan 20, 2015 at 5:00 PM, Martin Langhoff <martin.langhoff(a)gmail.com> > wrote: >> >> Hi Folks, >> >> We run a very large infra on MariaDB/RHEL6.x/RHEV/NetApp; thousands of >> VMs, each running MariaDB and very busy. MariaDB is 5.5.32. >> >> We recently had some NFS problems which led to minor DB corruption. >> Luckily, the corruption was isolated to a table holding cache data, >> and in fact one that is empty in most of our instances. That's the >> good news. >> >> The bad news is that the data dict is really unhappy about that table. >> >> - Cannot drop it, we get a nasty stack trace at [1] >> >> - Stopping MariaDB, removing the table by hand, restarting MariaDB, >> attempting to re-create the table hits a data dict error [2]. On disk >> I can see it created an ibd file, but no frm file. Following the >> instructions at >> >> http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html >> does not help. >> >> I have straced the process, and it does not seem to even try to create >> the frm file, so it really looks like it's internal state (data dict), >> not errors from further down the stack. OS / kernel logs are clean. >> >> Is there any reasonable method to cure this "in place" with minimal >> disruption? (These are large DBs, dump restore is more than awkward.) >> >> Oh and careful with NFSv4 :-/ >> >> thank you! >> >> >> martin >> >> 1 - Stack trace when trying to drop table >> >> Version: '5.5.32-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' >> port: 3306 MariaDB Server >> InnoDB: Error: tablespace id is 47073 in the data dictionary >> InnoDB: but in file ./moodle_sand/mdl_tag_correlation.ibd it is 48463! >> 150119 17:14:45 InnoDB: Assertion failure in thread 140045886629632 >> in file fil0fil.c line 778 >> 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.5/en/forcing-innodb-recovery.html >> InnoDB: about forcing recovery. >> 150119 17:14:45 [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 http://kb.askmonty.org/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: 5.5.32-MariaDB-log >> key_buffer_size=134217728 >> read_buffer_size=131072 >> max_used_connections=1 >> max_threads=187 >> thread_count=1 >> It is possible that mysqld could use up to >> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = >> 182265 K bytes of memory >> Hope that's ok; if not, decrease some variables in the equation. >> >> Thread pointer: 0x0xa92ee60 >> 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 = 0x7f5ef9531d78 thread_stack 0x48000 >> ??:0(my_print_stacktrace)[0xa8631e] >> ??:0(handle_fatal_signal)[0x6cdd0b] >> ??:0(??)[0x30a7c0f500] >> ??:0(??)[0x30a78328a5] >> ??:0(??)[0x30a7834085] >> ??:0(fil_node_open_file)[0x8f3add] >> ??:0(fil_node_prepare_for_io)[0x8f3be5] >> ??:0(fil_space_get_flags)[0x8f7420] >> ??:0(fil_space_get_zip_size)[0x8f7529] >> ??:0(dict_drop_index_tree)[0x8d64e6] >> ??:0(row_upd_clust_step)[0x86d84d] >> ??:0(row_upd_step)[0x86e22e] >> ??:0(que_run_threads)[0x9577a8] >> ??:0(que_eval_sql)[0x957ea6] >> ??:0(row_drop_table_for_mysql)[0x85ae6a] >> ??:0(ha_innobase::delete_table(char const*))[0x8424e3] >> ??:0(ha_delete_table(THD*, handlerton*, char const*, char const*, char >> const*, bool))[0x6d5233] >> ??:0(mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool, >> bool))[0x5ff928] >> ??:0(mysql_rm_table(THD*, TABLE_LIST*, char, char))[0x60001d] >> ??:0(mysql_execute_command(THD*))[0x58ad90] >> ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x58e369] >> ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned >> int))[0x58f790] >> ??:0(do_handle_one_connection(THD*))[0x647cdf] >> ??:0(handle_one_connection)[0x647d7c] >> ??:0(??)[0x30a7c07851] >> ??:0(??)[0x30a78e811d] >> >> Trying to get some variables. >> Some pointers may be invalid and cause the dump to abort. >> Query (0x7f5db800afa8): is an invalid pointer >> Connection ID (thread ID): 2 >> 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=off >> >> >> >> 2 - Error when attempting to recreate table >> 150119 17:15:22 InnoDB: Operating system error number 2 in a file >> operation. >> InnoDB: The error means the system cannot find the path specified. >> InnoDB: If you are installing InnoDB, remember that you must create >> InnoDB: directories yourself, InnoDB does not create them. >> 150119 17:15:22 InnoDB: Error: trying to open a table, but could not >> InnoDB: open the tablespace file './moodle_sand/mdl_tag_correlation.ibd'! >> InnoDB: Have you moved InnoDB .ibd files around without using the >> InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? >> InnoDB: It is also possible that this is a temporary table #sql..., >> InnoDB: and MySQL removed the .ibd file for this. >> InnoDB: Please refer to >> InnoDB: >> http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html >> InnoDB: for how to resolve the issue. >> >> >> -- >> martin.langhoff(a)gmail.com >> - ask interesting questions >> - don't get distracted with shiny stuff - working code first >> ~ http://docs.moodle.org/en/User:Martin_Langhoff >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : maria-discuss(a)lists.launchpad.net >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp > > -- martin.langhoff(a)gmail.com - ask interesting questions - don't get distracted with shiny stuff - working code first ~ http://docs.moodle.org/en/User:Martin_Langhoff

1 0

[Maria-discuss] Inclusion of Mysql security fixes in MariaDB
by Raina Masand 27 Jan '15

27 Jan '15

Hello, We recently were informed of some security fixes in Mysql 5.5.41: http://www.ubuntu.com/usn/usn-2480-1/ and are wondering whether there are plans to include these in an upcoming MariaDB release. Right now, we are running 10.0.13, so we're trying to plan the next upgrade. We see that there have been similar fixes included in MariaDB 10.0.14 and 10.0.15, so this seems likely. Based on this https://mariadb.com/kb/en/mariadb/development/security/ list of CVE's, it looks like the MariaDB 10.0.15 and MariaDB 5.5.40 include the same security fixes (presumably pulled from Mysql 5.5.40). Can we expect that the fixes from Mysql 5.5.41 will be included in an upcoming MariaDB 10.0.16 release? Would appreciate any insight into the general schedule for addressing these vulnerabilities. Thanks! Raina Masand and Lyle Franklin Cloud Foundry Core Services Team

3 3

[Maria-discuss] New Question: Getting column names from the fields that use non-ANSI characters
by AskMonty KB 27 Jan '15

27 Jan '15

Hello, A new question has been asked in "CONNECT" by evgenyko. Please answer it at http://mariadb.com/kb/en/getting-column-names-from-the-fields-that-use-non-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- I am trying to replicate example how to create pivot table like from MariaDB documentation: https://mariadb.com/kb/en/mariadb/documentation/storage-engines/connect/con… Everything works as fine as explained. However, if I replace in source table fields 'Beer' with '啤酒‘ and 'Car' with 'машина‘, I get the following CREATE TABLE statement: CREATE TABLE `pivex_cn` ( `who` varchar(135) NOT NULL, `week` int(11) NOT NULL, `å•¤é…’` decimal(6,2) NOT NULL `FLAG`=1, `Food` decimal(6,2) NOT NULL `FLAG`=1, `Ð¼Ð°ÑˆÐ¸Ð½Ð°` decimal(6,2) NOT NULL `FLAG`=1 ) ENGINE=CONNECT DEFAULT CHARSET=utf8 `TABLE_TYPE`='pivot' `TABNAME`='topivot_cn' Which then in SELECT * FROM pivex_cn; gives the following error: "Error Code: 1296. Got error 122 'Cannot find matching column' from CONNECT" All tables are using utf8 as default. I also tried uft8mb4, the same result :( -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/getting-column-names-from-the-fields-that-use-non-…

1 0

[Maria-discuss] Row size too large
by Reindl Harald 26 Jan '15

26 Jan '15

after upgrade the first testserver to 10.0.15: InnoDB: The total blob data length (13476124) is greater than 10% of the redo log file size (5120). Please increase innodb_log_file_size. * previously no problems * stopped server * increased innodb_log_file_size to 128MB * deleted ib_logfile* * started server * the same again why - that's only a small test-vm with not much data? ____________________________________________ ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs MariaDB [dbmail]> show create table dbmail_mimeparts; +------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dbmail_mimeparts | CREATE TABLE `dbmail_mimeparts` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `hash` char(128) NOT NULL, `data` longblob NOT NULL, `size` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `hash` (`hash`) ) ENGINE=InnoDB AUTO_INCREMENT=43391 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED | +------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

4 8

[Maria-discuss] New Question: External (Connect) Tables read-only after MariaDB upgrade from 10.0.12.0 to 10.0.15.0
by AskMonty KB 21 Jan '15

21 Jan '15

Hello, A new question has been asked in "CONNECT" by jhtclaessen. Please answer it at http://mariadb.com/kb/en/external-connect-tables-read-only-after-mariadb-up… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hello, I am testing MariaDB and I have ODBC connections to MSSQL server. This seemed to work pretty well in 10.0.12.0. After we installed a new 10.0.15.0 we could not get this working, all our external tables reported back as 'read-only'. (SQL Error (1036): Table '.....' is read only). We started troubleshooting but couldn't figure out why. So we upgraded the 10.0.12.0 version to 10.0.15.0 as well and now we have the same problem there. I see a lot of changes to the Connect Engine in 10.0.13.0, so that might be the problem. But I cannot downgrade anymore, hence we now have a problem, since we cannot use the functionality anymore due to the fact that we cannot update or insert records into another MSSQL database (through triggers). Does anyone have any clue what could cause this and how to solve this? Thanks in advance! -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/external-connect-tables-read-only-after-mariadb-up…

1 0

[Maria-discuss] Unable to DROP table, Data Dict problem
by Martin Langhoff 20 Jan '15

20 Jan '15

Hi Folks, We run a very large infra on MariaDB/RHEL6.x/RHEV/NetApp; thousands of VMs, each running MariaDB and very busy. MariaDB is 5.5.32. We recently had some NFS problems which led to minor DB corruption. Luckily, the corruption was isolated to a table holding cache data, and in fact one that is empty in most of our instances. That's the good news. The bad news is that the data dict is really unhappy about that table. - Cannot drop it, we get a nasty stack trace at [1] - Stopping MariaDB, removing the table by hand, restarting MariaDB, attempting to re-create the table hits a data dict error [2]. On disk I can see it created an ibd file, but no frm file. Following the instructions at http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html does not help. I have straced the process, and it does not seem to even try to create the frm file, so it really looks like it's internal state (data dict), not errors from further down the stack. OS / kernel logs are clean. Is there any reasonable method to cure this "in place" with minimal disruption? (These are large DBs, dump restore is more than awkward.) Oh and careful with NFSv4 :-/ thank you! martin 1 - Stack trace when trying to drop table Version: '5.5.32-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server InnoDB: Error: tablespace id is 47073 in the data dictionary InnoDB: but in file ./moodle_sand/mdl_tag_correlation.ibd it is 48463! 150119 17:14:45 InnoDB: Assertion failure in thread 140045886629632 in file fil0fil.c line 778 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.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 150119 17:14:45 [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 http://kb.askmonty.org/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: 5.5.32-MariaDB-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=187 thread_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 182265 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0xa92ee60 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 = 0x7f5ef9531d78 thread_stack 0x48000 ??:0(my_print_stacktrace)[0xa8631e] ??:0(handle_fatal_signal)[0x6cdd0b] ??:0(??)[0x30a7c0f500] ??:0(??)[0x30a78328a5] ??:0(??)[0x30a7834085] ??:0(fil_node_open_file)[0x8f3add] ??:0(fil_node_prepare_for_io)[0x8f3be5] ??:0(fil_space_get_flags)[0x8f7420] ??:0(fil_space_get_zip_size)[0x8f7529] ??:0(dict_drop_index_tree)[0x8d64e6] ??:0(row_upd_clust_step)[0x86d84d] ??:0(row_upd_step)[0x86e22e] ??:0(que_run_threads)[0x9577a8] ??:0(que_eval_sql)[0x957ea6] ??:0(row_drop_table_for_mysql)[0x85ae6a] ??:0(ha_innobase::delete_table(char const*))[0x8424e3] ??:0(ha_delete_table(THD*, handlerton*, char const*, char const*, char const*, bool))[0x6d5233] ??:0(mysql_rm_table_no_locks(THD*, TABLE_LIST*, bool, bool, bool, bool))[0x5ff928] ??:0(mysql_rm_table(THD*, TABLE_LIST*, char, char))[0x60001d] ??:0(mysql_execute_command(THD*))[0x58ad90] ??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x58e369] ??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x58f790] ??:0(do_handle_one_connection(THD*))[0x647cdf] ??:0(handle_one_connection)[0x647d7c] ??:0(??)[0x30a7c07851] ??:0(??)[0x30a78e811d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f5db800afa8): is an invalid pointer Connection ID (thread ID): 2 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=off 2 - Error when attempting to recreate table 150119 17:15:22 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 150119 17:15:22 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './moodle_sand/mdl_tag_correlation.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a temporary table #sql..., InnoDB: and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue. -- martin.langhoff(a)gmail.com - ask interesting questions - don't get distracted with shiny stuff - working code first ~ http://docs.moodle.org/en/User:Martin_Langhoff

1 1

[Maria-discuss] 10.0.x: [Warning] Unsafe statement written to the binary log using statement format
by Reindl Harald 19 Jan '15

19 Jan '15

uhm - binlog-format = ROW since at least 5 years everywhere how does 10.0.x come to the crazy idea it is "BINLOG_FORMAT = STATEMENT"? 150119 22:07:52 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses the general log, slow query log, or performance_schema table(s). This is unsafe because system tables may differ on slaves. Statement: repair table `mysql`.`column_stats`, `mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`event`, `mysql`.`func`, `mysql`.`general_log`, `mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`host`, `mysql`.`index_stats`, `mysql`.`ndb_binlog_index`, `mysql`.`plugin`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`proxies_priv`, `mysql`.`roles_mapping`, `mysql`.`servers`, `mysql`.`slow_log`, `mysql`.`table_stats`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user`

2 1

[Maria-discuss] build minimized MariaDB 10.0.x
by Reindl Harald 19 Jan '15

19 Jan '15

https://mariadb.atlassian.net/browse/MDEV-7076 well, in general i try to only have Aria/XtraDB/MyISAM -DWITH_XTRADB_STORAGE_ENGINE=ON \ -DWITH_PARTITION_STORAGE_ENGINE=ON \ -DWITH_ARCHIVE_STORAGE_ENGINE=OFF \ -DWITH_BLACKHOLE_STORAGE_ENGINE=OFF \ -DWITH_SPHINX_STORAGE_ENGINE=OFF \ -DWITH_MROONGA_STORAGE_ENGINE=OFF \ -DWITH_OQGRAPH_STORAGE_ENGINE=OFF \ -DWITH_SPIDER_STORAGE_ENGINE=OFF \ -DWITH_CASSANDRA_STORAGE_ENGINE=OFF \ but OOM in context of "mroonga" i want my ./configure --help back while in other aspects cmake is really cool there is no way i know to get a list of valid configure options :-( /usr/bin/ranlib libsql.a make[2]: Leaving directory `/home/builduser/rpmbuild/BUILD/mariadb-10.0.15' /usr/bin/cmake -E cmake_progress_report /home/builduser/rpmbuild/BUILD/mariadb-10.0.15/CMakeFiles 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 make[2]: Leaving directory `/home/builduser/rpmbuild/BUILD/mariadb-10.0.15' make[1]: *** [storage/mroonga/vendor/groonga/lib/CMakeFiles/libgroonga.dir/all] Error 2 make[1]: *** Waiting for unfinished jobs.... [ 99%] Built target sql make[1]: Leaving directory `/home/builduser/rpmbuild/BUILD/mariadb-10.0.15'

2 3

[Maria-discuss] Spider - query end
by Nicolas Payart 12 Jan '15

12 Jan '15

Hi list, I am using Spider Storage Engine for sharding data and remote access to some innodb tables on 3 different MariaDB 10.0.14 instances. It performs quite well in production except that sometimes, some queries (a minority of them fortunately) do not respond and stay indefinitely in "query end" state in processlist. What can make a query stay indefinitely in "query end" state? Maybe a bug in Spider Storage Engine? Any advice is welcome! Some more details: db01 =# show full processlist\G *************************** 4. row *************************** Id: 1699242 User: np Host: 10.0.0.1:24220 db: db01 Command: Query Time: 155777 State: query end Info: SELECT s.id,s.title,sum(s.cpt) AS sumcpt FROM s INNER JOIN u ON u.id = s.u_id INNER JOIN d ON s.d_id = d.id WHERE u.str = 'abc123456' AND d.str = 'def456789' GROUP BY s.title ORDER BY sumcpt DESC LIMIT 0,10 Progress: 0.000 About tables used in query: s is a spider table pointing on 24 innodb shards over 3 remote servers (PARTITION BY LIST (mod(id,24))) u is a spider table referencing one remote innodb table (not sharded, millions rows) d is a spider table referencing one remote innodb table (not sharded, <100 rows) Spider config: (none) =# show variables like 'spider%'; +---------------------------------------+--------+ | Variable_name | Value | +---------------------------------------+--------+ | spider_auto_increment_mode | -1 | | spider_bgs_first_read | -1 | | spider_bgs_mode | -1 | | spider_bgs_second_read | -1 | | spider_bka_engine | | | spider_bka_mode | -1 | | spider_block_size | 16384 | | spider_bulk_size | -1 | | spider_bulk_update_mode | -1 | | spider_bulk_update_size | -1 | | spider_casual_read | 1 | | spider_conn_recycle_mode | 1 | | spider_conn_recycle_strict | 0 | | spider_connect_mutex | OFF | | spider_connect_retry_count | 1000 | | spider_connect_retry_interval | 1000 | | spider_connect_timeout | 10 | | spider_crd_bg_mode | 1 | | spider_crd_interval | 4 | | spider_crd_mode | 1 | | spider_crd_sync | 0 | | spider_crd_type | -1 | | spider_crd_weight | -1 | | spider_delete_all_rows_type | -1 | | spider_direct_dup_insert | 1 | | spider_direct_order_limit | -1 | | spider_dry_access | OFF | | spider_error_read_mode | -1 | | spider_error_write_mode | -1 | | spider_first_read | -1 | | spider_force_commit | 1 | | spider_general_log | OFF | | spider_init_sql_alloc_size | -1 | | spider_internal_limit | -1 | | spider_internal_offset | -1 | | spider_internal_optimize | -1 | | spider_internal_optimize_local | -1 | | spider_internal_sql_log_off | ON | | spider_internal_unlock | OFF | | spider_internal_xa | OFF | | spider_internal_xa_id_type | 0 | | spider_internal_xa_snapshot | 0 | | spider_local_lock_table | OFF | | spider_lock_exchange | OFF | | spider_log_result_error_with_sql | 0 | | spider_log_result_errors | 0 | | spider_low_mem_read | -1 | | spider_max_order | -1 | | spider_multi_split_read | -1 | | spider_net_read_timeout | 30 | | spider_net_write_timeout | 60 | | spider_ping_interval_at_trx_start | 3600 | | spider_quick_mode | -1 | | spider_quick_page_size | -1 | | spider_read_only_mode | -1 | | spider_remote_access_charset | | | spider_remote_autocommit | 0 | | spider_remote_default_database | | | spider_remote_sql_log_off | 1 | | spider_remote_time_zone | | | spider_remote_trx_isolation | -1 | | spider_reset_sql_alloc | 0 | | spider_same_server_link | ON | | spider_second_read | -1 | | spider_select_column_mode | -1 | | spider_selupd_lock_mode | -1 | | spider_semi_split_read | -1 | | spider_semi_split_read_limit | -1 | | spider_semi_table_lock | 1 | | spider_semi_table_lock_connection | -1 | | spider_semi_trx | ON | | spider_semi_trx_isolation | -1 | | spider_skip_default_condition | -1 | | spider_split_read | -1 | | spider_sts_bg_mode | -1 | | spider_sts_interval | -1 | | spider_sts_mode | -1 | | spider_sts_sync | 0 | | spider_support_xa | OFF | | spider_sync_autocommit | OFF | | spider_sync_time_zone | OFF | | spider_sync_trx_isolation | OFF | | spider_table_init_error_interval | 1 | | spider_udf_ct_bulk_insert_interval | -1 | | spider_udf_ct_bulk_insert_rows | -1 | | spider_udf_ds_bulk_insert_rows | -1 | | spider_udf_ds_table_loop_mode | -1 | | spider_udf_ds_use_real_table | -1 | | spider_udf_table_lock_mutex_count | 20 | | spider_udf_table_mon_mutex_count | 20 | | spider_use_all_conns_snapshot | OFF | | spider_use_consistent_snapshot | OFF | | spider_use_default_database | ON | | spider_use_flash_logs | OFF | | spider_use_handler | 0 | | spider_use_pushdown_udf | -1 | | spider_use_snapshot_with_flush_tables | 0 | | spider_use_table_charset | -1 | | spider_version | 3.2.11 | +---------------------------------------+--------+ 99 rows in set (0.00 sec) -- Nicolas Payart

2 2

[Maria-discuss] future of SHOW ENGINE INNODB MUTEX in MariaDB
by Federico Razzoli 12 Jan '15

12 Jan '15

Hi! Morgan Tocker asked the community's opinions about SHOW ENGINE INNODB MUTEX: http://www.tocker.ca/2013/11/28/show-engine-innodb-mutex.html Despite these opinions, as Valerii Kravchuk notes on Facebook, the command disappeared in the latest 5.7 release. He also notes that the command has never been deprecated (!). Question is: will MariaDB preserve this command? Frankly, I don't know any other convenient way to obtain the same information... Regards, Federico

3 2