Open sircubbi opened 1 month ago
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).
It seems related to @bastelfreak change here https://github.com/puppetlabs/puppetlabs-mysql/pull/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
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)
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'
Describe the Bug
1619 changed the default-settings for charset from
utf8
toutf8mb3
(and the collation accordingly), however MariaDB before 10.6 still reportsutf8mb3
asutf8
.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:
mysql::db
(without specifying a charset/collate)Environment
Additional Context
utf8mb3
is an alias forutf8
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
: