Re: MyISAM/Blackhole Auto Increment Value Question

[Timothy Crider]

I have tried using both PHP's mysql_insert_id() and the native mysql LAST_INSERT_ID();

Here is an example

CREATE TABLE `a` (
`num` int(10) unsigned NOT NULL auto_increment,
`txt` char(25) default NULL,
PRIMARY KEY (`num`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8

CREATE TABLE `b` (
`num` int(10) unsigned NOT NULL auto_increment,
`txt` char(25) default NULL,
PRIMARY KEY (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

#### Trying to insert into table B ( this works ) ####

mysql> insert into b VALUES (NULL, 'b.1'); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into b VALUES (NULL, 'b.1'); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into b VALUES (NULL, 'b.1'); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `b`;
+-----+------+
| num | txt |
+-----+------+
| 1 | b.1 |
| 2 | b.1 |
| 3 | b.1 |
| 4 | b.1 |
+-----+------+
4 rows in set (0.00 sec)

#### Trying to insert into table A ( this fails ) ####
mysql> insert into a VALUES (NULL, 'a.1'); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into a VALUES (NULL, 'a.1'); SELECT LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)


So as you can see, I can insert into the normal MyISAM table and everything is fine, however, when I try to pull any Auto Increment info out of the blackhole table (Table A), it gives me the last ID inserted into the B table.

Any suggestions?