CODEWITHSUNDEEP
mysqlalter-table
ToolmakerSteve
ToolmakerSteve

Reputation: 21482

Correct mysql syntax to change a column with collation

This line of code was generated automatically by phpmyadmin (perhaps an old version running on my company's website, but I don't want to deal with that right now):

ALTER TABLE `lc_error_logs` CHANGE `OneDetailedMessage` `OneDetailedMessage` VARCHAR(5000) CHARSET=latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

When attempt to execute that SQL, get error message via phpmyadmin:

Query error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to user near '=latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL' at line 1.

I don't see what is wrong with the query, and would like to know what is wrong.

Notes:

  1. All I did was change the VARCHAR length.
  2. Works fine if I delete the column, then recreate it with these same settings. Is just something wrong with that ALTER TABLE syntax.
  3. I can change INT columns in the same table (e.g. INT to SMALLINT), without any problems. Something about the COLLATE? But that is the collate we use throughout this DB.
  4. mysql version: "5.5.47-0ubuntu0.14.04.1"
  5. It doesn't matter what values I use for VARCHAR length. It doesn't matter what I attempt to change (e.g. changing column name has the same error.) And I've had this problem before on other tables. I've never been able to successfully alter a VARCHAR in this DB, if I specify collation. (Even though this is the default collation for this DB, generated automatically when I do not specify collation.)

Upvotes: 0

Views: 3191

Answers (2)

Luca C.
Luca C.

Reputation: 12594

ALTER TABLE `lc_error_logs` CHANGE `OneDetailedMessage` `OneDetailedMessage` VARCHAR(5000) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL;

try CHARACTER SET instead of CHARSET=

Upvotes: 1

ToolmakerSteve
ToolmakerSteve

Reputation: 21482

The problem is with the CHARSET/COLLATE part. If I remove that, the query works (uses the DB's defaults for charset and collation):

ALTER TABLE `lc_error_logs` CHANGE `OneDetailedMessage` `OneDetailedMessage` VARCHAR(5000) NULL DEFAULT NULL;

Or the equivalent, slightly simpler:

ALTER TABLE `lc_error_logs` MODIFY `OneDetailedMessage` VARCHAR(5000) NULL DEFAULT NULL;

NOTE:

I still don't know what the correct syntax is, or whether it is an issue with how the DB is set up. So if someone can supply an answer that works even with specification of collation, I will accept that answer. Otherwise, I will accept this answer, and move on.

Upvotes: 0

Related Questions