Pdns: auth mysql: mysql 8 defaults to utf8mb4 which is incompatible with our latin1 schema declaration

[esosan]

• Program: Authoritative 4.3.1 with PowerDNS-Admin
• Issue type: Bug report

Short description

Add a new record (or modify something as TTL) fail.

Environment

• Operating system: ubuntu 20.40
• Software version: 4.3.1 (fresh install) with mysql 8.0.22 (ubuntu repo) as backend
• Software source: PowerDNS repository

Steps to reproduce

1. Try add new record, or change something like TTL.

Actual behaviour

On logs I have this error:

[webserver] 950d6d03-ca5e-44bc-9468-44f41b7064c8 HTTP ISE for "/api/v1/servers/localhost/zones/xxx.com": Exception: GSQLBackend unable to delete RRSet test.xxx.com|A: Could not execute mysql statement: delete from records where domain_id=? and name=? and type=?: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation '='

[alexpablo]

Same error with exact environment when I upgrade the packages mysql-server-8.0 and mysql-server-core-8.0 from version 8.0.21-0ubuntu0.20.04.4 to 8.0.22-0ubuntu0.20.04.2

[esosan]

some more infos:

Records table:
https://gist.github.com/esosan/8df2eb526ef7eeaf2bf46e4af55743a7

LOCALE output
https://gist.github.com/esosan/c7acbfa08db92231d8295e30dad74123

mysql SHOW VARIABLES;
https://gist.github.com/esosan/ed538192243ff11fc2e277aaeb1ffa53

[esosan]

mysql dump (no data):
https://gist.github.com/esosan/cc27680c038cd2897ea478960bdac2cb

[rubberduck09]

You need to change the Collation of any text field in your pdns database to "utf8mb4_0900_ai_ci" then it will work. I have done this wit phpmyadmin.
The "Bug" here is in the documentation where the Default Schema does not contain this Information.

[esosan]

Based on your suggestion I have changed a little bit the default schema.
https://gist.github.com/esosan/8bf2205179c7c1f4fdc230aa8948f154

I have try to restore the old data in the new schema without errors.

[Habbie]

via @zeha mysql 8: Important change: The default character set has changed from latin1 to utf8mb4.

[zeha]

Locally I set these things in my.cnf to make the tests work:

[client]
default-character-set = latin1

[rubberduck09]

Is it possible to put a small sql-file into the wiki which corrects the charset for 'old' installations which have been moved more than once to a new operating system version (thats the reason why i had this old collation in my db)?

[zeha]

From my understanding the SQL Schema is correct, and changing to utf8mb4 will hurt more than it does good. I guess some client changes will need to be made for pdns to work out of the box with MySQL 8.

[Habbie]

Locally I set these things in my.cnf to make the tests work:

This also works for me, so that's at least something we can document.

[Habbie]

This also works for me, so that's at least something we can document.

But, it breaks non-ASCII values in comments, accounts, etc. I have not yet managed to find a client charset+collation that works without breaking those.

[zeha]

Can't say I understand why this SQL works but the pdns queries do not...

mysql> select _utf8mb4'a' COLLATE utf8mb4_0900_ai_ci = _latin1'a' COLLATE latin1_swedish_ci;
+-------------------------------------------------------------------------------+
| _utf8mb4'a' COLLATE utf8mb4_0900_ai_ci = _latin1'a' COLLATE latin1_swedish_ci |
+-------------------------------------------------------------------------------+
|                                                                             1 |
+-------------------------------------------------------------------------------+

[Habbie]

Can't say I understand why this SQL works but the pdns queries do not...

I had similar questions after some experiments. My best guess is the statement interpolation..

[Habbie]

Sorted! See #9714.