I've got a table with 'wid' as auto_increment:

+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| wid | int(11) | NO | PRI | NULL | auto_increment |
| uid | int(11) | NO | | 0 | |
| type | varchar(16) | NO | MUL | NULL | |
| message | longtext | NO | | NULL | |
| severity | tinyint(3) | NO | | 0 | |
| link | varchar(255) | NO | | NULL | |
| location | text | NO | | NULL | |
| referer | varchar(128) | NO | | NULL | |
| hostname | varchar(128) | NO | | NULL | |
| timestamp | int(11) | NO | | 0 | |
+-----------+------------------+------+-----+---------+----------------+

Doing a query for the last record gives this:

mysql> select * from watchdog where wid >= 207502;
+--------+-----+------+---------------------+----------+------+------------------------------------------+---------+---------------+------------+
| wid | uid | type | message | severity | link | location
| referer | hostname | timestamp |
+--------+-----+------+---------------------+----------+------+------------------------------------------+---------+---------------+------------+
| 207502 | 0 | cron | Cron run completed. | 0 | | DELETED
| | IP | 1183703130 |
+--------+-----+------+---------------------+----------+------+------------------------------------------+---------+---------------+------------+
1 row in set (0.03 sec)

But setting the LAST_INSERT_ID and trying to add another record gives an
error:

mysql> SET INSERT_ID=207502;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO watchdog (uid, type, message, severity, link,
location, referer, hostname, timestamp) VALUES (0, 'x', 'x', 0, '', 'x',
'', 'ip', 0);
ERROR 1062 (23000): Duplicate entry '207502' for key 1

Immediately running the same line again (hit up arrow and press return)
does the insert correctly.

The table is actually being filled by commands generated by a mysqldump,
and the set/inset pair are coming from a subsequent mysqlbinlog output,
so since it's all generated code I'd expect it to work. If I create a
test table and run the same sorts of commands, I can't get it to fail,
so there must be something funny about this watchdog table. The DB is
otherwise idle when I'm doing this.

Anyone any ideas?

--
Derek Fountain on the web at http://www.derekfountain.org/

>I've got a table with 'wid' as auto_increment:
>
>+-----------+------------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+-----------+------------------+------+-----+---------+----------------+
>| wid | int(11) | NO | PRI | NULL | auto_increment |
>| uid | int(11) | NO | | 0 | |
>| type | varchar(16) | NO | MUL | NULL | |
>| message | longtext | NO | | NULL | |
>| severity | tinyint(3) | NO | | 0 | |
>| link | varchar(255) | NO | | NULL | |
>| location | text | NO | | NULL | |
>| referer | varchar(128) | NO | | NULL | |
>| hostname | varchar(128) | NO | | NULL | |
>| timestamp | int(11) | NO | | 0 | |
>+-----------+------------------+------+-----+---------+----------------+
>
>Doing a query for the last record gives this:
>
>mysql> select * from watchdog where wid >= 207502;
>+--------+-----+------+---------------------+----------+------+------------------------------------------+---------+---------------+------------+
>| wid | uid | type | message | severity | link | location
> | referer | hostname | timestamp |
>+--------+-----+------+---------------------+----------+------+------------------------------------------+---------+---------------+------------+
>| 207502 | 0 | cron | Cron run completed. | 0 | | DELETED
>| | IP | 1183703130 |
>+--------+-----+------+---------------------+----------+------+------------------------------------------+---------+---------------+------------+
>1 row in set (0.03 sec)
>
>But setting the LAST_INSERT_ID and trying to add another record gives an
>error:
>
>mysql> SET INSERT_ID=207502;
>Query OK, 0 rows affected (0.00 sec)

Do not try to duplicate wid's. You've already got a wid = 207502. Don't
try to insert another one. INSERT_ID should be greater than the wid of
any existing record.

>mysql> INSERT INTO watchdog (uid, type, message, severity, link,
>location, referer, hostname, timestamp) VALUES (0, 'x', 'x', 0, '', 'x',
>'', 'ip', 0);
>ERROR 1062 (23000): Duplicate entry '207502' for key 1

You just tried to insert another one.
>
>Immediately running the same line again (hit up arrow and press return)
>does the insert correctly.
>
>The table is actually being filled by commands generated by a mysqldump,
>and the set/inset pair are coming from a subsequent mysqlbinlog output,
>so since it's all generated code I'd expect it to work. If I create a

I have serious questions as to whether it's *SUBSEQUENT* mysqlbinlog
output. It sounds like it was *PREVIOUS* (to the mysqldump) mysqlbinlog
output.

>test table and run the same sorts of commands, I can't get it to fail,
>so there must be something funny about this watchdog table. The DB is
>otherwise idle when I'm doing this.

Gordon Burditt wrote:
> I have serious questions as to whether it's *SUBSEQUENT* mysqlbinlog
> output. It sounds like it was *PREVIOUS* (to the mysqldump) mysqlbinlog
> output.

Um, yes. Spot on. <blush> Sorry. }

--
Derek Fountain on the web at http://www.derekfountain.org/