Troubleshooting
Problem
RTM queries will cause MySQL errors in cacti.log when sql_mode = STRICT_TRANS_TABLES, ...
Symptom
After migrating rtm db to a remote host
Repeated SQL errors in cacti.log like
ERROR: A database operation failed! Error:'1264' ...
ERROR: A database operation failed! Error:'1364' ...
ERROR: A database operation failed! Error:'1366' ...
Cause
When sql_mode = STRICT_TRANS_TABLES, some RTM queries will fail the strict checking.
Diagnosing The Problem
Check if sql_mode has "STRICT_TRANS_TABLES" turned on.
mysql> show variables like 'sql_mode%';
+---------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------+
Resolving The Problem
To change the SQL mode at runtime to remove STRICT_TRANS_TABLES
mysql> SET GLOBAL sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Remove STRICT_TRANS_TABLES from sql_mode in my.cnf, so when MySQL restarts next time, it will also take the new value.
Related Information
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
isg3T1027621