path: root/mysql-test/r/log_tables.result

use mysql;
truncate table general_log;
select * from general_log;
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from general_log
truncate table slow_log;
select * from slow_log;
start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text
truncate table general_log;
select * from general_log where argument like '%general_log%';
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from general_log where argument like '%general_log%'
create table join_test (verbose_comment varchar (80), command_type varchar(64));
insert into join_test values ("User performed a usual SQL query", "Query");
insert into join_test values ("New DB connection was registered", "Connect");
insert into join_test values ("Get the table info", "Field List");
select verbose_comment, user_host, argument
from  mysql.general_log join join_test
on (mysql.general_log.command_type = join_test.command_type);
verbose_comment	user_host	argument
User performed a usual SQL query	USER_HOST	select * from general_log where argument like '%general_log%'
User performed a usual SQL query	USER_HOST	create table join_test (verbose_comment varchar (80), command_type varchar(64))
User performed a usual SQL query	USER_HOST	insert into join_test values ("User performed a usual SQL query", "Query")
User performed a usual SQL query	USER_HOST	insert into join_test values ("New DB connection was registered", "Connect")
User performed a usual SQL query	USER_HOST	insert into join_test values ("Get the table info", "Field List")
User performed a usual SQL query	USER_HOST	select verbose_comment, user_host, argument
from  mysql.general_log join join_test
on (mysql.general_log.command_type = join_test.command_type)
drop table join_test;
flush logs;
lock tables mysql.general_log WRITE;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log WRITE;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.general_log READ;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log READ;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log READ LOCAL, mysql.general_log READ LOCAL;
ERROR HY000: You can't use locks with log tables.
show create table mysql.general_log;
Table	Create Table
general_log	CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext,
  `thread_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `command_type` varchar(64) DEFAULT NULL,
  `argument` mediumtext
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
show fields from mysql.general_log;
Field	Type	Null	Key	Default	Extra
event_time	timestamp	NO		CURRENT_TIMESTAMP	
user_host	mediumtext	YES		NULL	
thread_id	int(11)	YES		NULL	
server_id	int(11)	YES		NULL	
command_type	varchar(64)	YES		NULL	
argument	mediumtext	YES		NULL	
show create table mysql.slow_log;
Table	Create Table
slow_log	CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) DEFAULT NULL,
  `last_insert_id` int(11) DEFAULT NULL,
  `insert_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
show fields from mysql.slow_log;
Field	Type	Null	Key	Default	Extra
start_time	timestamp	NO		CURRENT_TIMESTAMP	
user_host	mediumtext	NO		NULL	
query_time	time	NO		NULL	
lock_time	time	NO		NULL	
rows_sent	int(11)	NO		NULL	
rows_examined	int(11)	NO		NULL	
db	varchar(512)	YES		NULL	
last_insert_id	int(11)	YES		NULL	
insert_id	int(11)	YES		NULL	
server_id	int(11)	YES		NULL	
sql_text	mediumtext	NO		NULL	
flush logs;
flush tables;
SET GLOBAL GENERAL_LOG=ON;
SET GLOBAL SLOW_QUERY_LOG=ON;
show open tables;
Database	Table	In_use	Name_locked
mysql	general_log	0	0
flush logs;
show open tables;
Database	Table	In_use	Name_locked
mysql	general_log	0	0
flush tables;
show open tables;
Database	Table	In_use	Name_locked
mysql	general_log	0	0
SET GLOBAL GENERAL_LOG=OFF;
SET GLOBAL SLOW_QUERY_LOG=OFF;
flush tables;
show open tables;
Database	Table	In_use	Name_locked
SET GLOBAL GENERAL_LOG=ON;
SET GLOBAL SLOW_QUERY_LOG=ON;
truncate table mysql.general_log;
set names utf8;
create table bug16905 (s char(15) character set utf8 default 'пусто');
insert into bug16905 values ('новое');
select * from mysql.general_log;
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	set names utf8
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	create table bug16905 (s char(15) character set utf8 default 'пусто')
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	insert into bug16905 values ('новое')
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from mysql.general_log
drop table bug16905;
truncate table mysql.slow_log;
set session long_query_time=1;
select sleep(2);
sleep(2)
0
select * from mysql.slow_log;
start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text
TIMESTAMP	USER_HOST	QUERY_TIME	00:00:00	1	0	mysql	0	0	1	select sleep(2)
alter table mysql.general_log engine=myisam;
ERROR HY000: You cannot 'ALTER' a log table if logging is enabled
alter table mysql.slow_log engine=myisam;
ERROR HY000: You cannot 'ALTER' a log table if logging is enabled
drop table mysql.general_log;
ERROR HY000: You cannot 'DROP' a log table if logging is enabled
drop table mysql.slow_log;
ERROR HY000: You cannot 'DROP' a log table if logging is enabled
set global general_log='OFF';
alter table mysql.slow_log engine=myisam;
ERROR HY000: You cannot 'ALTER' a log table if logging is enabled
set global slow_query_log='OFF';
show create table mysql.general_log;
Table	Create Table
general_log	CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext,
  `thread_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `command_type` varchar(64) DEFAULT NULL,
  `argument` mediumtext
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
show create table mysql.slow_log;
Table	Create Table
slow_log	CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) DEFAULT NULL,
  `last_insert_id` int(11) DEFAULT NULL,
  `insert_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
