Bug #47633 assert in ha_myisammrg::info during OPTIMIZE
Submitted: 24 Sep 2009 19:47 Modified: 16 Aug 2010 11:08
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:6.0.14, 5.6.99, 5.5.5 OS:Any
Assigned to: Ingo Strüwing CPU Architecture:Any
Tags: merge
Triage: Triaged: D2 (Serious)

[24 Sep 2009 19:47] Matthias Leich
Description:
The assertion happens in ha_myisammrg.cc line 1137:
  DBUG_ASSERT(this->file->children_attached);
The problem was found during RQG tests and the assertion
occured during REPAIR, OPTIMIZE, ANALYZE ...

My script:
----------
--disable_abort_on_error
CREATE TEMPORARY TABLE t1  ( f1 INT );
ALTER TABLE t1 ENGINE = MERGE UNION (t_not_exists , t1 );
OPTIMIZE TABLE t1;

Result on mysql-6.0-codebase-bugfixing 2009-09-24
-------------------------------------------------
CREATE TEMPORARY TABLE t1  ( f1 INT );
ALTER TABLE t1 ENGINE = MERGE UNION (t_not_exists , t1 );
<--- IMHO the ALTER must fail.
  - The table t_not_exists does not exist.
  - t1 cannot consist of itself.
OPTIMIZE TABLE t1;
ERROR HY000: Lost connection to MySQL server during query

Thread 1 (process 2590):
#0  0x00007f9a701a4ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b75224 in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006f3e24 in handle_segfault (sig=6) at mysqld.cc:2755
#3  <signal handler called>
#4  0x00007f9a6f0a05c5 in raise () from /lib64/libc.so.6
#5  0x00007f9a6f0a1bb3 in abort () from /lib64/libc.so.6
#6  0x00007f9a6f0991e9 in __assert_fail () from /lib64/libc.so.6
#7  0x0000000000a4ac1f in ha_myisammrg::info (this=0x1697cb0, flag=8) at ha_myisammrg.cc:1137
#8  0x000000000088916f in mysql_admin_table (thd=0x16170a8, tables=0x16f0ef0, check_opt=0x1619748, operator_name=0xd745fd "optimize", lock_type=TL_WRITE, open_for_modify=true, no_warnings_for_error=false, extra_open_options=0, prepare_func=0,
    operator_func=0x85805c <handler::ha_optimize(THD*, st_ha_check_opt*)>, view_operator_func=0) at sql_table.cc:4958
#9  0x00000000008897c0 in mysql_optimize_table (thd=0x16170a8, tables=0x16f0ef0, check_opt=0x1619748) at sql_table.cc:5007
#10 0x0000000000709060 in mysql_execute_command (thd=0x16170a8) at sql_parse.cc:3086
#11 0x000000000070f0da in mysql_parse (thd=0x16170a8, inBuf=0x16f0e20 "OPTIMIZE TABLE t1", length=17, found_semicolon=0x40e89f20) at sql_parse.cc:5991
#12 0x000000000070fd16 in dispatch_command (command=COM_QUERY, thd=0x16170a8, packet=0x162a209 "", packet_length=17) at sql_parse.cc:1074
#13 0x00000000007111dd in do_command (thd=0x16170a8) at sql_parse.cc:756
#14 0x00000000006fdfe4 in handle_one_connection (arg=0x16170a8) at sql_connect.cc:1164
#15 0x00007f9a701a0040 in start_thread () from /lib64/libpthread.so.0
#16 0x00007f9a6f14108d in clone () from /lib64/libc.so.6
#17 0x0000000000000000 in ?? () 

Result on mysql-5.1-bugteam 2009-09-16
--------------------------------------
CREATE TEMPORARY TABLE t1  ( f1 INT );
ALTER TABLE t1 ENGINE = MERGE UNION (t_not_exists , t1 );
ERROR 42000: Not unique table/alias: 't1'
<--- I expect to see the same error in newer MySQL versions.
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	Table is already up to date

