| Bug #46184 | Crash, SELECT ... FROM derived table procedure analyze | ||
|---|---|---|---|
| Submitted: | 14 Jul 2009 20:21 | Modified: | 18 Dec 2009 13:13 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0,5.1,5.4 | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
| Tags: | crash | ||
[14 Jul 2009 20:22]
Matthias Leich
Backtrace
Attachment: backtrace.txt (text/plain), 5.86 KiB.
[14 Jul 2009 20:24]
Matthias Leich
Test script
Attachment: ml101.test (application/octet-stream, text), 1.63 KiB.
[15 Jul 2009 5:42]
MySQL Verification Team
nicer looking testcase: drop table if exists `t1`; create table `t1`(`a` int,`b` int,`c` int,`d` int,`e` int,`f` int,`g` int,`h` int,`i` int,`j` int,`k` int); insert into `t1` values (); select * from (select * from `t1`)`d` procedure analyse();
[15 Jul 2009 9:59]
Matthias Leich
[15 Jul 2009 14:14]
Philip Stoev
Pasting the backtrace as a comment, so that it is visible when searching:
#2 0x00000000006ecf81 in handle_segfault (sig=11) at mysqld.cc:2718
#3 <signal handler called>
#4 0x000000000074bf87 in fill_record (thd=0x16c9b48, ptr=0x1641920, values=@0x152f718, ignore_errors=true) at sql_base.cc:7475
#5 0x00000000008c9117 in select_union::send_data (this=0x16cd080, values=@0x152f718) at sql_union.cc:60
#6 0x000000000089d470 in analyse::end_of_records (this=0x152f680) at sql_analyse.cc:755
#7 0x000000000076f455 in end_send (join=0x16a32d0, join_tab=0x0, end_of_records=true) at sql_select.cc:17301
#8 0x0000000000788761 in do_select (join=0x16a32d0, fields=0x1606418, table=0x0, procedure=0x152f680) at sql_select.cc:15797
#9 0x00000000007a2fc8 in JOIN::exec (this=0x16a32d0) at sql_select.cc:2900
#10 0x000000000079d99c in mysql_select (thd=0x16c9b48, rref_pointer_array=0x16064f8, tables=0x1606b80, wild_num=0, fields=@0x1606418, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0x16cd080,
unit=0x16065e0, select_lex=0x1606310) at sql_select.cc:3091
#11 0x00000000008c966f in mysql_derived_filling (thd=0x16c9b48, lex=0x16cb418, orig_table_list=0x16ccb18) at sql_derived.cc:295
#12 0x00000000008c9410 in mysql_handle_derived (lex=0x16cb418, processor=0x8c94a1 <mysql_derived_filling(THD*, LEX*, TABLE_LIST*)>) at sql_derived.cc:56
#13 0x000000000075a95c in open_and_lock_tables_derived (thd=0x16c9b48, tables=0x16ccb18, derived=true, flags=0) at sql_base.cc:4242
#14 0x000000000070a382 in open_and_lock_tables (thd=0x16c9b48, tables=0x16ccb18) at ../../sql/mysql_priv.h:1519
#15 0x00000000006ffe49 in mysql_execute_command (thd=0x16c9b48) at sql_parse.cc:2636
#16 0x000000000070741e in mysql_parse (thd=0x16c9b48, inBuf=0x16054c0 "CREATE TEMPORARY TABLE my_table AS\nSELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000)", length=108, found_semicolon=0x40d83f30) at sql_parse.cc:5942
#17 0x000000000070803c in dispatch_command (command=COM_QUERY, thd=0x16c9b48, packet=0x1601469 "CREATE TEMPORARY TABLE my_table AS\nSELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000)", packet_length=108) at sql_parse.cc:1061
#18 0x000000000070951a in do_command (thd=0x16c9b48) at sql_parse.cc:743
#19 0x00000000006f6893 in handle_one_connection (arg=0x16c9b48) at sql_connect.cc:1158
#20 0x00007f5ac4a60040 in start_thread () from /lib64/libpthread.so.0
#21 0x00007f5ac3a0108d in clone () from /lib64/libc.so.6
[25 Aug 2009 13:04]
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/81504 2797 Sergey Glukhov 2009-08-25 Bug#46184 Crash, SELECT ... FROM derived table procedure analyze The crash happens because select_union object is used as result set for queries which have derived tables. select_union use temporary table as data storage and if fields count exceeds 10(count of values for procedure ANALYSE()) then we get a crash on fill_record() function. The fix is to disallow use of procedure ANALYSE() if derived tables exist. @ mysql-test/r/analyse.result test result @ mysql-test/r/subselect.result result fix @ mysql-test/t/analyse.test test case @ mysql-test/t/subselect.test test fix @ sql/sql_yacc.yy The crash happens because select_union object is used as result set for queries which have derived tables. select_union use temporary table as data storage and if fields count exceeds 10(count of values for procedure ANALYSE()) then we get a crash on fill_record() function. The fix is to disallow use of procedure ANALYSE() if derived tables exist.
[27 Aug 2009 11:20]
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/81709 2796 Sergey Glukhov 2009-08-27 Bug#46184 Crash, SELECT ... FROM derived table procedure analyze The crash happens because select_union object is used as result set for queries which have derived tables. select_union use temporary table as data storage and if fields count exceeds 10(count of values for procedure ANALYSE()) then we get a crash on fill_record() function. @ mysql-test/r/analyse.result test result @ mysql-test/r/subselect.result result fix @ mysql-test/t/analyse.test test case @ mysql-test/t/subselect.test test fix @ sql/sql_yacc.yy The crash happens because select_union object is used as result set for queries which have derived tables. select_union use temporary table as data storage and if fields count exceeds 10(count of values for procedure ANALYSE()) then we get a crash on fill_record() function.
[2 Sep 2009 10:25]
Bugs System
Pushed into 5.0.86 (revid:joro@sun.com-20090902102337-n5rw8227wwp5cpx8) (version source revid:sergey.glukhov@sun.com-20090827102219-sgjz0v5t1rfccs14) (merge vers: 5.0.86) (pib:11)
[2 Sep 2009 16:42]
Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:sergey.glukhov@sun.com-20090827105925-j56pdghdzb8nlw3p) (merge vers: 5.1.39) (pib:11)
[14 Sep 2009 16:06]
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)
[18 Sep 2009 19:38]
Paul DuBois
Noted in 5.0.86, 5.1.39, 5.4.4 changelogs. A query containing a subquery in the FROM clause and PROCEDURE ANALYSE() caused a server crash.
[1 Oct 2009 5:59]
Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25]
Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25]
Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:15]
Paul DuBois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50]
Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[12 Oct 2009 14:12]
Paul DuBois
Noted in 5.0.84sp1 changelog.
[14 Oct 2009 8:20]
Bugs System
Pushed into 5.0.88 (revid:build@mysql.com-20091014081604-yhwy9zh6fq8kcurj) (version source revid:build@mysql.com-20091014081604-yhwy9zh6fq8kcurj) (merge vers: 5.0.88) (pib:13)
[14 Oct 2009 14:39]
Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (merge vers: 5.1.41) (pib:13)
[14 Oct 2009 16:51]
Paul DuBois
Already noted in earlier changelogs.
[22 Oct 2009 6:37]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:09]
Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131937-nchb8tjk88jpfjav) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 19:16]
Paul DuBois
Noted in 5.5.0, 6.0.14 changelogs.
[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:09]
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)
[18 Dec 2009 13:13]
MC Brown
Already documented in 5.1.41
Description: CREATE TEMPORARY TABLE `t1` (`char_utf8_1` CHAR (1) CHARACTER SET utf8, `c_latin1_1` CHAR (1) CHARACTER SET latin1, `e_utf8` ENUM ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o', 'p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET utf8, `c_latin1_2` CHAR (1) CHARACTER SET latin1, `s_utf8_1` SET ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o', 'p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET utf8, `e_latin1_1` ENUM ('a','b','c','d','e','f','g','h','i','j','k','l','m','n', 'o','p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET latin1, `s_utf8_2` SET ('a','b','c','d','e','f','g','h','i','j','k','l','m','n', 'o','p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET utf8, `c_utf8_2` CHAR (1) CHARACTER SET utf8, `s_latin1_1` SET ('a','b','c','d','e','f','g','h','i','j','k','l','m','n', 'o','p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET latin1, `e_latin1_2` ENUM ('a','b','c','d','e','f','g','h','i','j','k','l','m','n', 'o','p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET latin1, `s_latin1_2` SET ('a','b','c','d','e','f','g','h','i','j','k','l','m','n', 'o','p','q','r','s','t','u','v','w','x','y','z') CHARACTER SET latin1); INSERT IGNORE INTO t1 VALUES ('f', NULL, 'p', 'h', 'k', NULL, 'b', 'b', 'u', 'k', 'd'); COMMIT; # THIS STATEMENT CRASHES THE SERVER CREATE TEMPORARY TABLE my_table AS SELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000); TEST RESULT TIME (ms) ------------------------------------------------------------ worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 main.ml101 [ fail ] Test ended at 2009-07-14 21:41:50 CURRENT_TEST: main.ml101 mysqltest: At line 30: query 'CREATE TEMPORARY TABLE my_table AS SELECT * FROM (SELECT * FROM `t1`) AS my_tab1 PROCEDURE ANALYSE(10, 2000)' failed: 2013: Lost connection to MySQL server during query My environment: --------------- - Intel Core2Duo (64 Bit) - Linux OpenSuSE 11.0 (64 Bit) - MySQL compile from source with compile-pentium64-debug-max - mysql-azalea (5.4) 2009-07-12 mysql-5.1-bugteam 2009-07-14 mysql-5.0-bugteam 2009-07-14 Observations when trying to shrink the test: -------------------------------------------- - no crash if the derived table "FROM (SELECT ...)" is replaced by a simple table "FROM t1" - many attempts to remove the CHARACTER SETs latin1 and utf8 or columns removed also the crash - TEMPORARY is not required I avoid via temporary the cleanup at test end. - PROCEDURE ANALYSE(10, 2000) is needed for the crash How to repeat: See above Suggested fix: http://dev.mysql.com/doc/refman/6.0/en/procedure-analyse.html says about "procedure analyse": ... It examines the result from a query and returns an analysis of the results that suggests optimal data types for each column. In case of CREATE TABLE ... PROCEDURE the suggestions about optimal data types are somehow too late because we already have the table with more or less optimal data types. At least till now I do not see a value of a INSERT INTO ... SELECT ... PROCEDURE ANALYSE(10, 2000)' CREATE TABLE ... AS SELECT ... PROCEDURE . I propose to disallow PROCEDURE ANALYSE in any statement which does not begin with "SELECT".