MySQL Bugs: #48265: MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40)

Bug #48265	MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40)
Submitted:	23 Oct 2009 12:32	Modified:	18 Jun 2010 2:08
Reporter:	Henry Rolofs	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.1.40	OS:	Linux (openSuSE 11.0)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	regression

Description:
i use on Linux Systems (openSUSE 11.1, 2.6.27.23-0.1-pae) a MySQL-Server 5.0.85
(mysql-5.0.85-linux-i686-glibc23) and try to change to MySQL-Server 5.1.40
(mysql-5.1.40-linux-i686-glibc23).

I have problems with merge tables if the table merge table and the "union" tables in different databases and the table name of the "union" tables include a -.

If i try to use the merge table i got an error messages

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

This happens with MySQL 5.1.40 and works without problems with MySQL 5.085

visd1891:/OVOReports/opt/mysql-5.1.40-linux-i686-glibc23 # ./bin/mysql --host 127.0.0.1 --port 3320 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.1.40 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE if exists `db1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP DATABASE if exists `db2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE `db1`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE `db2`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `db1`.`tb_1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `db1`.`tb_2-1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `db2`.`tb_3` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE `db2`.`tb_4-1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `db1`.`mrg1` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_1`);
Query OK, 0 rows affected (0.75 sec)

mysql> CREATE TABLE `db1`.`mrg2` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_2-1`);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `db1`.`mrg3` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_3`);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `db1`.`mrg4` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_4-1`);
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM `db1`.`mrg1`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg2`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg3`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg4`;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql>

Can someone produce it?

Regrads, Henry

How to repeat:
DROP DATABASE if exists `db1`;
DROP DATABASE if exists `db2`;

CREATE DATABASE `db1`;
CREATE DATABASE `db2`;

CREATE TABLE `db1`.`tb_1` (`a` text) ENGINE=MyISAM;
CREATE TABLE `db1`.`tb_2-1` (`a` text) ENGINE=MyISAM;

CREATE TABLE `db2`.`tb_3` (`a` text) ENGINE=MyISAM;
CREATE TABLE `db2`.`tb_4-1` (`a` text) ENGINE=MyISAM;

CREATE TABLE `db1`.`mrg1` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_1`);
CREATE TABLE `db1`.`mrg2` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_2-1`);

CREATE TABLE `db1`.`mrg3` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_3`);
CREATE TABLE `db1`.`mrg4` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_4-1`);

SELECT * FROM `db1`.`mrg1`;
SELECT * FROM `db1`.`mrg2`;
SELECT * FROM `db1`.`mrg3`;
SELECT * FROM `db1`.`mrg4`;

The last, and only the last, select statement produced the error message

ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

mistake in the Version field. changed it from 5.1.41 to 5.1.40

Thank you for the bug report. Verified as described:

miguel@laras:~/dbs$ 5.0/bin/mysql -uroot           
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3                            
Server version: 5.0.88-debug Source distribution         

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE if exists `db1`;
Query OK, 6 rows affected (0.05 sec) 

mysql> DROP DATABASE if exists `db2`;
Query OK, 2 rows affected (0.00 sec) 

mysql> 
mysql> CREATE DATABASE `db1`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE `db2`;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `db1`.`tb_1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)                      

mysql> CREATE TABLE `db1`.`tb_2-1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)                        

mysql> 
mysql> CREATE TABLE `db2`.`tb_3` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)                      

mysql> CREATE TABLE `db2`.`tb_4-1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)                        

mysql>
mysql> CREATE TABLE `db1`.`mrg1` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_1`);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `db1`.`mrg2` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_2-1`);
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> CREATE TABLE `db1`.`mrg3` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_3`);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `db1`.`mrg4` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_4-1`);
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> SELECT * FROM `db1`.`mrg1`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg2`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg3`;
Empty set (0.01 sec)

mysql> SELECT * FROM `db1`.`mrg4`;
Empty set (0.00 sec)

**********************************************************************************

miguel@laras:~/dbs$ 5.1/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1                            
Server version: 5.1.41-debug Source distribution         

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE if exists `db1`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP DATABASE if exists `db2`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 
mysql> CREATE DATABASE `db1`;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE `db2`;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> CREATE TABLE `db1`.`tb_1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.07 sec)                      

