I am always <sarcasm>overjoyed to learn about some obscure bug that breaks my software</sarcasm> and I got to find a new one last week.  This one, a problem that involves ADO, the MySQL ODBC connector, and MySQL.  Basically, the problem boils down to FLOAT columns in MySQL not resolving to the same value to rounding errors.  These rounding errors only seem to occur on some CPU’s and presumably, it’s because of the different ways that floating point math are implemented on those CPU’s.  Basically the upshot of this is that when a FLOAT column of 6.0 is selected it might be selected as 6.0000000000001 once and 5.99999999999 a second time.

Normally this wouldn’t be that big of an issue but if you’re using .NET and DataSets it can cause a huge issue.  If a DataSet is opened, edited and then the results saved with something like this:

        public void saveDataSet(DataSet pDataSet)
        {
            this.openConn();
            _commandBuilder.GetUpdateCommand();     
            _adapter.Update(pDataSet);
            this.closeConn();
        }

It appears that upon calling the Update method that the original record is retrieved to make sure that the values have not changed between the DataSet being edited and the changes being committed. Now normally this isn’t an issue but if your dataset is retrieved from MySQL, and there’s a float column, and the rounding error I described above happens, then a concurrency exception is thrown. Which looks like this:

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. Source:System.Data

And that makes sense, because you don’t want to overwrite someone else’s database edits. But of course, this isn’t an edit, this is a rounding error. As Sod’s law would have it I couldn’t get this error to throw on any of my CPU’s without some sort of manual intervention. So given that I KNOW there cannot be concurrent edits of the same database row I’ve had to change the above code to the following:

        public void saveDataSet(DataSet pDataSet)
        {
            this.openConn();
            _commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            _commandBuilder.GetUpdateCommand();   
            _adapter.Update(pDataSet);
            this.closeConn();
        }

Adding the ConflictOption simply ignores the concurrency exception and the problem goes away.