search

Cacti / plugin_cycle

GNU General Public License v2.0
4 stars 3 forks source link

Error: Illegal mix of collations #18

Closed jdcoats closed 4 years ago

jdcoats commented 4 years ago 

2020/04/06 20:10:56 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
--
2020/04/06 20:10:56 - CMDPHP SQL Backtrace: (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/06 20:10:56 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/06 20:10:56 - CMDPHP SQL Backtrace: (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/06 20:10:56 - CMDPHP SQL Backtrace: (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
jdcoats commented 4 years ago

this almost breaks cacti completely. Cant get to tech support page either. I know the recommendations reference this for languages other than english but it shouldn't be a requirement.

netniV commented 4 years ago

utf8mb4_unicode became a requirement to properly handle foreign characters issued by both end users and devices. Without it, characters could become improperly handled, display and stored. It is a documented pre-requisite of Cacti since 1.2.0

However, because it can take time to convert a large number of tables, we do not force this change, but give the operator an option to do it during upgrade. After upgrade, the command line program should be used.

Much like the requirements to use barracuda, innodb and large prefixes have all become requirements under 1.2.x due to the number of tables, key sizes etc.

jdcoats commented 4 years ago

if you install cycle plugin it breaks cacti completely, had to rm -rf the dir to regain access to it.

jdcoats commented 4 years ago

its a bug that sucks bad

jdcoats commented 4 years ago

the only thing that i do not have set is collation server.


version | 10.3.22-MariaDB-log | >= | 5.6 | MySQL 5.6+ and MariaDB 10.0+ are great releases, and are very good versions to choose. Make sure you run the very latest release though which fixes a long standing low level networking issue that was causing spine many issues with reliability.
-- | -- | -- | -- | --
collation_server | utf8mb4_general_ci | = | utf8mb4_unicode_ci | When using Cacti with languages other than English, it is important to use the utf8mb4_unicode_ci collation type as some characters take more than a single byte.
character_set_client | utf8mb4 | = | utf8mb4 | When using Cacti with languages other than English, it is important to use the utf8mb4 character set as some characters take more than a single byte.
jdcoats commented 4 years ago

it breaks all spine polling, i cant troubleshoot when its installed. no poller running and no graphs created.

jdcoats commented 4 years ago

I had to remove the plugin all together to get the system back in control. I will look at ti again tomorrow, but when installed it breaks things badly.

TheWitness commented 4 years ago

There is a convert_table.php script in the cli directory that gets most of it, but I don't think it sets the database collation and charset for new tables. It should do that maybe. I know the installer warns, but maybe not on and upgrade? I thought it always did.

netniV commented 4 years ago

I was of the opinion that the installer always warned about tables. As for server collation, that should be on the MySQL requirements section.

jdcoats commented 4 years ago

The warnings start by saying "When using Cacti with languages other than English" which indicates that people using English can ignore.

jdcoats commented 4 years ago

The errors exist even when collation is set as recommended. This plugin takes down the system when installed and enabled.

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | cacti              | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | mysql              | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | syslog             | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
# tail -n 100 /var/log/cacti.log
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[50]:cycle_graphs(), /plugins/cycle/cycle.php[88]:get_next_graphid(), /plugins/cycle/functions.php[295]:db_fetch_assoc(), /lib/database.php[473]:db_fetch_assoc_prepared(), /lib/database.php[487]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Row Failed!, Error: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'regexp'
2020/04/07 09:59:00 - ERROR PHP ERROR: Maximum execution time of 300 seconds exceeded in file: /var/www/localhost/htdocs/cacti/lib/functions.php on line: 960
2020/04/07 09:59:00 - CMDPHP PHP ERROR Backtrace:  (CactiShutdownHandler())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Cell Failed!, Error: PDOStatement::execute(): send of 37 bytes failed with errno=32 Broken pipe
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[29]:include_once(), /include/auth.php[28]:require_once(), /include/global.php[502]:include_once(), /include/global_languages.php[64]:read_user_i18n_setting(), /include/global_languages.php[668]:db_table_exists(), /lib/database.php[768]:db_fetch_cell(), /lib/database.php[383]:db_fetch_cell_prepared(), /lib/database.php[399]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Cell Failed!, Error: MySQL server has gone away
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[29]:include_once(), /include/auth.php[28]:require_once(), /include/global.php[508]:include_once(), /include/global_arrays.php[971]:db_table_exists(), /lib/database.php[768]:db_fetch_cell(), /lib/database.php[383]:db_fetch_cell_prepared(), /lib/database.php[399]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Cell Failed!, Error: MySQL server has gone away
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[29]:include_once(), /include/auth.php[28]:require_once(), /include/global.php[508]:include_once(), /include/global_arrays.php[1482]:is_realm_allowed(), /lib/auth.php[711]:is_user_perms_valid(), /lib/auth.php[2669]:db_fetch_cell_prepared(), /lib/database.php[399]:db_execute_prepared())
2020/04/07 09:59:00 - CMDPHP ERROR: A DB Cell Failed!, Error: MySQL server has gone away
2020/04/07 09:59:00 - CMDPHP SQL Backtrace:  (/plugins/cycle/cycle.php[29]:include_once(), /include/auth.php[28]:require_once(), /include/global.php[508]:include_once(), /include/global_arrays.php[1482]:is_realm_allowed(), /lib/auth.php[712]:db_table_exists(), /lib/database.php[768]:db_fetch_cell(), /lib/database.php[383]:db_fetch_cell_prepared(), /lib/database.php[399]:db_execute_prepared())
TheWitness commented 4 years ago

Yea, likely a bad plan. Follow instructions here: https://mediatemple.net/community/products/dv/204403914/default-mysql-character-set-and-collation

If Super is needed to change the defaults for the Database, there is no way we can do this in the upgrade script. It would have to be done by hand. I have not checked that.

Here are the lines of interest.

  1. Log into MySQL with SSH:
mysql -u admin -p`cat /etc/psa/.psa.shadow`

  1. Enter your database password when prompted.

  2. Run the following command to change the character set and collation of your database:

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
  1. Run the following command to change the character set and collation of your table:
ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_general_ci;

For either of these examples, please replace the example character set and collation with your desired values.

netniV commented 4 years ago

I don't think step 2 would occur if password is in the file listed as part of step 1.

Also, I believe we are using unicode not general?

netniV commented 4 years ago

Yeah, unicode supports the full sorting methods where general circumvents some of this.

jdcoats commented 4 years ago

mine were general and I changed them to unicode but the results were the same.

jdcoats commented 4 years ago

on another note, I set them from the cli 1st 

SET collation_server = 'utf8mb4_unicode_ci';
ALTER DATABASE cacti COLLATE = 'utf8mb4_unicode_ci';

but cacti tech support page checks my.cnf ? so i added it there , just so the tech support page was happy.

netniV commented 4 years ago

I am pretty sure that the tech support page doesn't look directly at configuration files, it does a SHOW VARIABLE statement for items it wasn't to know about. Strange it would not see the change if it was done.

jdcoats commented 4 years ago

the SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; showed as it does above but the tech support page wasn't happy until i added it to my.cnf. I didn't understand that either.

netniV commented 4 years ago

Maybe MySQL picked up the change and updated its variables. I'd have to look at the sources to be sure which I'm not in front of right now.

TheWitness commented 4 years ago

Make sure you run the convert_tables.php again after you change to unicode. I guess I should have paid more attention when I read the article. It was more the method that I wanted to catch and not the actual type. For mine it was this:

SHOW CREATE DATABASE cacti;
<snip>
CREATE DATABASE `cacti` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
<snip>
jdcoats commented 4 years ago 
MariaDB [(none)]> SHOW CREATE DATABASE cacti;
+----------+----------------------------------------------------------------------------------------------+
| Database | Create Database                                                                              |
+----------+----------------------------------------------------------------------------------------------+
| cacti    | CREATE DATABASE `cacti` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+----------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
TheWitness commented 4 years ago

So good so far. You can try this (bash):

for table in `mysql -e "show tables" cacti | grep -v Table`;do mysql -e "ALTER TABLE $table CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC" cacti;done
jdcoats commented 4 years ago

It converted audit_log and processes

# php convert_tables.php -u
Converting Database Tables to  utf8 with less than '1000000' Records
Skipping Table -> 'aggregate_graph_templates'
Skipping Table -> 'aggregate_graph_templates_graph'
Skipping Table -> 'aggregate_graph_templates_item'
Skipping Table -> 'aggregate_graphs'
Skipping Table -> 'aggregate_graphs_graph_item'
Skipping Table -> 'aggregate_graphs_items'
Converting Table -> 'audit_log' Successful
Skipping Table -> 'automation_devices'
Skipping Table -> 'automation_graph_rule_items'
Skipping Table -> 'automation_graph_rules'
Skipping Table -> 'automation_ips'
Skipping Table -> 'automation_match_rule_items'
Skipping Table -> 'automation_networks'
Skipping Table -> 'automation_processes'
Skipping Table -> 'automation_snmp'
Skipping Table -> 'automation_snmp_items'
Skipping Table -> 'automation_templates'
Skipping Table -> 'automation_tree_rule_items'
Skipping Table -> 'automation_tree_rules'
Skipping Table -> 'cdef'
Skipping Table -> 'cdef_items'
Skipping Table -> 'color_template_items'
Skipping Table -> 'color_templates'
Skipping Table -> 'colors'
Skipping Table -> 'data_debug'
Skipping Table -> 'data_input'
Skipping Table -> 'data_input_data'
Skipping Table -> 'data_input_fields'
Skipping Table -> 'data_local'
Skipping Table -> 'data_source_profiles'
Skipping Table -> 'data_source_profiles_cf'
Skipping Table -> 'data_source_profiles_rra'
Skipping Table -> 'data_source_purge_action'
Skipping Table -> 'data_source_purge_temp'
Skipping Table -> 'data_source_stats_daily'
Skipping Table -> 'data_source_stats_hourly'
Skipping Table -> 'data_source_stats_hourly_cache'
Skipping Table -> 'data_source_stats_hourly_last'
Skipping Table -> 'data_source_stats_monthly'
Skipping Table -> 'data_source_stats_weekly'
Skipping Table -> 'data_source_stats_yearly'
Skipping Table -> 'data_template'
Skipping Table -> 'data_template_data'
Skipping Table -> 'data_template_rrd'
Skipping Table -> 'external_links'
Skipping Table -> 'graph_exports'
Skipping Table -> 'graph_exports_tasks'
Skipping Table -> 'graph_local'
Skipping Table -> 'graph_template_input'
Skipping Table -> 'graph_template_input_defs'
Skipping Table -> 'graph_templates'
Skipping Table -> 'graph_templates_gprint'
Skipping Table -> 'graph_templates_graph'
Skipping Table -> 'graph_templates_item'
Skipping Table -> 'graph_tree'
Skipping Table -> 'graph_tree_items'
Skipping Table -> 'host'
Skipping Table -> 'host_graph'
Skipping Table -> 'host_snmp_cache'
Skipping Table -> 'host_snmp_query'
Skipping Table -> 'host_template'
Skipping Table -> 'host_template_graph'
Skipping Table -> 'host_template_snmp_query'
Skipping Table -> 'mac_track_aggregated_ports'
Skipping Table -> 'mac_track_approved_macs'
Skipping Table -> 'mac_track_device_types'
Skipping Table -> 'mac_track_devices'
Skipping Table -> 'mac_track_dot1x'
Skipping Table -> 'mac_track_interface_graphs'
Skipping Table -> 'mac_track_interfaces'
Skipping Table -> 'mac_track_ip_ranges'
Skipping Table -> 'mac_track_ips'
Skipping Table -> 'mac_track_macauth'
Skipping Table -> 'mac_track_macwatch'
Skipping Table -> 'mac_track_oui_database'
Skipping Table -> 'mac_track_ports'
Skipping Table -> 'mac_track_processes'
Skipping Table -> 'mac_track_scan_dates'
Skipping Table -> 'mac_track_scanning_functions'
Skipping Table -> 'mac_track_sites'
Skipping Table -> 'mac_track_snmp'
Skipping Table -> 'mac_track_snmp_items'
Skipping Table -> 'mac_track_temp_ports'
Skipping Table -> 'mac_track_vlans'
Skipping Table -> 'plugin_config'
Skipping Table -> 'plugin_db_changes'
Skipping Table -> 'plugin_hmib_hrDevices'
Skipping Table -> 'plugin_hmib_hrProcessor'
Skipping Table -> 'plugin_hmib_hrSWInstalled'
Skipping Table -> 'plugin_hmib_hrSWRun'
Skipping Table -> 'plugin_hmib_hrSWRun_ignore'
Skipping Table -> 'plugin_hmib_hrSWRun_last_seen'
Skipping Table -> 'plugin_hmib_hrStorage'
Skipping Table -> 'plugin_hmib_hrSystem'
Skipping Table -> 'plugin_hmib_hrSystemTypes'
Skipping Table -> 'plugin_hmib_processes'
Skipping Table -> 'plugin_hmib_types'
Skipping Table -> 'plugin_hooks'
Skipping Table -> 'plugin_monitor_dashboards'
Skipping Table -> 'plugin_monitor_notify_history'
Skipping Table -> 'plugin_monitor_reboot_history'
Skipping Table -> 'plugin_monitor_uptime'
Skipping Table -> 'plugin_notification_lists'
Skipping Table -> 'plugin_realms'
Skipping Table -> 'plugin_routerconfigs_accounts'
Skipping Table -> 'plugin_routerconfigs_backups'
Skipping Table -> 'plugin_routerconfigs_devices'
Skipping Table -> 'plugin_routerconfigs_devicetypes'
Skipping Table -> 'plugin_thold_contacts'
Skipping Table -> 'plugin_thold_daemon_data'
Skipping Table -> 'plugin_thold_daemon_processes'
Skipping Table -> 'plugin_thold_host_failed'
Skipping Table -> 'plugin_thold_host_template'
Skipping Table -> 'plugin_thold_log'
Skipping Table -> 'plugin_thold_template_contact'
Skipping Table -> 'plugin_thold_threshold_contact'
Skipping Table -> 'poller'
Skipping Table -> 'poller_command'
Skipping Table -> 'poller_data_template_field_mappings'
Skipping Table -> 'poller_item'
Skipping Table -> 'poller_output'
Skipping Table -> 'poller_output_boost'
Skipping Table -> 'poller_output_boost_processes'
Skipping Table -> 'poller_output_realtime'
Skipping Table -> 'poller_reindex'
Skipping Table -> 'poller_resource_cache'
Skipping Table -> 'poller_time'
Converting Table -> 'processes' Successful
Skipping Table -> 'reports'
Skipping Table -> 'reports_items'
Skipping Table -> 'sessions'
Skipping Table -> 'settings'
Skipping Table -> 'settings_tree'
Skipping Table -> 'settings_user'
Skipping Table -> 'settings_user_group'
Skipping Table -> 'sites'
Skipping Table -> 'snmp_query'
Skipping Table -> 'snmp_query_graph'
Skipping Table -> 'snmp_query_graph_rrd'
Skipping Table -> 'snmp_query_graph_rrd_sv'
Skipping Table -> 'snmp_query_graph_sv'
Skipping Table -> 'snmpagent_cache'
Skipping Table -> 'snmpagent_cache_notifications'
Skipping Table -> 'snmpagent_cache_textual_conventions'
Skipping Table -> 'snmpagent_managers'
Skipping Table -> 'snmpagent_managers_notifications'
Skipping Table -> 'snmpagent_mibs'
Skipping Table -> 'snmpagent_notifications_log'
Skipping Table -> 'thold_data'
Skipping Table -> 'thold_template'
Skipping Table -> 'user_auth'
Skipping Table -> 'user_auth_cache'
Skipping Table -> 'user_auth_group'
Skipping Table -> 'user_auth_group_members'
Skipping Table -> 'user_auth_group_perms'
Skipping Table -> 'user_auth_group_realm'
Skipping Table -> 'user_auth_perms'
Skipping Table -> 'user_auth_realm'
Skipping Table -> 'user_domains'
Skipping Table -> 'user_domains_ldap'
Skipping Table -> 'user_log'
Skipping Table -> 'vdef'
Skipping Table -> 'vdef_items'
Skipping Table -> 'version'
Skipping Table -> 'weathermap_auth'
Skipping Table -> 'weathermap_data'
Skipping Table -> 'weathermap_groups'
Skipping Table -> 'weathermap_maps'
Skipping Table -> 'weathermap_settings'
jdcoats commented 4 years ago

So good so far. You can try this (bash):

for table in `mysql -e "show tables" cacti | grep -v Table`;do mysql -e "ALTER TABLE $table CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC" cacti;done

Just confirming that this cleared the issue up for me. Thanks!

netniV commented 4 years ago

Nice little one liner there. that's the kind of thing I do most days at work and then people stare at it, because it looks unusual to them 👍