Bug #24278 Can't insert default value into a text colum
Submitted: 13 Nov 2006 20:13 Modified: 13 Jan 2008 14:13
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.4 OS:Windows (Windows Server 2003 R2 x64 Editi)
Assigned to: CPU Architecture:Any

[13 Nov 2006 20:13] [ name withheld ]
Description:
Trying to insert a row in into a table without specifying a value for a text column produces a SQLException.

Tested against MySQL Server 5.1.12-beta Windows x64 using connector/j 5.0.4.

Yet it works fine from connector/j 3.1.12 and from the mysql command line client.

mysql> insert into a(bar) values(1);
Query OK, 1 row affected, 1 warning (0.00 sec)

According to the 5.1 documentation on default data types:
http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html

"BLOB and TEXT columns cannot be assigned a default value."

How to repeat:
mysql> create table a ( `foo` tinytext NOT NULL, `bar` int(1) unsigned not null
default '0' );
Query OK, 0 rows affected (0.03 sec)

Then using connector/j try to run:

insert into a(bar) values(0)

the result is:

java.sql.SQLException: Field 'foo' doesn't have a default value
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3099)
        at com.mysql.jdbc.Statement.execute(Statement.java:695)
        at MySQLDefault.main(MySQLDefault.java:34)
[13 Nov 2006 20:52] Mark Matthews
Connector/J 5.0.x uses the sql_mode "strict_trans_tables" to catch truncation errors without having to scan all warning messages coming back from the server, and this causes an error to be raised when you don't specify values for columns without DEFAULTs as well.

You can work around this issue by setting "jdbcCompliantTruncation=false" in your JDBC URL, however to me it appears you have an application issue in that you're creating INSERT statements that have no values specified for columns that don't have DEFAULTs.
[13 Nov 2006 21:09] [ name withheld ]
I guess the error isn't too unreasonable then, if just a little misleading.  I'm fine with this bug being closed.
[4 Jan 2008 23:52] Chris Wilson
I think there is a bug here. If you have a table that has a not null VARCHAR column, and the default is blank, you get this error when inserting with connector/j 5.1.5.

If you set "jdbcCompliantTruncation=false" or use the command line client, the INSERT works without error. There clearly is a default value for the column, it's just an empty string.

The exception thrown my MySQL should at least point users to some documentation or this bug report, but preferably no exception should be thrown in this case as there is not actually a problem with the SQL statement, it's just the connector being overly paranoid.
[5 Jan 2008 2:32] Mark Matthews
The JDBC driver has to be paranoid by default, it's required by the JDBC spec itself, and much of the framework code that sits on top of JDBC expects the paranoia to be there.

I'd argue that strict_trans_tables is broken, if it's causing the server to raise an error about defaults on types that can't have them, which is why I'm re-opening this with the category changed.
[13 Jan 2008 14:13] Valeriy Kravchuk
According to the manual, http://dev.mysql.com/doc/refman/5.1/en/constraint-invalid-data.html, current behaviour of server in strict mode is NOT a bug. Compare:

mysql> set @@sql_mode='';
Query OK, 0 rows affected (0.02 sec)

mysql> create table b ( `foo` varchar(10) NOT NULL, `bar` int(1) unsigned not nu
ll
    -> default '0' );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into b(bar) values(0);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'foo' doesn't have a default value
1 row in set (0.00 sec)

With this:

mysql> set @@sql_mode='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into b(bar) values(0);
ERROR 1364 (HY000): Field 'foo' doesn't have a default value

Same is true for TINYTEXT type. You can not have NULL value in NOT NULL column of any type in strict mode.