MySQL Bugs: #27896: Error when ^inserting^ null in a datetime field via ODBC5 driver

Bug #27896	Error when ^inserting^ null in a datetime field via ODBC5 driver
Submitted:	17 Apr 2007 18:44	Modified:	6 Jun 2007 11:56
Reporter:	Louis Breda van	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.00.11, snapshot	OS:	Windows (VISTA64)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	datetime, null

Description:
Hello,

I just noticed that my insert query breaks when it tries to insert NULL into a datefield which is alowed to be NULL. Problem occurs when using a MS-ACCESS 2003 insert query.

Please fix that in the next beta.

Sincerely,

Louis

How to repeat:
Create a table containing a datetime field without "not null" restriction. Try to insert data using a msaccess query.

Works in snapshot. Please download and unzip with 7zip.

http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.0/

Access just doesn't use that field in the insert and uses the default value. Shown in general log:

3232 Query       INSERT INTO  `test`  (`id`) VALUES (2)

Table schema is:

create table test (id int not null auto_increment, somedate date, primary key (id));

Hello Jess,

I am not sure about the test build and your testing so here a reaction:
- first I do not know how to install the nightly build. I assume running update.bat or install.bat !!??? I used install.bat  and do not see the difference
- I should explain a bit more about when I see the problem, in fact I think I notice the same problem in two different querys:
1) a query I use to reset particular fields to NULL  
UPDATE Klachten SET test.egOrderStart = Null, test.egOrderEnd = Null, etc;
2) INSERT INTO test ( ImportDate,  Source, CreationDate)
SELECT somedate AS ImportDate, 'thatsme' AS Source, otherdate
FROM test2 LEFT JOIN Klachten ON test2.MyKey = test.MyKey
WHERE (((test.MyKey) Is Null));

In both situation the query, which used to run OK when using a MS-ACCESS backend, breaks as soon as the to be inserted date happens to be NULL.

Sincerely,

Louis

Hello,

Two additional remarks:
- table name in given example was not correct, intenstion is clear I think
- i think i how to install the nightly build ( I saw a hint in another report). Among things I did was, remove driver using control panel, uninstall from commandline running as administrator, installing from commandline running as administrator. End result, ODBC driver is not correclty in the registry / does not show up anymore in the windows odbc-administrator tool (controlpanel/systemetc/odbcetc

Louis

Hi Louis and thanks for your report.

As for installing snapshot, please take a look at archive 
mysql-connector-odbc-5.0-win32-nightly-YYYYmmDD.zip\Driver\lib\ folder and take whatever is there into SysWOW64 folder overwriting old files (you may wish to backup old files before overwriting). That should do the trick. Inform me please if new driver is in place.

Tonci,

I installed the latest build (yesterday build). The insert NULL problem is still there.

I also noticed another problem:

Sometimes when I have to to a large number of updates, I build them to a couple of transactions. That in order to force commits every xxxx records.

The code looks about like this:

beginloop
  cn.BeginTrans

  group of updates

  cn.CommitTrans
 
if not ready goto begin loop

with ODBC5 there is an error when reaching the first cn.begintrans
the first time "commands out of sync; you can't run this command now

Should I start another bug report for this one?

Louis

Louis, if you can repeat INSERT NULL problem without transactions then open new report. If INSERT NULL is a problem only with transactions then correct the synopsis. Waiting on your feedback.

Tonci,

Sorry for the confusion. I noticed a couple of problems, as far as I know they are not at all related.

Louis

Hi Louis. Can we deal with this first:
 - If original NULL DateTime problem still exists with latest connector/ODBC please post complete test case exhibiting this problem. If not, we should close this report.
 - For other problems, please open new reports.

Tonci,

Durring the latest testrun with the nightly build form I think 20/4 the problem was still there. I do not think it is repaired.

I order not to mix up thinks, I will open another issue related to the transaction problem.

Sincerely,

Louis

Hi Louis. As I don't have office on my Vistax64 yet I tried with XPPro SP2 32bit. What I've found is that MS Access 2003 does not identify DATE field as being "date" but rather of Variant type thus throwing error on update with NULL value. To bypass this problem, and get to what Jess has been testing, you need to define field as being of Date type and nullable in Table designer. After that, Access doesn't use it in update statement when no data is provided:
070501 18:37:08	     18 Query       SELECT `bug27896`.`Id` FROM `bug27896`
070501 18:37:12	     18 Query       SET AUTOCOMMIT=0
		     18 Query       START TRANSACTION
		     18 Query       INSERT INTO  `bug27896`  (`Id`) VALUES (0)
		     18 Query       COMMIT
		     18 Query       SET AUTOCOMMIT=1
		     18 Query       SET AUTOCOMMIT=1
		     18 Query       SELECT `Id`,`NDat`  FROM `bug27896`  WHERE `Id` = 0
...
070501 18:37:25	     18 Quit

Tonci,

Since you where testing, I was trying to support you by reconecting my DB towards MySQL again (using ODBC5 snapshot).

However, I have one problem after the other to work arround, just too much. That is also the reason. I stopped using MySQL and certainly ODBC5 a few weeks ago.

I sincerely hope on improved versions soon. I surrely will try again. 

The transaction problem I spoke about two weeks ago, is one of the issues which stopped me. I use transactions on a couple op places in this DB you know. I simply can not work arround them.

Sincerely,

Louis

Fix committed in rev 898, will be available in 2007-05-03 and lately nightly builds

A note has been added to the 5.00.12 changelog.