Re: [Maria-discuss] Restore from mysqldump fails with "Error in foreign key constraint"
How is providers in the `xu6gc_tj_notification_providers' table defined?
Is it also varchar(100) character sat uft8mbr collate utf8mb4-general-ci ??
The parent and child columns has to match exactly.
On June 3, 2023, at 7:55 AM, Alex mysqlstudent@gmail.com wrote:
Hi,
I'm trying to use mysqlbackup to backup a mariadb-10.5.19 database on fedora37 server using the following:
# mysqldump --verbose -u root -ppass --result-file=/var/backup/mysql-backup/backup-lsalex-2023-06-03-13008.sql lsalex
I usually use a more involved mysqlbackup command, but it also failed, so I figured it would be better to start out more simply as above. It fails with the following when restoring to a new, test database:
# mysql -u root -p lstest < /var/backup/mysql-backup/backup-lsalex-2023-06-03-13008.sql
Enter password: ERROR 1005 (HY000) at line 12884: Can't create table `lsalex`.`xu6gc_tj_notification_user_exclusions` (errno: 150 "Foreign key constraint is incorrectly formed")
This is a backup/restore of a Joomla database. Am I using the wrong command to perform the backup or restore? This isn't a database that I created and I really have no idea how it works. I've also searched extensively for this error, and it appears to be a programming error, not a database error, but obviously I have no control over Joomla programming.
Here's what I found from "SHOW ENGINE INNODB STATUS":
LATEST FOREIGN KEY ERROR ------------------------ 2023-06-03 10:09:01 0x7f3a8d8516c0 Error in foreign key constraint of table `lstest`.`xu6gc_tj_notification_user_exclusions`: Create table `lstest`.`xu6gc_tj_notification_user_exclusions` with foreign key `xu6gc_tj_notification_user_exclusions_ibfk_1` constraint failed. Field type or character set for column 'provider' does not match referenced column 'provider'.------------
Here's the lines from the backup mentioned in the error message:
12884 CREATE TABLE `xu6gc_tj_notification_user_exclusions` ( 12885 `user_id` int(11) NOT NULL, 12886 `client` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, 12887 `key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, 12888 `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, 12889 KEY `client1` (`client`,`provider`(50),`key`), 12890 KEY `key` (`key`), 12891 KEY `provider` (`provider`), 12892 CONSTRAINT `xu6gc_tj_notification_user_exclusions_ibfk_1` FOREIGN KEY (`provider`) REFERENCES `xu6gc_tj_notification_providers` (`provider`) 12893 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Any ideas are greatly appreciated.
On Sat, Jun 3, 2023 at 11:07 AM mark f edwards mark@edwardsmark.com wrote:
How is providers in the `xu6gc_tj_notification_providers' table defined?
Is it also varchar(100) character sat uft8mbr collate utf8mb4-general-ci ??
The parent and child columns has to match exactly.
I believe this is what you're asking for?
12798 CREATE TABLE `xu6gc_tj_notification_providers` ( 12799 `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 12800 `state` int(1) NOT NULL, 12801 PRIMARY KEY (`provider`) 12802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
How could the backup succeed (implying the data/schema was created successfully in the first place), but then the restore fails because of that same schema apparently being created incorrectly?
Is this a bug that should be reported to Joomla? If so, how do I describe it so they will understand?
I don't even really need this table for my testing, so I'll probably just delete it, but it concerns me that I can perform a backup that can't be restored.
https://dba.stackexchange.com/questions/203509/mysql-foreign-key-constraint-...
child says: `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_*general*_ci NOT NULL,
parent says: `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_*unicode*_ci NOT NULL,
notice collate does not match? mariadb is very particular on how the keys have to match up perfectly.
this is really not so much as a Joomla issue as it might be when the db was initially set up. Joomla really does not know (or care) how the data is stored. i am guessing you can use any SQL like postgres although i have not used Joomla very much. or maybe Joomla is LAMP only? i forget.
fwiw you could create a sed filter that does something like:
sed --script="s/`provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,/`provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,/"
sorta a workaround?
and please don't feel alone dealing with this issue. you are in GOOD COMPANY. many hours have been burned chasing down the foreign key constraint problem.
Thank you,
Mark Edwards
On Sat, Jun 3, 2023 at 8:24 AM Alex mysqlstudent@gmail.com wrote:
On Sat, Jun 3, 2023 at 11:07 AM mark f edwards mark@edwardsmark.com wrote:
How is providers in the `xu6gc_tj_notification_providers' table defined?
Is it also varchar(100) character sat uft8mbr collate utf8mb4-general-ci ??
The parent and child columns has to match exactly.
I believe this is what you're asking for?
12798 CREATE TABLE `xu6gc_tj_notification_providers` ( 12799 `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 12800 `state` int(1) NOT NULL, 12801 PRIMARY KEY (`provider`) 12802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
How could the backup succeed (implying the data/schema was created successfully in the first place), but then the restore fails because of that same schema apparently being created incorrectly?
Is this a bug that should be reported to Joomla? If so, how do I describe it so they will understand?
I don't even really need this table for my testing, so I'll probably just delete it, but it concerns me that I can perform a backup that can't be restored.
On 03.06.23 18:06, Mark Edwards wrote:
child says: `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_*general*_ci NOT NULL,
parent says: `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_*unicode*_ci NOT NULL,
notice collate does not match? mariadb is very particular on how the keys have to match up perfectly.
I missed the start of this thread, but this problem looks like
https://jira.mariadb.org/browse/MDEV-31086
to me.
Hi,
On 6/5/23 6:07 AM, Hartmut Holzgraefe wrote:
On 03.06.23 18:06, Mark Edwards wrote:
child says: `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_*general*_ci NOT NULL,
parent says: `provider` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_*unicode*_ci NOT NULL,
notice collate does not match? mariadb is very particular on how the keys have to match up perfectly.
I missed the start of this thread, but this problem looks like
Yep, that's it. Thanks so much. I suspect by the next time I need this backup for my test server, it'll have been fixed upstream.
Thanks also to Mark Edwards for his awesome support.
participants (5)
-
Alex -
Alex Regan
-
Hartmut Holzgraefe -
Mark Edwards -
mark f edwards