MySQL Bugs: #49031: ODBC Edit and Delete fails when using BOOL or DOUBLE in recordset

Bug #49031	ODBC Edit and Delete fails when using BOOL or DOUBLE in recordset
Submitted:	24 Nov 2009 12:04	Modified:	13 Jan 2010 7:27
Reporter:	Olivier Imbrechts	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.6	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC;Edit;Delete;BOOL;DOUBLE

Description:
I'm developping with Microsoft Visual C++ 6.0 on Windows XP SP3 using mysql server 5.1.37 and mysq connector odbc 5.1.6.

I'm using CRecordset to add/find/modify/delete entries in a mysql database.

Everything works OK when data fields are TEXT, VARCHAR, INTEGER or BLOB. When fields type are BOOL or DOUBLE, adding or searching entries still works but modifying or deleting entries fails. Both operations work OK with direct mysql calls ("DELETE FROM table", "UPDATE from table").

Symptoms are different according to the types used :

- when the type of the field is BOOL, Delete fonction returns OK but nothing is deleted. Edit function fails and displays the following TRACE:

Error: failure updating record.
Truncated incorrect DOUBLE value ' '"
State:S1000,Native:1292,Origin:[MySQL][ODBC 5.1 Driver][mysqld-5.1.37-community]

- when the type of the field is DOUBLE, both Delete and Edit functions fail and display the following TRACE:

Error: failure updating record.
Build WHERE -> insert_fields() failed.
State:HY000,Native 0,Origin:[MySQL][ODBC 5.1 Driver][mysqld-5.1.37-community]

Note: when using the previous connector version 5.1.5, BOOL type caused the same error than DOUBLE. So behaviour has changed since the the previous version.

How to repeat:
Create a CRecordset class with one field of type BOOL (then replace with DOUBLE) :

void MyRecordset::DoFieldExchange(CFieldExchange *pFx)
{
...
RFX_Bool(pFx, "value", m_value);
RFX_Double(pFx, "value", m_value);
...
}

Then add an entry into the database.

Try to delete that entry:

db.OpenEx(...);
recordset.Open(...);
recordset.Delete(...); -> SQLSetPos return -1 for type DOUBLE

Delete fails for type DOUBLE and succeed for type BOOL but nothing is deleted

Try to modify that entry:

db.OpenEx(...);
recordset.Open(...);
recordset.Edit(...);
recordset.m_value = new_value;
recordset.Update(); -> SQLSetPos return -1 for both types

Hi Olivier and thanks for your report.

I lost you along the path. Can you please attach small but *complete* test case (or project) demonstrating unwanted behavior. Please note what you expected and what you got. Also, please do put DDL/DML statements into test case so I have data you reproduced problem with.

Matching DOUBLE values is *not* an option as they are approximates, not exact values. Please do search documentation on how to make CRecordset updates based on PK values (providing primary key is *not* DOUBLE or BLOB or something similar!). General query log from MySQL server might help here as it should contain lines like "UPDATE tbl SET ... WHERE Fld1 = ... Fld2 = ... Fldn = ..." (basically determining record to update by matching every field's value).

I'm sending you a small program that shows the bug (zipped Visual C++ dsp).

The program allows to create/search/modify/delete an entry just by clicking on buttons. An entry is defined with an id (int) and a value (DOUBLE or TEXT or BIT - you have to modify the code (uncomment the corresponding lines) to change the type of the value; by default, the value is double).

First, create a database called "test_bug" ("CREATE DATABASE test_bug;").
Then create a table called "table_test" ("CREATE TABLE table_test (id INT, value_double DOUBLE);").
Finally, create a DSN called "TEST_DSN" with ODBC Data Source Administrator window (server=localhost/user=root/password=test/database=test_bug).

Using the program, try to create/find/modify/delete the entry. Creation/Search works OK but modification and delete fail.

The following TRACE message is displayed on error in debug:

Error: failure updating record
Build WHERE -> insert_fields() failed
State:HY000,Native:0,Origin:[MySQL][ODBC 5.1 Driver][mysqld-5.1.37-community]

Using SQL commands directly ("DELETE FROM table_test WHERE id=1;" for example), you still can do the operations you can't with ODBC.

If you replace the double field by a BIT field (and replace the double by a bool in the code), ODBC modification still fails and Delete returns OK but nothing is deleted.

If you replace the double field by a TEXT field (and replace the double by a CString in the code), ODBC calls works perfectly.

So the problem comes from the use of data types BIT and DOUBLE.

Olivier, I will check on your test case but I think the problem is in matching DOUBLE values via = sign... This just can't be done. See your general query log from MySQL server and try to find entry like this:
  DELETE FROM table_test WHERE id = xx AND value_double = x.yabrthkql...
This makes perfect sense in light of information that DELETE FROM table_test WHERE id = xx in command line client works as there is no  AND value_double = x.yabrthkql... part.

What should be done is:
  o Define *primary key* on table: ALTER TABLE table_test ADD PRIMARY KEY(id);
  o Find a way for MFC CRecordset to locate records based on *primary key* value. Something similar to this http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1: (ADO)

"DELETE FROM table_test WHERE id=1 AND value_double=3.14;" works perfectly when typed on MySQL Command Line Client window. The entry is deleted from the table.

The same operation through ODBC fails.

Sure it fails. Please check general query log from server and see what was actually sent. I thought this case was all so clear...

Ok, let's imagine all CPU's, frameworks, API's etc present same FLOAT identically. If you do not see why this is questionable, please refer to IEEE floating point number specification.
Then imagine this situation: A table with many columns of which several are DOUBLE, several BLOB containing fair amount of data etc.
Your DELETE query will go on *matching every* column value before defining row as the one it's searching for... Can you imagine such DELETE statement? It goes something like this:
DELETE FROM my_table WHERE acol = x, bcol=3.142357, ccol=5.67898, dcol="\dsjglifgkhcevfj hecfkhcfk.wecgkjexgvb,mjgc,cb,bljkhlhlhl.khjlk .... (and so on for many many kilobytes. Does this scenario looks sane to you?

So, instead of wasting time, please do as I suggested:
  o Define PK for the table. Each row in table *is* defined uniquely by this value (as is the case in SQL)
  o Find the way to force CRecordset to DELETE/UPDATE (generally, locate) record by it's PK value so to avoid cases described above.

If you do not wish to pursue above mentioned course of action, I will speculate that your next bug report will be "Unable to delete record from table containing BLOB" or something similar.

Now, I do not say there is no bug in c/ODBC (thus the report is in Analyzing") but rather point out bad practices that will definitely lead to more problems.

I misread your last response (english is not my native language) and understood you told me that "DELETE FROM table_test WHERE id = xx AND value_double = x.yabrthkql" could not work so I tried it in the command line client. As it worked in the command line, I responded immediately. This was just to give you more information quickly, not to ignore your advices.
Re-reading your previous response, I understand my misunderstanding.

In my general query log, my ODBC Delete and Edit commands are translated in the end into "SELECT *FROM 'table_test' LIMIT 0" (using DOUBLE field) so I think this is why Delete/Edit fails. With BOOL field, DELETE is sent with an invalid WHERE query.

I used to compare my ODBC commands with what I thought was the equivalent command in direct SQL commands. Configuring and using the general query log like you told me shows me the difference in the commands really sent.
What I still don't understand in the log is why the DELETE command is not translated in the exact same way while using a BOOL (BIT) field and a DOUBLE field. But as I was not using primary keys in these 2 cases, maybe these results are not really relevant. 

So, Using primary keys like you suggested works fine. Thanks for your quick reponse and advices and sorry if this was just bad practice.

Olivier, no problem. English is not native language for most of us.

Important part of this puzzle is to understand how DAO composes UPDATES/DELETES (and we all know it's not like any sane human would do it) so I'll keep this in "Analyzing" till I find some reference on how to control updates. Preferably something like I posted for ADO.

Finally, to what did DAO mapped BOOL field, I do not know... DOUBLE is an option however.

Olivier, I went over this report again and I think it should be closed and here's why. Any framework/API needs a way to automatically generate UPDATES/DELETES based on table structure. Since your table had no primary key (which holds value unique for each record in table), your API decided to match every possible field value to distinguish between rows (expected). Now, matching FLOATs, BLOBs ... is troublesome/impossible in many cases (as I explained before).
Since simply adding PK solves the problem (and it's recommended to have one in each table) I believe my analysis of the problem to be correct.

Synopsis should read: "Unable to DELETE/UPDATE table without PK ..." or, ultimately, "Can not position cursor for DELETE/UPDATE in table with no PK that has FLOAT field".