Cannot disable general query log

[carpii]

Ive had a problem with mysql for some time now.

When it starts, it writes the general query log to a file called ‘0’ in my mysql data directory. My server is VERY active, so this log file grows massive in a very short time.

Some time ago, I simply deleted the log file while mysql was running. It didnt recreate it so I thought the problem was solved.

That was a bit naive anyway, because linux continued to think the file was in use, and tonight started complaining about out of disk space errors. I restarted mysql and the disk space dropped back down the accurate value of 12%.

My real problem is that I cant seem to disable mysql writing to this 0 query log file.

Im running version
Ver 5.0.22-log for redhat-linux-gnu on i686 (Source distribution)

Does the “-log” indicate that the log is hardcoded? surely not

The MySQL manual claims general query log is off by default, and can be turned on by a command line parameter.

“To enable the general query log as of MySQL 5.1.6, start mysqld with the --log[=file_name] or -l [file_name] option, and optionally use --log-output to specify the log destination”

I dont pass either of these, and the equivalent values arent in my.cnf

Does anyone have any ideas please? Im stumped.

[csmiesing]

Could you include your my.cnf in your text?

[carpii]

Hi, here we go…

Mysql version = 5.0.22-log

---

Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## You can copy this file to# /etc/my.cnf to set global options,# mysql-data-dir/my.cnf to set server-specific options (in this# installation this directory is /var/lib/mysql) or# ~/.my.cnf to set user-specific options.## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the “–help” option.# The following options will be passed to all MySQL clients[client][mysqld]innodb# 05/05/2008.PAC - Configure query cachequery-cache-type = 1query-cache-size = 20Mkey_buffer = 30M# 25/02/2006.PAC - no logging#log-bin=mysql-binskip-log# >># 2008/05/28.PAC - Moved tmp dir onto sdb1 - /backup/mysqltmptmpdir = /tmp/# 25/02/2006.PAC moved data dir onto 400G disk# 2008/05/28.PAC - data is back on sda1 due to server movedatadir = /data/mysql/max_connections=400max_connect_errors = 999999999skip-name-resolveskip-lockinginnodb_data_home_dir = /data/mysqlinnodb_data_file_path = ibdata1:5000M;ibdata2:10M:autoextend# 05/05/2008.PAC - Upped from 384M to 1000Minnodb_buffer_pool_size = 1000Minnodb_additional_mem_pool_size = 20M[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[isamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout

[csmiesing]

are there no other my.cnf files used?
I don’t see any server id’s for example and this should be a slave, isn’t it?

use the command

show variables in mysql and look for the log setting
it should be off

[carpii]

[B]csmiesing wrote[/B]

are there no other my.cnf files used?

No, thats what it appears like. But I checked mt init scripts, and even the cmdline in ps output, and /etc/my.cnf is the only one being used.

[B]csmiesing wrote[/B]

I don’t see any server id’s for example and this should be a slave, isn’t it?

Hm well, I dont use replication, its a standalone server. Is it still relevant?

[B]csmiesing wrote[/B]

use the command show variables in mysql and look for the log setting it should be off

Log is “ON”. Its like this as soon as mysqld starts, yet theres nothing I can find which would explain this.

mysql> show variables like ‘%log%’;
±--------------------------------±-----------+
| Variable_name | Value |
±--------------------------------±-----------+
| back_log | 50 |
| bdb_log_buffer_size | 32768 |
| bdb_logdir | |
| binlog_cache_size | 32768 |
| expire_logs_days | 0 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | ON |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| sync_binlog | 0 |
±--------------------------------±-----------+

[csmiesing]

in your my.cnf settingsfile is a line

skip-log

that command doesn’t exist and when i use it in my.cnf file it does turn the logging on
so delete this line and restart your mysql server

[carpii]

Wow, thankyou so much!

I have no idea how to explain that. I added the config line myself a long time ago, and even commented it (so I didnt even consider the possibility of that being at fault).

I guess a fresh pair of eyes always helps, thanks again )