Error #1064 when altering varchar column

Question

I'm using phpmyadmin and when I attempt to alter my table's column description from varchar(1000) to varchar(2000) I get an 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 use near '=latin1 COLLATE latin1_swedish_ci NOT NULL' at line 1

The automatically generated SQL from phpmyadmin:

ALTER TABLE `showcase` 
CHANGE `description` `description` VARCHAR(2000) CHARSET=latin1 
COLLATE latin1_swedish_ci NOT NULL;

Answer 1

The ALTER TABLE statement is not correct. CHARSET and COLLATE can't be put into column definition.

Reference:

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html http://dev.mysql.com/doc/refman/5.7/en/create-table.html

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
      [NOT NULL | NULL] [[PRIMARY] KEY]

Answer 2

Use the following query to change only the length of the VARCHAR:

ALTER TABLE `showcase` MODIFY `description` VARCHAR(2000);

If you want to set the character set too, you can use the following query (your ALTER command as valid command):

ALTER TABLE `showcase` 
CHANGE `description` `description` VARCHAR(2000) CHARACTER SET latin1 
COLLATE latin1_swedish_ci NOT NULL

Answer 3

This appears to be a bug that was fixed for phpMyAdmin 4.5.4. I suggest you update your phpMyAdmin (note that 4.6.0 is due out in the next few days) to take advantage of this and other fixes.