mysql> CREATE TABLE `db1`.`tb_2-1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)                        

mysql> 
mysql> CREATE TABLE `db2`.`tb_3` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)                      

mysql> CREATE TABLE `db2`.`tb_4-1` (`a` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> CREATE TABLE `db1`.`mrg1` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_1`);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `db1`.`mrg2` (`a` text) ENGINE=MRG_MyISAM UNION=(`db1`.`tb_2-1`);
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE `db1`.`mrg3` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_3`);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `db1`.`mrg4` (`a` text) ENGINE=MRG_MyISAM UNION=(`db2`.`tb_4-1`);
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> SELECT * FROM `db1`.`mrg1`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg2`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg3`;
Empty set (0.00 sec)

mysql> SELECT * FROM `db1`.`mrg4`;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql>

thanks for the fast verifying.

I have done some more tests.

----------------------------------------------------------------------------------------------------------
FLUSH TABLES;

DROP DATABASE if exists `db_01`;
DROP DATABASE if exists `db_02`;

CREATE DATABASE `db_01`;
CREATE DATABASE `db_02`;

CREATE TABLE `db_01`.`tb_1-1`   (`a` text) ENGINE=MyISAM;
CREATE TABLE `db_02`.`tb_2-1`   (`a` text) ENGINE=MyISAM;

CREATE TABLE `db_01`.`merge_01` (`a` text) ENGINE=MRG_MyISAM UNION=(`db_01`.`tb_1-1`,`db_02`.`tb_2-1`);

SELECT * FROM `db_01`.`merge_01`;

After this statements i have this files on the file system

visd1891:/OVOReports/data/MySQL/OVO-Reporting/data/summary # ls -l db*
db_01:
total 36
-rw-rw---- 1 mysql mysql   61 Oct 26 12:30 db.opt
-rw-rw---- 1 mysql mysql   26 Oct 26 12:30 merge_01.MRG
-rw-rw---- 1 mysql mysql 8554 Oct 26 12:30 merge_01.frm
-rw-rw---- 1 mysql mysql    0 Oct 26 12:30 tb_1@002d1.MYD
-rw-rw---- 1 mysql mysql 1024 Oct 26 12:30 tb_1@002d1.MYI
-rw-rw---- 1 mysql mysql 8554 Oct 26 12:30 tb_1@002d1.frm

db_02:
total 20
-rw-rw---- 1 mysql mysql   61 Oct 26 12:30 db.opt
-rw-rw---- 1 mysql mysql    0 Oct 26 12:30 tb_2@002d1.MYD
-rw-rw---- 1 mysql mysql 1024 Oct 26 12:30 tb_2@002d1.MYI
-rw-rw---- 1 mysql mysql 8554 Oct 26 12:30 tb_2@002d1.frm
----------------------------------------------------------------------------------------------------------

You can see that every - in the table name are substituted with @002d

If we look in the MRG file that contains the names of the underlying tables.

visd1891:/OVOReports/data/MySQL/OVO-Reporting/data/summary # cat db_01/merge_01.MRG
tb_1-1
./db_02/tb_2@002d1

The table name of the 'local' table that are in the same database like the merge table and the 'far' table in the other database are stored different.

The "local" table name is stored as tb_-1 and the "far" table name is stored as "tb_2@002d1".

It looks like that the MRG_MyISAM engine works different for this both table names ("local" and "far" tables).

The stored table name tb_2@002d1 looks "more right" the tb_1-1 because this are the filenames on the file system. But for the MRG_MyISAM engine the  tb_2@002d1 in the MRG-File is the problem.

If i stopped the database and changed manually the MRG-File to 

visd1891:/OVOReports/data/MySQL/OVO-Reporting/data/summary # cat db_01/merge_01.MRG
tb_1-1
./db_02/tb_2-1

and start the database again

then the select statement (SELECT * FROM `db_01`.`merge_01`;) works without an error messages.

It looks like that the MRG-MyISAM engine

1. on the creation of the merge table has an different behavior for tables names ("local" or "far") when it creates the MRG-File.

2. on a access to an existing merge table the underlying table names with the - can processed  and with  @002d can't processed.

Regards, Henry

Hello,

can someone give me an information about the planed time frame for a fix for this bug?

I need this information for our migration from 5.0 to 5.1. The question is, waiting for a patch or stop the migration and switched back to 5.0.

For this decision i need an information about the planed time frame for this fix.

Regards, Henry

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

3372 Sergey Vojtovich	2010-02-08
      BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't
                  work in 5.1.40)
      
      MERGE engine fails to open child table from a different
      database if child table/database name contains characters
      that are subject for table name to filename encoding
      (WL1324).
      
      Another problem is that MERGE engine didn't properly open
      child table from the same database if child table name
      contains characters like '/', '#'.
      
      The problem was that table name to file name encoding was
      applied inconsistently:
      * On CREATE: encode table name + database name if child
        table is in different database; do not encode table
        name if child table is in the same database;
      * No decoding on open.
      
      With this fix child table/database names are always
      encoded on CREATE and decoded on open.
      
      Along with this patch comes fix for SHOW CREATE TABLE,
      which used to show child table/database path instead
      of child table/database names.
     @ mysql-test/r/merge.result
        A test case for BUG#48265.
     @ mysql-test/t/merge.test
        A test case for BUG#48265.
     @ storage/myisammrg/ha_myisammrg.cc
        On CREATE always write child table/database name encoded
        by table name to filename encoding to dot-MRG file.
        
        On open decode child table/database name.
        
        Fixed ::append_create_info() to return child
        table/database name instead of path.

Hello Sergey,

thanks for the patch.

After the patch is included in an official release we try again to migrate our databases from 5.0.x to 5.1.x

Regards, Henry

Good patch. No comments. Thanks for the --echo this time. :-)

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

3372 Sergey Vojtovich	2010-02-18
      BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't
                  work in 5.1.40)
      
      MERGE engine fails to open child table from a different
      database if child table/database name contains characters
      that are subject for table name to filename encoding
      (WL1324).
      
      Another problem is that MERGE engine didn't properly open
      child table from the same database if child table name
      contains characters like '/', '#'.
      
      The problem was that table name to file name encoding was
      applied inconsistently:
      * On CREATE: encode table name + database name if child
        table is in different database; do not encode table
        name if child table is in the same database;
      * No decoding on open.
      
      With this fix child table/database names are always
      encoded on CREATE and decoded on open. Compatibility
      with older tables preserved.
      
      Along with this patch comes fix for SHOW CREATE TABLE,
      which used to show child table/database path instead
      of child table/database names.
     @ mysql-test/r/merge.result
        A test case for BUG#48265.
     @ mysql-test/std_data/bug48265.frm
        MERGE table from 5.0 to test fix for BUG#48265 compatibility.
     @ mysql-test/t/merge.test
        A test case for BUG#48265.
     @ storage/myisammrg/ha_myisammrg.cc
        On CREATE always write child table/database name encoded
        by table name to filename encoding to dot-MRG file.
        
        On open decode child table/database name.
        Compatibilty with previous versions preserved.
        
        Fixed ::append_create_info() to return child
        table/database name instead of path.
     @ storage/myisammrg/myrg_open.c
        Move if (has_path) branch myisammrg_parent_open_callback.
        The callback function needs to know if child table was
        written along with database name to dot-MRG file. Needed
        for compatibility reasons.

Hi Sergey,

The similar issue happens to me except there is a dash in my database name, e.g. `test-1`

Will your fix cover this issue as well?

Thanks,

Winter

Approved with minor comments. Please see email.

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

3372 Sergey Vojtovich	2010-03-03
      BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't
                  work in 5.1.40)
      
      MERGE engine fails to open child table from a different
      database if child table/database name contains characters
      that are subject for table name to filename encoding
      (WL1324).
      
      Another problem is that MERGE engine didn't properly open
      child table from the same database if child table name
      contains characters like '/', '#'.
      
      The problem was that table name to file name encoding was
      applied inconsistently:
      * On CREATE: encode table name + database name if child
        table is in different database; do not encode table
        name if child table is in the same database;
      * No decoding on open.
      
      With this fix child table/database names are always
      encoded on CREATE and decoded on open. Compatibility
      with older tables preserved.
      
      Along with this patch comes fix for SHOW CREATE TABLE,
      which used to show child table/database path instead
      of child table/database names.
     @ mysql-test/r/merge.result
        A test case for BUG#48265.
     @ mysql-test/std_data/bug48265.frm
        MERGE table from 5.0 to test fix for BUG#48265 compatibility.
     @ mysql-test/t/merge.test
        A test case for BUG#48265.
     @ storage/myisammrg/ha_myisammrg.cc
        On CREATE always write child table/database name encoded
        by table name to filename encoding to dot-MRG file.
        
        On open decode child table/database name.
        Compatibilty with previous versions preserved.
        
        Fixed ::append_create_info() to return child
        table/database name instead of path.
     @ storage/myisammrg/myrg_open.c
        Move if (has_path) branch from myrg_parent_open() to
        myisammrg_parent_open_callback. The callback function
        needs to know if child table was written along with
        database name to dot-MRG file. Needed for compatibility
        reasons.

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100312095153-t4rtoqc7p96lmxvh) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

An entry has been added to the 5.5.4, 5.5.5, 6.0.14 changelogs:

        The MERGE engine failed to open a child table from a different
        database if the child table or database name contained
        characters that were the subject of table name to filename
        encoding.
      
        Further, the MERGE engine did not properly open a child table
        from the same database if the child table name contained
        characters such as '/', '#'.

5.5.4 changelog entry was moved to 5.5.5.

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100311142839-95g03yr96xpa09gm) (merge vers: 5.1.46) (pib:16)

The changelog entry has been updated to include version 5.1.46.

Hello together,

i have done some tests with the 5.1.46 that is published today.

It looks very good, all tests are fine.

Thanks for it. :-)

Next week i will try again to migrate our databases from 5.0.x to 5.1.46

Regards, Henry

Hi,

at the moment i migrate my databases from 5.0.90 to 5.1.46 (mysql_upgrade).

MRG-Tables
For all tables except the merge tables i got an ok. The file names of the merge tables are converted (msg_monat-02.MRG -> msg_monat@002d02.MRG) but inside the MRG-Files the file names of the underlying tables are not converted. 

=> After the mysql_upgrade finished i will all MRG-Table recreate.

Trigger

All files names of the trigger are converted but the old files are existing.

-rw-r--r-- 1 mysql mysql 41 Nov 23 19:32 inserttime_msg_02-19.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_02@002d19.TRN
-rw-r--r-- 1 mysql mysql 41 Nov 24 00:10 inserttime_msg_05-30.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_05@002d30.TRN
-rw-r--r-- 1 mysql mysql 41 Nov 24 00:10 inserttime_msg_07-28.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_07@002d28.TRN
-rw-r--r-- 1 mysql mysql 41 Nov 23 19:36 inserttime_msg_08-11.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_08@002d11.TRN
-rw-r--r-- 1 mysql mysql 41 Nov 24 00:10 inserttime_msg_11-24.TRN
-rw-r--r-- 1 mysql mysql 41 Nov 24 00:10 inserttime_msg_11-28.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_11@002d24.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_11@002d28.TRN
-rw-r--r-- 1 mysql mysql 41 Nov 24 00:10 inserttime_msg_12-06.TRN
-rw-rw---- 1 mysql mysql 41 Apr 26 10:05 inserttime_msg_12@002d06.TRN

Can i delete the "old" trigger files after the mysql_upgrade finished and i stopped the database?

Inside the TRN-Files are the "normal" tabel names used (without @002d). 

This are ok, because this is the real table name and not the file name like in the MRG-Files? 

Regards, Henry

Hello,

today we are finished our migration from 5.0.x to 5.1.46 successfully.

Thanks again for the patch, Henry

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)

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)

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)