This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hello, I have run into some weird behavior when using an auto incrementing key in a MySQL table. The table has a primary key with ID's of type INT, and is auto incrementing. My table had 20 entries already populating the table at the time of insert, with the ID's being 1-20. Upon insertion with LightSpeed, I found that entry 21 had an ID in the 200s. My first thought was that my KeyTable was supplying the weird values, so i switched my IdentityMethod to the type IdentityColumn, and removed the KeyTable table from the database. Unfortunately this did not solve the problem, so I am hoping for some insight into what is causing this. Thanks, Jeff
|
|
|
|
|
Hello Jeff, IdentityColumn tells LightSpeed to query the database to find what ID has been allocated after the insert. So I think that ID in the 200s is coming from MySQL, not from LightSpeed. (If you suspect otherwise, you can check by turning on logging in the LightSpeedContext, and see whether LightSpeed is sending an Id value or calling last_insert_id() after each insert.) I am not familiar with MySQL's autoincrement implementation, but my guess is that MySQL has somewhere remembered that the highest ID in the table (or the last inserted ID?) is in the 200s (from when LightSpeed was allocating IDs from the KeyTable) and is autoincrementing from there rather than from the last auto-allocated ID. Unfortunately I do not know how to reset the autoincrement counter -- sorry. |
|
|
|
|
Resetting the autoincrement counter did the trick, thanks! |
|
|
|
