I am not sure this is just how it is, my issue, or a bug, I searched the bugs database, but don't really know what to call this in order to properly look up the info:
MySQL 4.0.18-standard CREATE TABLE `addresses` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL default '0', `first_name` varchar(48) NOT NULL default '', `last_name` varchar(48) NOT NULL default '', `email_address` varchar(48) NOT NULL default '', `updated` timestamp(14) NOT NULL, `added` timestamp(14) NOT NULL default '00000000000000', PRIMARY KEY (`id`), UNIQUE KEY `emailuid` (`email_address`,`user_id`) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='foo'; CREATE TABLE addresses_incampaign ( id int(11) NOT NULL auto_increment, user_id int(11) NOT NULL default '0', address_id int(11) NOT NULL default '0', campaign_id int(11) NOT NULL default '0', updated timestamp(14) NOT NULL, added timestamp(14) NOT NULL default '00000000000000', PRIMARY KEY (id), UNIQUE KEY address_id (address_id) ) TYPE=InnoDB PACK_KEYS=0 COMMENT='bar'; As you can see, I have a unique key on addresses.email_address and addresses.user_id, note the unique key on address_id in the second table. I insert the following data TWICE: INSERT IGNORE INTO `addresses` (`user_id`,`first_name`,`last_name`,`email_address`) VALUES (1, 'Claire', 'Altman', '[EMAIL PROTECTED]'); INSERT IGNORE INTO `addresses_incampaign` (`user_id`, `address_id`, `campaign_id`) VALUES (1,LAST_INSERT_ID(), 2); Notice I am picking up the last_insert_id in the second insert, I then insert that data again, since I am using insert ignore and since there are unique keys on this, I should only still have one records in each table. Results are as follows: mysql> select * from addresses; +-----+---------+------------+-----------+---------------+ | id | user_id | first_name | last_name | email_address | +-----+---------+------------+-----------+---------------+ | 148 | 1 | Claire | Altman | [EMAIL PROTECTED] | +-----+---------+------------+-----------+---------------+ 1 row in set (0.00 sec) mysql> select * from addresses_incampaign ; +-----+---------+------------+-------------+ | id | user_id | address_id | campaign_id | +-----+---------+------------+-------------+ | 128 | 1 | 148 | 2 | | 129 | 1 | 149 | 2 | <- SOULD NOT HAVE HAPPENED +-----+---------+------------+-------------+ 2 rows in set (0.00 sec) Record 129 should not have been inserted, it should not have ever gotten a insert ID back from mysql, mysql said back 149, which is in fact the next record, but no new record was added to addresses so it really is not valid. Workarounds and suggestions? -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
