2

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...

Brett
  • 19,449
  • 54
  • 157
  • 290
  • 1
    Yes, the order of table creation is important. You must create the referenced table before you can declare a foreign key to it. – Bill Karwin Feb 25 '19 at 21:57
  • That said, MariaDB is becoming less and less of a drop-in replacement for MySQL. It has been slowly diverging from MySQL since 2010, when it forked. I don't use MariaDB. – Bill Karwin Feb 25 '19 at 21:58
  • 1
    You might like to review the MySQL foreign keys checklist that I contributed to: https://stackoverflow.com/a/4673775/20860 – Bill Karwin Feb 25 '19 at 21:59
  • Thanks Bill - any idea why importing works with MySQL then? – Brett Feb 25 '19 at 22:00
  • 1
    It can't work on MySQL either if you create the tables out of order, unless you are creating the tables as MyISAM or another storage engine that ignores foreign key definitions. Another possibility is that the table already exists before the import. – Bill Karwin Feb 25 '19 at 22:37
  • I mean this works fine even if `bar` doesn't exist: `create table foo ( i int, foreign key (i) references bar(i)) engine=myisam;` because myisam doesn't support FK constraints, therefore it gives no error. But the table won't actually have any FK. – Bill Karwin Feb 25 '19 at 22:38
  • @BillKarwin Ok, I found out what the difference was. When I was importing it to the staging site, I was doing so from an export from WordPress from a plugin called "WP Migrate DB" - looking at what it exports it doesn't seem to include the foreign key constraint and hence why it didn't produce any errors. On top of this, I found out it was possible to avoid foreign key errors by disabling the _foreign key check_ on import. – Brett Feb 26 '19 at 09:51
  • Yes, all comments about the order are BS. Sorry @BillKarwin but you are sending people on a wild goose chase here. The order is *not* the problem. I just had something similar and playing with it my problem was that I had a foreign key that referenced a utf8mb4 column... I clearly see other tables with foreign keys referencing tables later in the file that have no issue. The one that has the problem has utf8mb4 and that seems to be an issue with mariadb. – Stijn de Witt Jan 13 '22 at 09:37
  • Check the top of the dump file and see how it disables foreign key checks: `/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; ` That's on purpose. The mysqldump command knows what it's doing. Order is *not* the issue here. – Stijn de Witt Jan 13 '22 at 09:39

1 Answers1

0

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.

SaneGaming
  • 1
  • 2