Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6

I got the message in the morning today about the bug being fixed in MySQL 5.6.6…. which I reported in Early 2006 (while still being with MySQL) and running MySQL 4.1 I honestly thought this issue was fixed long ago as it was indeed pretty annoying. I must say I’m very impressed with Oracle team going and cleaning up such very old bugs. Here is a description from the bug:

I came across the bug in the real application which would use multiple table and the column type was inconsistent between them… so inserts into one table would happen with correct value, such as 3000000000, while inserting the same value in the different one will be truncated to 2147483647. This caused a lot of rows to have value of 2147483647 very quickly and select queries for values over 2bil becoming increasingly more expensive. Because there would be more and more queries for values over 2bil as data continued to be inserted the system essentially collapsed in matter of hours.

Thank you, Oracle team, for having this bug finally fixed (even though you’re about 6 years over my expectations while reporting this bug). There are also couple of advises to prevent problems like this to happen:

Use consistent data types Make sure you’re using consistent data types for the same values. Often it is just better to standardize on a few and not take decision in every single case. I for example use “int unsigned not null” for not overly large positive integers.

Consider using strict mode Storing different data when your application requests without throwing the error is not good idea in most cases, yet MySQL continues to be very loose by default. Consider running with sql_mode=strict_all_tables which will report error when data is truncated, together with using transactional tables for any data you value.

Share this post

Comments (2)

  • Gavin Towey

    How funny; I *just* got bit by this bug about a month ago, filed a report and had it marked as a duplicate of the original bug. This feels like fast resolution for me! =)

    August 30, 2012 at 11:43 am
  • Peter Zaitsev


    Well might be we now know how this bug was rediscovered to be fixed ūüôā

    August 30, 2012 at 11:46 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.