| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.9 | OS: | Windows (Windows 2000) |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[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]
MySQL Verification Team
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.
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;