My environment:
---------------
- MySQL compiled from source
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above
[9 Oct 2009 14:16] Konstantin Osipov
Ingo, could you please take a look at this bug?
That's a regression you or me introduced in one of our patches.
Thank you,
-kostja
[30 Nov 2009 15: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/92114

2899 Ingo Struewing	2009-11-30
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      
      The server crashed on an attempt to optimize a MERGE table with
      non-existent child table.
      
      mysql_admin_table() relied on the table to be successfully open
      if a table object had been allocated.
      
      Changed code to check return value of the open function before
      calling a handler:: function on it.
      
      This patch includes fixes to experimental test cases, which are
      unrelated to this bug report. The test case main.merge is
      fixed so that it is removed from the experimental list.
     @ mysql-test/collections/default.experimental
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Removed main.merge.
     @ mysql-test/r/merge.result
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Updated result file.
     @ mysql-test/suite/rpl/r/rpl_backup_multi.result
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Updated result file due to unrelated changes.
     @ mysql-test/suite/rpl/r/rpl_backup_shutdown.result
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Updated result file due to unrelated changes.
     @ mysql-test/suite/rpl/t/rpl_backup_multi.test
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Added suppression for disabled event warning (unrelated).
     @ mysql-test/suite/rpl/t/rpl_backup_shutdown.test
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Added suppression for disabled event warning (unrelated).
     @ mysql-test/t/merge.test
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Disabled failing SELECT with MERGE and function, which made this
        test file experimental (unrelated).
        Changed tests to respect changed TEMPORARY MERGE locking (unrelated).
        Changed tests to respect changed CREATE TABLE ... LIKE (unrelated).
        Changed tests to respect that no new tables can be created
        under LOCK TABLE (unrelated).
        Added test for Bug#47633.
     @ sql/sql_table.cc
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Do not call handler:: functions if the table was not opened
        successfully.
[3 Dec 2009 19:44] Ingo Strüwing
The above patch works for Linux. On Windows it reveals a new problem.

In the (new) test sequence:

CREATE TABLE tt1 (c1 INT, c2 INT) ENGINE=MyISAM;
CREATE TABLE tt2 (c1 INT, c2 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE mm1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(tt1,tt2)
  INSERT_METHOD=LAST;
INSERT INTO tt1 VALUES (111, 121);
INSERT INTO mm1 VALUES (211, 221);
INSERT INTO mm1 VALUES (212, 222);
INSERT INTO mm1 VALUES (213, 223);
CREATE TABLE mm2 LIKE mm1;
INSERT INTO mm2 SELECT * FROM mm1 LIMIT 40;
SELECT * FROM mm2;
DELETE FROM tt2 LIMIT 40;
OPTIMIZE TABLE tt2;

optimize fails at the end of mi_repair, when it cannot replace the old data file by the new one, because the old one doesn't exist any more. I'll have to figure out, where and why the old file vanishes. This problem does not happen on Linux.
[23 Dec 2009 20: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/95580

2926 Ingo Struewing	2009-12-23
      Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      
      The server crashed on an attempt to optimize a MERGE table with
      non-existent child table.
      
      mysql_admin_table() relied on the table to be successfully open
      if a table object had been allocated.
      
      Changed code to check return value of the open function before
      calling a handler:: function on it.
      
      This patch includes fixes to the test case main.merge.
      This patch includes a fix for memory mapped data files on Windows.
      This patch includes a fix for error reporting in nt_share_delete().
     @ mysql-test/r/merge.result
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Updated result file.
     @ mysql-test/t/merge.test
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Changed tests to respect changed TEMPORARY MERGE locking (unrelated).
        Changed tests to respect changed CREATE TABLE ... LIKE (unrelated).
        Changed tests to respect that no new tables can be created
        under LOCK TABLE (unrelated).
        Added test for Bug#47633.
     @ mysys/my_delete.c
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Fixed error reporting.
        Fixed indentation.
     @ mysys/my_mmap.c
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Added DBUG.
     @ sql/sql_table.cc
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Do not call handler:: functions if the table was not opened
        successfully.
     @ storage/myisam/mi_check.c
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        Unmap memory before exchanging data files. Needed on Windows.
[5 Jan 2010 17:33] Ritheesh Vedire
Patch Approved.
[5 Jan 2010 18:47] Ingo Strüwing
Queued to mysql-6.0-backup.
[20 Feb 2010 9:19] Bugs System
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)
[24 Feb 2010 15:26] Tony Bedford
An entry has been added to the 6.0.14 changelog:

        An assertion occurred in <filename>ha_myisammrg.cc</filename>
        line 1137:

DBUG_ASSERT(this->file->children_attached);

        The problem was found while running RQG tests and the assertion
        occurred during REPAIR, OPTIMIZE, and ANALYZE
        operations.
[9 Mar 2010 11:53] 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/102690

3124 Ingo Struewing	2010-03-09
      Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
        
        The server crashed on an attempt to optimize a MERGE table with
        non-existent child table.
        
        mysql_admin_table() relied on the table to be successfully open
        if a table object had been allocated.
        
        Changed code to check return value of the open function before
        calling a handler:: function on it.
        
        This patch includes fixes to the test case main.merge.
        This patch includes a fix for memory mapped data files on Windows.
        This patch includes a fix for error reporting in nt_share_delete().
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Updated result file.
     @ mysql-test/r/merge_sync.result
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Updated test result.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Changed tests to respect changed TEMPORARY MERGE locking (unrelated).
            Changed tests to respect changed CREATE TABLE ... LIKE (unrelated).
            Changed tests to respect that no new tables can be created
            under LOCK TABLE (unrelated).
            Added test for Bug#47633.
        Changed error numbers to symbolic names.
        Added test for child locking for ALTER under LOCK TABLE.
     @ mysql-test/t/merge_sync.test
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Fixed Debug Sync point for changed table locking.
        Commented out processlist, which can be unreliable.
     @ mysys/my_delete.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Fixed error reporting.
            Fixed indentation.
     @ mysys/my_mmap.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Added DBUG.
     @ sql/item_func.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Added Debug Sync point, required by merge_sync.test.
     @ sql/sql_table.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Do not call handler:: functions if the table was not opened
            successfully.
        Added Debug Sync point, required by merge_sync.test.
     @ storage/myisam/mi_check.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Unmap memory before exchanging data files. Needed on Windows.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Added Debug Sync point, required by merge_sync.test.
[26 May 2010 9:55] Konstantin Osipov
The fix is not in any main tree.
[26 May 2010 10:07] Konstantin Osipov
Please verify if the bug is repeatable in 5.5
[26 May 2010 10:12] Sveta Smirnova
Crash still repeatable with mysql-next-mr
[26 May 2010 11:19] Sveta Smirnova
mysql-trunk-bugfixing also crashes.
[31 May 2010 14:53] 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/109628

3082 Alexander Nozdrin	2010-05-31
      Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
      
        The server crashed on an attempt to optimize a MERGE table with
        non-existent child table.
      
        mysql_admin_table() relied on the table to be successfully open
        if a table object had been allocated.
      
        Changed code to check return value of the open function before
        calling a handler:: function on it.
      
        This patch includes fixes to the test case main.merge.
        This patch includes a fix for memory mapped data files on Windows.
        This patch includes a fix for error reporting in nt_share_delete().
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Updated result file.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Changed tests to respect changed TEMPORARY MERGE locking (unrelated).
            Changed tests to respect changed CREATE TABLE ... LIKE (unrelated).
            Changed tests to respect that no new tables can be created
            under LOCK TABLE (unrelated).
            Added test for Bug#47633.
        Changed error numbers to symbolic names.
        Added test for child locking for ALTER under LOCK TABLE.
        
        Since Bug 36171 is not pushed yet, not the whole patch has been backported.
     @ mysys/my_delete.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Fixed error reporting.
            Fixed indentation.
     @ mysys/my_mmap.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Added DBUG.
     @ sql/item_func.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Added Debug Sync point, required by merge_sync.test.
     @ sql/sql_table.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Do not call handler:: functions if the table was not opened
            successfully.
        Added Debug Sync point, required by merge_sync.test.
     @ storage/myisam/mi_check.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Unmap memory before exchanging data files. Needed on Windows.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Added Debug Sync point, required by merge_sync.test.
        
        merge_sync.test will be introduced by a patch for Bug 36171,
        which is not pushed yet.
[25 Jun 2010 15:33] 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/112235

3075 Alexander Nozdrin	2010-06-25
      Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
         Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
       
         The server crashed on an attempt to optimize a MERGE table with
         non-existent child table.
       
         mysql_admin_table() relied on the table to be successfully open
         if a table object had been allocated.
       
         Changed code to check return value of the open function before
         calling a handler:: function on it.
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Updated result file.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Changed tests to respect changed TEMPORARY MERGE locking (unrelated).
            Changed tests to respect changed CREATE TABLE ... LIKE (unrelated).
            Changed tests to respect that no new tables can be created
            under LOCK TABLE (unrelated).
            Added test for Bug#47633.
        Changed error numbers to symbolic names.
        Added test for child locking for ALTER under LOCK TABLE.
        
        Since Bug 36171 is not pushed yet, not the whole patch has been backported.
     @ mysys/my_delete.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Fixed error reporting.
            Fixed indentation.
     @ mysys/my_mmap.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Added DBUG.
     @ sql/item_func.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Added Debug Sync point, required by merge_sync.test.
     @ sql/sql_table.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Do not call handler:: functions if the table was not opened
            successfully.
        Added Debug Sync point, required by merge_sync.test.
     @ storage/myisam/mi_check.c
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
            Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
            Unmap memory before exchanging data files. Needed on Windows.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@sun.com-20091223200354-r2uzbdkj2v6yv111
        Added Debug Sync point, required by merge_sync.test.
        
        merge_sync.test will be introduced by a patch for Bug 36171,
        which is not pushed yet.
[25 Jun 2010 16:58] Konstantin Osipov
Pushed into next-mr-bugfixing.
[23 Jul 2010 12:23] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[16 Aug 2010 11:08] Tony Bedford
Also added to 5.5.6 changelog.