MySQL Bugs: #27909: Can't use Null for AI PK in RS.Update with Client-side cursor

Bug #27909	Can't use Null for AI PK in RS.Update with Client-side cursor
Submitted:	18 Apr 2007 1:29	Modified:	8 May 2007 8:45
Reporter:	Tobus .	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	5.1 alpha	OS:	Windows (XP/2000)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	AddNew, ADO, null, primary key, recordset, UPDATE

Description:
When you try to add a new record use ADO Recordsets, you get a "Multi-step ODBC failed" error if you use Null for an auto-increment primary key. This used to work under 3.51.12

I believe this *may* be a side-effect of the bugfix for #10130, where PK fields are now returned as non-nullable.

The workaround is to use 0 instead of Null for new records, but for large projects this could be a huge effort.

How to repeat:
If "Table1" has one field "Key" that is an auto-increment primary key, the following code works under 3.51.12 but fails with 3.51.14 (this code is VB6)

Set RS = New ADODB.Recordset
RS.Open "Select * From Table1 Where False"
RS.AddNew
RS("Key") = Null
RS.Update

The error occurs on the 'RS("Key") = Null' line. 

Suggested fix:
Remove the non-Nullable flag on autoincrement fields in AddNew?

Hi and thanks for your report. I am sorry but I can't repeat the problem with test case attached. Can you review it and tell me what you think?

Environment: MySQL 5.0.38BK on WinXP Pro SP2 localhost. MyODBC 3.51.14GA.
Results:
  MyODBC 3.51.14GA
070502  8:41:02	      4 Connect     root@localhost on test
		      4 Query       SET SQL_AUTO_IS_NULL=0
		      4 Query       select database()
		      4 Query       drop table if exists bug27909
		      4 Query       create table bug27909(x int unsigned not null auto_increment primary key)
		      4 Query       SELECT * FROM bug27909
		      4 Query       INSERT INTO bug27909 ( x) VALUES(0)
070502  8:41:03	      4 Query       drop table if exists bug27909
		      4 Quit       

connector/ODBC v5 (snapshot)
070502  8:39:15	      1 Connect     root@localhost on test
		      1 Query       SET NAMES utf8
		      1 Query       SELECT database()
		      1 Query       SELECT database()
		      1 Query       SELECT database()
		      1 Query       drop table if exists bug27909
070502  8:39:16	      1 Query       create table bug27909(x int unsigned not null auto_increment primary key)
		      1 Query       SELECT * FROM bug27909
		      2 Connect     root@localhost on test
		      2 Query       SET NAMES utf8
		      2 Query       INSERT INTO bug27909 ( x) VALUES(0)
		      2 Quit       
		      3 Connect     root@localhost on test
		      3 Query       SET NAMES utf8
		      3 Query       drop table if exists bug27909
		      1 Quit       
		      3 Quit

Test case

Attachment: 27909.vbs (application/octet-stream, text), 1.79 KiB.

In your test script you have ".CursorLocation = 1" (ie adUseNone). I am using adUseServer (the default). I was able to duplicate the error with both adUseServer and adUseClient. Can you please try with adUseServer and see if you can duplicate it.

Thanks
Toby

Toby, please read http://dev.mysql.com/doc/refman/5.0/en/cursor-restrictions.html, especially "Cursors are read-only; you cannot use a cursor to update rows.".

You may also check http://dev.mysql.com/doc/refman/5.0/en/myodbc-usagenotes-functionality.html 
23.1.6.1.2. Dynamic Cursor Support
Support for the dynamic cursor is provided in Connector/ODBC 3.51, but dynamic cursors are not enabled by default. You can enable this function within Windows by selecting the Enable Dynamic Cursor checkbox within the ODBC Data Source Administrator.
On other platforms, you can enable the dynamic cursor by adding 32 to the OPTION value when creating the DSN. 

