maria-discuss team mailing list archive

[Alex <mysqlstudent@xxxxxxxxx>]

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.