🚀 go-pugleaf

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

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

JDBC driver - 5.0.6
java version 1.5.0_04-b05

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);

Paul Nichols [TeamB] wrote:
> 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);
Thank you so much - that has fixed it.

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.