0

Today the headline The 32-Bit Dog Ate 16 Million Kids' CS Homework. Quote from blog.code.org

The way we store student coding activity is in a table that until today had a 32-bit index. What this means is that the database table could only store 4 billion rows of coding activity information. We didn’t realize we were running up to the limit, and the table got full. We have now made a new student activity table that is storing progress by students. With the new table, we are switching to a 64-bit index which will hold up to 18 quintillion rows of information. On the plus side, this new table will be able to store student coding information for millions of years. On the down side, until we’ve moved everything over to the new table, some students’ code from before today may temporarily not appear, so please be patient with us as we fix it.

We now suppose the use MariaDB/MySQL. Can you explain me in detail (with SQL statements and error messages) this issue? I don't understand this data loss very well.

1
  • Morale of this story: Check for errors after running SQL.
    – Rick James
    Jan 23, 2017 at 20:36

3 Answers 3

4

By index, he actually means key column. The maximum value MySQL can store in a signed INT column is 2147483647 (documentation link), which is stored in 32 bits (4 bytes, 2 to the power of 32).

Their problem would have occurred when the table reached 2147483647 rows (and less if there are gaps) - any subsequent INSERTs would fail, and the data would be lost.

It's easy to reproduce what's happened:

Create a test table with a INT primary key:

mysql> create table incfail 
       ( 
          pk INT PRIMARY KEY AUTO_INCREMENT
       );
Query OK, 0 rows affected (0.01 sec)

Set the primary key value to be nearly the maximum value:

mysql> alter table incfail AUTO_INCREMENT= 2147483646;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

INSERT a few values:

mysql> insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from incfail;
+------------+
| pk         |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql>

The primary key is now at its maximum value, and the next INSERT will fail:

mysql> insert into incfail values (NULL);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
mysql>

The data was lost:

mysql> select * from incfail;
+------------+
| pk         |
+------------+
| 2147483646 |
| 2147483647 |
+------------+
2 rows in set (0.01 sec)

mysql>
1
  • MySQL supports unsigned integer types, and these are commonly seen in auto increment columns in the wild. INT UNSIGNED is limited to 2^32 - 1 or 4,294,967,295. Jan 23, 2017 at 18:57
3

This is the relevant part:

"Any student progress from 9:19 to 10:33 a.m. on Friday was not saved..." explained the embarrassed CTO of the educational non-profit Code.org, "and unfortunately cannot be recovered."

For a short period all inserts have failed since no new entries could have been added to the table due to the technical limitation.


The other part

"On the down side, until we've moved everything over to the new table, some students' code from before today may temporarily not appear, so please be patient with us as we fix it."

Is talking about a temporarily unavailability of some of the data, since it is being moved from the old table to the new table, probably in chunks.


I'm guessing the issue was caused by declaring AUTO_INCREMENT with INT that was changed in the new table to BIGINT.

1
1

It could be that they changed from INT (which defaults to SIGNED) to INT UNSIGNED. This would increase the limit from 2 billion to 4 billion. However, the wording implied that they started with INT UNSIGNED and are moving to BIGINT UNSIGNED.

Another issue -- do they really have 2 billion rows? In some situations, ids are "burned", thereby leading to hitting the 2B limit without really having that many rows. This can happen from many operations:

  • DELETE + INSERT
  • REPLACE
  • INSERT IGNORE
  • and some others

In the first two cases, first a row is deleted, thereby throwing away an id; then the insert occurs, creating a new id.

In the INSERT cases, the processing pre-allocates the ids, but then fails to use them.

A deliberate way to mess with ids is to insert a row with a higher id. Subsequent inserts always go from the highest current value.

I wonder where the data comes from... If it is manual data entry, I am having trouble imagining how long it would take to enter 4 billion records!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.