| Bug #82910 | Crash when using CONVERT_TZ() in a SELECT query | ||
|---|---|---|---|
| Submitted: | 8 Sep 2016 15:55 | Modified: | 9 Sep 2016 12:24 |
| Reporter: | jocelyn fournier | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.6.xx | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Sep 2016 15:55]
jocelyn fournier
[8 Sep 2016 18:03]
MySQL Verification Team
I couldn't repeat which is your time_zone?. Thanks
[8 Sep 2016 18:23]
jocelyn fournier
Hi Miguel, When it crashes, system_time_zone is CEST. Using SELECT CONVERT_TZ(starttime,'CEST','Europe/Lisbon') starttime FROM envois3; it crashes as well. HTH, Jocelyn
[8 Sep 2016 18:34]
jocelyn fournier
Note I'm able to reproduce the crash on 3 different servers (2 linux Debian wheezy / jessie, 1 mac os 10.12). Tested with percona, mariadb 10.0, 10.1 and Vanilla MySQL, all on the same system timezone CEST, all are crashing. I've also been able to reproduce the crash in debug mode on MariaDB 10.0.27 with a different stacktrace : 2016-09-08 09:49:53 2b9e715bf700 InnoDB: Assertion failure in thread 47959506679552 in file lock0lock.cc line 4251 InnoDB: Failing assertion: (trx)->in_ro_trx_list == (trx)->read_only stack_bottom = 0x2b9e715bee68 thread_stack 0x48000 mysys/stacktrace.c:267(my_print_stacktrace)[0xe666ff] sql/signal_handler.cc:161(handle_fatal_signal)[0x872064] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x2b5470aff0a0] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x2b5472595125] /lib/x86_64-linux-gnu/libc.so.6(abort+0x180)[0x2b54725983a0] lock/lock0lock.cc:4251(lock_table_create)[0xb88867] lock/lock0lock.cc:4633(lock_table(unsigned long, dict_table_t*, lock_mode, que_thr_t*))[0xb89697] row/row0sel.cc:4085(row_search_for_mysql(unsigned char*, unsigned long, row_prebuilt_t*, unsigned long, unsigned long))[0xc586d4] handler/ha_innodb.cc:8864(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0xb33559] sql/handler.h:2929(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x8809e8] sql/handler.cc:2642(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x8785cb] sql/tztime.cc:1902(tz_load_from_open_tables)[0x76f40b] sql/tztime.cc:2347(my_tz_find(THD*, String const*))[0x7705fa] sql/item_timefunc.cc:2029(Item_func_convert_tz::get_date(st_mysql_time*, unsigned long long))[0x933de4] sql/item.cc:6575(Item::send(Protocol*, String*))[0x8955b5] sql/protocol.cc:912(Protocol::send_result_set_row(List<Item>*))[0x5d73fa] sql/sql_class.cc:2573(select_send::send_data(List<Item>&))[0x646107] sql/sql_select.cc:18972(end_send)[0x6e31d8] sql/sql_select.cc:18065(evaluate_join_record)[0x6e0f2f] sql/sql_select.cc:17843(sub_select(JOIN*, st_join_table*, bool))[0x6e0828] sql/sql_select.cc:17505(do_select)[0x6e008d] sql/sql_select.cc:3084(JOIN::exec_inner())[0x6bd27a] sql/sql_select.cc:2375(JOIN::exec())[0x6ba746] sql/sql_select.cc:3310(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x6bdaf7] sql/sql_select.cc:373(handle_select(THD*, LEX*, select_result*, unsigned long))[0x6b3f69] sql/sql_parse.cc:5294(execute_sqlcom_select)[0x6883aa] sql/sql_parse.cc:2563(mysql_execute_command(THD*))[0x68075f] sql/sql_parse.cc:6576(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x68b040] sql/sql_parse.cc:1311(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x67d972] sql/sql_parse.cc:999(do_command(THD*))[0x67cc38] sql/sql_connect.cc:1378(do_handle_one_connection(THD*))[0x79cd4d] sql/sql_connect.cc:1294(handle_one_connection)[0x79caac] /lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x2b5470af6b50] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x2b5472640fbd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x259ea68): SELECT *, UNIX_TIMESTAMP(starttime) AS unixstamp, CONVERT_TZ(starttime,'SYSTEM','Europe/Lisbon') starttime FROM envois On MySQL 5.6.33 the debug build is crashing at startup : mysqld: /root/mysql-5.6.33/sql/sql_base.cc:9517: void close_mysql_tables(THD*): Assertion `thd->transaction.stmt.is_empty()' failed. 13:06:37 UTC - 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. 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. key_buffer_size=33554432 read_buffer_size=65536 max_used_connections=0 max_threads=3000 thread_count=0 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 455838 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x3161ea820 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 = 7ffe04fdee28 thread_stack 0x40000 /usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xaeff8b] /usr/local/mysql/bin/mysqld(handle_fatal_signal+0x402)[0x76fafe] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x2abc8cdc90a0] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x2abc8dfef125] /lib/x86_64-linux-gnu/libc.so.6(abort+0x180)[0x2abc8dff23a0] /lib/x86_64-linux-gnu/libc.so.6(__assert_fail+0xf1)[0x2abc8dfe8311] /usr/local/mysql/bin/mysqld(_Z18close_mysql_tablesP3THD+0x41)[0x7be2b3] /usr/local/mysql/bin/mysqld(_Z10my_tz_initP3THDPKcc+0x79a)[0x8f2963] /usr/local/mysql/bin/mysqld(_Z11mysqld_mainiPPc+0x8ac)[0x668494] /usr/local/mysql/bin/mysqld(main+0x20)[0x65d06c] /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xfd)[0x2abc8dfdbead] /usr/local/mysql/bin/mysqld[0x65cf69] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0): is an invalid pointer Connection ID (thread ID): 0 Status: NOT_KILLED
[8 Sep 2016 20:04]
jocelyn fournier
Also if I try to run a SET time_zone command (even invalid) before the query, it doesn't crash as well. MariaDB [test]> show variables LIKE 'time_zone'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | time_zone | SYSTEM | +---------------+--------+ 1 row in set (0.01 sec) MariaDB [test]> show variables LIKE 'system_time_zone'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | system_time_zone | CEST | +------------------+-------+ 1 row in set (0.00 sec) MariaDB [test]> use test Database changed MariaDB [test]> SET time_zone='CEST'; ERROR 1298 (HY000): Unknown or incorrect time zone: 'CEST' MariaDB [test]> SELECT CONVERT_TZ(starttime,'SYSTEM','Europe/Lisbon') starttime FROM envois3; +---------------------+ | starttime | +---------------------+ | 2008-08-11 21:43:00 | +---------------------+
[9 Sep 2016 1:05]
MySQL Verification Team
Thank you for the feedback. Could you please point me where in the Manual says the table mysql.time_zone_name can be created with engine InnoDB instead of MyISAM. When I build from soure is:
c:\dbs>5.6\bin\mysql -uroot mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.34 Source distribution PULL: 2016-SEP-07
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show create table time_zone_name\G
*************************** 1. row ***************************
Table: time_zone_name
Create Table: CREATE TABLE `time_zone_name` (
`Name` char(64) NOT NULL,
`Time_zone_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names'
1 row in set (0.00 sec)
On another hand the file you uses has:
--
-- Table structure for table `time_zone_name`
--
DROP TABLE IF EXISTS `time_zone_name`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `time_zone_name` (
`Name` char(64) NOT NULL,
`Time_zone_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Time zone names';
/*!40101 SET character_set_client = @saved_cs_client */;
Thanks.
[9 Sep 2016 7:49]
jocelyn fournier
Hi Miguel, Indeed. It seems this table has been in InnoDB format for a long time now (2014) but I don't really know why (perhaps a wrong attempt to convert in a batch MyISAM tables into InnoDB format). The point is it was working properly in 5.5 and it starts crashing badly in 5.6, without any warning in the log about the wrong system table format. So it would be nice to have an error "Storage engine 'InnoDB' does not support system tables" instead of a crash :) Thanks and regards, Jocelyn Fournier
[9 Sep 2016 10:17]
MySQL Verification Team
Thank you for the feedback. When I applied your timezone.sql file I got: c:\dbs>5.6\bin\mysql -uroot mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.34 Source distribution PULL: 2016-SEP-07 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source c:/dbs/timezone.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) <CUT> Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ERROR 1726 (HY000): Storage engine 'InnoDB' does not support system tables. [mysql.time_zone_name] Query OK, 0 rows affected (0.00 sec) ERROR 1146 (42S02): Table 'mysql.time_zone_name' doesn't exist <CUT> and when re-start the server: 2016-09-09 07:08:53 20880 [Note] - '::' resolves to '::'; 2016-09-09 07:08:53 20880 [Note] Server socket created on IP: '::'. 2016-09-09 07:08:53 20880 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_name' doesn't exist tryi ng to live without them 2016-09-09 07:08:53 20880 [Note] Event Scheduler: Loaded 0 events 2016-09-09 07:08:53 20880 [Note] 5.6\bin\mysqld: ready for connections. Version: '5.6.34' socket: '' port: 3306 Source distribution PULL: 2016-SEP-07 So I could say !bug. Thanks.
[9 Sep 2016 10:22]
jocelyn fournier
Yes actually I tested the testcase on MariaDB which doesn't do this check. But if the system table in InnoDB format is already existing in the database (I don't know in which MySQL version the "Storage engine 'InnoDB' does not support system tables" check was introduced at table creation), it does crash.
[9 Sep 2016 10:27]
jocelyn fournier
Note that after switching from 5.5 to 5.6 with this innodb time_zone_name table, mysql_upgrade didn't report an issue with this table neither.
[9 Sep 2016 10:27]
Louis R
Also, the table was in InnoDB *before* (for more than 2 years), so for a reason has been converted without a warning by a previous version of MySQL. Even if it's not possible to convert the table to InnoDB with current versions without MariaDB, it was possible before I guess, as we've been able to have InnoDB system tables up & running with MySQL 5.5 for 2 years, and crash MySQL 5.6 on upgrade without any warning. So maybe not a bug, but I think there's a real need for a warning somewhere, maybe at least at startup?
[9 Sep 2016 11:59]
MySQL Verification Team
Thank you for the feedback. The today situation is: Manual notice about to convert system tables to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/converting-tables-to-innodb.html "Important Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type." Both version 5.5 and 5.6 doesn't permit to create/alter system table for InnoDB engine: c:\dbs>5.5\bin\mysql -uroot mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.53 Source distribution PULL: 2016-SEP-07 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source c:/dbs/timezone.sql Query OK, 0 rows affected (0.00 sec) <CUT> Query OK, 0 rows affected (0.00 sec) ERROR 1726 (HY000): Storage engine 'InnoDB' does not support system tables. [mysql.time_zone_name] Query OK, 0 rows affected (0.00 sec) ERROR 1146 (42S02): Table 'mysql.time_zone_name' doesn't exist c:\dbs>5.5\bin\mysql -uroot mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.53 Source distribution PULL: 2016-SEP-07 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter table time_zone_name engine InnoDB; ERROR 1726 (HY000): Storage engine 'InnoDB' does not support system tables. [mysql.time_zone_name]
[9 Sep 2016 12:24]
jocelyn fournier
Well if in the manual it's specified to not convert the system table to InnoDB, it means it was allowed at some point :) I agree those tables shouldn't have been converted to InnoDB, but in this particular case it wasn't causing any issue in MySQL 5.5, and with 5.6, it was really painful to find out what was wrong (server keep crashing, no offending query reported in the stack trace while in production, no error / warning, no clue from the stack trace...). It could also occur while migrating for example from MariaDB to MySQL, since for example current MariaDB 5.5 version still allows this kind of MyISAM -> InnoDB conversion. So I think at least adding an extra check in mysql_upgrade would be helpful :)
