MySQL Forums
Forum List  »  MyISAM

auto_increment behavior inconsistent vs. BDB....howcome?
Posted by: J L
Date: August 03, 2006 10:21AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
auto_increment behavior inconsistent vs. BDB....howcome?
2990
J L
August 03, 2006 10:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.