Problem Migrating from v4.5.50 to v5.8.46

Self-Hosted | v4/Legacy
1

I had 4.5.18 installed on a hostgator server and for some weird reasons they did some updates that messed everything up and I exported the db to local host. I installed php 7.4 and invoice ninja 4.5.50 and imported the db. Now I want to install in v5 but the migration from localhost on my pc always crashes on the taskstatus table with this error

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’ (SQL: insert into task_statuses (user_id, account_id, public_id, name, sort_order, updated_at, created_at) values (1, 1, 2, Backlog, 0, 2024-06-05 22:47:04, 2024-06-05 22:47:04))

I tried resetting the autoincrement to 1 and inserting the data back even did the same in v5 and the same error keeps coming back. How do I bypass this because to me it seems like it’s data that is not that important for me.

This is what the table contained before I deleted everything from it

INSERT INTO task_statuses (id, user_id, account_id, created_at, updated_at, deleted_at, name, sort_order, public_id) VALUES
(0, 1, 1, ‘2024-06-05 19:42:23’, ‘2024-06-05 19:42:23’, NULL, ‘Backlog’, 0, 6),
(1, 1, 1, ‘2024-06-05 18:57:47’, ‘2024-06-05 18:57:47’, NULL, ‘Backlog’, 0, 5),
(2, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘Backlog’, 0, 1),
(3, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘Ready to do’, 1, 2),
(4, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘In progress’, 2, 3),
(5, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘Done’, 3, 4);

How do I proceed?

2

Hi,

It looks like you may have more than task status with a public_id of 0.

3

There seems to be only one record that has an id of 0 in the task_statuses table

Is it corrected to have a record with a 0 id on an auto_increment?

4

0 is an expected value for public_id, but not id.

@david do you have any suggestions?

5

That’s correct. I deleted all the records in the table and manually recreated the table then changed the insert to as follow

INSERT INTO task_statuses (id, user_id, account_id, created_at, updated_at, deleted_at, name, sort_order, public_id) VALUES
(1, 1, 1, ‘2024-06-05 19:42:23’, ‘2024-06-05 19:42:23’, NULL, ‘Backlog’, 0, 6),
(2, 1, 1, ‘2024-06-05 18:57:47’, ‘2024-06-05 18:57:47’, NULL, ‘Backlog’, 0, 5),
(3, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘Backlog’, 0, 1),
(4, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘Ready to do’, 1, 2),
(5, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘In progress’, 2, 3),
(6, 1, 1, ‘2021-02-08 17:51:06’, ‘2021-02-08 17:51:06’, NULL, ‘Done’, 3, 4);

After inserting, I was surprised to find there is still a record with a 0 id which should not be the case because auto_increment is not supposed to accept a 0 primary id

6

The public_id field in v4 isn’t an auto increment field

7

You are correct. It seems the error is related to id which is the primary key

It seems to be seeding the table from somewhere and I can’t figure out

8

It took a while to get the solution but I was finally able to figure it out

Even though the db sql export file has the creation and data insertion codes together with the constraints, the auto_increment on the primary key was not being set. The solution was to go into the the db table via phpmyadmin, go to structure and click on change column for id and select the A_I option to enable AUTO_INCREMENT.

After that, I run the insertion of data and and did the migration with no issues.

1 Like
9

Glad to hear it, thanks for sharing the solution!