| Bug #46615 | Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table | ||
|---|---|---|---|
| Submitted: | 8 Aug 2009 9:03 | Modified: | 18 Jun 2010 12:53 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S3 (Non-critical) |
| Version: | 5.1,5.4 | OS: | Any |
| Assigned to: | Kristofer Pettersson | CPU Architecture: | Any |
[3 Mar 2010 14:23]
Kristofer Pettersson
Progress report: The supplied test case doesn't execute but stops with error: mysqltest: At line 8: query 'ALTER TABLE t1_merge_1_A ENGINE = MERGE UNION ( t1_base_0_A , t1_base_0_A )' failed: 1066: Not unique table/alias: 't1_base_0_A'
[3 Mar 2010 14:29]
Kristofer Pettersson
Changing test case to: CREATE TABLE `table0_int_autoinc` ( `int` int, pk integer auto_increment, `int_key` int, primary key (pk), key (`int_key` )); SET AUTOCOMMIT=OFF; CREATE TABLE IF NOT EXISTS t1_base_0_A LIKE test . table0_int_autoinc ; CREATE TABLE IF NOT EXISTS t1_base_1_A LIKE test . table0_int_autoinc ; CREATE TABLE IF NOT EXISTS t1_merge_1_A LIKE test . table0_int_autoinc ; ALTER TABLE t1_merge_1_A ENGINE = MERGE UNION ( t1_base_0_A , t1_base_1_A ); CREATE VIEW t1_view_0_A AS SELECT `int_key` FROM t1_base_0_A A NATURAL JOIN t1_merge_1_A B ; INSERT INTO t1_view_0_A ( `int`,`pk`,`int_key` ) SELECT `int`,`pk`,`int_key` FROM t1_base_0_A; ..causes crash on Ubuntu and MacOSX similar to the one described.
[23 Mar 2010 9:51]
Kristofer Pettersson
Reduced test case: CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 LIKE t1; SET AUTOCOMMIT=OFF; CREATE VIEW t1_view AS SELECT c1 FROM t1 NATURAL JOIN t2 ; # Crashes if other fields than the ones listed in the view definition are used. INSERT INTO t1_view ( `c1`, `c2` ) SELECT `c1`, `c2` FROM t1;
[23 Mar 2010 15:55]
Kristofer Pettersson
The above test should fail because of ER_BAD_FIELD_ERROR in find_field_in_view() called during setup_fields(), but handle_insert() isn't checked for return code before we attempt to invalidate the query cache. The error changes the prerequisite for the table list and this triggers the assertion in query cache invalidation.
[24 Mar 2010 8:37]
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/104152 3368 Kristofer Pettersson 2010-03-24 Bug#46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table If the listed columns in the view definition of the table used in a 'INSERT .. SELECT ..' statement mismatched, a debug assertion would trigger in the cache invalidation code following the failing statement. Although the find_field_in_view() function correctly generated ER_BAD_FIELD_ERROR during setup_fields(), the error failed to propagate further than handle_select(). This patch fixes the issue by adding a check for the return value. @ mysql-test/r/query_cache_with_views.result * added test for bug 46615 @ mysql-test/t/query_cache_with_views.test * added test for bug 46615 @ sql/sql_parse.cc * added check for handle_select() return code before attempting to invalidate the cache.
[24 Mar 2010 16:31]
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/104241 3368 Kristofer Pettersson 2010-03-24 Bug#46615 Assertion in Query_cache::invalidate in INSERT in a VIEW of a MERGE table If the listed columns in the view definition of the table used in a 'INSERT .. SELECT ..' statement mismatched, a debug assertion would trigger in the cache invalidation code following the failing statement. Although the find_field_in_view() function correctly generated ER_BAD_FIELD_ERROR during setup_fields(), the error failed to propagate further than handle_select(). This patch fixes the issue by adding a check for the return value. @ mysql-test/r/query_cache_with_views.result * added test for bug 46615 @ mysql-test/t/query_cache_with_views.test * added test for bug 46615 @ sql/sql_parse.cc * added check for handle_select() return code before attempting to invalidate the cache.
[6 Apr 2010 8:01]
Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:holyfoot@mysql.com-20100324121447-c96zkpqssj59n4in) (merge vers: 5.1.46) (pib:16)
[15 Apr 2010 16:28]
Paul DuBois
Noted in 5.1.46 changelog. In debug builds, if the listed columns in the view definition of the table used in an INSERT ... SELECT statement mismatched, an assertion was raised in the query cache invalidation code following the failing statement. Setting report to Need Merge pending push to Celosia.
[28 May 2010 6:04]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100422150750-vp0n37kp9ywq5ghf) (pib:16)
[28 May 2010 7:00]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100402151743-xowc2u930h729jsy) (merge vers: 5.5.4-m3) (pib:16)
[30 May 2010 1:12]
Paul DuBois
Noted in 5.5.5 changelog.
[17 Jun 2010 12:08]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:54]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:35]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Description: When inserting in a view that references a merge table and autocommit=off, mysqld asserts as follows: mysqld: sql_cache.cc:1649: void Query_cache::invalidate(THD*, TABLE_LIST*, my_bool): Assertion `!using_transactions || tables_used->table!=0' failed. #6 0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6 #7 0x00000000007e1958 in Query_cache::invalidate (this=0xf80cc0, thd=0x7fb96802d4a8, tables_used=0x219c0b0, using_transactions=1 '\001') at sql_cache.cc:1649 #8 0x0000000000657351 in mysql_execute_command (thd=0x7fb96802d4a8) at sql_parse.cc:3206 #9 0x000000000065d3eb in mysql_parse (thd=0x7fb96802d4a8, inBuf=0x219bf38 "INSERT INTO t1_view_0_A ( `int`,`pk`,`int_key` ) SELECT `int`,`pk`,`int_key` FROM t1_base_0_A", length=94, found_semicolon=0x7fb96eebeed0) at sql_parse.cc:5931 #10 0x000000000065e215 in dispatch_command (command=COM_QUERY, thd=0x7fb96802d4a8, packet=0x7fb968081809 "INSERT INTO t1_view_0_A ( `int`,`pk`,`int_key` ) SELECT `int`,`pk`,`int_key` FROM t1_base_0_A", packet_length=94) at sql_parse.cc:1213 #11 0x000000000065f5e4 in do_command (thd=0x7fb96802d4a8) at sql_parse.cc:854 #12 0x000000000064bf15 in handle_one_connection (arg=0x7fb96802d4a8) at sql_connect.cc:1127 #13 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0 #14 0x000000315a4e627d in clone () from /lib64/libc.so.6 How to repeat: Note that this case is not very realistic: * autocommit is OFF but engine is MyISAM; * The view is a join between the merge table and one of its parts; CREATE TABLE `table0_int_autoinc` ( `int` int, pk integer auto_increment, `int_key` int, primary key (pk), key (`int_key` )); SET AUTOCOMMIT=OFF; CREATE TABLE IF NOT EXISTS t1_base_0_A LIKE test . table0_int_autoinc ; CREATE TABLE IF NOT EXISTS t1_base_0_A LIKE test . table0_int_autoinc ; CREATE TABLE IF NOT EXISTS t1_merge_1_A LIKE test . table0_int_autoinc ; ALTER TABLE t1_merge_1_A ENGINE = MERGE UNION ( t1_base_0_A , t1_base_0_A ); CREATE VIEW t1_view_0_A AS SELECT `int_key` FROM t1_base_0_A A NATURAL JOIN t1_merge_1_A B ; INSERT INTO t1_view_0_A ( `int`,`pk`,`int_key` ) SELECT `int`,`pk`,`int_key` FROM t1_base_0_A;