Corrective changes for charset/collate with v16 on EL9

[sircubbi]

Describe the Bug

#1619 changed the default-settings for charset from utf8 to utf8mb3 (and the collation accordingly), however MariaDB before 10.6 still reports utf8mb3 as utf8.

Therefore every puppet run reports corrective changes, unless you explicitly provide proper charset/collate manually.

Expected Behavior

Puppet should run without corrective changes

Steps to Reproduce

Steps to reproduce the behavior:

1. use stock EL9 system which comes with MariaDB 10.5 by default
2. create a database with mysql::db (without specifying a charset/collate)
3. run puppet several times and see corrective changes:

Info: Applying configuration version 'puppet-production-96de84926e6'
Notice: /Stage[main]/Profile::Server::XXX/Mysql::Db[YYY]/Mysql_database[YYY]/charset: charset changed 'utf8' to 'utf8mb3' (corrective)
Notice: /Stage[main]/Profile::Server::XXX/Mysql::Db[YYY]/Mysql_database[YYY]/collate: collate changed 'utf8_general_ci' to 'utf8mb3_general_ci' (corrective)

Environment

• Modul v16
• RockyLinux 9
• MariaDB 10.5.22

Additional Context

utf8mb3 is an alias for utf8 in MariaDB-Versions older than 10.6 (see https://mariadb.com/docs/server/ref/mdb/character-sets/utf8mb3/ -> Collation name changed in MariaDB Community Server 10.6.1 from utf8_general_ci to utf8mb3_general_ci)

Therefore on a default installation of EL9 it gets reported as utf8/utf8_general_ci:

> show variables like '%_database';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| character_set_database | utf8            |
| collation_database     | utf8_general_ci |
| skip_show_database     | OFF             |
+------------------------+-----------------+
3 rows in set (0.020 sec)

[sircubbi]

Actually what I kind of would like to have, is a global setting for charset and collation. If something like that would be set in the mysql::server-context (maybe even put into the mysqld-configsection), it could be reused for the mysql::db-definitions. The need of repeating charset and collation in every mysql::db-definition is error-prone.

It would be really nice to especially set the collation on a global-level via hiera/class-defaults, because the default of *_general_ci is a bad idea anyway (it should be *_unicode_ci instead).

[vchepkov]

It seems related to @bastelfreak change here #1624
From what I can tell, the defaults in the defined resource do not match defaults in the type
https://github.com/puppetlabs/puppetlabs-mysql/blob/main/lib/puppet/type/mysql_database.rb#L17-L26

And, there is another bug, in the provider somewhere
if you create database and the change properties, provider will fail to update both

mysql::db { 'testing':
  user     => 'testing',
  password => 'testing',
  host     => 'localhost',
  grant    => ['ALL'],
  charset  => 'binary',
  collate  => 'binary',
}

Notice: /Stage[main]/Main/Mysql::Db[testing]/Mysql_database[testing]/charset: charset changed 'utf8' to 'binary'
Error: Execution of '/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe alter database `testing` COLLATE binary' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary' at line 1
Error: /Stage[main]/Main/Mysql::Db[testing]/Mysql_database[testing]/collate: change from 'utf8_general_ci' to 'binary' failed: Execution of '/bin/mysql --defaults-extra-file=/root/.my.cnf -NBe alter database `testing` COLLATE binary' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary' at line 1

[vchepkov]

So, second error is cause by inconsistency in mysql syntax
while character set is accepted without quotes, collate must be quoted

 alter database testing  COLLATE binary;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary' 

 alter database testing  COLLATE "binary";
Query OK, 1 row affected (0.001 sec)

[vchepkov]

imho, provider should update both at once, since setting can be incompatibe

alter database testing  CHARACTER SET "binary" COLLATE "binary";
Query OK, 1 row affected (0.001 sec)

 alter database testing  CHARACTER SET "binary" COLLATE "utf8_general_ci";
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'

[saz]

This is also affecting Debian 11.

[kenyon]

Related: #1392 was an attempt to use utf8mb4.