MySQL unknown column error when using ALTER, don't understand behaviour

Question

I was wondering if someone could help me.

I have a odd behaviour while issueing a ALTER command. The command comes from MySQL Workbench sync and it is failing. I have a table with fields:

`id`  int(11) NOT NULL AUTO_INCREMENT ,
`text`  varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`updated`  datetime NULL DEFAULT NULL ,
`remote_addr`  varchar(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`http_user_agent`  varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`user_id`  int(11) NULL DEFAULT NULL ,
`category`  varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`created`  datetime NULL DEFAULT NULL ,
PRIMARY KEY (`id`)

And I want to issue the ALTER command:

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`,
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

I get in response:

Unknown column 'created' in 'logs'

But

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`

works by itself, and:

ALTER TABLE `logs`
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

also works by itself.

I don't understand why when both are combined in one query it doesn't work and says that 'created' doesn't exist. I know that it definately exists.

Note that I'm not worried about the change column for 'created', it is generated by MWB when comparing and preparing to sync. But was just wondering why both actions can't be put on one query.

I am using MySQL 5.5.8

Update

I actually can do multiple clauses okay. I have been doing it on other tables just fine.

I forgot to mention this. But when I remove the AFTER part it works.

So this does not work:

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`,
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

But this does:

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL,
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

Answer 1

I had the same problem. I solved it by doing the CHANGE COLUMN (or MODIFY COLUMN) before ADD COLUMN.

In your example that would give the following SQL statement :

ALTER TABLE `logs`
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`,
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`;

Answer 2

This appears to be a bug: http://bugs.mysql.com/bug.php?id=60650

I submitted this question as an example.