Bug #46384 | mysqld segfault when trying to create table with same name as existing view | ||
---|---|---|---|
Submitted: | 25 Jul 2009 18:19 | Modified: | 19 Dec 2009 0:07 |
Reporter: | David Abdemoulaie | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Views | Severity: | S1 (Critical) |
Version: | 5.1.36, 5.1.38 | OS: | Any (Gentoo & OS X) |
Assigned to: | Satya B | CPU Architecture: | Any |
Tags: | CREATE TABLE, segfault, VIEW |
[25 Jul 2009 18:19]
David Abdemoulaie
[25 Jul 2009 18:29]
David Abdemoulaie
debug trace
Attachment: mysqld.trace.bz2 (application/x-bzip2, text), 189.54 KiB.
[25 Jul 2009 18:39]
Valeriy Kravchuk
I can't repeat this with recent 5.1.38 from debug: valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test < ~/Downloads/dump.sql valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.38-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE magic ENGINE=MEMORY -> SELECT -> s.shop_id AS shop_id, -> s.id AS shift_id, -> st.dow AS dow, -> st.start AS start, -> st.end AS end, -> su.user_id AS manager_id -> FROM shifts s -> LEFT JOIN shift_times st ON s.id = st.shift_id -> LEFT JOIN shifts_users su ON s.id = su.shift_id -> LEFT JOIN shift_positions sp ON su.shift_position_id = sp.id -> WHERE sp.level = 1 -> ; Query OK, 3220 rows affected (0.83 sec) Records: 3220 Duplicates: 0 Warnings: 0 Maybe one of the bugs that are already fixed.
[25 Jul 2009 22:24]
MySQL Verification Team
Thank you for the bug report. I couldn't repeat with the binary release version reported on Ubuntu 9.04 64-bit. Could you please provide your my.cnf. Thanks in advance. miguel@lara:~/mysql-5.1.36-linux-x86_64-glibc23$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.36 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE magic ENGINE=MEMORY -> SELECT -> s.shop_id AS shop_id, -> s.id AS shift_id, -> st.dow AS dow, -> st.start AS start, -> st.end AS end, -> su.user_id AS manager_id -> FROM shifts s -> LEFT JOIN shift_times st ON s.id = st.shift_id -> LEFT JOIN shifts_users su ON s.id = su.shift_id -> LEFT JOIN shift_positions sp ON su.shift_position_id = sp.id -> WHERE sp.level = 1; Query OK, 3220 rows affected (0.24 sec) Records: 3220 Duplicates: 0 Warnings: 0
[25 Jul 2009 23:13]
David Abdemoulaie
my.cnf
Attachment: my.cnf (application/octet-stream, text), 2.36 KiB.
[26 Jul 2009 0:20]
David Abdemoulaie
Well... I found the cause of the problem. It's 95% my fault :) So, I had been working on optimizing a query and at some point tried the query above as a VIEW, named `magic`. Now, I thought I had dropped this, but I probably used DROP TABLE instead of DROP VIEW. I happened to randomly check SHOW CREATE TABLE magic; after starting the server after a crash to see this: mysql> show create table magic\G *************************** 1. row *************************** View: magic Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`dabdemoulaie`@`localhost` SQL SECURITY DEFINER VIEW `magic` AS select `s`.`shop_id` AS `shop_id`,`s`.`id` AS `shift_id`,`st`.`dow` AS `dow`,`st`.`start` AS `start`,`st`.`end` AS `end`,`su`.`user_id` AS `manager_id` from (((`shifts` `s` left join `shift_times` `st` on((`s`.`id` = `st`.`shift_id`))) left join `shifts_users` `su` on((`s`.`id` = `su`.`shift_id`))) left join `shift_positions` `sp` on((`su`.`shift_position_id` = `sp`.`id`))) where (`sp`.`level` = 1) character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) So, apparently the issue here seems to just be a case of MySQL horribly handling the case of me attempting to create a table with the same name as a view. I removed this view and have yet to crash the server. :)
[26 Jul 2009 1:37]
David Abdemoulaie
Here are the exact steps to recreate the segfault. I have been able to do it on every 5.1.36 installation I can get my hands on: CREATE VIEW magic AS SELECT s.shop_id AS shop_id, s.id AS shift_id, st.dow AS dow, st.start AS start, st.end AS end, su.user_id AS manager_id FROM shifts s JOIN shift_times st ON s.id = st.shift_id JOIN shifts_users su ON s.id = su.shift_id JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1; Query OK, 0 rows affected (0.13 sec) CREATE TABLE magic ENGINE=MEMORY SELECT s.shop_id AS shop_id, s.id AS shift_id, st.dow AS dow, st.start AS start, st.end AS end, su.user_id AS manager_id FROM shifts s JOIN shift_times st ON s.id = st.shift_id JOIN shifts_users su ON s.id = su.shift_id JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1; ERROR 2013 (HY000): Lost connection to MySQL server during query
[26 Jul 2009 1:42]
David Abdemoulaie
I'll also add that in attempting to duplicate this using simpler SELECT queries, I haven't been able to reproduce the segfault.
[26 Jul 2009 1:56]
David Abdemoulaie
I found a simpler way to reproduce the segfault. It appears related to a VIEW with a JOIN. I also confirmed on arch linux: Linux mgslice 2.6.24-23-xen #1 SMP Mon Jan 26 03:09:12 UTC 2009 x86_64 Quad-Core AMD Opteron(tm) Processor 2347 HE AuthenticAMD GNU/Linux use test Database changed mysql> CREATE TABLE `a` ( -> `id` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -> ; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO a (id) VALUES (1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE `aa` ( -> `id` int(11) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO aa (id) VALUES (1),(2),(3); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> CREATE VIEW magic AS SELECT a.id AS a_id, aa.id AS aa_id FROM a JOIN aa ON a.id = aa.id; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE magic ENGINE=MEMORY -> SELECT a.id AS a_id, aa.id AS aa_id FROM a JOIN aa ON a.id = aa.id; ERROR 2013 (HY000): Lost connection to MySQL server during query
[26 Jul 2009 6:31]
Valeriy Kravchuk
Verified just as described: mysql> select version(); +--------------+ | version() | +--------------+ | 5.1.38-debug | +--------------+ 1 row in set (0.00 sec) mysql> CREATE VIEW magic AS -> SELECT -> s.shop_id AS shop_id, -> s.id AS shift_id, -> st.dow AS dow, -> st.start AS start, -> st.end AS end, -> su.user_id AS manager_id -> FROM shifts s -> JOIN shift_times st ON s.id = st.shift_id -> JOIN shifts_users su ON s.id = su.shift_id -> JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1;Query OK, 0 rows affected (0.13 sec) mysql> CREATE TABLE magic ENGINE=MEMORY -> SELECT -> s.shop_id AS shop_id, -> s.id AS shift_id, -> st.dow AS dow, -> st.start AS start, -> st.end AS end, -> su.user_id AS manager_id -> FROM shifts s -> JOIN shift_times st ON s.id = st.shift_id -> JOIN shifts_users su ON s.id = su.shift_id -> JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1;ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> 090726 09:26:57 mysqld_safe mysqld restarted Stack trace is: stack_bottom = 0xb026af64 thread_stack 0x30000 0 mysqld 0x00569413 my_print_stacktrace + 44 1 mysqld 0x000f852f handle_segfault + 853 2 libSystem.B.dylib 0x940472bb _sigtramp + 43 3 ??? 0xffffffff 0x0 + 4294967295 4 mysqld 0x001b246f _ZN13select_create7prepareER4ListI4ItemEP18st_select_lex_unit + 313 5 mysqld 0x0019cb34 _ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit + 3348 6 mysqld 0x001a5a49 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex + 693 7 mysqld 0x001a5e7a _Z13handle_selectP3THDP6st_lexP13select_resultm + 558 8 mysqld 0x00111fae _Z21mysql_execute_commandP3THD + 8100 9 mysqld 0x0011abec _Z11mysql_parseP3THDPKcjPS2_ + 580 10 mysqld 0x0011b9a4 _Z16dispatch_command19enum_server_commandP3THDPcj + 3074 11 mysqld 0x0011cca6 _Z10do_commandP3THD + 654 12 mysqld 0x00107d66 handle_one_connection + 366 13 libSystem.B.dylib 0x9400c095 _pthread_start + 321 14 libSystem.B.dylib 0x9400bf52 thread_start + 34
[27 Jul 2009 20:37]
David Abdemoulaie
I thought I had mentioned this before, but I have duplicated it on 5.0.70 as well.
[18 Aug 2009 11:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/80984 2790 Satya B 2009-08-18 Fix for BUG#46384 - mysqld segfault when trying to create table with same name as existing view When trying to create a table with the same name as existing view with join, mysql server crashes. The problem is when create table is issued with the same name as view, while verifying with the existing tables, we assume that base table object is created always. In this case, since it is a view over multiple tables, we don't have the mysql derived table object. Fixed the logic which checks if there is an existing table to not to assume that table object is created when the base table is view over multiple tables. modified: mysql-test/r/create.result mysql-test/t/create.test sql/sql_insert.cc
[27 Aug 2009 6:56]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81658 2795 Satya B 2009-08-27 Fix for BUG#46384 - mysqld segfault when trying to create table with same name as existing view When trying to create a table with the same name as existing view with join, mysql server crashes. The problem is when create table is issued with the same name as view, while verifying with the existing tables, we assume that base table object is created always. In this case, since it is a view over multiple tables, we don't have the mysql derived table object. Fixed the logic which checks if there is an existing table to not to assume that table object is created when the base table is view over multiple tables. modified: mysql-test/r/create.result mysql-test/t/create.test sql/sql_insert.cc
[27 Aug 2009 9:39]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81688 2795 Satya B 2009-08-27 Fix for BUG#46384 - mysqld segfault when trying to create table with same name as existing view When trying to create a table with the same name as existing view with join, mysql server crashes. The problem is when create table is issued with the same name as view, while verifying with the existing tables, we assume that base table object is created always. In this case, since it is a view over multiple tables, we don't have the mysql derived table object. Fixed the logic which checks if there is an existing table to not to assume that table object is created when the base table is view over multiple tables. modified: mysql-test/r/create.result mysql-test/t/create.test sql/sql_insert.cc
[27 Aug 2009 13:51]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81742 2797 Satya B 2009-08-27 Fix for BUG#46384 - mysqld segfault when trying to create table with same name as existing view When trying to create a table with the same name as existing view with join, mysql server crashes. The problem is when create table is issued with the same name as view, while verifying with the existing tables, we assume that base table object is created always. In this case, since it is a view over multiple tables, we don't have the mysql derived table object. Fixed the logic which checks if there is an existing table to not to assume that table object is created when the base table is view over multiple tables. modified: mysql-test/r/create.result mysql-test/t/create.test sql/sql_insert.cc
[4 Sep 2009 6:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82406 2807 Satya B 2009-09-04 Fix for BUG#46384 - mysqld segfault when trying to create table with same name as existing view When trying to create a table with the same name as existing view with join, mysql server crashes. The problem is when create table is issued with the same name as view, while verifying with the existing tables, we assume that base table object is created always. In this case, since it is a view over multiple tables, we don't have the mysql derived table object. Fixed the logic which checks if there is an existing table to not to assume that table object is created when the base table is view over multiple tables. @ mysql-test/r/create.result BUG#46384 - mysqld segfault when trying to create table with same name as existing view Testcase for the bug @ mysql-test/t/create.test BUG#46384 - mysqld segfault when trying to create table with same name as existing view Testcase for the bug @ sql/sql_insert.cc BUG#46384 - mysqld segfault when trying to create table with same name as existing view Fixed create_table_from_items() method to properly check, if the base table is a view over multiple tables.
[14 Sep 2009 16:05]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[23 Sep 2009 8:00]
MySQL Verification Team
FYI: testcase and stack trace from 5.1.38 drop table if exists t1,v2; drop view if exists v2; create table t1(a int)engine=myisam; create or replace view v2 as select 1 from t1,t1 a; create table if not exists v2 select 1 from t1; mysqld.exe!create_table_from_items()[sql_insert.cc:3398] mysqld.exe!select_create::prepare()[sql_insert.cc:3617] mysqld.exe!JOIN::prepare()[sql_select.cc:638] mysqld.exe!mysql_select()[sql_select.cc:2377] mysqld.exe!handle_select()[sql_select.cc:268] mysqld.exe!mysql_execute_command()[sql_parse.cc:2674] mysqld.exe!mysql_parse()[sql_parse.cc:5935] mysqld.exe!dispatch_command()[sql_parse.cc:1215] mysqld.exe!do_command()[sql_parse.cc:854] mysqld.exe!handle_one_connection()[sql_connect.cc:1127] mysqld.exe!pthread_start()[my_winthread.c:85] mysqld.exe!_callthreadstart()[thread.c:295] mysqld.exe!_threadstart()[thread.c:275] kernel32.dll!BaseThreadStart()
[6 Oct 2009 8:57]
Bugs System
Pushed into 5.0.87 (revid:joro@sun.com-20091006073202-rj21ggvo2gw032ks) (version source revid:satya.bn@sun.com-20090904065154-6adncuygkuragleb) (merge vers: 5.0.86) (pib:11)
[6 Oct 2009 8:59]
Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:mikael@mysql.com-20090907102257-fflwgm6rp5m5kmfw) (merge vers: 5.1.39) (pib:11)
[6 Oct 2009 23:11]
Paul DuBois
Noted in 5.0.87, 5.1.40, 5.4.3 changelogs. An attempt to create a table with the same name as an existing view could cause a server crash.
[18 Dec 2009 10:39]
Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:55]
Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:10]
Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:24]
Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)