When checking my logs after I heard that /opt/zextras/log/nginx_clamav_signature_provider_access.log never rotates I found worrying errors in /opt/zextras/log/mysql_error.log
This spring I updated PostgreSQL to 16 and I guess I got these errors after that. I followed to guide https://docs.zextras.com/carbonio-ce/html/release/other-upgrades.html#postgresql-16-upgrade but I did not do the last optional step (remove unused DB). And I am not sure the errors in mysql_error.log has anything to do with this upgrade but the first error was seen 2024-02-04. Below is when I saw the error for the first time and then how it looks today.
Today I am running 24.7.1 on 5.15.0-121-generic #131~20.04.1-Ubuntu and I want to be sure that the errors will not cause any problems updating to 24.9.1. All updates 2024 has worked well with some smaller issues.
240204 11:44:53 mysqld_safe mysqld from pid file /opt/zextras/log/mysql.pid ended
240204 11:59:37 mysqld_safe Starting mysqld daemon with databases from /opt/zextras/db/data
2024-02-04 11:59:38 0 [Note] Starting MariaDB 10.4.32-MariaDB-log source revision c4143f909528e3fab0677a28631d10389354c491 as process 5277
2024-02-04 11:59:39 0 [Note] InnoDB: Using Linux native AIO
2024-02-04 11:59:39 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2024-02-04 11:59:39 0 [Note] InnoDB: Uses event mutexes
2024-02-04 11:59:39 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-02-04 11:59:39 0 [Note] InnoDB: Number of pools: 1
2024-02-04 11:59:39 0 [Note] InnoDB: Using SSE2 crc32 instructions
2024-02-04 11:59:39 0 [Note] InnoDB: Initializing buffer pool, total size = 5G, instances = 8, chunk size = 128M
2024-02-04 11:59:39 0 [Note] InnoDB: Completed initialization of buffer pool
2024-02-04 11:59:39 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2024-02-04 11:59:43 0 [Note] InnoDB: Upgrading redo log: 2*524288000 bytes; LSN=227898588
2024-02-04 11:59:43 0 [Note] InnoDB: Starting to delete and rewrite log files.
2024-02-04 11:59:43 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 524288000 bytes
2024-02-04 11:59:43 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 524288000 bytes
2024-02-04 11:59:43 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2024-02-04 11:59:44 0 [Note] InnoDB: New log files created, LSN=227898588
2024-02-04 11:59:44 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2024-02-04 11:59:44 0 [Note] InnoDB: Creating sys_virtual system tables.
2024-02-04 11:59:44 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-02-04 11:59:44 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-02-04 11:59:44 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-02-04 11:59:44 0 [Note] InnoDB: Waiting for purge to start
2024-02-04 11:59:44 0 [Note] InnoDB: 10.4.32 started; log sequence number 227898588; transaction id 767336
2024-02-04 11:59:44 0 [Note] InnoDB: Loading buffer pool(s) from /opt/zextras/db/data/ib_buffer_pool
2024-02-04 11:59:44 0 [Note] InnoDB: Cannot open '/opt/zextras/db/data/ib_buffer_pool' for reading: No such file or directory
2024-02-04 11:59:44 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-02-04 11:59:44 0 [Note] Server socket created on IP: '127.0.0.1'.
2024-02-04 11:59:44 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU
2024-02-04 11:59:44 0 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2024-02-04 11:59:49 6 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-02-04 11:59:49 6 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-02-04 11:59:49 0 [Note] Reading of all Master_info entries succeeded
2024-02-04 11:59:49 0 [Note] Added new Master_info '' to hash table
2024-02-04 11:59:49 0 [Note] /opt/zextras/common/sbin/mysqld: ready for connections.
Version: '10.4.32-MariaDB-log' socket: '/opt/zextras/data/tmp/mysql/mysql.sock' port: 7306 Zextras MariaDB binary distribution
2024-02-04 12:00:03 9 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-02-04 12:00:03 9 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
...
241019 10:31:24 mysqld_safe mysqld from pid file /run/carbonio/mysql.pid ended
241019 10:43:03 mysqld_safe Starting mariadbd daemon with databases from /opt/zextras/db/data
2024-10-19 10:43:10 0 [Note] Starting MariaDB 10.5.25-MariaDB-log source revision 29c185bd771ac441121468b3850d6dc8d13b8a1f as process 4002
2024-10-19 10:43:11 0 [Note] InnoDB: Uses event mutexes
2024-10-19 10:43:11 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-10-19 10:43:11 0 [Note] InnoDB: Number of pools: 1
2024-10-19 10:43:11 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-10-19 10:43:11 0 [Note] InnoDB: Using Linux native AIO
2024-10-19 10:43:11 0 [Note] InnoDB: Initializing buffer pool, total size = 4966055936, chunk size = 134217728
2024-10-19 10:43:11 0 [Note] InnoDB: Completed initialization of buffer pool
2024-10-19 10:43:25 0 [Note] InnoDB: 128 rollback segments are active.
2024-10-19 10:43:26 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-10-19 10:43:26 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-10-19 10:43:26 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-10-19 10:43:26 0 [Note] InnoDB: 10.5.25 started; log sequence number 635521100; transaction id 2453199
2024-10-19 10:43:26 0 [Note] InnoDB: Loading buffer pool(s) from /opt/zextras/db/data/ib_buffer_pool
2024-10-19 10:43:26 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-10-19 10:43:27 0 [Note] Server socket created on IP: '127.0.0.1'.
2024-10-19 10:43:28 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU
2024-10-19 10:43:28 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
2024-10-19 10:43:38 1 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-10-19 10:43:38 1 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-10-19 10:43:38 0 [Note] Reading of all Master_info entries succeeded
2024-10-19 10:43:38 0 [Note] Added new Master_info '' to hash table
2024-10-19 10:43:38 0 [Note] /opt/zextras/common/sbin/mariadbd: ready for connections.
Version: '10.5.25-MariaDB-log' socket: '/run/carbonio/mysql.sock' port: 7306 Zextras MariaDB binary distribution
2024-10-19 10:43:41 0 [Note] InnoDB: Buffer pool(s) load completed at 241019 10:43:41
2024-10-19 10:44:15 6 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-10-19 10:44:15 6 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-10-19 10:44:16 6 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
2024-10-19 10:44:16 6 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
I do not dare to run mysql_upgrade or delete the old database. Should I delete the old database with:
pg_dropcluster 12 main --stop pg_dropcluster 16 main_pristine --stop
And is it safe to run mysql_update or should I do something else first?
I am grateful for any the help.
//Sigtrap
Today I removed the old databases:
pg_dropcluster 12 main --stop pg_dropcluster 16 main_pristine --stop
Looks ok.
The log still recommends to run mysql_upgrade, but the Zextras guide does not include it. I am so worried that mysql_upgrade will corrupt something. Is it only me that has these errors in /opt/zextras/log/mysql_error.log ?
2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade 2024-10-27 10:44:57 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
Would be good to hear anything from Zextras or a Carbonio admin that have seen these errors and know the safe way to fix it.
Thanks
//Sigtrap
The error messages in your first post are not related to PostgreSQL's upgrade, so running pg_dropcluster is irrelevant w.r.t. the error messages. Indeed, the two commands do:
pg_dropcluster 12 main --stop
remove the old data contained in PG v12's DB, which were migrated to v16
pg_dropcluster 16 main_pristine --stop
remove the vanilla v16 db which was renamed before the migration. None of them is necessary, you could keep a backup of the v12 just in case (using pg_dump).
In the docs you don't find mentions of Mysql because you usually don't need to touch it. I will ask whether the warning is harmless or require some interaction. If so, write directions in the docs.
In my personal experience, I found that running a mysql_update is harmless in most of the cases, because if it encounters some problem, it stops and does not touch the MySQL DB so you can investigate further. If you do not want to risk running it directly on your production DB, you can make a dump of the DB, restore it on a different machine, run mysql_upgrade there and check the result. If everything works, you can run it on the production DB.
HTH,
Stefano
Thanks. I will then try to run mysql_update. And no one else has reported that they have any errors in /opt/zextras/log/mysql_error.log .
But I am sure that for Carbonio CE backup I should backup the databases with zmslapcat and mysqldump (user data I backup with the script "cmbackup"). PostgreSQL I do not backup, e.g with pg_dump.
When upgrading Zextras only "make a backup"(?) and "backup the LDAP data" are preliminary tasks.
I am still missing a good guide how to backup and restore Carbonio CE but that question is for another thread 😉
Thanks again
//Sigtrap
New problem.
Simple question. How do I run "mysql_upgrade"?
In my Carbonio 24.7.1 on Ubuntu 20.04 mariadb is running as zextras and the only mysql_upgrade is /opt/zextras/common/bin/mysql_upgrade
# su - zextras $ mysql_upgrade Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) FATAL ERROR: Upgrade failed
Does anyone know how to safely do this?
Thanks
//Sigtrap
I found the solution, here in the forum 😐
https://community.zextras.com/forum/carbonio-general-thread/errors-in-mysql_error-log/#post-7826
I will now upgrade to 24.9.1
Thanks for the forum, and no thanks to me that somehow missed to search here. <3
//Sigtrap