Jason, SELECT LAST_INSERT_ID() seemed to work from the mysql client. That is a workaround. I forward this message to Sinisa and Venu, so that they can check the Perl interface.
>From the manual page of mysql_insert_id() I found the following comment: " Charles Merriam: This may only work on most recent versions of MySQL; and may not work even then. The work around is to do a SELECT LAST_INSERT_ID() after your insert. I'm not sure about the performance penalty; www.truegift.com is not performance driven. Good luck! " Regards, Heikki http://www.innodb.com -- Order commercial MySQL/InnoDB support at https://order.mysql.com/ >allrighty, for you perl/mysql folks here is the trouble I'm having. >I'm tyring to do a transaction in mysql/innodb in perl with the following >basic flow >eval{ > Insert my first record > get the insert_id > (using $sth->{mysql_insert_id}) > now perform other inserts that need that key > commit;}; >if($@){ > rollback; and other logic} >the only problem is the {mysql_insert_id} doesn't give me anything back. >NADA... >Is this a bug only in transactions or innodb? since I know the function is >working in autocommit / MyIsam tables. >Do I have to do the select last_insert_id ? Because that IS working (and >consequently what I am doing now).using .43 on linux RH7.1 compiled from source >-- Jayce^ mysql> create table ait (a int not null auto_increment, b int, primary key (a)) type = innodb; Query OK, 0 rows affected (0.03 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> insert into ait (b) values (5); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> insert into ait (b) values (5); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec) mysql> insert into ait (b) values (5); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.01 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) mysql> insert into ait (b) values (5); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 4 | +------------------+ 1 row in set (0.01 sec) mysql> insert into ait (b) values (5); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec) mysql> insert into ait (b) values (5); Query OK, 1 row affected (0.00 sec) mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 6 | +------------------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
