MySQL Bugs: #12065: Wrong values for Inserts and updates

Bug #12065	Wrong values for Inserts and updates
Submitted:	20 Jul 2005 16:16	Modified:	22 Aug 2005 14:07
Reporter:	Gene Black	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S1 (Critical)
Version:	1.1.13	OS:	Windows (Windows XP Pro SP2)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any

Description:
I don't know what triggered this, but now that I'm into it, I definately can't get out of it. It started by doing a select, then editing the table to add several new rows. Everything appeared to work correctly right down to the "Apply Changes". However, when I reran the select, several of my fields had the wrong values. I had logging turned on on my SQL server (Server version 4.1.11-Debian_4-log) and captured the following:

(Note I've had to change some of the values here slightly as this information is confidential in nature. My changes have preserved the length of the numbers involved and have also been done as to not cause any confusion as to where numbers were and were not duplicated.)

27793 Query INSERT INTO `Billing`.`lservice` (`cust_id`, `item`,`number`,`price`,`eucl`,`usf`,`e911`,`start_date`,`quantity`) VALUES (6734517,'PBX Trunk Service','4230000000','4230000000', '4230000000','4230000000','4230000000','2005-07-18',1)
*** Only the first 4230000000 belongs. The rest were duplicates the Query Browser chose to insert instead of the values I had provided. There were a few more inserts where the same field had been duplicated the same way, and then I had this insert:

27793 Query INSERT INTO `Billing`.`lservice` (`cust_id`, `number`,`price`,`item`,`eucl`,`usf`,`e911`,`start_date`,`quantity`) VALUES (6734517,'4230000000','4230000000','Basic Line Service','Basic Line Service','Basic Line Service','Basic Line Service','2005-07-18',1)
**** As you can see here the second field was duped once, and then it proceeded to dupe the forth field a few times. I can't determine the pattern for it's duplication.

Now, after this duplication problem, several of the fields duplicated were invalid values for the fields and this resulted in various results such as max field value. No real suprise there. Attempting to edit these fields though had even more screwed up results. Check out this update that occured as a result of trying to edit one of the incorrect cells:

050720 11:17:12 27816 Query UPDATE `Billing`.`lservice` SET `price`=xo;^A:///C%3A/Documents%20and%20Settings/Gene/Application%20Data/MySQL/mysqlx_user_connections.xml WHERE `service_id`=117

All I tried to do was set the price field to 1 as a test. Same results with any of the other incorrect fields that I tried. Also note that when this error occured the display in the Query browser suffered a bit too - I get the usual "Error while applying actions." in the bar below, but below that where it normally shows the error returned I get the bar for the fields ("!","Description","ErrorNr") and the far right side of the description bar looks like it's cut off with a white patch and there is no room left for rows below the bar other than maybe 1-2 pixels in height. I can't find a way to expand the area so I'm not sure if an error is displaying there or not.

I've done the following to try and fix this:

1. I exited the Query Browser and then restarted it, reconnected, etc. Still get the same error when trying to modify the bad fields.

2. I loaded up my trusty copy of MySQL Control Center (Don't badmouth it, it's the more reliable of the two) and it can edit those fields without a problem.

3. I recently upgraded Query Browser, so on a hunch, I uninstalled it, downloaded a fresh copy (same version - 1.1.13) and installed it. Same problem.

I have not tried to recreate the origional insert problem. I'm at a loss as to why it's struggling so badly with the current update situation and have not been able to find a workaround.

Comments?

How to repeat:
Have not tried to recreate the origional insert. With the update though simply trying to modify any of the corrupted fields (note that I say corrupted because the wrong values were origonally inserted by the Query Browser, but as far as I can determine from the server, their current values are completely legal and acceptable) produces the error. There may be a connection between this error and the field in question being the max value (99999999.99 in one instance for a
field defined as: `price` decimal(9,2) NOT NULL default '0.00').

Suggested fix:
Unknown

Hello Gene,

Thank you for the report, but we really need repeatable test case to fix this problem.
Can you reproduce the same behavior on the test table for example?
If so, please write the full set of actions that causes duplicate values.

After further testing, the problem appears to be version 1.1.13 in general. I can produce the problem by:

1. Double click on a table name to auto generate the select.
2. Click execute to execute the select.
3. Click edit to edit the table.
4. Pick any cell in the table (as far as I can tell, my most popular choice was a decimal(9,2) field) and modify it (I typically set it to 1).
5. Click Apply changes.
6. Query Browser then submits the update query to the server. The update query appears to be correct except for the value of the field being updated. The value appears to be set to random trash. Here's an example:

UPDATE `Billing`.`lservice` SET `price`=Àò WHERE `service_id`=119

Following these steps will repeat the problem 100% of the time. In trying to discover the cause I reverted back to version 1.1.10. Everything works fine in 1.1.10. Upgrading to 1.1.13 will bring the problem back 100% of the time. Uninstalling (through Add and Remove Programs) 1.1.10 and then installing 1.1.13 will yield the same results. I've tried some other things as well, but in a nutshell, I am unable to find a scenario in which version 1.1.13 works. I'm surprised no one else has reported this yet. I downloaded 1.1.13 four different times from different servers and did a binary file compare - no differences were found. I'm running Windows XP Pro SP2 with the latest updates installed. 
What additional info can I provide to help track this down?

Hi!

I was able to reproduce this bug using the provided test-case.

Thanks for your bug report.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html