Bug #8183 MERGE and SYM cause wrong SHOW CREATE TABLE
Submitted: 28 Jan 2005 17:29 Modified: 10 Jun 2005 19:28
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.9 OS:Microsoft Windows (Windows 2000)
Assigned to: Reggie Burnett CPU Architecture:Any

[28 Jan 2005 17:29] Dave Pullin
Description:
Creating a table with Engine=MERGE and UNION of table(s) that are in another database that is symbolically linked by a SYM file to another folder, causes the SHOW CREATE to report the folder name as if it were the database name.

[I have some more serious problems (server hangs) when operating on this 'broken' merge table that may be the result of this confusion over the folder and database name, but I can't isolate them yet.]

In the following 'symdb' is a database that is symbolically linked to a folder called 'symdbfolder'

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.9-nt-log |
+--------------+
1 row in set (0.01 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists symdb.bug_sub ;
Query OK, 0 rows affected (0.00 sec)

mysql> create table symdb.bug_sub (x int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into symdb.bug_sub (x) values(1);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> drop table if exists bug_merged ;
Query OK, 0 rows affected (0.01 sec)

mysql> create table bug_merged (x int)engine=merge union=(symdb.bug_sub);
Query OK, 0 rows affected (0.04 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table bug_merged;
+------------+------------------------------------------------------------------
-----------------------------------------------------------------+
| Table      | Create Table
                                                                 |
+------------+------------------------------------------------------------------
-----------------------------------------------------------------+
| bug_merged | CREATE TABLE `bug_merged` (
  `x` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`symdbfolder`.`bug_sub`) |
+------------+------------------------------------------------------------------
-----------------------------------------------------------------+
1 row in set (0.01 sec)

Observe that UNION=(`symdbfolder`.`bug_sub`)  is naming the folder, not the database.

The .MRG that's created contains:
d:\MySQLdata\symdb/bug_sub
(which, I think, is correct).

How to repeat:
create a file D:\MySQLdata\symdb.sym (assuming D:\MySQLdata\ to be your data directory), containing one line:
e:\symdbfolder\

Then run:
select version();
flush tables;
drop table if exists symdb.bug_sub ;
create table symdb.bug_sub (x int);
insert into symdb.bug_sub (x) values(1);

drop table if exists bug_merged ;
create table bug_merged (x int)engine=merge union=(symdb.bug_sub);
flush tables;
show create table bug_merged;
[30 Jan 2005 6:04] Aleksey Kishkin
I'd added 'engine=myisam' for bug_sub' table in the 'create table' statement
[8 Feb 2005 14:15] Ingo Strüwing
I cannot repeat this on Linux.
[28 Feb 2005 21:47] Miguel Solorzano
C:\mysql\bin>touch c:\mysql\data\symdb.sym
C:\mysql\bin>edit c:\mysql\data\symdb.sym
C:\mysql\bin>type c:\mysql\data\symdb.sym
d:\symdbfolder\

C:\mysql\bin>mkdir d:\symdbfolder

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.10-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.10-nt |
+-----------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists symdb.bug_sub ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table symdb.bug_sub (x int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into symdb.bug_sub (x) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> use symdb;
Database changed
mysql> drop table if exists bug_merged ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table bug_merged (x int)engine=merge union=(symdb.bug_sub);
Query OK, 0 rows affected (0.06 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table bug_merged\G
*************************** 1. row ***************************
       Table: bug_merged
Create Table: CREATE TABLE `bug_merged` (
  `x` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`symdbfolder`.`bug_sub`)
1 row in set (0.01 sec)

mysql>
[4 Jun 2005 22:51] Patrick Galbraith
This worked fine on Max OSX

radha:/usr/local/mysql/data root# uname -a
Darwin radha.local 7.9.0 Darwin Kernel Version 7.9.0: Wed Mar 30 20:11:17 PST 2005; root:xnu/xnu-517.12.7.obj~1/RELEASE_PPC  Power Macintosh powerpc

radha:/usr/local/mysql/data root# ln -s /Volumes/disk2s1/symdbfolder/ `pwd`/symdb
radha:/usr/local/mysql/data root# chown mysql:mysql symdb/
radha:/usr/local/mysql/data root# mysql 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.12-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+-----------+
| Database  |
+-----------+
| federated |
| foo       |
| mysql     |
| symdb     |
| test      |
+-----------+
5 rows in set (0.00 sec)

mysql> create table symdb.bug_sub ( x int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into symdb.bug_sub (x) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> use symdb
Database changed
mysql> create table bug_merged (x int) engine=merge union=(symdb.bug_sub);
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.24 sec)

mysql> show create table bug_merged;
+------------+---------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                        |
+------------+---------------------------------------------------------------------------------------------------------------------+
| bug_merged | CREATE TABLE `bug_merged` (
  `x` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`bug_sub`) |
+------------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from bug_merged;
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.08 sec)
[4 Jun 2005 22:52] Patrick Galbraith
forgot to include version:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.12-debug-log |
+------------------+
1 row in set (0.00 sec)
[4 Jun 2005 23:42] Patrick Galbraith
On windows XP, I am able to reproduce this:

c:\mysql\bin>mkdir d:\symdbfolder

c:\mysql\bin>edit c:\mysql\data\symdb.sym

c:\type c:\mysql\data\symdb.sym

c:\mysql\bin>mysql

+------------+
| version()  |
+------------+
| 4.1.12a-nt |
+------------+
1 row in set (0.40 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists symdb.bug_sub;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> create table symdb.bug_sub (x int);
Query OK, 0 rows affected (0.76 sec)

mysql> insert into symdb.bug_sub (x) values(1);
Query OK, 1 row affected (0.40 sec)

mysql> use symdb;
Database changed
mysql> drop table if exists bug_merged;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table bug_merged (x int) engine=merge union=(symdb.bug_sub);
Query OK, 0 rows affected (0.64 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table bug_merged;
+------------+------------------------------------------------------------------
-----------------------------------------------------------------+
| Table      | Create Table
                                                                 |
+------------+------------------------------------------------------------------
-----------------------------------------------------------------+
| bug_merged | CREATE TABLE `bug_merged` (
  `x` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`symdbfolder`.`bug_sub`) |
+------------+------------------------------------------------------------------
-----------------------------------------------------------------+
1 row in set (0.01 sec)
[5 Jun 2005 0:00] Patrick Galbraith
Even though I can repeat this, I notice not problems as far as locking up or functionality. I tested this by running several queries, stopping and starting the server, with no problems.

mysql> show tables;
+-----------------+
| Tables_in_symdb |
+-----------------+
| bug_merged      |
| bug_sub         |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from bug_merged;
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from bug_sub;
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> insert into bug_sub (x) values (82828);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug_sub (x) values (9999);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bug_merged;
+-------+
| x     |
+-------+
|     1 |
| 82828 |
|  9999 |
+-------+
3 rows in set (0.37 sec)

mysql>
mysql> delete from bug_merged;
Query OK, 3 rows affected (0.37 sec)

mysql> select * from bug_merged;
Empty set (0.01 sec)

mysql>
mysql> insert into bug_sub values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug_sub values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bug_sub values (33333333);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bug_merged;
+----------+
| x        |
+----------+
| 33333333 |
|        2 |
|        1 |
+----------+
3 rows in set (0.00 sec)

mysql> \q
Bye

C:\mysql\bin>mysqladmin.exe shutdown

Then, in a different terminal (command.com):

$ pwd
/cygdrive/c/mysql

Patrick Galbraith@krsna /cygdrive/c/mysql
$ ./bin/mysqld-nt.exe --datadir=c:/mysql/data --character-sets-dir=c:/mysql/sha
re/charsets --language=c:/mysql/share/english --basedir=c:/mysql --skip-grant-t
ables --port=3306 --socket=c:/mysqld.sock

Then, in the cygwin terminal:

C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.12a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> connect symdb;
Connection id:    2
Current database: symdb

mysql> select * from bug_merged;
----------+
 x        |
----------+
 33333333 |
        2 |
        1 |
----------+
 rows in set (0.01 sec)

mysql> show create table bug_merged;
------------+------------------------------------------------------------------
----------------------------------------------------------------+
 Table      | Create Table
                                                                |
------------+------------------------------------------------------------------
----------------------------------------------------------------+
 bug_merged | CREATE TABLE `bug_merged` (
 `x` int(11) default NULL
 ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`symdbfolder`.`bug_sub`) |
------------+------------------------------------------------------------------
----------------------------------------------------------------+
 row in set (0.00 sec)

mysql> delete from bug_merged where x = 2;
uery OK, 1 row affected (0.44 sec)

mysql> select * from bug_merged;
----------+
 x        |
----------+
 33333333 |
        1 |
----------+
 rows in set (0.00 sec)

mysql> drop table bug_merged;
uery OK, 0 rows affected (0.09 sec)

mysql>
[9 Jun 2005 14:58] 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/internals/25820
[10 Jun 2005 19:19] Reggie Burnett
Fixed in 4.1.13
[10 Jun 2005 19:28] Paul Dubois
Noted in 4.1.13 changelog.