maria-discuss team mailing list archive

[Mark Edwards <mark@xxxxxxxxxxxxxxx>]

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@xxxxxxxxx> wrote:

>
>
> On Sat, Jun 3, 2023 at 11:07 AM mark f edwards <mark@xxxxxxxxxxxxxxx>
> 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.
>
>
>