Re: auto_increment behavior inconsistent vs. BDB....howcome?
Posted by:
J L
Date: August 07, 2006 04:29PM
Well, as you suggested, when I use an integer instead of a varchar, there are no problems with BDB. Taking a step further, though, it seems that the behavior is also as expected when a fixed length CHAR is used instead of a VARCHAR as I had originally done. I guess I hit on the one data type that would foul things up. Perhaps this is worth a note in the docs. Below is the dialogue of the new test cases I used if you're curious.
Cheers, JL
---------------------------------------------------------
mysql> CREATE TABLE TEST_BDB_NUM (NUMBER INT NOT NULL, ID MEDIUMINT NOT NULL AUT
O_INCREMENT, PRIMARY KEY (NUMBER, ID)) ENGINE = BDB;~
Query OK, 0 rows affected (0.42 sec)
mysql> INSERT INTO TEST_BDB_NUM (NUMBER) VALUES (11),(22),(33);~
Query OK, 3 rows affected (0.17 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TEST_BDB_NUM; ~
+--------+----+
| NUMBER | ID |
+--------+----+
| 11 | 1 |
| 22 | 1 |
| 33 | 1 |
+--------+----+
3 rows in set (0.00 sec)
mysql> INSERT INTO TEST_BDB_NUM (NUMBER) VALUES (11),(22),(33);~
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM TEST_BDB_NUM; ~
+--------+----+
| NUMBER | ID |
+--------+----+
| 11 | 1 |
| 11 | 2 |
| 22 | 1 |
| 22 | 2 |
| 33 | 1 |
| 33 | 2 |
+--------+----+
6 rows in set (0.00 sec)