Cacti and MySQL 8

stormonts · Post by **stormonts** » Tue Apr 14, 2020 2:07 pm

We have run into what we think is an issue with MySQL 5.7 and CentOS 7, so we were debating upgrading to MySQL 8 to see if that resolves the issue. We are currently on Cacti 1.2.9 and wondered if any of the alerts generated by the MySQL upgrade check are fixed in newer version of Cacti or if we have to manually fix them.

Code: Select all

The MySQL server at localhost, version 5.7.29 - MySQL Community Server (GPL),
will now be checked for compatibility issues for upgrade to MySQL 8.0.19...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  Warning: The following objects have names that conflict with new reserved
    keywords. Ensure queries sent by your applications use `quotes` when
    referring to them or they will result in errors.
  More information:
    https://dev.mysql.com/doc/refman/en/keywords.html

  cacti.aggregate_graph_templates_graph.grouping - Column name
  cacti.cdef.system - Column name
  cacti.data_source_profiles_rra.rows - Column name
  cacti.graph_templates_graph.grouping - Column name
  cacti.plugin_hooks.function - Column name

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  No issues found

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

15) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

16) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

17) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/

  global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
    which allows insertion of zero dates
  session.sql_mode -  of 164 session(s) does not contain either NO_ZERO_DATE or
    NO_ZERO_IN_DATE which allows insertion of zero dates
  cacti.automation_networks.next_start - column has zero default value:
    0000-00-00 00:00:00
  cacti.automation_networks.last_started - column has zero default value:
    0000-00-00 00:00:00
  cacti.automation_processes.heartbeat - column has zero default value:
    0000-00-00 00:00:00
  cacti.data_source_purge_temp.last_mod - column has zero default value:
    0000-00-00 00:00:00
  cacti.data_source_stats_hourly_cache.time - column has zero default value:
    0000-00-00 00:00:00
  cacti.graph_tree.locked_date - column has zero default value: 0000-00-00
    00:00:00
  cacti.graph_tree.last_modified - column has zero default value: 0000-00-00
    00:00:00
  cacti.host.status_fail_date - column has zero default value: 0000-00-00
    00:00:00
  cacti.host.status_rec_date - column has zero default value: 0000-00-00
    00:00:00
  cacti.plugin_monitor_notify_history.notification_time - column has zero
    default value: 0000-00-00 00:00:00
  cacti.plugin_monitor_reboot_history.reboot_time - column has zero default
    value: 0000-00-00 00:00:00
  cacti.poller.last_update - column has zero default value: 0000-00-00 00:00:00
  cacti.poller.last_status - column has zero default value: 0000-00-00 00:00:00
  cacti.poller.last_sync - column has zero default value: 0000-00-00 00:00:00
  cacti.poller_command.time - column has zero default value: 0000-00-00 00:00:00
  cacti.poller_output.time - column has zero default value: 0000-00-00 00:00:00
  cacti.poller_output_boost.time - column has zero default value: 0000-00-00
    00:00:00
  cacti.poller_output_boost_arch_1586890759.time - column has zero default
    value: 0000-00-00 00:00:00
  cacti.poller_output_realtime.time - column has zero default value: 0000-00-00
    00:00:00
  cacti.poller_resource_cache.update_time - column has zero default value:
    0000-00-00 00:00:00
  cacti.poller_time.start_time - column has zero default value: 0000-00-00
    00:00:00
  cacti.poller_time.end_time - column has zero default value: 0000-00-00
    00:00:00
  cacti.thold_data.lasttime - column has zero default value: 0000-00-00 00:00:00
  cacti.thold_data.lastchanged - column has zero default value: 0000-00-00
    00:00:00
  cacti.user_log.time - column has zero default value: 0000-00-00 00:00:00

18) Schema inconsistencies resulting from file removal or corruption
  No issues found

19) Tables recognized by InnoDB that belong to a different engine
  No issues found

20) Issues reported by 'check table x for upgrade' command
  No issues found

21) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

Errors:   0
Warnings: 33
Notices:  0

No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.

Post by **TheWitness** » Thu Apr 16, 2020 3:56 pm

There have been reports of success. If your report has any specific details, it would be good to upload them. MySQL 8 is a pretty large divergence from earlier MySQL Versions. For example, they are attempting to wholesale remove a number of tunables, and in their place, provide more cognitive tunings of memory settings, which I applaud. However, getting it working is a much heavier list. Anything you can contribute to it's adoption should go into github.com. I for one, played with it, and it was a bit of a pain initially to get around all the security landmines that had been added to the base install.

stormonts · Post by **stormonts** » Thu Apr 16, 2020 3:59 pm

Thanks for your input Larry. I'm not sure we will make the jump, but will definitely include anything I find in Github.

Post by **TheWitness** » Thu Apr 16, 2020 4:03 pm

Personally, I'm looking to MariaDB 10.4+ plus Galera for horizontal scalability and resiliency over MySQL. However, I'm perplexed that MariaDB, has thus far, only though their Column Store product, offered Sharding and what is akin to a parallel query. PostgreSQL is therefore ahead of both MariaDB and MySQL sadly.

Anyway, I know a few of you guys have Oracle entitlements. So, we will work with the community to continue to support MySQL.

Post by **TheWitness** » Thu Apr 16, 2020 4:26 pm

Oh crap, I just scrolled down on that post. It was cut off. It had all the details. Wohoo!

Post by **TheWitness** » Thu Apr 16, 2020 4:30 pm

So, the column naming issue has been addressed. The zero date thing we have addressed by setting a sepecific SQL_MODE to allow it, but at some point we should pick a better zero date which is like Dec-31-1970 or something. Oracle is just being a purest here. For other unnamed add-ons, this zero date things becomes a heavy lift though.

freecris · Post by **freecris** » Thu Dec 17, 2020 7:39 am

Hi,

So, the column naming issue has been addressed. The zero date thing we have addressed by setting a sepecific SQL_MODE to allow it

Since which release did you addressed these issues?
Will be a sql script to alter table to correct previous installed version?

thank you

Post by **TheWitness** » Thu Dec 17, 2020 8:19 pm

Just use the latest. 1.2.16.

Cacti

Cacti and MySQL 8

Cacti and MySQL 8

Re: Cacti and MySQL 8

Re: Cacti and MySQL 8

Re: Cacti and MySQL 8

Re: Cacti and MySQL 8

Re: Cacti and MySQL 8

Re: Cacti and MySQL 8

Re: Cacti and MySQL 8

Who is online