Bug #27453 MySQL Eror when using connector.net and VB2005 / V-Studio.NET products
Submitted: 26 Mar 2007 18:30 Modified: 13 Apr 2007 9:41
Reporter: Bill Angus Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:5-any OS:Windows (Win2000)
Assigned to: CPU Architecture:Any

[26 Mar 2007 18:30] Bill Angus
Description:
Interop error between MySQL server, connector, and VB2005.

Failure occurs when trying to add data records programmatically to a VB.NET datatable object when datatable is bound to a datagridview control and to a MySQL datatable that is automatically updated via a MySQL Commandbuilder object.

How to repeat:
1. Create an empty MySQL table with half a dozen fields in it and define a user-enetered primary key integer field (but not an auto-increment ID field).
2. Create a windows forms app with a datagridview control on it. 
3. create a datatable and a mysqlcommand builder object as in the connector.net example (incidentally, the example binds the datagridview to the datatable).
4. write program code to add 4 rows to the datatable with some specific data in them (one row at a time with primary key field containing numbered values 1000, 1005, 1010, ...).
5. Run the app.

System will return MySQL error on the 2nd iteration of step 4. Ignoring the error (asking the debugger to resume), will result in completion of step 5 as if no error had happened. 

The failure on 2nd iteration is accompanied by an erroneous message from MySQL that the priumary key field 2005 already exists.

Suggested fix:
There is an obvious workaround, (add the fields, but take the .NET objects out of the loop). The proper fix would be to fix the mysql command builder object so that it didn't try to send a duplicate key on the 2nd (only) record being entered into the VB2005 datatable object.
[28 Mar 2007 17:45] Bill Angus
I just set the datasource of the datagridview control to nul, then add the fields to the datatable, then reset the datasource to the datagridview control (and everything works ok). It is only when the datasource reamins linked to the datagridview that this error occurs.
[13 Apr 2007 8:40] Tonci Grgin
Hi Bill and thanks for your report. I have seen a few similar reports as of late and trying to compare them with yours.
[13 Apr 2007 9:41] Tonci Grgin
Hi Bill. I am unable to reproduce given problem on:
 - MySQL server 5.0.38BK on WinXP Pro SP2 localhost
 - c/NET latest sources, Rev 671., NET FW 2.0
 - Attached test case

I'm not so good at VB so I used C#. Can you review my test case and see if it is what you wanted?
[13 Apr 2007 9:42] Tonci Grgin
C:\mysql507\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.38-log Source distribution

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

mysql> use test;
Database changed
mysql> create table bug27453 (
    -> Id INT NOT NULL PRIMARY KEY,
    -> CharFld1 VARCHAR(30),
    -> CharFld2 VARCHAR(30),
    -> CharFld3 VARCHAR(30),
    -> CharFld4 VARCHAR(30),
    -> CharFld5 VARCHAR(30));
Query OK, 0 rows affected (0.11 sec)

mysql> select * from bug27453;
+------+---------------+----------+---------------+---------------+----------+
| Id   | CharFld1      | CharFld2 | CharFld3      | CharFld4      | CharFld5 |
+------+---------------+----------+---------------+---------------+----------+
| 1000 | Row 1 VCFld 1 | NULL     | NULL          | Row 1 VCFld 4 | NULL     |
| 1005 | Row 2 VCFld 1 | NULL     | Row 2 VCFld 3 | NULL          | NULL     |
| 1010 | Row 3 VCFld 1 | NULL     | Row 3 VCFld 3 | NULL          | NULL     |
| 1015 | Row 4 VCFld 1 | NULL     | NULL          | Row 4 VCFld 4 | NULL     |
+------+---------------+----------+---------------+---------------+----------+
4 rows in set (0.00 sec)
[13 Apr 2007 9:45] Tonci Grgin
MySQLData pdb file didn't fit.

Attachment: Bug27453.zip (application/x-zip-compressed, text), 145.28 KiB.