Bug #7324 modifying same table multiple times from same table editor window not working
Submitted: 15 Dec 2004 16:15 Modified: 16 Mar 2005 13:14
Reporter: Mark Junker Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.1.3 OS:Microsoft Windows (WinXP Pro SP2)
Assigned to: Mike Lischke CPU Architecture:Any

[15 Dec 2004 16:15] Mark Junker
Description:
Hi,

it seems that the "alter table" statement isn't usable when modifying the same table in the same table editor window (didn't close between these changes) when the table uses foreign keys.

In any case, the alter table statement is sub-optimal. Example:

ALTER TABLE `test`.`test1` MODIFY COLUMN `test1_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 MODIFY COLUMN `value2` INTEGER UNSIGNED NOT NULL,
 CHANGE COLUMN `value3` `value3_2` TEXT NOT NULL,
 MODIFY COLUMN `value4` INTEGER UNSIGNED NOT NULL
, COMMENT = '';

This statement was created but the only thing I changed was the column name from "value3" to "value3_2".

ALTER TABLE `test`.`test1` CHANGE COLUMN `value4` `value4_2` INTEGER UNSIGNED NOT NULL DEFAULT 0,
 MODIFY COLUMN `test2_id` INTEGER UNSIGNED NOT NULL
, DROP INDEX `FK_test1_1`
, COMMENT = 'InnoDB free: 8192 kB';

The only thing I did to get this statement was to rename the column 'value4' to 'value4_2'. Everything else stayed the same.

How to repeat:
Please execute the following statements:

CREATE TABLE `test`.`test2` (
  `test2_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `data` TEXT NOT NULL,
  PRIMARY KEY(`test2_id`)
)
TYPE = InnoDB;

Then create a table with the table editor that produces the following statement:

CREATE TABLE `test`.`test1` (
  `test1_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `test2` TEXT NOT NULL,
  `test3` INTEGER UNSIGNED NOT NULL,
  `test2_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(`test1_id`)
)
TYPE = InnoDB;

In the same window (do not close it), create a foreign key from test1.test2_id to test2.test2_id with a "on delete cascade".

Now change (in the same window) the column name from test2 to test2_2 and now you can get the following statement:

ALTER TABLE `test`.`test1` MODIFY COLUMN `test1_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 CHANGE COLUMN `test2` `test2_2` TEXT NOT NULL,
 MODIFY COLUMN `test3` INTEGER UNSIGNED NOT NULL,
 MODIFY COLUMN `test2_id` INTEGER UNSIGNED NOT NULL
, DROP INDEX `FK_test1_1`
, COMMENT = '';
[16 Feb 2005 13:14] Mike Lischke
I followed your steps and created the tables as mentioned. And I also saw the effect you meant. However, since the created SQL is not wrong (albeit non-optimal) and appears so only in the one case you described I don't think it is worth to investigate that. As soon as you have closed the editor once you will see minimal SQL queries again when modifying a table.

If the SQL is wrong, though, this would be a different matter.
[17 Mar 2005 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".