Moving to MariaDB from MySQL - Foreign key constraint is incorrectly formed

Question

I just updated my local dev environment that uses XAMPP and the new version of XAMPP uses MariaDB, whereas the old version I was on was using MySQL, which I'm fine with.

Now, I thought MariaDB was supposed to be fully compatible with MySQL as it's essentially just a "drop-in" replacement, however I had trouble importing a database that I exported straight from MySQL before the upgrade.

I get the below error:

Query:
/*Table structure for table `blm_wc_download_log` */
CREATE TABLE `blm_wc_download_log` (
  `download_log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `permission_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `user_ip_address` varchar(100) COLLATE utf8mb4_unicode_520_ci DEFAULT '',
  PRIMARY KEY (`download_log_id`),
  KEY `permission_id` (`permission_id`),
  KEY `timestamp` (`timestamp`),
  CONSTRAINT `fk_blm_wc_download_log_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `blm_woocommerce_downloadable_product_permissions` (`permission_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

Error occured at:2019-02-26 05:30:20
Line no.:9919
Error Code: 1005 - Can't create table `my-db`.`blm_wc_download_log` (errno: 150 "Foreign key constraint is incorrectly formed")

Here is blm_woocommerce_downloadable_product_permissions:

CREATE TABLE `blm_woocommerce_downloadable_product_permissions` (
  `permission_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `download_id` varchar(36) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `order_key` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `user_email` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `downloads_remaining` varchar(9) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `access_granted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `access_expires` datetime DEFAULT NULL,
  `download_count` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`permission_id`),
  KEY `download_order_key_product` (`product_id`,`order_id`,`order_key`(16),`download_id`),
  KEY `download_order_product` (`download_id`,`order_id`,`product_id`),
  KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Could it be because blm_woocommerce_downloadable_product_permissions is created further down in the file that it errors because it thinks the table doesn't exist? But in saying that, I have never had any trouble importing an SQL dump of this database into MySQL before.

What is the issue here? May as well stick with MySQL if there are going to be compatibility issues...

Answer 1

Yeah, most likely you are not creating the tables before applying the FK, you could comment the constraint line out make your tables and then apply the constraint once its fully created.