MySQL Bugs: #29659: ODBC-driver does not allow to assign NULL to a (varchar)field

Bug #29659	ODBC-driver does not allow to assign NULL to a (varchar)field
Submitted:	9 Jul 2007 19:29	Modified:	17 Oct 2007 16:08
Reporter:	Louis Breda van	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.16	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	ADODB, key, null, ODBC, varchar

Description:
Hello,

I try to use MySQL 5120 64 bit (INODB) as backend for MsAccess via a local ODBC-connection.

I just started testing and noticed two problems:

1) mysql odbc requires that you have either a primairy key, or define a group of fields as sutch. IMHO, that is wrong! I only define a primairy key, if I use that key. Beside that, it is quite unlogical to me that the darabase engine  does not know which record it is processing at a certain moment. This problem relates to at least 5.0 driver, I did not test 3.5

IMHO this is a serieus bug, which can be bypassed however by defining a primairy key. 

2) I can not assign NULL to wards a (varchar)field over the ODBC-connecion.
This is a critical one, since I can not substitute NULL by something else. 
NULL is e.g. definitly not equal to "0"

This problem is related to both 5.0 and 5.31 driver

Note that I am using ADODB recordsets.
- 3.51 driver options OPTION=3;Packet Size=8192
- 5.0 driver options OPTION=3;Packet Size=4096

Sincerely,

Louis
 

How to repeat:
open a adodb connection (cn)
open a adodb recordset (rs) on that connection
(rs.Open "MyTable", cn, adOpenDynamic, dbOpenDynaset)

cn.BeginTrans

while blabla < blaba2

 rs.field1 = y (OK)
 rs.field2 = NULL <== here and on the moment the statement is reached, there is
an error (translated: You are trying to assign Null to a field which is not of type variant)

   blabla2 = blabal2 + 1
wend

cn.CommitTrans

also note that infact the statement is rs.field2 = rs_msaccess.fieldx which contains null

The ODBC driver is not preventing this, try using:

rs.field2 = vbNull
or
rs.field2 = Nothing

Louis, did you tried what Jess suggested?

Tonci, Jess,

Something is wrong on my system. I did the initial "test (big word)", after installing the db.

Yesterday and today, I did thy to test again, but 
- I can access a mysql table when linking the table from the access gui (odbc)
- but for some stupic reason, I can not access any thing using visual basic
even simple thinks like open a recordset does not work (3x and 5x), see code below; at open ODBC does not support the options ???

So, probalbly I am doing some stupid thing here or there is something not correctly configured.

What ever, I have to find out what first.

Louis
---------

Sub tstmysql()

    Dim cn_tmp As New ADODB.Connection
    Dim rsTst As New ADODB.Recordset
    Dim connTempDBTst As String

'## below example how I switch between backend DB
'Public Const connTempDB As String = cMsAccessEngine + cTempDB
'Public Const connTempDB As String = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=tempdb;UID=root;PASSWORD=xxxxx;OPTION=3;Packet Size=8192"
'Public Const connTempDB As String = "DRIVER={MySQL Connector/ODBC v5};SERVER=localhost;DATABASE=tempdb;UID=root;PASSWORD=my5120;OPTION=3;Packet Size=4096"

'## here the test connection strings    
    connTempDBTst = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=oeps;UID=ikke;PASSWORD=xxxxx;OPTION=3;Packet Size=8192"
    'connTempDBTst = "DRIVER={MySQL Connector/ODBC v5};SERVER=localhost;DATABASE=oeps;UID=root;PASSWORD=xxxxx;OPTION=3;Packet Size=4096"

    cn_tmp.Open connTempDBTst

'## statement below is rejected (options not supported ??)
    rsTst.Open "mytable", cn_tmp, adOpenDynamic, adLockOptimistic

    Stop

    rsTst.Close
    Set rsTst = Nothing

    cn_tmp.Close
    Set cn_tmp = Nothing

End Sub 

'## I asume the problem is somewhere else

Tonci, Jess,

I found a couple of problems:

1) I could not open the table, and I assume also a query, because the mysql odbc driver does not understand / support the regular syntax to open a table / query.

That syntax is IMHO to open a table is rs.open, "Table-/QueryName/(sql-string)", cursoroption, lockingoption

However rs.open "TableName" and probably also "QueryName" gives an error

For a table (not a query I suppose), I could bypass this error by using "SELECT * FROM tablename"

2) I tryed Jess his suggestions for testing null behavoir
- when assigning vbNull to a varchar / text field the content becomes "1"; thant is not OK of course but it happened for all the three tested odbc connections (mysql3, mysql5011 and towards an second msaccess DB
- assigning "nothing" is not allowed with all three connections (type does not match)
- I think assigning null towards a field by rs1.field = rs2.field (containing null) is quite different

3) I also tried commit behavoir:

cn.begintrans

inserts / updates

