Announcement

Announcement Module
Collapse
No announcement yet.

Cannot disable general query log

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Cannot disable general query log

    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.

  • #2
    Could you include your my.cnf in your text?

    Comment


    • #3
      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

      Comment


      • #4
        In the meantime, Ive set up a cron job to keep the log file from growing too large


        #!/bin/sh
        cd /data/mysql
        mv 0 0.old -f
        mysqladmin flush-logs -u root -p ***
        rm 0.old


        But as you can see, its not a nice solution )

        Comment


        • #5
          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

          Comment


          • #6
            csmiesing wrote


            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.

            csmiesing wrote


            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?

            csmiesing wrote


            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 |
            +---------------------------------+------------+

            Comment


            • #7
              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

              Comment


              • #8
                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 )

                Comment

                Working...
                X