Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a problem with MySQL on FreeBSD.

It makes a lot of 1GB binary files every 20 minutes and it's filling free disk space rapidly.

-rw-rw----  1 mysql mysql 1073741982 2013-09-14 18:26 mysql-bin.000174
-rw-rw----  1 mysql mysql 1073741890 2013-09-14 18:42 mysql-bin.000175
-rw-rw----  1 mysql mysql 1073741988 2013-09-14 18:59 mysql-bin.000176
-rw-rw----  1 mysql mysql 1073741964 2013-09-14 19:15 mysql-bin.000177
-rw-rw----  1 mysql mysql 1073741974 2013-09-14 19:29 mysql-bin.000178
-rw-rw----  1 mysql mysql 1073741923 2013-09-14 19:46 mysql-bin.000179
-rw-rw----  1 mysql mysql  577143852 2013-09-14 19:59 mysql-bin.000180

It happens without any internet activity from users. I have max_binlog_file=526870912 in my.cnf

I tried to do

freebsd# mysql -u root -p
mysql> flush logs;
mysql> flush master;

All mysql-bin files is deleted as a result and counting starts from mysql-bin.000001.

I shutdown -r now my FreeBSD then (mysql_enable="YES" in rc.conf). MySQL works but starts to make 1GB bin files every 20 minutes again.

APPENDIX 1

Mysql works for aimsniff and netams. I've stoped netams. And mysql stoped to produce "mysql-bin." files. So the problem is between mysql and netams presumably. But I can't find the reason yet.

There's some lines of "mysqlbinlog mysql-bin.000179 | less" command when I try to read the mysql-bin. file which was created before the current one.

'fw-sher1# mysqlbinlog mysql-bin.000179 | less
'/*!40019 SET @@session.max_insert_delayed_threads=0*/;
'/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 'DELIMITER /!/;
'# at 4
'#130915 11:27:20 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.81-log created 130915 11:27:20
'Warning: ignoring Append_block as there is no Create_file event for file_id: 1417
'Warning: ignoring Append_block as there is no Create_file event for file_id: 1417
'Warning: ignoring Append_block as there is no Create_file event for file_id: 1417
'Warning: ignoring Append_block as there is no Create_file event for file_id: 1417
'# at 98
'#130915 11:27:19 server id 1 end_log_pos 8313
'#Append_block: file_id: 1417 block_len: 8192
'# at 8313
'#130915 11:27:19 server id 1 end_log_pos 16528
'#Append_block: file_id: 1417 block_len: 8192
'# at 16528
'#130915 11:27:19 server id 1 end_log_pos 24743
'#Append_block: file_id: 1417 block_len: 8192
'# at 24743
'#130915 11:27:19 server id 1 end_log_pos 32958
'#Append_block: file_id: 1417 block_len: 8192
'# at 32958
'#130915 11:27:19 server id 1 end_log_pos 41173
'#Append_block: file_id: 1417 block_len: 8192
'# at 41173
'#130915 11:27:19 server id 1 end_log_pos 82248
'#Append_block:Warning: ignoring Append_block as there is no Create_file event for file_id: 1417
'Warning: ignoring Append_block as there is no Create_file event for file_id: 1417
'Warning: ignoring Append_block as there is no Create_file event for file_id: 1417

And so on an on and on. It doesn't make any sense for me.

What says netams.log

File: netams.log Line 1006126 Col 0 180576689 bytes 84%
13.09.2013 23:59:52.8916 data-source:1 [WARN]: Got the following ERRORS from MySQL: level = Error, code = 126, message = Incorrect key file for table './netams/monitor.MYI'; try to repair it.
13.09.2013 23:59:52.8916 data-source:1 [WARN]: Got the following ERRORS from MySQL: level = Error, code = 126, message = Incorrect key file for table './netams/monitor.MYI'; try to repair it.
13.09.2013 23:59:52.8919 data-source:1 [WARN]: Failed load data into sql for monitor try "debug storage"
14.09.2013 00:01:20.0461 data-source:1 [WARN]: SQL Load data: Incorrect key file for table './netams/monitor.MYI'; try to repair it
14.09.2013 00:01:20.0464 data-source:1 [WARN]: Got the following WARNINGS from MySQL: level = Warning, code = 1262, message = Row 243 was truncated; it contained more data than there were input columns.
14.09.2013 00:01:20.0464 data-source:1 [WARN]: Got the following WARNINGS from MySQL: level = Warning, code = 1262, message = Row 265 was truncated; it contained more data than there were input columns.

share|improve this question
1  
What software is running against the DB? Do you know what those log files are? –  Chris S Sep 14 '13 at 19:33
2  
Obvious question, but, have you already try to check what's there actually in those log files in oder to find what's generating them? –  dawud Sep 14 '13 at 19:33
    
where checking what's in the logs can be accomplished using mysqlbinlog mysql-bin.000179 | less –  the-wabbit Sep 14 '13 at 22:34
    
Looks like database corruption in the netams tables? Have you tried repairing those tables? –  Stefan Lasiewski Sep 15 '13 at 15:48
    
found that it was all about netams. I've stoped netams. And mysql stoped to produce "mysql-bin." files. So the problem is between mysql and netams presumably. netams.log says things like that: 13.09.2013 23:59:52.8919 data-source:1 [WARN]: Failed load data into sql for monitor try "debug storage" 14.09.2013 00:01:20.0461 data-source:1 [WARN]: SQL Load data: Incorrect key file for table './netams/monitor.MYI'; try to repair it. How can I fix it? –  Mako Sep 15 '13 at 16:00

1 Answer 1

14.09.2013 00:01:20.0461 data-source:1 [WARN]: SQL Load data: Incorrect key file for table './netams/monitor.MYI'; try to repair it
14.09.2013 00:01:20.0464 data-source:1 [WARN]: Got the following WARNINGS from MySQL: level = Warning, code = 1262, message = Row 243 was truncated; it contained more data than there were input columns.
14.09.2013 00:01:20.0464 data-source:1 [WARN]: Got the following WARNINGS from MySQL: level = Warning, code = 1262, message = Row 265 was truncated; it contained more data than there were input columns.

This error indicates that this the netams database has some corrupt tables. Note the section which says "Incorrect key file for table './netams/monitor.MYI'; try to repair it".

Repairing tables can usually be done with a few commands. Repairing a table can occasionally lead to data loss, so back up the tables first. The repair can take a while depending on the size of the database-- sometimes the repair may appear to hang for 10-20 minutes, but it is actually quietly scanning for corrupt rows. Do this in a secondary window so you can keep an eye on it, and be patient.

For tips on repairing tables, check out the MySQL manual:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.