search

puppetlabs / puppetlabs-mysql

MySQL Puppet Module / Manifests + Types & Providers
Apache License 2.0
380 stars 790 forks source link

Default utf8 character set and collate for databases is deprecated #1619

Open mdetrano opened 6 months ago

mdetrano commented 6 months ago

Use Case

If the charset and collate fields are not set for a mysql::db resource, the default is to use "utf8" and "utf8_general_ci". Those values were deprecated and had been treated as aliases for 'utf8mb3' and 'utf8mb3_general_ci'. More recent versions of Mariadb (perhaps Mysql too) will report utf8mb3 for the database, even if set using "utf8"..and this module will attempt to change and rerun the sql scripts when that difference is detected.

Describe the Solution You Would Like

I believe the defaults set in db.pp should be updated. Also, if this module can catch the utf8 - utf8mb3 differences and treat them as NOT different, that could smooth out some issues.

Describe Alternatives You've Considered

Explicitly setting the charset and collate values in the manifest can avoid the attempted updates.

Additional Context

Ran into this issue after an update of Mariadb to 10.11.6

bastelfreak commented 5 months ago

@mdetrano can you take a look at https://github.com/puppetlabs/puppetlabs-mysql/pull/1624 ?

mdetrano commented 5 months ago

@bastelfreak

@mdetrano can you take a look at #1624 ?

I gave that PR a try. First on a system running Mariadb 10.5.x... in that setup it does NOT work, it will see a database created with default settings as having changed each time, and attempt to recreate it (because of the way the utf8 vs. utf8mb3 charset is reported).

If I subsequently update to Mariadb 10.11 (in el9 type systems there is a packaging module for this), then that PR DOES work fine (the charset gets reported as utf8mb3.. which matches its expectations).

Not sure if there is a way to have this module catch when the only 'difference' it detects is between utf8 or utf8mb3 in the reported charset and collate settings, and just quietly ignore that. That could make this module friendlier to both common versions in use today and future or optional updates.