Bug #47648 main.merge fails sporadically
Submitted: 25 Sep 2009 13:23 Modified: 8 Mar 2010 11:37
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:6.0, mysql-next-4284 OS:Any
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: experimental, pushbuild, sporadic, test failure

[25 Sep 2009 13:23] Alexander Nozdrin
Description:
The test case made experimental due to this bug.

main.merge [ fail ]
        Test ended at 2009-09-24 07:54:17

CURRENT_TEST: main.merge
mysqltest: At line 287: query 'SELECT * FROM t4 WHERE c1 < f1()' failed: 1146: Table 'test.t3' doesn't exist

The result from queries just before the failure was:
< snip >
CREATE TABLE t3 (c1 INT NOT NULL);
INSERT INTO t3 VALUES (3), (33);
LOCK TABLES t3 READ;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST SELECT * FROM t3;
ERROR HY000: 'test.t4' is not BASE TABLE
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
UNLOCK TABLES;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t4 SELECT * FROM t3;
# Alter temporary MERGE table.
ALTER TABLE t4 UNION=(t1);
LOCK TABLES t4 WRITE;
# Alter temporary MERGE table under LOCk tables.
ALTER TABLE t4 UNION=(t1,t2);
UNLOCK TABLES;
# MERGE table and function.
CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);

More results from queries before failure can be found in G:\pb2\test\sb_1-764251-1253762384.78\mysql-6.0.14-alpha-win-x86-test\mysql-test\var-ps_row\log\merge.log

 - saving 'G:/pb2/test/sb_1-764251-1253762384.78/mysql-6.0.14-alpha-win-x86-test/mysql-test/var-ps_row/log/main.merge/' to 'G:/pb2/test/sb_1-764251-1253762384.78/mysql-6.0.14-alpha-win-x86-test/mysql-test/var-ps_row/log/main.merge/'

Retrying test, attempt(2/3)...

main.merge                               [ retry-fail ]
        Test ended at 2009-09-24 07:54:19

CURRENT_TEST: main.merge
mysqltest: At line 287: query 'SELECT * FROM t4 WHERE c1 < f1()' failed: 1146: Table 'test.t3' doesn't exist

The result from queries just before the failure was:
< snip >
CREATE TABLE t3 (c1 INT NOT NULL);
INSERT INTO t3 VALUES (3), (33);
LOCK TABLES t3 READ;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST SELECT * FROM t3;
ERROR HY000: 'test.t4' is not BASE TABLE
SELECT * FROM t4;
ERROR HY000: Table 't4' was not locked with LOCK TABLES
UNLOCK TABLES;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t4 SELECT * FROM t3;
# Alter temporary MERGE table.
ALTER TABLE t4 UNION=(t1);
LOCK TABLES t4 WRITE;
# Alter temporary MERGE table under LOCk tables.
ALTER TABLE t4 UNION=(t1,t2);
UNLOCK TABLES;
# MERGE table and function.
CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);

More results from queries before failure can be found in G:\pb2\test\sb_1-764251-1253762384.78\mysql-6.0.14-alpha-win-x86-test\mysql-test\var-ps_row\log\merge.log

 - saving 'G:/pb2/test/sb_1-764251-1253762384.78/mysql-6.0.14-alpha-win-x86-test/mysql-test/var-ps_row/log/main.merge/' to 'G:/pb2/test/sb_1-764251-1253762384.78/mysql-6.0.14-alpha-win-x86-test/mysql-test/var-ps_row/log/main.merge/'

Test has failed 2 times, no more retries!

How to repeat:
XRef: http://pb2.norway.sun.com/web.py?template=mysql_show_test_failure&test_failure_id=2337249&...
[25 Sep 2009 13:24] Alexander Nozdrin
Bug#47139 seems to be a different problem.
[13 Oct 2009 7:03] Tor Didriksen
./mtr --comment=ps    --force --timer         --skip-ndbcluster --ps-protocol --valgrind-mysqld main.merge

We are reading free memory:

==6003== Thread 4:
==6003== Invalid read of size 4
==6003==    at 0x82E735A: Query_tables_list::first_not_own_table() (sql_lex.h:992)
==6003==    by 0x82D8E11: check_table_access(THD*, unsigned long, TABLE_LIST*, bool, bool, unsigned) (sql_parse.cc:5376)
==6003==    by 0x82DBE66: mysql_execute_command(THD*) (sql_parse.cc:2108)
==6003==    by 0x838D974: Prepared_statement::execute(String*, bool) (sql_prepare.cc:3766)
==6003==    by 0x8391828: Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*) (sql_prepare.cc:3398)
==6003==    by 0x8391CFC: mysqld_stmt_execute(THD*, char*, unsigned) (sql_prepare.cc:2519)
==6003==    by 0x82E56D6: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1028)
==6003==    by 0x82E6AEA: do_command(THD*) (sql_parse.cc:756)
==6003==    by 0x82D3631: handle_one_connection (sql_connect.cc:1164)
==6003==    by 0xB0551E: start_thread (in /lib/libpthread-2.9.so)
==6003==    by 0x410704D: clone (in /lib/libc-2.9.so)
==6003==  Address 0x58a2f04 is 20 bytes inside a block of size 1,072 free'd
==6003==    at 0x400590A: free (vg_replace_malloc.c:323)
==6003==    by 0x871CEE9: my_no_flags_free (my_malloc.c:65)
==6003==    by 0x870D814: free_root (my_alloc.c:349)
==6003==    by 0x82E6847: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1438)
==6003==    by 0x82E6AEA: do_command(THD*) (sql_parse.cc:756)
==6003==    by 0x82D3631: handle_one_connection (sql_connect.cc:1164)
==6003==    by 0xB0551E: start_thread (in /lib/libpthread-2.9.so)
==6003==    by 0x410704D: clone (in /lib/libc-2.9.so)
[19 Oct 2009 14:13] Tor Didriksen
Simplified test case:

