Foreign keys problem with MariaDB

This is the place for general discussion and updates about the ZXDB Database. This forum is not specific to Spectrum Computing.

Moderator: druellan

Post Reply
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Foreign keys problem with MariaDB

Post by moroz1999 »

I've found the following problem. when I'm trying to import the database dump, I'm getting an error:

Server version: 10.2.18-MariaDB-log - Source distribution

-- Dumping structure for table zxdb.aliases
CREATE TABLE IF NOT EXISTS `aliases` (
`id` int(11) NOT NULL,
`entry_id` int(11) NOT NULL,
`release_seq` smallint(6) NOT NULL DEFAULT '0',
`idiom_id` char(2) COLLATE utf8_bin DEFAULT NULL,
`title` varchar(250) COLLATE utf8_bin NOT NULL,
`library_title` varchar(250) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_alias_key` (`entry_id`,`release_seq`,`idiom_id`,`title`),
KEY `fk_alias_idiom` (`idiom_id`),
CONSTRAINT `fk_alias_entry` FOREIGN KEY (`entry_id`) REFERENCES `entries` (`id`),
CONSTRAINT `fk_alias_idiom` FOREIGN KEY (`idiom_id`) REFERENCES `idioms` (`id`),
CONSTRAINT `fk_alias_release` FOREIGN KEY (`entry_id`, `release_seq`) REFERENCES `releases` (`entry_id`, `release_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

MySQL said: Documentation
#1005 - Can't create table `d17227_zxdb`.`aliases` (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

This error usually comes when source table column type somehow differs from destination table's type. I suspect that in this case the problem may be with aliases.idiom_id (Default null) and idiom.id (default NONE, not null). Sounds ridiculously, but that's the only difference I see.
May be that's not the only foreign key problem in a database, may be that's a false alarm.
I only MariaDB on my web hosting and MariaDB seems to be a successor of older MySQL, but it has some slight incompatibilities and differences. Latest ZXDB is imported just fine into good old MySQL on my local machine.
User avatar
kolbeck
Manic Miner
Posts: 309
Joined: Mon Nov 13, 2017 9:04 pm

Re: Foreign keys problem with MariaDB

Post by kolbeck »

I’ve used MariaDB all time without any problems. Have a look at my setup here https://github.com/thomasheckmann/zxinfo-db

/Thomas
https://api.zxinfo.dk/v3/ - ZXDB API for developers
zxinfo-file-browser - Cross platform app to manage your files
https://zxinfo.dk - another ZXDB frontend
User avatar
Einar Saukas
Bugaboo
Posts: 3070
Joined: Wed Nov 15, 2017 2:48 pm

Re: Foreign keys problem with MariaDB

Post by Einar Saukas »

I'm using MariaDB 10.3 myself, so there's certainly no incompatibility.

Either there's a bug in your MariaDB version (try upgrading to latest stable release) or your ZXDB file was corrupted somehow (try downloading it again).


EDIT: Also did you already have a previous version of ZXDB in your server? If so, try DROP DATABASE ZXDB before importing it again.
User avatar
moroz1999
Manic Miner
Posts: 329
Joined: Fri Mar 30, 2018 9:22 pm

Re: Foreign keys problem with MariaDB

Post by moroz1999 »

Thanks guys! It seems to me that the problem can also be in phpMyAdmin. I've disabled "foreign keys check" in import dialogue, and it was just fine.
Unfortunately, since it's a shared hosting, I cannot upgrade anything by myself :)
Post Reply