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 20:44	Modified:	6 Jun 2007 13:56
Reporter:	Louis Breda van
Status:	Closed
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	5.00.11, snapshot	OS:	Microsoft Windows (VISTA64)
Assigned to:	Bugs System	Target Version:
Tags:	null, datetime

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.