Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Gbever's avatar

Odd DB issue

My Laravel project contains data from a pre existing site. The site was created using MSSQL. Since we wanted do reduce costs and make the project more compatible with tools like forge, we migrated everything to MySql. No issues there, everything has been migrated for some time.

Im creating some views to add new records into a few database tables that already have many records. The id is auto-increment ,and was created by Laravel in MSSQL before the migration to MySQL.

When I attempt to add a new record, I get an integrity error:

 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3' for key 'PRIMARY' (SQL: insert into `industries` (`name`) values (test industry))

I see in MySql that the id field is set to auto increment, and also in the original migration file. Each time I try and add a record, i get this message, and the id increments, as if Laravel is keeping track of this somewhere. You can see here that the id it is attempting to insert is 3, this was my third time. If i attempt to add another record now, I will get the same error ,with a duplicate entry of '4'.

I had this happen to another table, and I just kept adding records and failing until it reached what would be the next id and from then on it worked fine, but that is felling "hacky" and not a good solution overall.

Any ideas here?

0 likes
3 replies
jlrdw's avatar

Just change the next Auto increment in one of the SQL tools. I use sqlyog, but there is Heidi and others.

bobbybouwmann's avatar
Level 88

The problem is probably that while you migrated to MySQL, the auto-increment value wasn't taken into account. Because of that, all auto-increments start at 1.

You can set the auto-increment of the table to the latest ID. After that, there shouldn't be an issue anymore.

ALTER TABLE `industries` AUTO_INCREMENT = (SELECT MAX(id) FROM `industries`);

Let me know if that works for you.

1 like
Gbever's avatar

Thanks to both of you, you are correct about the issue. I got a syntax error at the "(SELECT " in the statement provided above, so I manually added in the highest increment to see if it would fix the problem, and it worked. Thank you so much, your answer solved my issue here and probably in many other areas, ill need to scour the DB. Much appreciated.

1 like

Please or to participate in this conversation.