maria-discuss team mailing list archive

[mark f edwards <mark@xxxxxxxxxxxxxxx>]

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