Professional Web Applications Themes

Setting LAST_INSERT_ID gives error - MySQL

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 | | ...

  1. #1

    Default Setting LAST_INSERT_ID gives error

    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/
    Derek Guest

  2. #2

    Default Re: Setting LAST_INSERT_ID gives error

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

    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.
     

    You just tried to insert another one. 

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

  3. #3

    Default Re: Setting LAST_INSERT_ID gives error

    Gordon Burditt wrote: 

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

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

Similar Threads

  1. LAST_INSERT_ID()
    By Gef.Mongoose@gmail.com in forum MySQL
    Replies: 6
    Last Post: March 11th, 08:43 AM
  2. LAST_INSERT_ID() performance??
    By Tim in forum MySQL
    Replies: 9
    Last Post: January 26th, 01:57 AM
  3. last_insert_id()
    By lingo_user in forum Macromedia ColdFusion
    Replies: 0
    Last Post: June 29th, 07:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139