auth: gsql schema cleanup

[Habbie]

Over the years, the gsql schemas for the various backends have accumulated some accidents. There were also accidents in the first versions of the schemas. This ticket collects them.

• [ ] Y2038-problems #11872
• [ ] various columns in various schemas could be simplified to TEXT (please read all comments in #11836)
• [ ] utf8 fields in the gmysql schema should be utf8mb4

[hlindqvist]

• [ ] The gmysql schema started out without specifying a charset for character types (example), inheriting the default from the db environment.
  When this was later improved for new installations, the migration steps did not change the charset for already existing columns, instead only the default charset applied to any newly created columns was changed (example).
  Seemingly if your environment used a different charset than 'latin1' when the tables were originally created, the original character-type columns will still be in that other charset after applying all the migration scripts.
  See also: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-character-set

[silverwind]

Regarding mysql charset: some columns specify deprecated utf8 charset:

https://github.com/PowerDNS/pdns/blob/bb10ed912e06ee33b501dc38e9fcfb1ca99a6707/modules/gmysqlbackend/schema.mysql.sql#L52

There was https://github.com/PowerDNS/pdns/pull/8622 that would have fixed. Keep note that future MySQL will likely re-alias utf8 to utf8mb4:

utf8 is currently an alias for utf8mb3, but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4.

[murphy83]

Just stumbled upon another issue when upgrading my instance to Version 4.7. => the given documentation for migration contains: ALTER TABLE domains ADD options VARCHAR(64000) DEFAULT NULL; Which causes errors with at least MariaDB 10.6 => you need to use TEXT as datatype for the option field in this case.

Maybe even the idea of using a database-versioning system such as Liquibase might be handy, especially for systems running docker based and therefor highly automated => Deploy a new version of the container should also take care of upgrading the database if this is required by an update.