Error restoring from backup

Using my Dolibarr
1

I need to migrate server and so I installed version 15.0.1 on my new server and I use version 13.0.0 on the old server and when I go to restore the backup of the old server I get the error:

Error
SQL Command:

CREATE TABLE llx_adherent_type (
rowid int(11) NOT NULL,
entity int(11) NOT NULL DEFAULT ‘1’,
tms timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
statut smallint(6) NOT NULL DEFAULT ‘0’,
libelle varchar(50) NOT NULL,
morphy varchar(3) NOT NULL,
duration varchar(6) DEFAULT NULL,
subscription varchar(3) NOT NULL DEFAULT ‘yes’,
vote varchar(3) NOT NULL DEFAULT ‘yes’,
note text,
mail_valid text
) ENGINE=InnoDB DEFAULT CHARSET=utf8
MySQL Messages : Documentation

#1005 - Cannot create table dolibarr.llx_adherent_type (error no. 150 “Foreign key constraint is incorrectly formed”) (Information…)

2

Hi, try restoring your backup without foreign key verification.
Hope that helps.
Regards,
Marc

3

I performed the restore without the foreign key verification but the error remains

I would like to understand why this error occurs with several tables

#1005 - Cannot create table dolibarr .llx_adherent_type (error no. 150 “Foreign key constraint is incorrectly formed”) (Information…)

4

I would try restoring a backup to the exact version it was created from, i.e. if it was created with version 13.0.0 I would restore it to a Dolibarr system of the same version 13.0.0 and migrate to 15.0.1 after the backup was successfully imported.

5

Hello I did this, I installed the same version of Dollibarr but when restoring the same error, by PHPMyadmin and by the command line.

6

Now I have this error when restoring the backup I use version 15.0.1 on both the old and the new Dolibarr

ERROR 1822 (HY000) at line 1324: Failed to add the foreign key constraint. Missing index for constraint ‘fk_adherent_adherent_type’ in the referenced table ‘llx_adherent_type’

7

I just had a look at the respective part in a backup from Dolibarr version 15.0.1:

--
-- Table structure for table `llx_adherent_type`
--

DROP TABLE IF EXISTS `llx_adherent_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `llx_adherent_type` (
  `rowid` int NOT NULL AUTO_INCREMENT,
  `entity` int NOT NULL DEFAULT '1',
  `tms` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `statut` smallint NOT NULL DEFAULT '0',
  `libelle` varchar(50) NOT NULL,
  `morphy` varchar(3) NOT NULL,
  `duration` varchar(6) DEFAULT NULL,
  `subscription` varchar(3) NOT NULL DEFAULT '1',
  `amount` double(24,8) DEFAULT NULL,
  `vote` varchar(3) NOT NULL DEFAULT '1',
  `note` text,
  `mail_valid` text,
  PRIMARY KEY (`rowid`),
  UNIQUE KEY `uk_adherent_type_libelle` (`libelle`,`entity`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

As you can see, there are two additional lines and an AUTO_INCREMENT is missing from your dump:

  PRIMARY KEY (`rowid`),
  UNIQUE KEY `uk_adherent_type_libelle` (`libelle`,`entity`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

You could try adding these lines into the backup file and see, if this solves the issue. However, there is probably some other underlying issue which causes this. For instance, you are also missing the field amount. Did you have any trouble in the past while performing a migration to a newer release?

There is still an additional option. Dolibarr comes with a repair script for the database: /install/repair.php More info can be found on this page: Troubleshooting - Dolibarr ERP CRM Wiki

You can try your luck with it. BUT: It is good to have a (working) backup before doing this, which is not the case in your scenario. So you may want to take a server snapshot or do whatever can guarantee to be able to roll back to the state before running the script.

Hope this helps.

8

Not sure if this helps but, for me, I had this issue when I was trying to populate demo data - more or less, the same function - restoring the database. I came across Rrestore db from server to localhost (XAMPP) and realized that dropping and recreating the database is a solution. In my case, I simply dropped all the tables in the DB and ran the script although in your case, I am assuming there are anyways no tables in the DB. Your issue is most probably resolved by now but I am hoping this helps someone who comes across the thread