cn.CommitTrans
        

that is accepted with odbc 3 but *NOT* odbc50011 (dit not try nightly build)
(transaction can not have posible multiplerecordsets ????)

So, for that reason alone, I can not use odbc5

Sincerely,

Louis

Hello Louis,

thanks for writing a bug report.
Unfortunately, we could not reproduce your error.
It seems, there is something wrong in your database design.
Always recommended is a design by using the 3rd normal form.

Maybe it is necessary to look into your Entity Relation Ship Model with all datatypes and constraints to figure out more.

Please use the newest version of ODBC 3.51. This should work for your project.

Your question to transactions:
Transactions are senseful for queries, that will modify your data.
For example: You have a bank account table for costumer X and a bank account table for costumer Y. X wants to transfer money to Y.
Now you have to make sure, that the money is subtracted from the X account and added to the Y account.
You make an update on both tables.
But the network can shut down or something else happened during your updates.
You have to be sure, that really both tables will be updated or non of these tables.
Therefor you use transactions. You put both update into one transaction. If one of the updates fail, the other will be rolled back. Only if both updates were ok, the commit happened.
The second and most used method for this is writing a procedure and using this instead of two updates.

Regards,

Susanne

Louis, I'll just add to what Susanne said:
1) I could not open the table, and I assume also a query, because the mysql odbc driver does not understand / support the regular syntax to open a table / query.

Already verified as a problem in Bug#28098

2) I tryed Jess his suggestions for testing null behavoir...

I think this is a bug in ADO library as same problem is noticed in JetEngine (Bug#28294, I think)

Other NULL behavior related changes: Bug#27909

And, of course, there's your's Bug#27896

"Packet Size" option is new to me so I can't comment on that.

Please, let's stay focused on version 3.51 as it's stable and leave v5 out of discussion for now, or at least until we determine what's wrong in 3.51.

Louis, Bug#27755 may contain some useful info.

Susanne,

First you are considering my DB-structure to be the cause of the transaction problem. However:
- I can simply reproduce the problem with one mysql db containing only one table   with only two fields (an integer as PK and a tezt field), not any futher constrain. 
- so nothing to blame there IMHO, however I agree with tonci that we should have a working 3.xx version first

Second, I am surely not a fan from completely normalised database, due to lots of tables and overhead and probably large performance impact. 

That Ofcourse does not mean that you should not carefully think about table design!

Sincerely,

Louis

Louis, what now? We can't reproduce the problem here and Jess and Susanne seem not to see a bug here.

do not know,

I can just add that I found another error

rs.open "SELECT * FROM MyTable" ,option ,option works, however only up to an .update there it expect "SELECT * FROM MyTable;" (the difference is the ";).

Must say that IMHO the only correct syntax is with ";",  but it is almost ever written without ";".

This brings me to another point being the config files and gui, there the syntax in relation to eg "/" or "\" is also quite "unclear". In the same file you meet every variant of "/" and "\" I realy don't know what is correct, intended or accepted :< :<

Louis 

Louis

Louis, "\" is for windows and "/" is used on 'nix. If you can quote exact page I'll ping docs team.

As for the rest of report, I asked for another review to make sure if there's bug here.

Louis:

> 1) mysql odbc requires that you have either a primairy key, or define ...

This is the default behavior of *MS Access* as it always ask for PK field before declaring *anything* updateable. This has nothing to do with MyODBC.

2) I can not assign NULL to wards a (varchar)field over the ODBC-connecion.

We've been over this. We can't reproduce it.

I propose we close this report and you can open another one about things reported later in this. Do you agree?

Tonci,

I used the mini tst DB to recheck
 "rec1!field = rec2!field where rec2!field does contain null"

It worked. I do not know why I got the message when starting the real db, which is lots lots more complex.

I see two major differences between the test DB ans the real DB:
1) in this simple test DB I only access the remote DB over the visual basic connection and not on *both* a) a visual basic conection and b) a gui table connection

2) In the real DB I do not have one backend DB but many backend DB's even from different type (msacces and mysql(for test only at this moment)).

Both points makes things difficult. One on the problems I am facing now is that most of the querys do contain tables physicly stored in multiple backend DB. Visual Basic ADO-recordsets can only opened against one connection. That is a conflict on programming level.

A conflict I can only bypass by using a connect to the main DB instate of the real backend(S). The Main DB has all remote tables linked in the gui which solves the problem (in a less elegant way).
However it might be that this solution only works as long as all backends are of the same type, perhaps even only if they all of type msaccess.

For the moment using msaccess backends is working quite well. Never the less It would be more flexible to have the option to use other backends, however not at any cost.

So, to make a long story short, in a simple one connection situation I can not reproduce the problem as well

Louis

Louis, the main principle of bug-fixing is to have small, self-sufficient test case which reproduces the bug whenever it's run. Without that we can't do anything (can't fix what you can't see).

