Mysql – Very Large Log Files

logsMySQLwindows

I'm running Windows Server 2008 R2 and executed a large query by accident. Next, the sever ran out of hard drive space.

I now have a file in my administrtor documents (log.log) that is over 18GB. I can't seem to delete the file as it just appears again. Any help.

When I delete the log it says I don't have administrator privilages. (I do)

Best Answer

MySQL is a big pain in the neck in Windows because of the way open files behave.

In Linux, whenever I see a log file filling up a disk (i.e., /var/log/mysqld.log), I attempt to truncate the file

echo -n > /var/log/mysqld.log

Linux and mysqld has no problem with it. Windows, on the other hand, does.

I tried to do this in MySQL 5.5.12 on my desktop, that is, zapping the error file and I got this message:

C:\>copy con C:\MySQL_5.5.12\data\LW-REDWARDS2.err
^Z
The process cannot access the file because it is being used by another process.
        0 file(s) copied.

C:\>

You must go to the Task Manager and kill the mysqld process.

Then, with the lock of the file handle released, you could attempt a zap of the log file.

Then, restart mysql with

C:\> net start mysql

You will have to allow time for mysqld to perform InnoDB Crash Recovery

UPDATE 2012-04-25 17:37 EDT

Diskspace is needed to write shutdown information into the mysql error log. During any mysql operation that produces temp tables (which are always MyISAM), diskspace is always needed. What happens if there is no diskspace for temp tables?

According to MySQL 5.0 Certification Study Guide Page 408,409 Section 29.2 bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no error occurs. The server suspends the operation until space becomes available, and then completes the operation.

Trying a standard shutdown of mysqld with no available diskspace complicates the problem. Just kill the process, delete the 18G log file, start mysql back up again, and live with InnoDB crash recovery. Trust me, it is better than looking for alternatives in a Windows environment which will not work because of the same disk issue.

Related Question