default values problem with mysql version 5

Anton Jungreuthmayer

unread,

Aug 17, 2007, 11:16:34 AM8/17/07

to

I have an application written in java with jBuilder.

This uses JDBC to access a mySQL database which I am converting from
version 4.1 to version 5.0

When my application tries to INSERT a record but does not specify a
value for a text field that is defined as NOT NULL with no default set
the following error message is returned :

Field 'somedata' doesn't have a default value

this doesn't happen with JDBC on mysql 4.1
It also doesn't happen when I run the command in mysql, or when using
mySql query browser but only when I connect using JDBC

Any clues ?

Anton

Paul Nichols [TeamB]

unread,

Aug 17, 2007, 11:46:01 AM8/17/07

to

This is probably a result of the changes to mySQL 5 and the JDBC
Drivers. Which MySQL JDBC driver (5.1 is the latest) are you using and
which JDK version?

This is not going to be related to JB, unless you are using the
QueryDataSet, which may or may not work correctly with newer JDBC
implementations.

Anton Jungreuthmayer

unread,

Aug 17, 2007, 11:51:07 AM8/17/07

to

JDBC driver - 5.0.6
java version 1.5.0_04-b05

Paul Nichols [TeamB]

unread,

Aug 19, 2007, 2:45:09 AM8/19/07

to

Anton Jungreuthmayer wrote:
> Paul Nichols [TeamB] wrote:
>> Anton Jungreuthmayer wrote:
>>> I have an application written in java with jBuilder.
>>>
>>> This uses JDBC to access a mySQL database which I am converting from
>>> version 4.1 to version 5.0
>>>
>>> When my application tries to INSERT a record but does not specify a
>>> value for a text field that is defined as NOT NULL with no default
>>> set the following error message is returned :
>>>
>>> Field 'somedata' doesn't have a default value
>>>
>>> this doesn't happen with JDBC on mysql 4.1
>>> It also doesn't happen when I run the command in mysql, or when using
>>> mySql query browser but only when I connect using JDBC
>>>
>>> Any clues ?
>>>
>>> Anton
>>

>> This is not going to be related to JB, unless you are using the
>> QueryDataSet, which may or may not work correctly with newer JDBC
>> implementations.
>>
>
> JDBC driver - 5.0.6
> java version 1.5.0_04-b05

Thanks for this info. The problem has to do with a commit error with V5
and the newer mySQL database transaction mechanism. It is a bug and has
been reported.

To get around this issue try setting jdbcCompliantTruncation=false. This
will prevent the exception from being thrown before it gets to the MySQL
Server.

See example below:

Class.forName("com.mysql.jdbc.Driver");
String dburl = "jdbc:mysql://server/TEST";
String dburlTrunc =
"jdbc:mysql://server/TEST?jdbcCompliantTruncation=false";
String dbuser = "TEST";
String dbpass = "TEST";
Connection connection = DriverManager.getConnection(dburl, dbuser,
dbpass);

Anton Jungreuthmayer

unread,

Aug 19, 2007, 5:02:29 AM8/19/07

to

Thank you so much - that has fixed it.

Paul Nichols [TeamB]

unread,

Aug 19, 2007, 10:29:40 PM8/19/07

to

Anton Jungreuthmayer wrote:
> Paul Nichols [TeamB] wrote:

> Thank you so much - that has fixed it.

Super!!! Glad that did the trick.

Just mark this down somewhere. It is a JDBC bug in the mySQL drivers,
not with Java JDK or JBuilder.