Upgrade 19.0.3->20.0.0 PostgresSQL migration error in llx_societe_commerciaux

[shasta123]

Bug

Hi,

I tried to upgrade 19.0.3 to 20.0.0. The database migration scripts throws an error:

DB_ERROR_42830 (Req 220): ALTER TABLE llx_societe_commerciaux ADD CONSTRAINT fk_societe_commerciaux_fk_c_type_contact_code FOREIGN KEY (fk_c_type_contact_code) REFERENCES llx_c_type_contact(code);
ERROR: 42830: there is no unique constraint matching given keys for referenced table "llx_c_type_contact" LOCATION: transformFkeyCheckAttrs, tablecmds.c:11602

I ignored the error for now, and it seems to work.

Cheers,
Michael

Dolibarr Version

20.0.0

Environment PHP

8.2.20-1~deb12u1

Environment Database

PostgreSQL 15.8-0+deb12u1

Steps to reproduce the behavior and expected behavior

• Checked out 20.0 branch
• Opened /install/
• Perform migration

Attached files

olibarr=# select * from llx_societe_commerciaux;
rowid | fk_soc | fk_user | import_key | fk_c_type_contact_code
-------+--------+---------+------------+------------------------
1 | 24 | 3 | | SALESREPTHIRD
2 | 25 | 3 | | SALESREPTHIRD
(2 rows)

[eldy]

Can you paste also the content of select * from llx_c_type_contact

[shasta123]

Hi,

Sure, sorry, I forgot to add the dump in my initial report. At least it shows that "code" is not unique...

llx_c_type_contact.sql.txt

Cheers,
Michael

[GMellar]

This is the same for me using PHP 8.2 and Postgresql. Maybe the unique constraint was not created for postgresql during previous migrations. We could create it manually but I will wait for eldy's answer on that before I have to revert from the backup again.

[GMellar]

I tried to create a unique constraint but the code field is not unique (the combimation element, source and code is) which breaks the foreign key constraint in llx_societe_commerciaux because code is referenced which is not unique. I don't understand why the upgrade is possible in MariaDB. I attached my table too.
llx_c_type_contact.txt

[ouch67]

I am using PostgreSQL and am trying to upgrade to dolibarr 20.0.0 from 19.0.3. I get the same error.

And like GMellar the [code] field that the migration process is trying to use does not have unique entries in it making the foreign key impossible to make.

[msoula]

The table llx_societe_commerciaux is used to link companies (societe) to users.

In the v20 release, a new column, fk_c_type_contact_code, was introduced to specify the type of contact (referencing the llx_c_type_contact table). However, the code column cannot serve as a foreign key to the llx_c_type_contact table because multiple records may share the same value. For example, the default project contact types, PROJECTLEADER and PROJECTCONTRIBUTOR, are used twice, once for external contacts and once for internal contacts.

Currently, the fk_c_type_contact_code column in the llx_societe_commerciaux table is not in use. Therefore, it seems acceptable to ignore the warning until the next release addresses this issue.

[GMellar]

Currently, the fk_c_type_contact_code column in the llx_societe_commerciaux table is not in use. Therefore, it seems acceptable to ignore the warning until the next release addresses this issue.

I agree when it is not used the issue could be addressed later but Ignoring this error is not possible because that's the way a relational database works. A foreign key can not be created with the current data structure.

When the column is not used a solution could be to remove it in an update. I still do not know why there is no complaint on mariadb. At least some people should have updated to 20.0.0.

[shasta123]

I'm baffled. It seems MySQL accepts non-unique fkeys.

FWIW: I can confirm both create and upgrade does work on MariaDB 1:10.11.6-0+deb12u1 (see log)
test-mysql.txt

Both MySQL and PostgreSQL (correctly) deny to create an unique index on "code" in "llx_c_type_contact", but
only Postgres enforces the referenced fkey to be unique.

For now i think it's safe commenting out the creation of fk_societe_commerciaux_fk_c_type_contact_code as long as code is not used. At least that's what I did.

[GMellar]

I will try that but I'm not sure if this is the right way to solve this. It won't cause any trouble because the default value on fk_c_type_contact_code is set to 'SALESREPTHIRD' so even if there is no foreign key and the column is used in the code it won't break anything but the database consistency.

[n-rodriguez]

Hi there! Any news?

it won't break anything but the database consistency.

Which is the most important

[ludovicandrieux]

Same problem here.
FYI, I find an older ticket with same crash : #29775

[szerifi]

Same issue here migrating from 19.0.3 to 20.0.0 or 20.0.1.
In Postgresql we can't create a foreign key when the referenced key is not unique. No way to do that as it's designed so of course.
The "Code" row in "llx_c_type_contact" table is not unique. "RowID" is unique however.
Ignoring the error works but that could be an issue in future upgrades if Dolibarr team enforces something around the FK or the field.
Better is to wait an update in the script.

NB
In the migration script (v19 to 20), here's the affected lines:

ALTER TABLE llx_societe_commerciaux ADD COLUMN fk_c_type_contact_code varchar(32) NOT NULL DEFAULT 'SALESREPTHIRD';

ALTER TABLE llx_societe_commerciaux ADD UNIQUE INDEX uk_societe_commerciaux_c_type_contact (fk_soc, fk_user, fk_c_type_contact_code);
ALTER TABLE llx_c_type_contact ADD INDEX idx_c_type_contact_code (code);
ALTER TABLE llx_societe_commerciaux ADD CONSTRAINT fk_societe_commerciaux_fk_c_type_contact_code FOREIGN KEY (fk_c_type_contact_code) REFERENCES llx_c_type_contact(code);
ALTER TABLE llx_societe_commerciaux ADD CONSTRAINT fk_societe_commerciaux_fk_soc FOREIGN KEY (fk_soc) REFERENCES llx_societe(rowid);
ALTER TABLE llx_societe_commerciaux ADD CONSTRAINT fk_societe_commerciaux_fk_user FOREIGN KEY (fk_user) REFERENCES llx_user(rowid);

[rmaziere]

Hi,
I've got the same problem to upgrade from 19.0.1 to 20.0.0 with PostgreSQL on Debian with DoliDeb.
I commented the SQL command to pass without error.
The file is /usr/share/dolibarr/htdocs/install/mysql/migration/19.0.0-20.0.0.sql

[n-rodriguez]

Hi there! Any news? This is a blocking issue that prevents upgrade to Dolibarr 20.0.0 when using Postgres DB.

ping @eldy

Thank you!