| Bug #7085 | Stored procedure + merge table + view produces an error | ||
|---|---|---|---|
| Submitted: | 7 Dec 2004 22:27 | Modified: | 9 Aug 2006 11:45 |
| Reporter: | Victoria Reznichenko | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.0.13-beta-debug | OS: | Linux (linux) |
| Assigned to: | Tomash Brechko | CPU Architecture: | Any |
[14 Sep 2005 7:48]
Valeriy Kravchuk
This bug still exists, although error message changed slightly:
mysql> drop view if exists vtotal;
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql> show warnings;
+-------+------+-----------------------------+
| Level | Code | Message |
+-------+------+-----------------------------+
| Note | 1051 | Unknown table 'test.vtotal' |
+-------+------+-----------------------------+
1 row in set (0,00 sec)
mysql> flush tables;
Query OK, 0 rows affected (0,00 sec)
mysql> drop table if exists total;
Query OK, 0 rows affected (0,01 sec)
mysql> flush tables;
Query OK, 0 rows affected (0,00 sec)
mysql> create table u1(str varchar(10));
Query OK, 0 rows affected (0,02 sec)
mysql> create table u2(str varchar(10));
Query OK, 0 rows affected (0,07 sec)
mysql> create table total(str varchar(10))type=merge union(u1,u2);
Query OK, 0 rows affected, 1 warning (0,05 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------
------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
------------+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+--------------------------------------------------------------
------------+
1 row in set (0,00 sec)
mysql> show create table total;
+-------+-----------------------------------------------------------------------
---------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
---------------------------------------------+
| total | CREATE TABLE `total` (
`str` varchar(10) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNION=(`u1`,`u2`) |
+-------+-----------------------------------------------------------------------
---------------------------------------------+
1 row in set (0,03 sec)
mysql> create view vtotal as select * from total;
Query OK, 0 rows affected (0,11 sec)
mysql> desc vtotal;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| str | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0,16 sec)
mysql> drop view vtotal;
Query OK, 0 rows affected (0,00 sec)
mysql> drop table total;
Query OK, 0 rows affected (0,00 sec)
So, it works outside SP. Then:
mysql> delimiter //
mysql> create procedure vt()
-> begin
-> drop view if exists vtotal;
-> flush tables;
-> drop table if exists total;
-> flush tables;
-> create table total(str varchar(10))type=merge union(u1,u2);
-> create view vtotal as select * from total;
-> end //
Query OK, 0 rows affected, 1 warning (0,05 sec)
mysql> call vt()//
Query OK, 0 rows affected, 3 warnings (0,03 sec)
mysql> show warnings
-> //
+---------+------+--------------------------------------------------------------
------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
------------+
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
| Note | 1051 | Unknown table 'test.vtotal'
|
| Note | 1051 | Unknown table 'total'
|
+---------+------+--------------------------------------------------------------
------------+
3 rows in set (0,00 sec)
mysql> call vt()//
ERROR 1168 (HY000): All tables in the MERGE table are not identically defined
mysql> select version();
-> //
+-------------------+
| version() |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)
[9 Aug 2006 11:45]
MySQL Verification Team
I was unable to repeat with source server:
miguel@hegel:~/dbs/mysql-5.0> bk changes | head
ChangeSet@1.2242, 2006-08-07 07:41:49+04:00, evgen@sunlight.local +1 -0
sql_view.cc:
Memory leak fix
ChangeSet@1.2241, 2006-08-07 02:29:59+04:00, evgen@sunlight.local +1 -0
Merge epotemkin@bk-internal.mysql.com:/home/bk/mysql-5.0
into sunlight.local:/local_work/leak_fix
MERGE: 1.2205.11.2
ChangeSet@1.2205.11.2, 2006-08-07 00:06:03+04:00, evgen@sunlight.local +1 -0
miguel@hegel:~/dbs/mysql-5.0>
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbk
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.25-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table u1(str varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> create table u2(str varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> create procedure vt()
-> begin
-> drop view if exists vtotal;
-> flush tables;
-> drop table if exists total;
-> flush tables;
-> create table total(str varchar(10))type=merge union(u1,u2);
-> create view vtotal as select * from total;
-> end //
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> call vt()//
Query OK, 0 rows affected, 3 warnings (0.02 sec)
mysql> call vt()//
Query OK, 0 rows affected (0.02 sec)
mysql>
Description: If I execute stored procedure twice, I get error: mysql> delimiter // mysql> create procedure vt() -> begin -> drop view if exists vtotal; -> flush tables; -> drop table if exists total; -> flush tables; -> create table total(str varchar(10))type=merge union(u1,u2); -> create view vtotal as select * from total; -> end // Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> call vt()// Query OK, 0 rows affected, 3 warnings (0.06 sec) mysql> call vt()// ERROR 1016 (HY000): Can't open file: 'total.MRG' (errno: 143) # perror 143 MySQL error: 143 = Conflicting table definitions in sub-tables of MERGE table If I execute the above DROP/CREATE statements directly, it works fine. Without VIEW it also works like a charm. How to repeat: create table u1(str varchar(10)); create table u2(str varchar(10)); delimiter // create procedure vt() begin drop view if exists vtotal; flush tables; drop table if exists total; flush tables; create table total(str varchar(10))type=merge union(u1,u2); create view vtotal as select * from total; end // call vt()// call vt()//