List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:June 19 2009 8:03pm
Subject:feedback/review requested for fix to MySQL bug #44927
View as plain text  
Hello,

In our storage engine, TokuDB, we implement a different auto-increment
behavior than InnoDB and MyISAM. We feel that this different behavior
has value for potential customers. We realize that a potential
downside is that users may get confused with different auto-increment
behavior than that found in InnoDB and MyISAM. The details can be
found in the MySQL bug #44927
(http://bugs.mysql.com/bug.php?id=44927).

To implement this behavior, we needed to make a simple change to
MySQL. The patch can be found in the bug report linked above.

We were wondering if we could please get feedback on the patch. We
would like to contribute this patch to MySQL and make it part of 5.1
code.

In summary, here is our auto-increment behavior:
A user can select ANY column in a key to be an auto-increment field
(unlike InnoDB, that enforces the first column in the key be the
auto-increment field). If the column is not the first in a key, the
field is always increasing. In MyISAM, subgroups are created based on
the value of preceding columns. Here is an example for what TokuDB
does:

mysql> create table foo (a int, b int auto_increment, primary key
(a,b))engine=tokudb;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into foo (a) values (1),(1),(2),(2),(2);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
+---+---+
5 rows in set (0.00 sec)

Here is what MyISAM does:

mysql> create table foo (a int, b int auto_increment, primary key
(a,b))engine=MyISAM;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into foo (a) values (1),(1),(2),(2),(2);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
+---+---+
5 rows in set (0.00 sec)

Thanks
-Zardosht
Thread
feedback/review requested for fix to MySQL bug #44927Zardosht Kasheff19 Jun