MySQL Bugs: #41256: Incorrect handling of "Null" under certain conditions

Bug #41256	Incorrect handling of "Null" under certain conditions
Submitted:	5 Dec 2008 12:14	Modified:	27 Feb 11:22
Reporter:	Louis Breda van
Status:	Closed
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	5.1.5	OS:	Microsoft Windows (VISTA64)
Assigned to:	Bugs System	Target Version:	5.1
Tags:	null, ODBC
Triage:	D3 (Medium)

Description:
Hello,

I noticed that ODBC5 is updating fields which "unexpected" values where the value should
be Null.

This applys at least to BIGINTS but perhaps also to other field types (do not know).

In case of the BIGINT I mostly see 18446744069414584320 inserted but somtimes "3". 

As far a I know, ODBC3 is behaving correctly.

Sincerely,

Louis

How to repeat:
** Test two times:

One time with ODBC3 activated ==> OK
One time with ODBC5 activated ==> NOK !! 

Check record content between each stage using MySQL Query browser

Start and EndOrderkey will *NOT* be assiged value Null at the end but have 
the value 18446744069414584320.
Under other yet unkown conditions I have also seen a value of "3" in state of the assigen
value Null   

Values which  

Option Compare Database
Option Explicit

Public Const cStrODBC3a_Options As String = "8195" 'CStr(cFLAG_FIELD_LENGTH +
cFLAG_FOUND_ROWS + cFLAG_NAMED_PIPE)
Public Const cStrODBC5a_Options As String = "67117059" 'CStr(cFLAG_FIELD_LENGTH +
cFLAG_FOUND_ROWS + cFLAG_MULTI_STATEMENTS + cFLAG_NAMED_PIPE)

'Public Const connTstDB As String = "DRIVER={MySQL ODBC 5.1
Driver};SERVER=localhost;DATABASE=test;UID=thatsme;PASSWORD=louis;OPTION=3;Packet
Size=8192"
'Public Const connTstDB As String = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=test;UID=thatsme;PASSWORD=louis;OPTION=3;Packet
Size=8192"

Public Const connTstDB As String = "DRIVER={MySQL ODBC 5.1
Driver};SERVER=localhost;DATABASE=test;UID=thatsme;PASSWORD=louis;OPTION=8195;Packet
Size=8192"
'Public Const connTstDB As String = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=test;UID=thatsme;PASSWORD=louis;OPTION=67117059;Packet
Size=8192"

'-- MySQL Administrator dump 1.4
'--
'-- ------------------------------------------------------
'-- Server version   5.1.30-community

'/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
'/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
'/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
'/*!40101 SET NAMES utf8 */;

'/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
'/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
'/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

'--
'-- Create schema in_deltas
'--

'CREATE DATABASE IF NOT EXISTS in_deltas; USE in_deltas;

'--
'-- Definition of table `TableHavingNullProblems`
'--

'DROP TABLE IF EXISTS `TableHavingNullProblems`;

'CREATE TABLE `TableHavingNullProblems` (
'  `Nature` varchar(4) NOT NULL,
'  `NumberKey` varchar(11) NOT NULL,
'  `TheNummer` varchar(11) DEFAULT NULL,
'  `Hfd` varchar(1) DEFAULT NULL,
'  `Order` int(10) DEFAULT NULL,
'  `AddrKey` varchar(35) DEFAULT NULL,
'  `StartOrderKey` bigint(20) unsigned DEFAULT NULL,
'  `StartStatus` varchar(3) DEFAULT NULL,
'  `EindStatus` varchar(3) DEFAULT NULL,
'  `EndOrderKey` bigint(20) unsigned DEFAULT NULL,
'  `OrderType` varchar(150) DEFAULT NULL,
'  `DetailKey` varchar(60) DEFAULT NULL,
'  PRIMARY KEY (`Nature`,`NumberKey`)
') ENGINE=InnoDB DEFAULT CHARSET=latin1;

Sub TestNull2()

    Dim cn As New ADODB.Connection
    Dim rsTst As New ADODB.Recordset

    cn.Open connTstDB

    cn.Execute "truncate TableHavingNullProblems;"

    rsTst.CursorLocation = adUseClient
    rsTst.Properties("Update Criteria") = adCriteriaKey
    rsTst.Open "SELECT * FROM TableHavingNullProblems;", cn, adOpenDynamic,
