Description:
It appears that DDL statements are not being parsed for variable replacement. DML is working fine for variable replacement. I haven't tried all DDL, just create table and alter table.
'njoy,
Mark
How to repeat:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
id int(11) NOT NULL auto_increment,
name varchar(40) NOT NULL default '',
ts datetime NOT NULL default '2004-01-01 00:00:00',
PRIMARY KEY (id),
KEY name (name)
);
DROP PROCEDURE IF EXISTS add_tbl;
DELIMITER //
CREATE PROCEDURE add_tbl (IN in_name varchar(40))
BEGIN
INSERT INTO
tbl
VALUES
(NULL, in_name, now());
CREATE TABLE
in_name
( id int not null auto_increment primary key,
name varchar(40) not null default '',
ts datetime not null default '2004-01-01 00:00'
);
END//
DELIMITER ;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| casc |
| col |
| profile |
| tbl |
+----------------+
4 rows in set (0.04 sec)
mysql> call add_tbl('mark');
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| casc |
| col |
| in_name |
| profile |
| tbl |
+----------------+
5 rows in set (0.00 sec)
Variable replacement works fine for the insert . . .
mysql> select * from tbl;
+----+------+---------------------+
| id | name | ts |
+----+------+---------------------+
| 1 | mark | 2005-03-12 21:19:11 |
+----+------+---------------------+
1 row in set (0.00 sec)
Same goes for "alter table"
CREATE TABLE col (
id int(11) NOT NULL auto_increment,
name varchar(40) NOT NULL default '',
tbl varchar(40) NOT NULL default '',
ts datetime NOT NULL default '2004-01-01 00:00:00',
PRIMARY KEY (id),
KEY name (name),
KEY tbl (tbl)
);
DROP PROCEDURE IF EXISTS add_col;
DELIMITER //
CREATE PROCEDURE add_col (IN in_name varchar(40), IN in_tbl varchar(40))
BEGIN
INSERT INTO
col
VALUES
(NULL, in_name, in_tbl, now());
ALTER TABLE
in_tbl
ADD
in_name int not null default 0;
END//
DELIMITER ;
mysql> rename table in_name to mark;
Query OK, 0 rows affected (0.00 sec)
mysql> call add_col('testcol', 'mark');
ERROR 1146 (42S02): Table 'test.in_tbl' doesn't exist
mysql> rename table mark to in_tbl;
Query OK, 0 rows affected (0.00 sec)
mysql> call add_col('testcol', 'mark');
Query OK, 0 rows affected (0.01 sec)
mysql> show create table in_tbl;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| in_tbl | CREATE TABLE `in_tbl` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
`ts` datetime NOT NULL default '2004-01-01 00:00:00',
`in_name` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from col;
+----+---------+------+---------------------+
| id | name | tbl | ts |
+----+---------+------+---------------------+
| 1 | testcol | mark | 2005-03-12 21:23:08 |
| 2 | testcol | mark | 2005-03-12 21:24:09 |
+----+---------+------+---------------------+
2 rows in set (0.00 sec)