| Bug #19182 | CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure. | ||
|---|---|---|---|
| Submitted: | 18 Apr 2006 23:53 | Modified: | 4 Jun 2007 18:01 |
| Reporter: | Danny Holstein | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.1 | OS: | Linux (Linux, WinXP) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
| Tags: | rt_q1_2007 | ||
[19 Apr 2006 8:48]
Valeriy Kravchuk
Thank you for a problem report. Please, send a self-contained test case, with all the tables referenced in your procedure. Or, even better, a simpler one, showing that CREATE TABLE bar ... SELECT .. FROM foo; does not work as expected (and nothing else). Please, also try to repeat with a newer version, 5.0.20.
[19 Apr 2006 15:13]
Danny Holstein
The following causes an error -- it executes correctly on the first iteration but not with the following iterations, column A in table temp1 isn't there:
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (B INT, C INT);
INSERT INTO temp (B,C) VALUES(2,3),(5,6),(8,9);
DROP TABLE IF EXISTS temp1;
CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A))
SELECT B,C FROM temp;
SELECT * FROM temp1;
DROP PROCEDURE IF EXISTS dum_proc;
delimiter //
CREATE PROCEDURE dum_proc()
BEGIN
SET @I=1;
label2: LOOP
DROP TABLE IF EXISTS temp1;
CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A))
SELECT B,C FROM temp;
SELECT * FROM temp1;
SET @I=@I + 1;
IF @I < 5 THEN ITERATE label2; END IF;
LEAVE label2;
END LOOP label2;
END
//
delimiter ;
CALL dum_proc();
[20 Apr 2006 17:30]
Valeriy Kravchuk
Verified just as described in the last comment on 5.0.21-BK (ChangeSet@1.2084.92.1, 2006-04-20 12:06:22+02:00):
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.21
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS temp;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Cmysql> CREATE TABLE temp (B INT, C INT);
INSQuery OK, 0 rows affected (0.02 sec)
Emysql> INSERT INTO temp (B,C) VALUES(2,3),(5,6),(8,9);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> DROP TABLE IF EXISTS temp1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A))
-> SELECT B,C FROM temp;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM temp1;
+---+------+------+
| A | B | C |
+---+------+------+
| 1 | 2 | 3 |
| 2 | 5 | 6 |
| 3 | 8 | 9 |
+---+------+------+
3 rows in set (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE dum_proc()
-> BEGIN
-> SET @I=1;
-> label2: LOOP
-> DROP TABLE IF EXISTS temp1;
-> CREATE TABLE temp1 (A INT AUTO_INCREMENT, PRIMARY KEY (A))
-> SELECT B,C FROM temp;
-> SELECT * FROM temp1;
-> SET @I=@I + 1;
-> IF @I < 5 THEN ITERATE label2; END IF;
-> LEAVE label2;
-> END LOOP label2;
-> END
-> //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call dum_proc();
+---+------+------+
| A | B | C |
+---+------+------+
| 1 | 2 | 3 |
| 2 | 5 | 6 |
| 3 | 8 | 9 |
+---+------+------+
3 rows in set (0.02 sec)
ERROR 1054 (42S22): Unknown column 'test.temp1.A' in 'field list'
mysql> call dum_proc();
ERROR 1054 (42S22): Unknown column 'test.temp1.A' in 'field list'
mysql> call dum_proc();
ERROR 1054 (42S22): Unknown column 'test.temp1.A' in 'field list'
[14 Jul 2006 10:34]
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/commits/9166
[25 Oct 2006 18:17]
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/commits/14396 ChangeSet@1.2216, 2006-10-25 22:14:40+04:00, kroki@moonlight.intranet +6 -0 Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure. When CREATE .. SELECT was used from a stored routine or prepared statement, on re-execution columns specified in SELECT part could be overwritten by columns specified in CREATE part, instead of being added to the list of columns. The cause of a bug was that cached LEX::create_list was modified, and then together with LEX::key_list was emtied (instead of being restored to original state). The solution is to restore LEX::create_list to its original state, and to not reset LEX::key_list, as it is not modified.
[27 Oct 2006 11:22]
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/commits/14466 ChangeSet@1.2554, 2006-10-27 14:00:07+04:00, kroki@moonlight.intranet +6 -0 Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure. When CREATE .. SELECT was used from a stored routine or prepared statement, on re-execution columns specified in SELECT part could be overwritten by columns specified in CREATE part, instead of being added to the list of columns. The cause of a bug was that cached LEX::create_list was modified, and then together with LEX::key_list was emtied (instead of being restored to original state). The solution is to restore LEX::create_list to its original state, and to not reset LEX::key_list, as it is not modified.
[27 Oct 2006 14:00]
Tomash Brechko
The bug was in 4.1 too, so it is fixed there. Queued to 4.1-runtime, 5.0-runtime and 5.1-runtime.
[27 Oct 2006 18:40]
Tomash Brechko
It appeared that the patch was not correct. It has been reverted from runtime team trees. A new fix is needed.
[31 Oct 2006 18:47]
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/commits/14631 ChangeSet@1.2298, 2006-10-31 21:45:59+03:00, kostja@bodhi.local +13 -0 A fix and test cases for Bug#4968 "Stored procedure crash if cursor opened on altered table" Bug#19733 "Repeated alter, or repeated create/drop, fails" Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure." Bug#19182 affects 4.1 and this fix will be ported to 4.1 when (if) it's approved. The problem that caused all of the above bugs is that functions mysql_prepare_table, mysql_create_table and mysql_alter_table are not re-execution friendly: during their operation they modify contents of LEX (members create_info, alter_info, key_list, create_list), thus making the LEX unusable for the next execution. In particular, these function remove processed columns and keys from create_list, key_list and drop_list. Search the code for drop_it.remove() and similar to find evidence. The fix is to supply to these functions a usable copy of each of the above structures at every re-execution of an SQL statement. To simplify memory management, LEX::key_list and LEX::create_list were added to LEX::alter_info, a fresh copy of which is created for every execution.
[1 Nov 2006 12:57]
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/commits/14676 ChangeSet@1.2298, 2006-11-01 15:56:12+03:00, kostja@bodhi.local +13 -0 A fix and test cases for Bug#4968 "Stored procedure crash if cursor opened on altered table" Bug#19733 "Repeated alter, or repeated create/drop, fails" Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure." Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" Bug#19182 affects 4.1 and this fix will be ported to 4.1 when (if) it's approved. The problem that caused all of the above bugs is that functions mysql_prepare_table, mysql_create_table and mysql_alter_table are not re-execution friendly: during their operation they modify contents of LEX (members create_info, alter_info, key_list, create_list), thus making the LEX unusable for the next execution. In particular, these function remove processed columns and keys from create_list, key_list and drop_list. Search the code for drop_it.remove() and similar to find evidence. The fix is to supply to these functions a usable copy of each of the above structures at every re-execution of an SQL statement. To simplify memory management, LEX::key_list and LEX::create_list were added to LEX::alter_info, a fresh copy of which is created for every execution.
[9 Nov 2006 21:34]
Konstantin Osipov
Code reviews were sent by email and IRC.
[17 Jan 2007 17:56]
Konstantin Osipov
Pushed into 4.1.23 and 5.0.36, NULL-merged into 5.1. 5.1 requires a separate patch.
[19 Jan 2007 3:57]
Paul DuBois
Noted in 4.1.23, 5.0.36, 5.1.15 changelogs.
[19 Jan 2007 21:52]
Konstantin Osipov
This bug is still present in 5.1
[28 May 2007 11:43]
Konstantin Osipov
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/27449 ChangeSet@1.2515, 2007-05-28 15:30:01+04:00, kostja@vajra.(none) +17 -0 5.1 version of a fix and test cases for bugs: Bug#4968 ""Stored procedure crash if cursor opened on altered table" Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing" Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from stored procedure." Bug#19733 "Repeated alter, or repeated create/drop, fails" Bug#22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server" Bug#24879 "Prepared Statements: CREATE TABLE (UTF8 KEY) produces a growing key length" (this bug is not fixed in 5.0) Re-execution of CREATE DATABASE, CREATE TABLE and ALTER TABLE statements in stored routines or as prepared statements caused incorrect results (and crashes in versions prior to 5.0.25). In 5.1 the problem occured only for CREATE DATABASE, CREATE TABLE SELECT and CREATE TABLE with INDEX/DATA DIRECTOY options). The problem of bugs 4968, 19733, 19282 and 6895 was that functions mysql_prepare_table, mysql_create_table and mysql_alter_table are not re-execution friendly: during their operation they modify contents of LEX (members create_info, alter_info, key_list, create_list), thus making the LEX unusable for the next execution. In particular, these functions removed processed columns and keys from create_list, key_list and drop_list. Search the code in sql_table.cc for drop_it.remove() and similar patterns to find evidence. The fix is to supply to these functions a usable copy of each of the above structures at every re-execution of an SQL statement. To simplify memory management, LEX::key_list and LEX::create_list were added to LEX::alter_info, a fresh copy of which is created for every execution. The problem of crashing bug 22060 stemmed from the fact that the above metnioned functions were not only modifying HA_CREATE_INFO structure in LEX, but also were changing it to point to areas in volatile memory of the execution memory root. The patch solves this problem by creating and using an on-stack copy of HA_CREATE_INFO in mysql_execute_command. Additionally, this patch splits the part of mysql_alter_table that analizes and rewrites information from the parser into a separate function - mysql_prepare_alter_table, in analogy with mysql_prepare_table, which is renamed to mysql_prepare_create_table.
[28 May 2007 11:43]
Konstantin Osipov
Queued into 5.1-runtime
[1 Jun 2007 19:22]
Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 18:01]
Paul DuBois
Moved 5.1 changelog entry from 5.1.15 to 5.1.20.
Description: I've tried the following example with MySQL 5.0.19, it works by itself. It DOESN'T work from a stored procedure, the first column IS NOT created. mysql> CREATE TABLE bar (m INT) SELECT n FROM foo; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bar; +------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ 1 row in set (0.00 sec) How to repeat: This is the code that doesn't execute correctly from the procedure, it does work by itself: SELECT date(min(BegTime)) FROM hits.time_hits INTO @startDT; DROP PROCEDURE IF EXISTS clickiterate; delimiter // CREATE PROCEDURE clickiterate(IN startDT DATETIME) BEGIN label2: LOOP SET @week_end=DATE_ADD(startDT, INTERVAL 3 DAY); DROP TABLE IF EXISTS temp; CREATE TABLE temp (ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID)) SELECT BegTime FROM hits.time_hits WHERE BegTime>DATE_SUB(startDT, INTERVAL .25 DAY) AND BegTime<DATE_ADD(startDT, INTERVAL 3.25 DAY); ALTER TABLE temp DROP PRIMARY KEY, ADD PRIMARY KEY(`ID`, `BegTime`), ENGINE = MEMORY; label1: LOOP SELECT count(*) FROM temp WHERE BegTime>DATE_SUB(startDT, INTERVAL 30 MINUTE) AND BegTime<DATE_ADD(startDT, INTERVAL 30 MINUTE) INTO @P; IF @P IS NULL THEN SET @P=0; END IF; UPDATE hits.ordersales SET hits_per_hr=@P where OrderDT=startDT; SET startDT=DATE_ADD(startDT, INTERVAL 10 MINUTE); IF startDT < @week_end THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; SET startDT = @week_end; IF startDT < now() THEN ITERATE label2; END IF; LEAVE label2; END LOOP label2; END // delimiter ; CALL clickiterate(@startDT);