alter table mysql.general_log engine=myisam;
alter table mysql.slow_log engine=myisam;
show create table mysql.general_log;
Table	Create Table
general_log	CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext,
  `thread_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `command_type` varchar(64) DEFAULT NULL,
  `argument` mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
show create table mysql.slow_log;
Table	Create Table
slow_log	CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) DEFAULT NULL,
  `last_insert_id` int(11) DEFAULT NULL,
  `insert_id` int(11) DEFAULT NULL,
  `server_id` int(11) DEFAULT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
set global general_log='ON';
set global slow_query_log='ON';
select * from mysql.general_log;
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	set names utf8
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	create table bug16905 (s char(15) character set utf8 default 'пусто')
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	insert into bug16905 values ('новое')
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from mysql.general_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	drop table bug16905
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	truncate table mysql.slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	set session long_query_time=1
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select sleep(2)
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from mysql.slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	alter table mysql.general_log engine=myisam
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	alter table mysql.slow_log engine=myisam
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	drop table mysql.general_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	drop table mysql.slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	set global general_log='OFF'
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	set global slow_query_log='ON'
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from mysql.general_log
flush logs;
lock tables mysql.general_log WRITE;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log WRITE;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.general_log READ;
ERROR HY000: You can't use locks with log tables.
lock tables mysql.slow_log READ;
ERROR HY000: You can't use locks with log tables.
set global general_log='OFF';
set global slow_query_log='OFF';
set @save_storage_engine= @@session.storage_engine;
set storage_engine= MEMORY;
alter table mysql.slow_log engine=ndb;
ERROR HY000: This storage engine cannot be used for log tables"
alter table mysql.slow_log engine=innodb;
ERROR HY000: This storage engine cannot be used for log tables"
alter table mysql.slow_log engine=archive;
ERROR HY000: This storage engine cannot be used for log tables"
alter table mysql.slow_log engine=blackhole;
ERROR HY000: This storage engine cannot be used for log tables"
set storage_engine= @save_storage_engine;
drop table mysql.slow_log;
drop table mysql.general_log;
drop table mysql.general_log;
ERROR 42S02: Unknown table 'general_log'
drop table mysql.slow_log;
ERROR 42S02: Unknown table 'slow_log'
use mysql;
CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext,
`thread_id` int(11) DEFAULT NULL,
`server_id` int(11) DEFAULT NULL,
`command_type` varchar(64) DEFAULT NULL,
`argument` mediumtext
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) DEFAULT NULL,
`last_insert_id` int(11) DEFAULT NULL,
`insert_id` int(11) DEFAULT NULL,
`server_id` int(11) DEFAULT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
set global general_log='ON';
set global slow_query_log='ON';
use test;
flush tables with read lock;
unlock tables;
use mysql;
lock tables general_log read local, help_category read local;
ERROR HY000: You can't use locks with log tables.
unlock tables;
use mysql;
RENAME TABLE general_log TO renamed_general_log;
ERROR HY000: Cannot rename 'general_log'. When logging enabled, rename to/from log table must rename two tables: the log table to an archive table and another table back to 'general_log'
RENAME TABLE slow_log TO renamed_slow_log;
ERROR HY000: Cannot rename 'slow_log'. When logging enabled, rename to/from log table must rename two tables: the log table to an archive table and another table back to 'slow_log'
truncate table general_log;
select * from general_log;
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from general_log
truncate table slow_log;
select * from slow_log;
start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text
create table general_log_new like general_log;
rename table general_log TO renamed_general_log, general_log_new TO general_log;
create table slow_log_new like slow_log;
rename table slow_log TO renamed_slow_log, slow_log_new TO slow_log;
rename table general_log TO general_log_new, renamed_general_log TO general_log, slow_log to renamed_slow_log;
ERROR HY000: Cannot rename 'slow_log'. When logging enabled, rename to/from log table must rename two tables: the log table to an archive table and another table back to 'slow_log'
select * from general_log;
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	create table slow_log_new like slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	rename table slow_log TO renamed_slow_log, slow_log_new TO slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	rename table general_log TO general_log_new, renamed_general_log TO general_log, slow_log to renamed_slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from general_log
select * from renamed_general_log;
event_time	user_host	thread_id	server_id	command_type	argument
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from general_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	truncate table slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	select * from slow_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	create table general_log_new like general_log
TIMESTAMP	USER_HOST	THREAD_ID	1	Query	rename table general_log TO renamed_general_log, general_log_new TO general_log
select * from slow_log;
start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text
select * from renamed_slow_log;
start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text
set global general_log='OFF';
RENAME TABLE general_log TO general_log2;
set global slow_query_log='OFF';
RENAME TABLE slow_log TO slow_log2;
set global general_log='ON';
ERROR 42S02: Table 'mysql.general_log' doesn't exist
set global slow_query_log='ON';
ERROR 42S02: Table 'mysql.slow_log' doesn't exist
RENAME TABLE general_log2 TO general_log;
RENAME TABLE slow_log2 TO slow_log;
set global general_log='ON';
set global slow_query_log='ON';
flush logs;
flush logs;
drop table renamed_general_log, renamed_slow_log;
use test;
use mysql;
repair table general_log;
Table	Op	Msg_type	Msg_text
mysql.general_log	repair	status	OK
repair table slow_log;
Table	Op	Msg_type	Msg_text
mysql.slow_log	repair	status	OK
create table general_log_new like general_log;
create table slow_log_new like slow_log;
show tables like "%log%";
Tables_in_mysql (%log%)
general_log
general_log_new
ndb_binlog_index
slow_log
slow_log_new
drop table slow_log_new, general_log_new;
use test;
SET GLOBAL LOG_OUTPUT = 'TABLE';
SET GLOBAL general_log = 0;
FLUSH LOGS;
TRUNCATE TABLE mysql.general_log;
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log
ADD COLUMN seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
SET GLOBAL general_log = 1;
FLUSH LOGS;
SELECT * FROM mysql.general_log;
event_time	user_host	thread_id	server_id	command_type	argument	seq
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	FLUSH LOGS	1
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT * FROM mysql.general_log	2
SELECT * FROM mysql.general_log;
event_time	user_host	thread_id	server_id	command_type	argument	seq
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	FLUSH LOGS	1
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT * FROM mysql.general_log	2
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT * FROM mysql.general_log	3
SELECT "My own query 1";
My own query 1
My own query 1
SELECT "My own query 2";
My own query 2
My own query 2
SELECT * FROM mysql.general_log;
event_time	user_host	thread_id	server_id	command_type	argument	seq
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	FLUSH LOGS	1
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT * FROM mysql.general_log	2
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT * FROM mysql.general_log	3
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT "My own query 1"	4
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT "My own query 2"	5
EVENT_TIME	USER_HOST	THREAD_ID	SERVER_ID	Query	SELECT * FROM mysql.general_log	6
SET GLOBAL general_log = 0;
FLUSH LOGS;
ALTER TABLE mysql.general_log DROP COLUMN seq;
ALTER TABLE mysql.general_log ENGINE = CSV;
SET @old_long_query_time:=@@long_query_time;
SET GLOBAL slow_query_log = 0;
FLUSH LOGS;
TRUNCATE TABLE mysql.slow_log;
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log
ADD COLUMN seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
SET SESSION long_query_time = 1;
SET GLOBAL slow_query_log = 1;
FLUSH LOGS;
SELECT "My own slow query", sleep(2);
My own slow query	sleep(2)
My own slow query	0
SELECT "My own slow query", sleep(2);
My own slow query	sleep(2)
My own slow query	0
SELECT "My own slow query", sleep(2);
My own slow query	sleep(2)
My own slow query	0
SELECT "My own slow query", sleep(2);
My own slow query	sleep(2)
My own slow query	0
SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3;
start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	seq
START_TIME	USER_HOST	QUERY_TIME	00:00:00	1	0	test	NULL	NULL	1	SELECT "My own slow query", sleep(2)	2
START_TIME	USER_HOST	QUERY_TIME	00:00:00	1	0	test	NULL	NULL	1	SELECT "My own slow query", sleep(2)	3
START_TIME	USER_HOST	QUERY_TIME	00:00:00	1	0	test	NULL	NULL	1	SELECT "My own slow query", sleep(2)	4
SET GLOBAL slow_query_log = 0;
SET SESSION long_query_time =@old_long_query_time;
FLUSH LOGS;
ALTER TABLE mysql.slow_log DROP COLUMN seq;
ALTER TABLE mysql.slow_log ENGINE = CSV;
drop procedure if exists proc25422_truncate_slow;
drop procedure if exists proc25422_truncate_general;
drop procedure if exists proc25422_alter_slow;
drop procedure if exists proc25422_alter_general;
use test//
create procedure proc25422_truncate_slow (loops int)
begin
declare v1 int default 0;
declare continue handler for sqlexception /* errors from truncate */
begin end;
while v1 < loops do
truncate mysql.slow_log;
set v1 = v1 + 1;
end while;
end//
create procedure proc25422_truncate_general (loops int)
begin
declare v1 int default 0;
declare continue handler for sqlexception /* errors from truncate */
begin end;
while v1 < loops do
truncate mysql.general_log;
set v1 = v1 + 1;
end while;
end//
create procedure proc25422_alter_slow (loops int)
begin
declare v1 int default 0;
declare ER_BAD_LOG_STATEMENT condition for 1575;
declare continue handler for ER_BAD_LOG_STATEMENT begin end;
while v1 < loops do
set @old_log_state = @@global.slow_query_log;
set global slow_query_log = 'OFF';
alter table mysql.slow_log engine = CSV;
set global slow_query_log = @old_log_state;
set v1 = v1 + 1;
end while;
end//
create procedure proc25422_alter_general (loops int)
begin
declare v1 int default 0;
declare ER_BAD_LOG_STATEMENT condition for 1575;
declare continue handler for ER_BAD_LOG_STATEMENT begin end;
while v1 < loops do
set @old_log_state = @@global.general_log;
set global general_log = 'OFF';
alter table mysql.general_log engine = CSV;
set global general_log = @old_log_state;
set v1 = v1 + 1;
end while;
end//
set @iterations=100;
"Serial test (proc25422_truncate_slow)"
call proc25422_truncate_slow(@iterations);
"Serial test (proc25422_truncate_general)"
call proc25422_truncate_general(@iterations);
"Serial test (proc25422_alter_slow)"
call proc25422_alter_slow(@iterations);
"Serial test (proc25422_alter_general)"
call proc25422_alter_general(@iterations);
"Parallel test"
call proc25422_truncate_slow(@iterations);
call proc25422_truncate_slow(@iterations);
call proc25422_truncate_general(@iterations);
call proc25422_truncate_general(@iterations);
call proc25422_alter_slow(@iterations);
call proc25422_alter_slow(@iterations);
call proc25422_alter_general(@iterations);
call proc25422_alter_general(@iterations);
drop procedure proc25422_truncate_slow;
drop procedure proc25422_truncate_general;
drop procedure proc25422_alter_slow;
drop procedure proc25422_alter_general;
FLUSH TABLE mysql.general_log;
show warnings;
Level	Code	Message
FLUSH TABLE mysql.slow_log;
show warnings;
Level	Code	Message
DROP TABLE IF EXISTS `db_17876.slow_log_data`;
DROP TABLE IF EXISTS `db_17876.general_log_data`;
DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`;
DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`;
DROP DATABASE IF EXISTS `db_17876`;
CREATE DATABASE db_17876;
CREATE TABLE `db_17876.slow_log_data` (
`start_time` timestamp  default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`user_host` mediumtext ,
`query_time` time ,
`lock_time` time ,
`rows_sent` int(11) ,
`rows_examined` int(11) ,
`db` varchar(512) default NULL,
`last_insert_id` int(11) default NULL,
`insert_id` int(11) default NULL,
`server_id` int(11) default NULL,
`sql_text` mediumtext 
);
CREATE TABLE `db_17876.general_log_data` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext,
`thread_id` int(11) DEFAULT NULL,
`server_id` int(11) DEFAULT NULL,
`command_type` varchar(64) DEFAULT NULL,
`argument` mediumtext
);
CREATE procedure `db_17876.archiveSlowLog`()
BEGIN
DECLARE start_time, query_time, lock_time CHAR(20);
DECLARE user_host MEDIUMTEXT;
DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT;
DECLARE dbname MEDIUMTEXT;
DECLARE sql_text BLOB;
DECLARE done INT DEFAULT 0;
DECLARE ER_SP_FETCH_NO_DATA CONDITION for 1329;
DECLARE cur1 CURSOR FOR SELECT * FROM mysql.slow_log;
OPEN cur1;
REPEAT
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1;
FETCH cur1 INTO
start_time, user_host, query_time, lock_time,
rows_set, rows_examined, dbname, last_insert_id,
insert_id, server_id, sql_text;
END;
IF NOT done THEN
BEGIN
INSERT INTO
`db_17876.slow_log_data`
            VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined,
dbname, last_insert_id,  insert_id, server_id, sql_text);
END;
END IF;
END;
UNTIL done END REPEAT;
CLOSE cur1;
TRUNCATE mysql.slow_log;
END //
CREATE procedure `db_17876.archiveGeneralLog`()
BEGIN
DECLARE event_time CHAR(20);
DECLARE user_host, argument MEDIUMTEXT;
DECLARE thread_id, server_id INT;
DECLARE sql_text BLOB;
DECLARE done INT DEFAULT 0;
DECLARE command_type VARCHAR(64);
DECLARE ER_SP_FETCH_NO_DATA CONDITION for 1329;
DECLARE cur1 CURSOR FOR SELECT * FROM mysql.general_log;
OPEN cur1;
REPEAT
BEGIN
BEGIN
DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1;
FETCH cur1 INTO
event_time, user_host, thread_id, server_id,
command_type, argument;
END;
IF NOT done THEN
BEGIN
INSERT INTO
`db_17876.general_log_data`
            VALUES(event_time, user_host, thread_id, server_id,
command_type, argument);
END;
END IF;
END;
UNTIL done END REPEAT;
CLOSE cur1;
TRUNCATE mysql.general_log;
END //
SET @old_general_log_state = @@global.general_log;
SET @old_slow_log_state = @@global.slow_query_log;
SET GLOBAL general_log = ON;
SET GLOBAL slow_query_log = ON;
select "put something into general_log";
put something into general_log
put something into general_log
select "... and something more ...";
... and something more ...
... and something more ...
call `db_17876.archiveSlowLog`();
call `db_17876.archiveGeneralLog`();
SET GLOBAL general_log = OFF;
SET GLOBAL slow_query_log = OFF;
call `db_17876.archiveSlowLog`();
call `db_17876.archiveGeneralLog`();
DROP TABLE `db_17876.slow_log_data`;
DROP TABLE `db_17876.general_log_data`;
DROP PROCEDURE IF EXISTS `db_17876.archiveSlowLog`;
DROP PROCEDURE IF EXISTS `db_17876.archiveGeneralLog`;
DROP DATABASE IF EXISTS `db_17876`;
SET GLOBAL general_log = @old_general_log_state;
SET GLOBAL slow_query_log = @old_slow_log_state;