SQL errors when migrating from v14 to v16 latest build

[josett225]

Bug

Hi There

When migrating from v14 to v16 latest build from github, I am getting this error message :

Error DB_ERROR_1364: insert into llx_c_type_contact(element, source, code, libelle, active ) values ('conferenceorbooth', 'internal', 'MANAGER', 'Conference or Booth manager', 1);Field 'rowid' doesn't have a default value

Error DB_ERROR_1364: insert into llx_c_type_contact(element, source, code, libelle, active ) values ('conferenceorbooth', 'external', 'SPEAKER', 'Conference Speaker', 1);Field 'rowid' doesn't have a default value Error DB_ERROR_1364: insert into llx_c_type_contact(element, source, code, libelle, active ) values ('conferenceorbooth', 'external', 'RESPONSIBLE', 'Booth responsible', 1);Field 'rowid' doesn't have a default value

After looking to the database, it seems rowid as not been configured with AUTOINCREMENT. How could we fix this?

Regard

Environment Version

16.0.0

Environment OS

No response

Environment Web server

No response

Environment PHP

7.4.25

Environment Database

MySQL or MariaDB 5.5.5-10.2.44-MariaDB-10.2.44+maria~stretch-log

Environment URL(s)

/install

Expected and actual behavior

No response

Steps to reproduce the behavior

Normal upgrade process

Attached files

No response

[josett225]

Hi there, Any idea on this topic?

[eldy]

Try to run manually: ALTER TABLE llx_c_type_contact ADD PRIMARY KEY(rowid); ALTER TABLE llx_c_type_contact CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT;

[josett225]

Hi @eldy Many thanks for answering. I am getting the following error message when typing the 2 alter lines:

SQL query: Copy ALTER TABLE llx_c_type_contact ADD PRIMARY KEY(rowid); MySQL said: Documentation

1068 - Multiple primary key defined

If I run the second ALTER only : ALTER TABLE llx_c_type_contact CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT;

SQL query: Copy ALTER TABLE llx_c_type_contact CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT;

MySQL said: Documentation

1833 - Cannot change column 'rowid': used in a foreign key constraint 'fk_element_contact_fk_c_type_contact' of table 'serious.llx_element_contact'

To make work the second ALTER I deactivated the foreign key checks. Not sure It is good or not but it worked. Now I have the AUTO_INCREMENT is activated on rowid

Thanks for your help.

[josett225]

At least the error message has disappeared after a new install and the two missing rows in llx_c_type_contact have been added.

[StephaneLesage]

@eldy I have a similar problem when upgrading to v16. I restore a v14 database to a test installation. Upgrade to v15 is OK. Upgrade to v16 fails with the same errors:

Erreur DB_ERROR_1833: ALTER TABLE llx_c_type_contact CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT; Cannot change column 'rowid': used in a foreign key constraint 'fk_societe_contact_fk_c_type_contact' of table 'dolidev.llx_societe_contact'

Same when I check 'AI' in phpmyadmin... The script should either:

• enable "ignore foreign key constraints" ?
• or remove it, make the change, and then restore it ?

[hregis]

@eldy @josett225 @StephaneLesage

foreign key fk_societe_contact_fk_c_type_contact not exists in llx_societe_contact but with this name fk_societe_contacts_fk_c_type_contact with s in "contact" can you check your foreign key list in llx_societe_contact with phpmyadmin ?

[StephaneLesage]

foreign keys in llx_societe_contact:

foreign keys in llx_societe_contacts:

[hregis]

@StephaneLesage llx_societe_contact (without s at the end) is not an official table of Dolibarr ! maybe an external module ?

[hregis]

@eldy @StephaneLesage @josett225

the foreign key fk_element_contact_fk_c_type_contact is for the table llx_element_contact the foreign key fk_societe_contacts_fk_c_type_contact is for the table llx_societe_contacts

but fk_societe_contact_fk_c_type_contact for llx_societe_contact is not an official dolibarr table but this is for an external module...

[hregis]

@StephaneLesage @defrance maybe a module of patasmonkey ?

[hregis]

@StephaneLesage try this :

ALTER TABLE llx_societe_contact DROP FOREIGN KEY fk_societe_contact_fk_c_type_contact;
ALTER TABLE llx_societe_contacts DROP FOREIGN KEY fk_societe_contacts_fk_c_type_contact;
ALTER TABLE llx_c_type_contact ADD PRIMARY KEY(rowid); -- (remove if error)
ALTER TABLE llx_c_type_contact CHANGE COLUMN rowid rowid INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE llx_societe_contacts ADD CONSTRAINT  fk_societe_contacts_fk_c_type_contact FOREIGN KEY (fk_c_type_contact)   REFERENCES llx_c_type_contact(rowid);
ALTER TABLE llx_societe_contact ADD CONSTRAINT  fk_societe_contact_fk_c_type_contact FOREIGN KEY (fk_c_type_contact)   REFERENCES llx_c_type_contact(rowid);

[StephaneLesage]

@StephaneLesage @defrance maybe a module of patasmonkey ?

It was of ATM's module "default contact". I disabled it, remove it, and then drop the tables. Then migration is fine.

[hregis]

@josett225 your bug is fixed ?

[josett225]

Hi @hregis

Sorry for being late answering. I will look into it tomorrow and come back to you as I have been testing some modules from Patas and ATM too.

Regards

Jose

[Hystepik]

It seems to be fixed so I close the issue. Feel free to reopen if not.