Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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> 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> 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.