2

Some time ago I updated my MySQL server to the v5.6:

SELECT VERSION();
5.6.30-0ubuntu0.14.04.1

Now the query logging isn't working though the configs in the /etc/mysql/my.cnf seem to be correct:

[mysqld]
general_log_file = /var/log/mysql/mysql.log
general_log      = 1
log              = /var/log/mysql-query.log # Added now, but it also hasn't helped.

The log files exist and are writable for the mysql user:

$ ls -lia /var/log/mysql | grep "mysql-query"
393371 -rwxrwxrwx  1 mysql adm   0 Jun  5 17:09 mysql-query.log
$ ls -lia /var/log | grep "mysql.log"
403924 -rwxrwxrwx  1 mysql adm   0 Jun  5 16:35 mysql.log

How to get MySQL logging queries again?


UPDATE

Additional information:

The error log displays some errors on (re-)starting the server.

2016-06-07 19:56:55 5861 [Note] /usr/sbin/mysqld: Normal shutdown

2016-06-07 19:56:55 5861 [Note] Giving 0 client threads a chance to die gracefully
2016-06-07 19:56:55 5861 [Note] Event Scheduler: Purging the queue. 0 events
2016-06-07 19:56:55 5861 [Note] Shutting down slave threads
2016-06-07 19:56:55 5861 [Note] Forcefully disconnecting 0 remaining clients
2016-06-07 19:56:55 5861 [Note] Binlog end
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'partition'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_METRICS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_CMP'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'INNODB_TRX'
2016-06-07 19:56:55 5861 [Note] Shutting down plugin 'InnoDB'
2016-06-07 19:56:55 5861 [Note] InnoDB: FTS optimize thread exiting.
2016-06-07 19:56:55 5861 [Note] InnoDB: Starting shutdown...
2016-06-07 19:56:57 5861 [Note] InnoDB: Shutdown completed; log sequence number 95410297
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'ARCHIVE'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'BLACKHOLE'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'MEMORY'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'MRG_MYISAM'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'MyISAM'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'CSV'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'sha256_password'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'mysql_old_password'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'mysql_native_password'
2016-06-07 19:56:57 5861 [Note] Shutting down plugin 'binlog'
2016-06-07 19:56:57 5861 [Note] /usr/sbin/mysqld: Shutdown complete

160607 19:56:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
160607 19:56:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2016-06-07 19:56:58 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-06-07 19:56:58 0 [Note] /usr/sbin/mysqld (mysqld 5.6.30-0ubuntu0.14.04.1) starting as process 6430 ...
2016-06-07 19:56:58 6430 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2016-06-07 19:56:58 6430 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)

2016-06-07 19:56:58 6430 [Note] Plugin 'FEDERATED' is disabled.
2016-06-07 19:56:58 6430 [ERROR] Function 'innodb' already exists
2016-06-07 19:56:58 6430 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-06-07 19:56:58 6430 [ERROR] Function 'federated' already exists
2016-06-07 19:56:58 6430 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2016-06-07 19:56:58 6430 [ERROR] Function 'blackhole' already exists
2016-06-07 19:56:58 6430 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2016-06-07 19:56:58 6430 [ERROR] Function 'archive' already exists
2016-06-07 19:56:58 6430 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2016-06-07 19:56:58 6430 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-06-07 19:56:58 6430 [Note] InnoDB: The InnoDB memory heap is disabled
2016-06-07 19:56:58 6430 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-06-07 19:56:58 6430 [Note] InnoDB: Memory barrier is not used
2016-06-07 19:56:58 6430 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-06-07 19:56:58 6430 [Note] InnoDB: Using Linux native AIO
2016-06-07 19:56:58 6430 [Note] InnoDB: Using CPU crc32 instructions
2016-06-07 19:56:58 6430 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-06-07 19:56:58 6430 [Note] InnoDB: Completed initialization of buffer pool
2016-06-07 19:56:58 6430 [Note] InnoDB: Highest supported file format is Barracuda.
2016-06-07 19:56:58 6430 [Note] InnoDB: 128 rollback segment(s) are active.
2016-06-07 19:56:58 6430 [Note] InnoDB: Waiting for purge to start
2016-06-07 19:56:58 6430 [Note] InnoDB: 5.6.30 started; log sequence number 95410297
2016-06-07 19:56:58 6430 [Note] Server hostname (bind-address): '*'; port: 3306
2016-06-07 19:56:58 6430 [Note] IPv6 is available.
2016-06-07 19:56:58 6430 [Note]   - '::' resolves to '::';
2016-06-07 19:56:58 6430 [Note] Server socket created on IP: '::'.
2016-06-07 19:56:58 6430 [Note] Event Scheduler: Loaded 0 events
2016-06-07 19:56:58 6430 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.30-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Maybe it relates somehow to the issue.

  • what does show variables like '%general_%' yield? is it showing general log on? if not try setting it globally and see if it encounters any error or you may refer to error log if there is any other error – Nawaz Sohail Jun 6 '16 at 9:04
  • Thanks for your comment and for the hint! SHOW VARIABLES LIKE '%general%'; returns a result table with two rows: Variable_name:general_log | Value:OFF, Variable_name:general_log_file | Value:/var/lib/mysql/ubuntuvm.log. I wonder -- I didn't set these variables... – automatix Jun 6 '16 at 10:04
  • @NawazSohail I altered the variables like this: SET GLOBAL general_log = 'ON'; and SET GLOBAL general_log_file = '/var/log/mysql/test.log';. It worked, but the TTL of the settings is the current session -- after it they get reset. – automatix Jun 6 '16 at 10:17
1

Also needed:

log_output = 'FILE'

Meanwhile, you have possibly thrown lots of stuff into the table mysql.general_log. Do SELECT * FROM mysql.general_log LIMIT 11;

  • Just added log_output = 'FILE' into my.cnf, but it's still not working. SELECT * FROM mysql.general_log LIMIT 11; provides an empty result set. – automatix Jun 24 '16 at 10:52
  • FILE writes to a file. TABLE writes to a table. FILE,TABLE does both. – Rick James Jun 24 '16 at 15:21
0

I faced the similar issue some days ago, few suggestions.

  1. Check the output of the following command:

    show variables like '%general%';

  2. If the output listing general log file and still it is not logging queries, modify the following parameters.

general_log = ON
general_log_file = /var/log/mysql/mysql.log

  1. Still if it is not working, check the access permission of the /var/log/mysql/mysql.log path. Change the access permission as follows:

chmod 755 /var/log/mysql/mysql.log

I hope this answer will solve log query issue. Thank you.

  • Thank you for your answer! SHOW VARIABLES LIKE '%general%'; returns a result table with two rows: Variable_name:general_log | Value:OFF, Variable_name:general_log_file | Value:/var/lib/mysql/ubuntuvm.log. I didn't set these variables. How to make MySQL follow the configs defined in the my.cnf? – automatix Jun 6 '16 at 10:06
  • Set those parameters in my.cnf and restart the MySQL server. – rathishDBA Jun 6 '16 at 12:21
  • I've already done this (s. the question) and restarted MySQL several times. But these settings seem to be ignored. – automatix Jun 6 '16 at 12:49
  • Check that, variable_name:general_log - value: OFF, instead of setting it as 1 on my.cnf set it as ON, and restart the server. – rathishDBA Jun 6 '16 at 14:37
  • Just tried this out. It doesn't work. The variables from the table seem to have a higher priority than the settings in the my.cnf and override whatever I define in the config file. – automatix Jun 6 '16 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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