Menu
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`
338
To fix the error in your SELECT statement, you need to make sure that the column (s) you specified in your SQL statement actually exists in your database table. Because the error above says that user_name column is unknown, let’s check the users table and see if the column exists or not.
The MySQL ALTER COLUMN query is a MySQL statement that is responsible to change the structure of a table, either for adding a table column, altering the column, renaming the column, removing the column or renaming the table itself.
Why the #1054 - Unknown column error occurs in MySQL and how to fix it? Let’s see when the #1054 error occurs in MySQL. While inserting a varchar value, if you will forget to add single quotes, then this error will arise. You need to use single quotes around the string value to fix this error as shown below −
When you specify column names in an INSERT statement, then the error can be triggered on an INSERT statement because of a wrong column name, just like in the SELECT statement. First, you need to check that you have the right column names in your statement.
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`;
This appears to be a bug: http://bugs.mysql.com/bug.php?id=60650
I submitted this question as an example.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