adLockOptimistic

    rsTst.AddNew
    
    rsTst!Nature = "ape"
    rsTst!NumberKey = 1
    rsTst!DetailKey = "Dummy"
    
    rsTst.Update
    
    Stop

    rsTst.MoveFirst

    rsTst!Order = 123
    rsTst!HFD = "J"
    rsTst!AddrKey = "SomeKey"
    rsTst!StartOrderKey = 20081204113622#
    rsTst!StartStatus = "ape"
    rsTst!EindStatus = "ape"
    rsTst!EndOrderKey = 20081204113623#
    rsTst!OrderType = "any"

    rsTst.Update

    Stop

    rsTst.MoveFirst

    rsTst!Order = Null
    rsTst!HFD = Null
    rsTst!AddrKey = "No match!"
    rsTst!StartOrderKey = Null
    rsTst!StartStatus = Null
    rsTst!EindStatus = Null
    rsTst!EndOrderKey = Null
    rsTst!OrderType = Null

    rsTst.Update

    Stop
    
    rsTst.Close
    Set rsTst = Nothing
    
    cn.Close
    Set cn = Nothing

End Sub

Hello,

I should also remark another ODBC5 Null bug here.

When trying to assign Null to a Decimal field you will get the error:
'OUT OF RANGE'

Louis

Louis, I see... Asked Jess to take a look.

patch + test

Attachment: bug41256.diff (application/octet-stream, text), 3.77 KiB.

Jess Thanx,

But, test at my side has to wait for the binairy, since I am not compiling my own binairy
(perhaps some time in the futhure, who knows :>). Too complex for now.

Sincerely,

Louis
PS. I Assume this fixed both problems the Bigint and the decimal one !?

Vb6 Project to test the bug on Null Fields with Driver 5.1.5

Attachment: Bug-Odbc5-41256-vbtest.zip (application/zip, text), 3.16 KiB.

Hello, i am working on w2000sp4 and XpProSp3 .
My access to MysQL is through ADO with MyOdbc Driver.
Odbc Driver 5 don't work correctly with Null Fields as Driver 3 do it .
For standard fields with integer, bigint or mediumint, the final value stored in the base
is unpredictable when they are assigned with NULL on Insert statement ... Generaly, first
fields=0, last field=[value>0]

For extra field with auto-increment specification:
when assigning this field to NULL, the stored value is generaly always the same (23 at
this time ...), uncomfortable when it's a key ...
when assigning this field to zero, the process is correct ...

To test with VB6 : see attached file .

We have just moved from connector 3.51 to 5.1.5 for our existing ASP application and come
across this problem. Our platform is ADO/MDAC 2.8 on Win 2003.

Appears to occur when inserting or updating with an ADO Recordset object (but not on
direct execution of an update or insert statement via Connection object).

Anybody care to guess if it's worth going back to 5.1.4 until a patch is available?

Darren

All - I will put this in a dev snapshot for testing shortly.

Pushed as rev 826, will be released in 5.1.6

Jess,

I will try that relase as soon as it is available.
The actual situation, where you can neither rely on ODBC3 nor ODBC5
is ^not pleasant^.

I would really prefer to use ODBC5 every where, in a lot of cases e.g. 
subquerys I need it. However to often I find the code not working reliable.
I think in most cases related to this problem. 

So at this moment, it is causing me to much "hope and pray" / hours

Sincerely,

Louis

An entry was added to the 5.1.6 changelog:

MySQL Connector/ODBC updated some fields with random values, rather than with NULL.

Is there any expected release date for version 5.1.6?

Darren, no, not really. But we're finalizing it.

Hi, friends, good afternoon.

I have a website that used to use MySQL Database 3.
Recently, I was forced to do migration to MySQL 5.1.3.

So, I had to update the MyODBC Connector.
Now, the website is using 5.1.5 version.

And I was surprised myself when I discovered this problem of Null updates using
ADODB.RecordSet component.

I'd like to know if someone has some idea to workaround this problem, because it's
impossibile to make changes in the whole asp code. I need a couple of weeks make this
changes.

Taking advantage, I'd like when the problem would be fixed, because I've read some
comments, and this problem is happening since last year.

Regards

Sergio Menezes
São Paulo/Brazil