Dadabik upgrade violating unique constraint

[darren]

Hi i am trying to upgrade my dadabik but i am getting this error on step 3/4

[08] Error: during query execution. INSERT INTO "dadabik_permissions_beta" (id_permission,subject_type_permission,id_subject,object_type_permission,object_permission,id_permission_type,value_permission) SELECT id_permission,subject_type_permission,id_subject,object_type_permission,object_permission,id_permission_type,value_permission FROM "dadabik_permissions"
The DBMS server said: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "unique_permission_beta" DETAIL: Key (id_subject, object_type_permission, object_permission, id_permission_type)=(1, table, boolean_values, 14) already exists.

The error is self explanatory but the solution is not. I don't fully understand how this could happen because the table its pulling from has a unique constraint on it already for the primary key

I cant login anymore either btw

Dadabik vers: 11.9
Browser Opera
OS Linux
DBMS postgres 13.1

 

[darren]

I went through each duplicate and deleted one of them, now the error im facing is "Error during file copy"

when looking at the php error log it outputs this " PHP Warning: copy(): The first argument to copy() function cannot be a directory in /var/www/html/natcat/up2.php on line 0"

 

[eugenio]

hello,
let's try to face one problem at a time. As you said, the table "dadabik_permissions" should have a unique constraint, but in the second post you said you deleted duplication. Was the unique constraint actually set?

 

[darren]

hello,
let's try to face one problem at a time. As you said, the table "dadabik_permissions" should have a unique constraint, but in the second post you said you deleted duplication. Was the unique constraint actually set?

Yes the unique constraint was actually set, it seems the unique constraint for the _beta version of the table is a different requirement than the non beta version where the only constraint was the unique primary key. the additional constraint of having "id_subject, object_type_permission, object_permission, id_permission_type" was not required and so nothing was in violation until being imported into the _beta table

 

[eugenio]

Ok, I see.
The only possible reason that comes to my mind is the following: your 11.9 app was in turn an upgrade from an older version, probably < 10.4.
I am saying that because the V 10.4 upgrade script asked the user to manually add that unique constraint you are missing, maybe you missed that part?

 

[darren]

I can see that, I ended up truncating all the dadabik tables and reinserting the data into the new one, going line by line on the ones that may be violating the constraint. I still have no idea why i had a "failed to copy" error. there was no permissions issue and the error: "PHP Warning: copy(): The first argument to copy() function cannot be a directory in /var/www/html/natcat/up2.php on line 0" points to a problem with subdirectories within the folder being moved.

 

[eugenio]

It's difficult to track how things got wrong if the upgrade was previously not correctly made. The permissions of your applications could be in the wrong state if you didn't follow the correct procedure when you upgraded through V 10.4.

What I suggest is to revert your application to its original state (before the last upgrade attempt) using a backup, then:

1) check all your table/groups permissions combination in the permissions page and for each one, if they are fine, save both table level and field level

2) Execute this:
ALTER TABLE dadabik_permissions alter column object_permission type VARCHAR(130);
ALTER TABLE dadabik_permissions ADD CONSTRAINT unique_permission UNIQUE (id_subject,object_type_permission,object_permission,id_permission_type);

3) make a new backup and retry the upgrade.

Best,

 

[darren]

Thank you, what i ended up doing was deleting all the dadabik tables, installing a fresh dadabik 12.0 then truncating the tables and inserting the old data from my previous dadabik tables into the new ones, fixing errors if there were any, this way whatever new constraints or table alterations since 10.4 are maintained. im still encountering errors here and there and some of my tables my tables need to be reinstalled but so far its better than rebuilding my entire dadabik from the ground up. I am having some issue that i would need solving but they are outside the parameter of my original question

 

[eugenio]

You don't need to rebuild your dadabik from the ground up, If you want to bring back your application in a clean state, the correct procedure is the one I explained in the previous post.

best,