DZone
Java Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Java Zone > 6-year-old MySQL Bug is Finally Fixed in MySQL 5.6

6-year-old MySQL Bug is Finally Fixed in MySQL 5.6

Peter Zaitsev user avatar by
Peter Zaitsev
·
Sep. 07, 12 · Java Zone · Interview
Like (0)
Save
Tweet
7.40K Views

Join the DZone community and get the full member experience.

Join For Free

I got a message this morning about a bug that has been fixed in MySQL 5.6.6… which I reported about in early 2006 when I ran 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 the Oracle team for going and cleaning up such very old bugs. Here is a description from the bug:

If you perform  match of constant which is too large  to the column
instead of simply responding with empty set, MySQL   truncates the
constant, performs the lookup  and only then  discards results:

CREATE TABLE `trunc` (
  `i` int(11) NOT NULL default '0',
  KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



mysql> select i,count(*) cnt from trunc  group by i order by cnt desc
limit 10;
+------------+-------+
| i          | cnt   |
+------------+-------+
| 2147483647 | 76047 |
| 1421638051 |     3 |
|  985505567 |     3 |
| 1046160975 |     2 |
|  141017389 |     2 |
|  848130626 |     2 |
|  888665819 |     2 |
| 1001437915 |     2 |
|  118824892 |     2 |
| 2104712727 |     2 |
+------------+-------+
10 rows in set (0.34 sec)

(Just some random data. The only row we really need is with
2147483647)

mysql> explain select count(*) from trunc where i=4147483647;
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref   | rows  | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | trunc | ref  | i             | i    |       4 |
const | 81602 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
1 row in set, 1 warning (0.01 sec)

4bil is out of range for unsigned column. I would expect "Impossible
Where clause" here

Lets look at the query execution:

| Handler_read_next     | 1305742982 |


mysql> select  count(*) from trunc where i=4147483647;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.04 sec)

| Handler_read_next     | 1305819030 |


As you  can see  there were over 70,000 row reads performed.

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

Thank you, Oracle team, for finally fixing this bug (even though you’re about 6 years late). Here are two pieces of advice to prevent this error from happening:

Use consistent data types. Make sure you’re using consistent data types for the same values. Often it's just better to standardize a few and to not make a decision in every single case. I, for example, use “int unsigned not null” for positive intergers that aren't too big.

Consider using strict mode. Storing different data types when your application requests without throwing the error out is not a 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 the error when data is truncated, along with using transactional tables for any data you value.

 

MySQL Database

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Growth in Java Development for Web and Mobile Apps
  • How To Integrate Third-Party Login Systems in Your Web App Using OAuth 2.0
  • A Simple Guide to Heaps, Stacks, References, and Values in JavaScript
  • Everything I Needed to Know About Observability, I Learned from ‘Bewitched’

Comments

Java Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo