Bug #35068 Assertion fails when reading from i_s.tables and there is incorrect merge table

Description:
mysqld: sql_class.cc:2750: void THD::restore_backup_open_tables_state(Open_tables_state*): Assertion `open_tables == 0 && temporary_tables == 0 && handler_tables == 0 && derived_tables == 0 && lock == 0 && locked_tables == 0 && prelocked_mode == NON_PRELOCKED' failed.

This is repeatable at least with recent mysql-5.1-engines tree. Not sure if it would be repeatable with the main tree.

How to repeat:
BUILD/compile-pentium-debug-max

create table m1(a int) engine=merge union=(t1);
select * from information_schema.tables;

Thank you for the report.

Verified as described using 5.1 and 6.0 main trees.

Version 5.0 is not affected.

Only debug binaries are affected.

Looks like inconsistency between I_S and new MERGE handling.
It's easy to fix in open_tables(), less easy in get_all_tables().

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/44200

ChangeSet@1.2565, 2008-03-18 19:37:57+01:00, istruewing@stella.local +5 -0
  Bug#35068 - Assertion fails when reading from i_s.tables
              and there is incorrect merge table
  
  SELECT * on INFORMATION_SCHEMA.TABLES crashed the server when
  a MERGE table included a non-existent MyISAM table.
  
  The MERGE table adds its children to the table list so that
  open_tables() opens them too. If a child cannot be opened,
  open_tables() returns with the open MERGE table and opened
  MyISAM tables as far as they could be opened. However, the
  returned error message was ER_NO_SUCH_TABLE.
  
  get_all_tables() from information schema assumes that it opens
  one table at a time. If it cannot be opened, and the error is
  ER_NO_SUCH_TABLE, it assumes that no table needs to be closed.
  
  Fixed error handling for MERGE tables in open_tables(). If
  open_tables() failes due to an open error of a child, it converts
  the error to ER_ADMIN_WRONG_MRG_TABLE. This does also improve
  the diagnosis in other cases.

ok to push

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/46333

ChangeSet@1.2568, 2008-05-03 21:51:42+02:00, istruewing@stella.local +3 -0
  Bug#35068 - Assertion fails when reading from i_s.tables
              and there is incorrect merge table
    
  SELECT * on INFORMATION_SCHEMA.TABLES crashed the server when
  a MERGE table included a non-existent MyISAM table.
    
  The MERGE table adds its children to the table list so that
  open_tables() opens them too. If a child cannot be opened,
  open_tables() returns with the open MERGE table and opened
  MyISAM tables as far as they could be opened. However, the
  returned error message is ER_NO_SUCH_TABLE.
    
  get_all_tables() from information schema assumed that it opens
  one table at a time. If it cannot be opened, and the error is
  ER_NO_SUCH_TABLE, it assumed that no table needs to be closed.
    
  Added condition !thd->open_tables to detect open failure
  of MERGE table. So if any table was opened, closing will
  not be skipped.

According to our previous discussion we should use less-informative error message(ER_WRONG_MRG_TABLE instead of ER_NO_SUCH_TABLE).
Your fix does not hide ER_NO_SUCH_TABLE.
Another problem is that open_normal_and_derived_tables() returns
misleading error message IMHO. Why should we return ER_NO_SUCH_TABLE for test.t1
if we try to open tm1 table?

Example:
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
SELECT * FROM tm1;
ERROR 42S02: Table 'test.t1' doesn't exist

So the message looks strange for me.
I propose a patch below which fixes problem above and
the problem described in the bug report.
It is similar to algorithm view does.

Suggested fix for bug#35068

Attachment: p.patch (application/octet-stream, text), 5.86 KiB.

please commit a changeset with your patch. looks ok so far

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/54196

2739 Sergey Glukhov	2008-09-16
      Bug#35068 Assertion fails when reading from i_s.tables and there is incorrect merge table
      Hide "Table doesn't exist" errors if the table belongs to a merge table.

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/55883

2771 Sergey Glukhov	2008-10-09
      Bug#35068 Assertion fails when reading from i_s.tables and there is incorrect merge table
      Hide "Table doesn't exist" errors if the table belongs to a merge table.

Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081009085516-5i4dv8hwtdxm0fbm) (version source revid:sergey.glukhov@sun.com-20081009091302-q6w7nz07eodal02o) (pib:5)

Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081009085516-5i4dv8hwtdxm0fbm) (version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)

The versions are actually 5.1.31, 6.0.9.

Noted in 5.1.31, 6.0.9 changelogs.

Selecting from an INFORMATION_SCHEMA table into an incorrectly
defined MERGE table caused an assertion failure.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)