auto_increment behavior inconsistent vs. BDB....howcome?
I'm not sure where to post this exactly, but it involves an unexpected difference in auto_increment behavior between the MyISAM and BDB engines which, per the documentation, should be the same. To illustrate, I reference the ANIMALS table example given in the MySQL 5.0 manual
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
If I follow the example exactly as given then the behavior is correct and consistent for both MyISAM and BDB engines wherein the id value increments only on the insert of a group name that already exists. Furthermore, I find that if values are inserted to a new row that exactly match the grp & name of an existing row then the id correctly increments and keeps the primary key unique. All well and good.
However, when I extended the concept of the example to my own table, the incrementation works for MyISAM tables, but fails for BDB as shown in the dialogue below. Any idea why? I may be OK to just use a MyISAM for my purposes, but I'd prefer BDB if this behavior can be explained and worked around.
Thanks for the help,
JL
---------------------------------------------------------------------
mysql> CREATE TABLE TEST_MYISAM (NAME VARCHAR(30) NOT NULL, ID MEDIUMINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (NAME, ID)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO TEST_MYISAM (NAME) VALUES ('BILL'),('MARY'),('GARGAMEL');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TEST_MYISAM;
+----------+----+
| NAME | ID |
+----------+----+
| BILL | 1 |
| GARGAMEL | 1 |
| MARY | 1 |
+----------+----+
3 rows in set (0.00 sec)
mysql> INSERT INTO TEST_MYISAM (NAME) VALUES ('BILL'),('MARY'),('GARGAMEL');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TEST_MYISAM;
+----------+----+
| NAME | ID |
+----------+----+
| BILL | 1 |
| BILL | 2 |
| GARGAMEL | 1 |
| GARGAMEL | 2 |
| MARY | 1 |
| MARY | 2 |
+----------+----+
6 rows in set (0.01 sec)
mysql> CREATE TABLE TEST_BDB (NAME VARCHAR(30) NOT NULL, ID MEDIUMINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (NAME, ID)) ENGINE = BDB;
Query OK, 0 rows affected (0.86 sec)
mysql> INSERT INTO TEST_BDB (NAME) VALUES ('BILL'),('MARY'),('GARGAMEL');
Query OK, 3 rows affected (0.24 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TEST_BDB;
+----------+----+
| NAME | ID |
+----------+----+
| BILL | 1 |
| GARGAMEL | 1 |
| MARY | 1 |
+----------+----+
3 rows in set (0.01 sec)
mysql> INSERT INTO TEST_BDB (NAME) VALUES ('BILL'),('MARY'),('GARGAMEL');
ERROR 1062 (23000): Duplicate entry 'BILL-1' for key 1
mysql> SELECT * FROM TEST_BDB;
+----------+----+
| NAME | ID |
+----------+----+
| BILL | 1 |
| GARGAMEL | 1 |
| MARY | 1 |
+----------+----+
3 rows in set (0.00 sec)