The actual error, 80040E21, is coming from MS cursor engine (http://msdn2.microsoft.com/en-us/library/ms722707.aspx) not being able to deal with NOT NULL field being passed NULL value and has nothing to do with us AFAIS. You don't want us to declare NOT NULL field as being nullable, don't you? In any case, this cursor engine is buggy (http://support.microsoft.com/kb/247029) and weird so, to me, not defining cursor location, thus bypassing MS cursor engine, seems the best way to go.

I have consulted and here's the news:

As the behavior of MyODBC 3.51.14 regarding null values for PK has indeed changed you might try using a new MyODBC option introduced intentionally in order to keep the compatibility with old application that still expect the result for SELECT * FROM tab WHERE PK IS NULL: OPTION=8388608.

Regards.

Hi Tonci,

Thanks for your quick reply.

The main reason I filed this as bug is that it breaks compatability with previous versions. Versions up to 3.51.12 did not show this behaviour, and upgrading to 3.51.14 will break any programs that use it. As this appears to be an unintended side effect of the fix for #10130, I really feel that it should be fixed if possible.

The original problem (in bug #10130) was that MyODBC was returning autoincrement primary keys as "NULLABLE" fields, and because of this .Net was assuming they couldn't be Primary Keys and thus not marking them as such. In MySQL however, auto-increment primary keys have a default value of NULL and *can* be set to NULL using SQL:

mysql> show columns from bug27909;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| x     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+

mysql> insert into bug27909 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> update bug27909 set x = null where x = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

The problem is that .Net mishandles the primary key flag, not that MyODBC returns NULLABLE. As a workaround, the fix for #10130 returns NOT NULLABLE for the column, meaning .Net will allow it to be a Primary Key, but also meaning that existing programs that use NULL in a recordset now fail.

It seems to me that in practice, an autoincrement primary key field is NOT NULLABLE for SELECTs and UPDATEs*, but _is_ NULLABLE for INSERTS. I believe the best solution that both a) correctly represents the actual NULL-ability in MySQL and b) allows .Net to correctly recognise Primary Keys is to set the field to NOT_NULLABLE initially, then change it to NULLABLE if and when the AddNew method is called. This means that SELECTs and UPDATEs will see still see the field as NON-NULLABLE, but INSERTs will again be able to use NULL.

I don't know how the internal stuff of ADO->ODBC works, but if the AddNew method invokes a routine in the MyODBC code then it should be possible to do, yes?. If not, then I think at least an option to preserve the old behaviour for us non-.Net users (as well as those .Net users who don't need to query the Primary Key status of a field) would be preferable to the current situation.

I have changed the status back to "Open" because this seems to be a workaround for a specific .Net wierdness that is incompatable with earlier versions and breaks existing applications. I think the creation of an error when using "NULL" to add a new record was unintentional and unexpected, and that a solution can be found that would allow both situations to work.

Thanks again for your time,
Toby

* The warning when UPDATEing to NULL says the default value for the data type (0) is used - not the next autoinc value, and UPDATEing more than one column to NULL returns a duplicate key violation - it doesn't "work" in the same way that inserting NULLs does.

Hi Toby and thanks for sharing your thoughts with us.

> The main reason I filed this as bug is that it breaks compatability with previous versions. Versions up to 3.51.12 did not show this behaviour, and upgrading to 3.51.14 will break any programs that use it. As this appears to be an unintended side effect of the fix for #10130, I really feel that it should be fixed if possible.

Did you tried OPTION=8388608?

> The problem is that .Net mishandles the primary key flag, not that MyODBC returns NULLABLE. As a workaround, the fix for #10130 returns NOT NULLABLE for the column, meaning .Net will allow it to be a Primary Key, but also meaning that existing programs that use NULL in a recordset now fail.

I think the patch in question is "in place" and according to standards.

> It seems to me that in practice, an autoincrement primary key field is NOT NULLABLE for SELECTs and UPDATEs*, but _is_ NULLABLE for INSERTS. I believe the best solution that both a) correctly represents the actual NULL-ability in MySQL and b) allows .Net to correctly recognise Primary Keys is to set the field to NOT_NULLABLE initially, then change it to NULLABLE if and when the AddNew method is called. This means that SELECTs and UPDATEs will see still see the field as NON-NULLABLE, but INSERTs will again be able to use NULL.

From what I see at the first glance this would require a lot of work not to mention bending and stretching of specifications. Since ODBC is well documented and established standard I don't think this is good approach.

Taken in account last two of your quotations I am willing to declare this "Verified" but only as feature request (S4) and if you agree...

So?

Hi Tonci,

Thank you for the time you've spent on this. I'm happy to downgrade it to S4 (and have done so), although I think it is more serious than this (and so did the clients who phoned in when their websites had stopped working).

I haven't had chance to look at the option yet, but from reading it's description it's more about returning the last inserted id than allow null in an update. I should be able to test it later this week, and will let you know if it changes anything.

Toby

Toby, please do test as the result will determine my ruling on the matter.

Hi Tonci,

I tried with both OPTION=8388608 and with "Allow auto increment null search" checked, but it made no difference. RS("x") = Null failed in every case.

Toby

Ok Toby, thanks.

Verified as described. "Non Nullable column can not be updated to NULL", error is thrown by MS cursor engine. Test case is attached (27909.vbs) one has only to add new option 8388608 to it.

Current 3.51 works as expected with option 8388608 while 5.1 fails thus change in Version.

If possible, take Bug#41256 into consideration while fixing this.

Lawrin, keep http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#operator_is-null in mind.