Table Data Not Updating

[Steve A.]

I'm using the F11 “Insert/Update Data for the Table” option to try modifying the data in the first row of the attached “gageball” table. If I try to change any column of data in the first row (gb_name=Master) the data is not updated in the table. For example if I change column cell of Gb_slope in the first row of Master to a 10 then click another cell it then highlights and bolds the GB_Slope column to indicate that data has changed. I then click on the Save Changes button and then when I click on Refresh the original value is back in Gb_slope for the first row. But ….if I try this same update in the 2nd row of data (gb_name=new1) then Save Changes and then Refresh it works Ok and the data is changed.

I originally noticed the problem in version 5.22a and have also downloaded version 5.25 and the same problem ocuurs.

The attached file is a SQL Dump of the gageball table definition and the two records in this table.

[peterlaursen]

Reproduced with MySQL 5.0.28.

if you do as you describe SQLyog generates the query:

Code:

update `gageball` set `GB_Name`='adarsh',`GB_Tolerance`='0.003000',`GB_Slope_1`='1.000000',`GB_Slope_2`='0.961500',
`GB_Slope_3`='0.980400',`GB_Slope_4`='1.020400',`GB_Slope_5`='0.980400',`GB_Slope_6`='1.041700',
`GB_Slope_7`='0.961500',`GB_Slope_8`='1.020400',`GB_Slope_9`='0.961500',`GB_Slope_10`='1.000000',
`GB_Min_Size`='1.674670',`GB_Max_Size`='1.685770',`GB_Zero_Size`='1.680220',`GB_Calibration_Range`='0.006000',
`GB_Offset_1`='1.679466',`GB_Offset_2`='1.680230',`GB_Offset_3`='1.680833',`GB_Offset_4`='1.679626',
`GB_Offset_5`='1.680400' where `GB_Name`='Master' and `GB_Tolerance`='0.003000' and `GB_Slope_1`='1.000000' and `GB_Slope_2`='0.961500' and `GB_Slope_3`='0.980400' and `GB_Slope_4`='1.020400' and `GB_Slope_5`='0.980400' and `GB_Slope_6`='1.041700' and `GB_Slope_7`='0.961500' and `GB_Slope_8`='1.020400' and `GB_Slope_9`='0.961500' and `GB_Slope_10`='1.000000' and `GB_Min_Size`='1.674670' and `GB_Max_Size`='1.685770' and `GB_Zero_Size`='1.680220' and `GB_Calibration_Range`='0.006000' and `GB_Offset_1`='1.679466' and `GB_Offset_2`='1.680230' and `GB_Offset_3`='1.680833' and `GB_Offset_4`='1.679626' and `GB_Offset_5`='1.680400'

now try

Code:

select * from `gageball`
where `GB_Name`='Master' and `GB_Tolerance`='0.003000' and `GB_Slope_1`='1.000000' and `GB_Slope_2`='0.961500' and `GB_Slope_3`='0.980400' and `GB_Slope_4`='1.020400' and `GB_Slope_5`='0.980400' and `GB_Slope_6`='1.041700' and `GB_Slope_7`='0.961500' and `GB_Slope_8`='1.020400' and `GB_Slope_9`='0.961500' and `GB_Slope_10`='1.000000' and `GB_Min_Size`='1.674670' and `GB_Max_Size`='1.685770' and `GB_Zero_Size`='1.680220' and `GB_Calibration_Range`='0.006000' and `GB_Offset_1`='1.679466' and `GB_Offset_2`='1.680230' and `GB_Offset_3`='1.680833' and `GB_Offset_4`='1.679626' and `GB_Offset_5`='1.680400'

and nothing is returned!

If you add a primary key to the table SQLyog will generate a where-clause like

Code:

WHERE `the_pk` = 'the pk_value'

.. and it works too!!

This is a bug with the DECIMAL implementation with MySQL 5.0.27-28  

It is known that this will occur with FLOAT and DOUBLE types as they are not exact.

It should not be the situation with DECIMALS. But it was!

I think it is a coincidence that you upgraded MySQL at about the same time as you upgraded SQLyog to version 5.22.

Just upgrade your MySQL server to 5.0.37!

But still: having a primary key in a table is always recommended – and you should!

And whenever you have such problem try to understand the SQL in the HISTORY tab!

[Author]

Posts