CODEWITHSUNDEEP
mysqldelphifiredac
Jim Sawyer
Jim Sawyer

Reputation: 21

MySQL Auto_Increment trying to automatically insert prior used values

I Use Delphi 10.2, MySQL. I have a table that has about 50,000 records and has an Auto_Increment primary key. It has suddenly, and on it's own with no help from me, started trying to re-insert old key values. As a matter of fact, it started over with the value 1. I have no idea how to fix this and I hope you might be able to help.

Thanks, Jim Sawyer

Upvotes: 1

Views: 236

Answers (2)

A Lombardo
A Lombardo

Reputation: 774

you can reset the autoincrement value to any value you want with the following command

ALTER TABLE <table_name> AUTO_INCREMENT = <new value>;

so if new value is 100, the next inserted record receives a value of 100.

Upvotes: 0

Rob Lambden
Rob Lambden

Reputation: 2293

If the MySQL table is defined with an auto increment primary key then you should never specify the key value. MySQL should not re-use old key values, but you may want to check if there is any table corruption. You can also reset the table's auto-increment value using an ALTER TABLE command. (There's a tutorial on this here: https://www.mysqltutorial.org/mysql-reset-auto-increment)

You can use the Firedac monitoring to confirm whether or not you are sending the primary key to MySQL - set you connection to be monitored using the FireDAC component - they supply a monitoring tool that you can setup to see all of the SQL being transferred. Normally the Firedac layer would do an insert with no primary key and then use LAST_INSERT_ID to update the TField to have the actual value inserted.

If you are sending the wrong key then alter your logic so you don;t send the primary key on an insert.

Upvotes: 2

Related Questions