how exactly code.org lost data with 32-bit index? Suppose they use MySQL database 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.
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&gt; 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&gt; 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&gt; insert into incfail values (NULL);
Query OK, 1 row affected (0.00 sec)

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

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

mysql&gt;


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

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


The data was lost: 

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

mysql&gt;