Here we have several problems as, beside MySQL SW, you use Access and ADO etc. over which we have no control. There is also an issue of versions which may vary greatly from machine to machine (thanks to MS).

So, I propose again for this report to be closed (to many issues raised at the same place) and, if you want, open separate report for 1 problem at the time. Please report against 3.51 branch as it is stable. We can deal with v5 later.

Thanks for your interest in MySQL.

Tonci,

I am a DB user/programmer, using msaccess and considering using mysql as backend. That is my position. Regulairly, I notice a problem and I report that. 
I also try to give some background info, so that you understand where my problems are.

I have a real live envirement, not a test envirement. That has advantages and disavantages. 

Since this bug can not be reproduced for now (by the both of us), close it. Perhaps it hapens again in a reproducable situation later.

Louis

Louis, thanks, I think this is the best way to go, for now. BTW MyODBC 3.51.17 is almost out with numerous bug fixes so you might give it a shot.

Thanks for your interest in MySQL.

Tonci,

Not directly related to this bug, some (bad) experiences with the latest release:
- I am testing with one of my backend DB's replaced with a MySQL equivalent
tesing on VISTA 64 machine with 64 bit 5120
- ODBC 5 => forget it
- ODBC 3.16 => after some querys the system hangs due to missing record (SQL form bug may be)
- ODBC 3.17 => after a few querys 5120 engine passes away on an runs sql "delete * from table"; Note that same statement has been sucsesfully executed a few times before the problem ocurred.

So, regret to say, not working yet.

Louis

Tonci,

- the problem that an existing error does not show up via odbc, also occurs when using 3.17

Louis

Sorry

the sentence should of course have been:
- the problem that an existing *record* does not show up via odbc, also occurs when using
3.17

Louis

Louis, I must admit, I've lost you... We need another report.

As for original problem, we can't repeat it (see attached test case)...

C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.44-max-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from bug29659;
+---+---------+
| x | VCField |
+---+---------+
| 1 | NULL    |
+---+---------+
1 row in set (0.00 sec)

mysql>

--------
070718 17:29:38	      4 Connect     root@localhost on test
		      4 Query       SET SQL_AUTO_IS_NULL=0
		      4 Query       select database()
		      4 Query       SELECT @@tx_isolation
		      4 Query       drop table if exists bug29659
		      4 Query       create table bug29659(x int unsigned not null auto_increment primary key, VCField VARCHAR(30) DEFAULT NULL)
		      4 Query       SELECT * FROM bug29659
		      4 Query       INSERT INTO `test`.`bug29659` (`x`,`VCField`) VALUES (0,'Test')
		      4 Query       SELECT @@IDENTITY
		      4 Query       UPDATE `test`.`bug29659` SET `VCField`=NULL WHERE `x`=1 AND `VCField`='Test'
070718 17:29:39	      4 Quit

Test case

Attachment: 29659.vbs (application/octet-stream, text), 1.93 KiB.

Tonci,

All the problems I reported today, can be easely reproduced by me by running a DB report batch. (I thing > 95% change that the problems will occur).

Best way to find the problem is probably when I activate certain traces, perhaps even better using a DB version with extra debug code.

Please advice.

Louis

Louis, I would advice this as a general rule when creating report:
 - Open new report
 - Add full test case to it. Compact but self-contained
 - Add ODBC trace (Control Panel / Administrative tools / Data Sources (ODBC) / Tracing / Start tracing now
 - Add relevant part of MySQL server log (add "log" to my.ini or start server with "--log", without apostrophes, of course)
 - Use MyODBC 3.51.17 as it is a stable branch
 - Provide MDAC info on your system (relevant part)

Tonci,

I did start a report run with the indicated traces over a hour ago. That does not work.

The reasons are:
- MySQL is only connedcted as "expirement" containing two small tables, but
- the other backend DB's do together contain a few GByte of data
- mysql 3.17 can not be configured over the ODBC-nterface, the registery is not properly set
- the odbc-trace is almost sure mainly tracing the other big database querys
- after more than an hour not even one report has been generated, but the trace was already 100 MB

So tracing in this way, is hardly an option.

Sincerely,

Louis

Louis, what's the status of your problem? For us, still CRp...

Tonci,

I think I did hardly do any testing with mysql in the past few month. 

The first half year, I was trying to migrate my backend DB to MySQL. Too much trouble by then, so I developed work arrounds to bypass MsAccess 2Gbyte limmitation (thats why I did consider MySQL).

Probably MySQL and the ODBC-driver has become better in the mean time and I would love to test that, but that is hardly possible / would imply to many changes by now.

However, I am surely working in a direction which makes ik easyer again to replace the actual backend MsAccess DB by any other DB-types. 

I will probably try again later this year.

Sorry I can't test now.

Louis

Louis, I wish you luck in your efforts and thanks for your interest in MySQL.