Table Data Not Updating

[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