In article <[EMAIL PROTECTED]>,
Scott Haneda <[EMAIL PROTECTED]> writes:
> 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?
The behavior is indeed strange, but it's not a bug, since it's documented:
If you use `INSERT IGNORE' and the record is ignored, the
`AUTO_INCREMENT' counter still is incremented and
`LAST_INSERT_ID()' returns the new value.
The solution is, of course, not to use INSERT IGNORE at all. You
should instead catch any error, check that it's ER_DUP_KEY and not try
the second INSERT in that case.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
