https://dba.stackexchange.com/questions/203509/mysql-foreign-key-constraint-is-incorrectly-formed

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.