Geek Answers Handbook

MySQL unknown column error when using ALTER, do not understand the behavior

I was wondering if anyone could help me.

I have weird behavior when issuing an ALTER command. The command comes from MySQL Workbench synchronization, and it fails. 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 on its own, and:

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

also works on its own.

I do not understand why, when both of them are combined in one request, this does not work and says that "created" does not exist. I know that it definitely exists.

Note that the change column for 'created' does not bother me; it is generated by MWB when comparing and preparing for synchronization. But it was just interesting why both actions could not be applied to the same query.

I am using MySQL 5.5.8

Refresh

I can really do a few articles in order. I do it on other tables just fine.

I forgot to mention this. But when I delete 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 it does:

 ALTER TABLE `logs` ADD COLUMN `updated` DATETIME NULL DEFAULT NULL, CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`
+4
source share
3 answers

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

In your example, which will 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`;
+2
source

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

I presented this question as an example.

+2
source

From the documentation

You can issue multiple ADD, ALTER, DROP, and CHANGE statements in a single ALTER TABLE statement, separated by commas. This is a MySQL extension for standard SQL that allows only one statement for each ALTER TABLE statement. For example, to delete multiple columns in a single expression, do the following:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

Thus, you are allowed to issue

ALTER TABLE t CHANGE ..., CHANGE ...

Not a combination of various modification operators. I do not know, although 5.5.8 changed this behavior.

+1
source

More articles:


All Articles