Increase compatibility with MySQL 8.x

[TheWitness]

Feature Request

Is your feature request related to a problem? Please describe

We normally do not test on MySQL 8, but recently had a user report an issue with MySQL 8 and our audit database script. As such, we need to start documenting compatibility errors with MySQL 8.x and create a project to remediate.

Describe the solution you'd like

Evaluate the install and operation of Cacti when using MySQL 8

Additional context

MySQL has been altering/breaking things recently.

[TheWitness]

First issue, our recommendations no longer are valid.

[TheWitness]

The procedure to create a user at the same time you are creating grants is not supported.

[TheWitness]

We are getting errors related to the password caching in MySQL 8.0.31 missing a Shared library. So, we need to set:

# required to make life easier on MySQL 8.x
default_authentication_plugin = mysql_native_password

Until this issue is fixed by Oracle. Here is the error we are getting:

SQLSTATE[HY000] [2059] Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

[TheWitness]

The latest MySQL 8.x does not show the number of characters for various integer columns now, so audit_database.php fails.

[TheWitness]

Looks like utf8mb4 was not good enough for Oracle leading to this install issue.

[TheWitness]

The sql_mode option NO_AUTO_CREATE_USER is no longer supported.

[TheWitness]

The following two options are no longer supported:

innodb_file_format = Barracuda
innodb_large_prefix = 1

[TheWitness]

The recommendation for buffer pool instances is too high. Needs to be corrected.

[TheWitness]

The default user/password of 'cactiuser'@'localhost' with a password of 'cactiuser' is no longer possible.

[TheWitness]

Errors updating tables during upgrade with a mix of collations. Lovely.

[TheWitness]

The issue above was cleared by dropping and re-creating the database with the proper default settings in /etc/my.cnf.

[TheWitness]

More issues with Automation. Very odd.

[TheWitness]

Okay, have a workaround for Automation.