2

I'm trying to figure out how to turn on/off the MySQL 5.1.48 general query log, without restarting the server (not an option in production right now). In my test environment, it's not working.

According to this, I should be able to say:

set global general_log = 'OFF';

which does work: the general query log no longer has queries added to it. But when I do:

set global general_log = 'ON';

it complains that:

ERROR 1146 (42S02): Table 'mysql.general_log' doesn't exist

(true, it doesn't), and the log adds a standard startup header ("mysqld, Version: 5.1.48..."), and then nothing else.

Can I just go to another MySQL database with the same platform/version, copy its show create table mysql.general_log;, and run it here? Is that safe, or is this symptomatic of deeper problems? And am I supposed to have to do that when I turn on the general query log? I don't see anything about that in the manual.

2 Answers 2

6

The global general_log variable turns logging on and off (off by default) to the currently selected logging destination. If none is specified that is logging to the mysql.general_log table:

If --log-output is omitted or given without a value, the default is FILE. (For MySQL 5.1.6 through 5.1.20, the default logging destination is TABLE.)

In order to turn logging on to file you will have to restart MySQL specifying --log-output=FILE (or the my.cnf equivalent)

For logging to table the following is worth taking note of:

For new installations of MySQL 5.1.6 or higher, the log tables are created during the installation procedure along with the other system tables. If you upgrade MySQL from a release older than 5.1.6 to MySQL 5.1.6 or higher, you must upgrade the system tables after upgrading to make sure that the log tables exist. See Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.

Section 4.4.8 of the MySQL manual says:

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

It's worth reading through that part of the manual to ensure that your system tables are all correct and at the right version.

Oh, and I cannot stress this enough times:

Back up your databases before doing anything like this

1
  • Wow, that worked magic for me. I did mysql_upgrade -p --force and then a service mysql restart and now turning general_log on and off works like it should. Thank you very much Matt!
    – bawkstoo
    Apr 13, 2011 at 20:09
1

I have created that missing table and worked for me.

use mysql;

CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .