MySQL Bugs: #25593: query (that has parameters) cannot update field if new value is longer that orig

Bug #25593	query (that has parameters) cannot update field if new value is longer that orig
Submitted:	12 Jan 2007 19:22	Modified:	3 Apr 2007 14:17
Reporter:	Raigedas	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (win xp)
Assigned to:		CPU Architecture:	Any

Description:
get error
"Multiple-step operation generated errors. Check each status value."
when updating (must type in more character than there originaly were before operation) field of TEXT type (maybe others too) with ADOQuery (on Borland Delphi) which has parameters.
actualy this bug exists for at least a year. but all that time i was managing it with variuos workarounds.

my configuration:
MySQL server: it existsed on 4.0.* . it still exists on 5.0.27
ODBC connector: 3.51.12
client: program writen in Borland Delphi. program must use ADOQuery _with_ parameters.

actually i am not sure if it is bug of mysql server, or myodbc, or ADO components in Delphi...

How to repeat:
----------------------
on mysql server:

USE test;
CREATE TABLE tbug (
  cid integer not null auto_increment,
  cdefinition TEXT,
  primary key (cid)
) ENGINE=MyISAM;
INSERT INTO tbug (cid,cdefinition) VALUES (1,'aaa');
INSERT INTO tbug (cid,cdefinition) VALUES (2,'bbbbbbbbbbbbbbbbbbbbb');
INSERT INTO tbug (cid,cdefinition) VALUES (3,'12345678');

-----------------------------
create DSN: call it "bug64len"
-----------------------------
create a client:

new empty project.

add ADOConnection1 . set properties for it:
LoginPrompt := False;
ConnectionString := 'DATA SOURCE=bug64len';

add ADOQuery1 . link it with ADOConnection1. 

if you add sql that is without parameters then you will not find a bug. set sql with parameters like this:

SELECT * 
FROM tbug 
WHERE cid = :num

do at runtime:
ADOQuery1.Parameters.ParamByName('num').Value := 1;
if ADOQuery1.Active then
  ADOQuery1.Requery()
else ADOQuery1.Open();

now.... originally there is text 'aaa' in the row. if you update it but keep the same length (or shorter) then you get no error. but if you update to the longer one then you get an error:

ADOQuery1.Edit;
ADOQuery1.FieldByName('cdefinition').AsString :=
  'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
ADOQuery1.Post;

---
i can send a sample program demonstrating the bug.
---

---- this must be fixed....

regards,
Raigedas

source of the Delphi7 project that demonstrates the bug

Attachment: bugreport_mysql_text_length64.7z (application/x-7zip-compressed, text), 5.46 KiB.

Hi and thanks for your report. I believe this is the duplicate of Bug#21578. Can you please crosscheck (and run my example too) and get back to me with your findings?

i guess you are right. this is almost a dublicate if #21578 bug. but:

1. i have provided some more accurate information. Albert Molina only stated that 
"...only can save first 85 characters". but it is not all that. actually it can save _not more character than there originaly_ were before operation. Not exactly 85 characters.

2. i have provided example project for Delphi7, maybe you will be able to repeat it.

Hi. I can agree with you regarding test case and punctuality so I din't set Duplicate flag but the problem remains the same (as I see it). I would like you to cross-examine my test case by comparing it to yours. I don't have D7 but surely your D7 can load D6 project.

i have recreated and passed your test-case. it displays that fields' classname is TMemoField on both cases (with empty and non-empty table). (and by the way, that code does not look good for me.. actually is has a memory leakage... but ok, it is not what we are interested in this time...). 

two points:
1. actually my test-case and yours one are completely different. Ok, i have taken the idea to check what is the ClassName of the field in my test-case: and it is TMemoField also!
2. i cannot understand, why i get that error ONLY IF i use parameters in my ADOQuery?

Hi. It may not look so but we are actualy moving towards the solution.

It is as I feared. D6 ADO implementation is badbadbad, I can't get pass the param substitution. As I can't have every piece of SW available I'll ask a colleague of mine to try your test case on D7.

Hi.

Using what's available to me, Delphi 2005, and latest MyODBC (3.51.14) driver I am unable to repeat:

Server version: 5.0.38-log Source distribution

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

mysql> CREATE TABLE tbug25593 (
    ->   cid integer not null auto_increment,
    ->   cdefinition TEXT,
    ->   primary key (cid)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tbug25593 (cid,cdefinition) VALUES (1,'aaa');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO tbug25593 (cid,cdefinition) VALUES (2,'bbbbbbbbbbbbbbbbbbbbb'
);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbug25593 (cid,cdefinition) VALUES (3,'12345678');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbug25593;

+-----+-----------------------+
| cid | cdefinition           |
+-----+-----------------------+
|   1 | aaa                   |
|   2 | bbbbbbbbbbbbbbbbbbbbb |
|   3 | 12345678              |
+-----+-----------------------+
3 rows in set (0.00 sec)

mysql> select * from tbug25593;

+-----+--------------------------------------------------------------+
| cid | cdefinition                                                  |
+-----+--------------------------------------------------------------+
|   1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   2 | bbbbbbbbbbbbbbbbbbbbb                                        |
|   3 | 12345678                                                     |
+-----+--------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

and the log:
070403 16:09:32	    115 Connect     root@LAPTOP on test
		    115 Query       SET SQL_AUTO_IS_NULL=0
		    115 Query       select database()
		    115 Query       select * from tbug25593
where cid = 1
		    115 Query       UPDATE `test`.`tbug25593` SET `cdefinition`='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' WHERE `cid`=1
070403 16:09:40	    111 Query       select * from tbug25593
070403 16:09:53	    115 Quit       

I will attach my test case but for now, I think it might be that either 3.51.14 or Delphi2005 corrected the situation.

D2005 & MyODBC 3.51.14 test case

Attachment: Bug25593.zip (application/x-zip-compressed, text), 8.74 KiB.