Hello, I have a running Dolibarr 5.0.1 installation running on MAMP under OS X that I would like to migrate to an instance running on AWS so that several users my use it with an internet connection.
I have managed to create the instance, get it up and running and made it available on a subdomain.My problem is restoring the backup. I have copied over the /documents/ folder and the sql dump file. When I ssh into the instance and run the mysql command I get a "ERROR 1215 (HY000) at line 28: Cannot add foreign key constraint". As per dolibarr documentation I tried adding "SET FOREIGN_KEY_CHECKS=0;SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";" into the sql dump header but still no go... Can someone help me ?
In order to reproduce the issue, could you confirm if you followed the same steps that are documented in this guide?https://docs.bitnami.com/aws/apps/dolibarr/#how-to-backup-and-restore-dolibarr-using-the-application-administration-interface
Both the MAMP and the AWS version are 5.0.1, right?
Did you have any plugin installed in your MAMP Dolibarr version?
Yes I followed the documented steps, and both MAMP and AWS are running 5.0.1 versions.Could it be a permissions problem ?
Have you tried running
mysql> set foreign_key_checks = 0;
before the command from the "MySQL import" field?
You can find out more information about a mysql foreign key error by issuing the command
SHOW ENGINE INNODB STATUS;
Best regards,Carlos R. Hernández
This is the INNODB output :
LATEST FOREIGN KEY ERROR
2017-05-23 18:18:22 7f253d542700 Error in foreign key constraint of table bitnami_dolibarr/llx_accounting_account:
FOREIGN KEY (`fk_pcg_version`) REFERENCES `llx_accounting_system` (`pcg_version`)
) ENGINE=InnoDB AUTO_INCREMENT=4785 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
for correct foreign key definition.
Could this be because my Dolibarr install is in french ? I would doubt that but...
I don't think the problem is related to the application language. Probably it is due to inconsistent INNODB versions between MAMP and AWS. Can you execute inside both (MAMP and AWS) this command in the mysql CLI?
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_ver%';