MySQLSyntaxErrorException on ALTER TABLE ... CONVERT TO ... in MySQL Connector/J Running Ubuntu 14.04 with mysql-connector-java-5.1.28 installed.

Let's create a table:

CREATE TABLE mytable (value1 VARCHAR(32000), value2 TEXT, value3 VARCHAR(1000), value4 VARCHAR(32000)) CHARACTER SET latin1 COLLATE latin1_swedish_ci;


Executing the following ALTER TABLE statement via Statement.execute() fails saying that "Column length too big..*".

According to the documentation ALTER TABLE ... CONVERT TO ... should however perform automatic type conversion as necessary to ensure that the new column is long enough to store as many characters as the original column.
The statement indeed runs in the native MySQL client performing the necessary conversions.

Am I stumbling into a bug in JDBC or is this behavior expected?

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'value1' (max = 21845); use BLOB or TEXT instead
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
    at Main.main(Main.java:32)
In my previous response I have not taken into account CONVERT TO behavior, so I have tried your test, first using the mysql native client and all works fine, column are automatically converted, and then using a jdbc connector and actually I got the same error... but after some test and some research I have found the solution.

You have to add in your jdbc connection url the parameter jdbcCompliantTruncation and set it to false

jdbc:mysql://localhost/test?jdbcCompliantTruncation=false



  Should the driver throw java.sql.DataTruncation exceptions when data
  is truncated as is required by the JDBC specification when connected
  to a server that supports warnings (MySQL 4.1.0 and newer)? This
  property has no effect if the server sql-mode includes
  STRICT_TRANS_TABLES.
  
  Default: true


Source: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

Running my java code using the above connection url the table mytable are correctly converted (I have tried only with an empty table):

mysql&gt; desc mytable;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| value1 | mediumtext    | YES  |     | NULL    |       |
| value2 | mediumtext    | YES  |     | NULL    |       |
| value3 | varchar(1000) | YES  |     | NULL    |       |
| value4 | mediumtext    | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+


So, if the server runs without STRICT_TRANS_TABLES in sql_mode, ex:

mysql&gt; show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+


the jdbcCompliantTruncation=false cause the alignment of the behavior between server and jdbc client.