CREATE TABLE t1 (c1 INT NOT NULL);
CREATE TABLE t2 (c1 INT NOT NULL);
CREATE TABLE t3 (c1 INT NOT NULL);

CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);

CREATE TABLE t4 (c1 INT NOT NULL)
ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST
;

SELECT * FROM t4 WHERE c1 < f1();
DROP FUNCTION f1;
DROP TABLE t4, t3, t2, t1;
[19 Oct 2009 14:18] Tor Didriksen
Adding this to  Prepared_statement::cleanup_stmt()
   delete lex->sphead;
   lex->sphead= 0;
+  lex->mark_as_requiring_prelocking(NULL);
   /* The order is important */
   lex->unit.cleanup();
   cleanup_items(free_list);

avoids the SEGFAULT (don't point to something which has been freed).
However: the reprepare logic doesn't work.
We reprepare 3 times, and then give up.
[19 Nov 2009 14:04] Tor Didriksen
Commented out the query which fails:
bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-codebase-bugfixing/
revision-id: tor.didriksen@sun.com-20091118150615-csn3i1sri0bhhabl
[15 Feb 2010 14: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/100404

3100 Konstantin Osipov	2010-02-15
      A fix and a test case for Bug#47648 "main.merge fails sporadically".
      If a prepared statement used both a MyISAMMRG table and a stored 
      function or trigger, execution could fail with "No such table"
      error. 
      The error would come from a failure of the MyISAMMRG engine
      to meet the expectations of the prelocking algorithm, 
      in particular maintain lex->query_tables_own_last pointer
      in sync with lex->query_tables_last pointer/the contents
      of lex->query_tables. When adding merge children, the merge
      engine would extend the table list. Then, when adding 
      prelocked tables, the prelocking algorithm would use a pointer
      to the last merge child to assign to lex->query_tables_own_last.
      Then, when merge children were removed at the end of
      open_tables(), lex->query_tables_own_last
      was not updated, and kept pointing
      to a removed merge child.
      
      The fix ensures that query_tables_own_last is always in
      sync with lex->query_talbes_last.
     @ mysql-test/r/merge.result
        Update results (Bug#47648).
     @ mysql-test/t/merge.test
        Add a test case for Bug#47648.
        Update the result file to reflect a fix of another bug
        in MyISAMMRG code: not maintaining lex->query_tables_own_last
        allowed a stored function or trigger to modify a merge table
        which was already updated by the main statement.
        It is not allowed for other storage engines, and should
        not be allowed for MyISAMMRG.
     @ storage/myisammrg/ha_myisammrg.cc
        When adding children to the list of tables to open,
        make sure that we properly set lex->query_tables_own_last.
        When removing the children, update lex->query_tables_own_last
        if necessary.
[15 Feb 2010 15:54] 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/100416

3100 Konstantin Osipov	2010-02-15
      A fix and a test case for Bug#47648 "main.merge fails sporadically".
      If a prepared statement used both a MyISAMMRG table and a stored 
      function or trigger, execution could fail with "No such table"
      error or crash. 
      The error would come from a failure of the MyISAMMRG engine
      to meet the expectations of the prelocking algorithm, 
      in particular maintain lex->query_tables_own_last pointer
      in sync with lex->query_tables_last pointer/the contents
      of lex->query_tables. When adding merge children, the merge
      engine would extend the table list. Then, when adding 
      prelocked tables, the prelocking algorithm would use a pointer
      to the last merge child to assign to lex->query_tables_own_last.
      Then, when merge children were removed at the end of
      open_tables(), lex->query_tables_own_last
      was not updated, and kept pointing
      to a removed merge child.
      
      The fix ensures that query_tables_own_last is always in
      sync with lex->query_talbes_last.
      
      This is a regression introduced by WL#4144 and present only
      in next-4284 tree and 6.0.
     @ mysql-test/r/merge.result
        Update results (Bug#47648).
     @ mysql-test/t/merge.test
        Add a test case for Bug#47648.
        Update the result file to reflect a fix of another bug
        in MyISAMMRG code: not maintaining lex->query_tables_own_last
        allowed a stored function or trigger to modify a merge table
        which was already updated by the main statement.
        It is not allowed for other storage engines, and should
        not be allowed for MyISAMMRG.
     @ storage/myisammrg/ha_myisammrg.cc
        When adding children to the list of tables to open,
        make sure that we properly set lex->query_tables_own_last.
        When removing the children, update lex->query_tables_own_last
        if necessary.
[15 Feb 2010 16:12] Dmitry Lenev
It is OK to push this patch after fixing two typos mentioned in an "OK to push" e-mail.
[15 Feb 2010 16:14] 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/100420

3100 Konstantin Osipov	2010-02-15
      A fix and a test case for Bug#47648 "main.merge fails sporadically".
      If a prepared statement used both a MyISAMMRG table and a stored 
      function or trigger, execution could fail with "No such table"
      error or crash. 
      The error would come from a failure of the MyISAMMRG engine
      to meet the expectations of the prelocking algorithm, 
      in particular maintain lex->query_tables_own_last pointer
      in sync with lex->query_tables_last pointer/the contents
      of lex->query_tables. When adding merge children, the merge
      engine would extend the table list. Then, when adding 
      prelocked tables, the prelocking algorithm would use a pointer
      to the last merge child to assign to lex->query_tables_own_last.
      Then, when merge children were removed at the end of
      open_tables(), lex->query_tables_own_last
      was not updated, and kept pointing
      to a removed merge child.
      
      The fix ensures that query_tables_own_last is always in
      sync with lex->query_tables_last.
      
      This is a regression introduced by WL#4144 and present only
      in next-4284 tree and 6.0.
     @ mysql-test/r/merge.result
        Update results (Bug#47648).
     @ mysql-test/t/merge.test
        Add a test case for Bug#47648.
        Update the result file to reflect a fix of another bug
        in MyISAMMRG code: not maintaining lex->query_tables_own_last
        allowed a stored function or trigger to modify a merge table
        which was already updated by the main statement.
        It is not allowed for other storage engines, and should
        not be allowed for MyISAMMRG.
     @ storage/myisammrg/ha_myisammrg.cc
        When adding children to the list of tables to open,
        make sure that we properly set lex->query_tables_own_last.
        When removing the children, update lex->query_tables_own_last
        if necessary.
[15 Feb 2010 16:25] 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/100422

3768 Konstantin Osipov	2010-02-15 [merge]
      Merge next-4284 -> 6.0-codebase-4284 (Bug#47648)
[15 Feb 2010 16:36] 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/100423

3100 Konstantin Osipov	2010-02-15
      A fix and a test case for Bug#47648 "main.merge fails sporadically".
      If a prepared statement used both a MyISAMMRG table and a stored 
      function or trigger, execution could fail with "No such table"
      error or crash. 
      The error would come from a failure of the MyISAMMRG engine
      to meet the expectations of the prelocking algorithm, 
      in particular maintain lex->query_tables_own_last pointer
      in sync with lex->query_tables_last pointer/the contents
      of lex->query_tables. When adding merge children, the merge
      engine would extend the table list. Then, when adding 
      prelocked tables, the prelocking algorithm would use a pointer
      to the last merge child to assign to lex->query_tables_own_last.
      Then, when merge children were removed at the end of
      open_tables(), lex->query_tables_own_last
      was not updated, and kept pointing
      to a removed merge child.
      
      The fix ensures that query_tables_own_last is always in
      sync with lex->query_tables_last.
      
      This is a regression introduced by WL#4144 and present only
      in next-4284 tree and 6.0.
     @ mysql-test/r/merge.result
        Update results (Bug#47648).
     @ mysql-test/t/merge.test
        Add a test case for Bug#47648.
        Update the result file to reflect a fix of another bug
        in MyISAMMRG code: not maintaining lex->query_tables_own_last
        allowed a stored function or trigger to modify a merge table
        which was already updated by the main statement.
        It is not allowed for other storage engines, and should
        not be allowed for MyISAMMRG.
     @ storage/myisammrg/ha_myisammrg.cc
        When adding children to the list of tables to open,
        make sure that we properly set lex->query_tables_own_last.
        When removing the children, update lex->query_tables_own_last
        if necessary.
[15 Feb 2010 16:44] 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/100425

3768 Konstantin Osipov	2010-02-15 [merge]
      Merge next-4284 -> 6.0-codebase-4284 (Bug#47648)
[16 Feb 2010 16:48] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20100215164330-7efayi19n4qcemu0) (merge vers: 6.0.14-alpha) (pib:16)
[16 Feb 2010 16:57] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20100215163553-c0cwx9y2ugzarey2) (pib:16)
[18 Feb 2010 10:05] Konstantin Osipov
Queued into mysql-next-mr.
[6 Mar 2010 10:57] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)
[8 Mar 2010 11:37] Tony Bedford
An entry has been added to the 6.0.14 and 5.5.3 changelogs:

        If a prepared statement used both a MyISAM MERGE table and a
        stored function or trigger, execution sometimes failed with a
        "No such table" error.