MariaDB: mysqld.log has grown to 16GB and is not archiving

[tooliedotter]

Platform

AWS

bndiagnostic ID know more about bndiagnostic ID

9555ca43-7080-bfe0-4a73-9ce0a9b8c46d

bndiagnostic output

===== Begin of bndiagnostic tool output =====

? Resources: Found possible issues
? Connectivity: Found possible issues
? Mariadb: Found possible issues
✓ Processes: No issues found
✓ Apache: No issues found
✓ Php: No issues found

[Resources]

Your disk is almost full: You could try to increase your instance's storage.

Filesystem Size Used Avail Use% Mounted on /dev/nvme0n1p1 30G 28G 1.1G 97% /

Please check your cloud provider's documentation for more information.

Your instance has little available RAM memory.

Press [Enter] to continue:
 total used free shared buff/cache available Mem: 3885 1533 529 108 1823 1969
Swap: 0 0 0

You could try to increase your instance's memory. Please check your cloud
provider's documentation for more information.

You can also enable swap memory to improve performance.

https://docs.bitnami.com/installer/faq/linux-faq/administration/increase-m
emory-linux/

[Connectivity]

Server ports 22, 80 and/or 443 are not publicly accessible. Please check the
following guide to open server ports for remote access:

https://docs.bitnami.com/general/faq/administration/use-firewall/

[Mariadb]

Found recent error messages in the MariaDB error log:

2024-03-19 13:49:14 1503 [ERROR] Incorrect definition of table
Press [Enter] to continue:
mysql.column_stats: expected column 'histogram' at position 10 to have type
longblob, found type varbinary(255).

Please check the following guide to troubleshoot MariaDB issues:

https://docs.bitnami.com/aws/apps/wordpress/troubleshooting/debug-errors-m
ariadb/

bndiagnostic was not useful. Could you please tell us why?

I am already aware that the disk is nearly full; the huge MariaDB Log file is why!

Describe your issue as much as you can

The mysqld.log file has grown to 16.5GB in size; it appears to have stopped archiving each week. The most recent log archive date is 5 weeks ago. In order to do anything I had to delete some files, but that doesn't stop the MariaDB log file from continuing to grow. I was planning to upgrade my server next month, but what do I do about this out-of-control log? How can I get the automatic archiving/purge cycle restarted?

[mdhont]

You could disable binary logging by adding skip-log-bin in the mariadb.cnf file under [mysqld] directive.

[tooliedotter]

That's great, I'm happy to add that directive. Two questions:

1. Where is mariadb.cnf located? I'm not finding it. Here's what I did find:

$ sudo find . -name mariadb.*
./etc/monit/conf.d/mariadb.conf
./opt/bitnami/bndiagnostic/original-data/permissions/mariadb.permissions
./opt/bitnami/mariadb/lib/pkgconfig/mariadb.pc
./opt/bitnami/mariadb/share/policy/selinux/mariadb.te
./opt/bitnami/mariadb/share/mariadb.logrotate
./opt/bitnami/mariadb/include/mysql/server/private/mariadb.h

2. How do I get the archiving of log entries restarted? It's been 5 weeks since a segment of log entries were archived by whatever process does that work. My guess is that the currently huge log file will not shrink without intervention. What do I do?

[mdhont]

Apologies, the configuration file should be /opt/bitnami/mariadb/conf/my.cnf

To enable archiving of the log entries I recommend following the official guide:

https://mariadb.com/kb/en/general-query-log/
https://mariadb.com/kb/en/purge-binary-logs/

[tooliedotter]

Thanks Michiel, I found the config file and made the suggested change.

Now when I run the SHOW BINARY LOGS command, I get this:

MariaDB [(none)]> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
MariaDB [(none)]>

which seems appropriate. However, running the PURGE BINARY LOGS file gets me this error:

MariaDB [(none)]> PURGE BINARY LOGS BEFORE '2023-12-31 11:59:59';
Error: #1046 No database selected

and I still have 17GB mysqld.log file that I need to empty or reduce. What do I do now?

[tooliedotter]

The log filled up the hard drive again, so I stopped MariaDB, downloaded the log and manually removed 47,401,349 lines. The error seemed to be an endless repetition of the following:

2024-02-11  8:01:05 210172 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2024-02-11  8:01:05 210172 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

1. Any suggestions as to how to stop this error?
2. And I still need to know how to reactivate the archiving of the log contents. Neither of the links you provided answers that question.

[tooliedotter]

OK question number 1 in #1457 (comment) is solved with these two commands which were affirmed in several support websites as correcting the error. They IMMEDIATELY worked for me.

ALTER TABLE mysql.column_stats MODIFY histogram longblob;
ALTER TABLE mysql.column_stats MODIFY hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB');

I'll await your answer to question number 2 in the response above.

[jotamartos]

Hi @tooliedotter

And I still need to know how to reactivate the archiving of the log contents. Neither of the links you provided answers that question.

You deactivated the binary logging as posted above. The error you got when running the "PURGE" command is that you didn't select any database. You can use the following to purge the binary logging

use DATABASE_NAME;
PURGE BINARY LOGS BEFORE '2024-03-01 12:00:00';

Regarding the errors in the log, you have different options.

• You can deactivate the error logging in the MySQL's configuration. You will need to comment out the error_log line and restart the server
• You can also change the logrotate configuration, to remove the file when the size is bigger than a specific value, or configure it to only keep the last 1 or 2 files for future reference. You can learn more about logrotate here

https://docs.bitnami.com/general/apps/joomla/administration/configure-use-logrotate/
https://www.digitalocean.com/community/tutorials/how-to-manage-logfiles-with-logrotate-on-ubuntu-16-04

[tooliedotter]

Hi Juan,

• Thanks for the information on logrotate, I'll try to reestablish the prior configuration. Any ideas as to why it would stop rotating the logs? I'm assuming that the AMI would have had it set up from the start.
• You might not have noticed that the database in question was table mysql.column_stats, the MariaDB operational database. I was not going to risk using a PURGE command in case it blew everything up, all 22 websites on my server.
• I downloaded the contents of the mysqld.log file (18GB at the time) and looked inside using EMEditor. I discovered that it was the same 2 error messages 44 million times each (literally). The two commands above fixed the error.

[mdhont]

Hi @tooliedotter,

Thanks for the information on logrotate, I'll try to reestablish the prior configuration. Any ideas as to why it would stop rotating the logs? I'm assuming that the AMI would have had it set up from the start.

If the log files have grown too large, logrotate may not rotate them because of size restrictions. Another cause could be the disk space available.

[tooliedotter]

Thanks for the explanation Michiel. It's counter-intuitive that the logrotate would stop rotating at the time it most needs to function.

I just checked the /opt/bitnami/mariadb/logs folder, and it appears that the log rotation has resumed. Thanks for your help, we can close this thread.