MDEV-4412 : SLOW QUERY LOG - add affected rows (UPDATE / DELETE) in slow query log

Added Rows_affected to slow query log & mysql.slow_log table.

1 files changed, 23 insertions, 17 deletions

diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result
index 4471c01c99b..8e39ed861bd 100644
--- a/mysql-test/r/log_tables.result
+++ b/mysql-test/r/log_tables.result
@@ -17,7 +17,7 @@ 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	thread_id
+start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected
 truncate table general_log;
 select * from general_log where argument like '%general_log%';
 event_time	user_host	thread_id	server_id	command_type	argument
@@ -82,7 +82,8 @@ slow_log	CREATE TABLE `slow_log` (
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
-  `thread_id` bigint(21) unsigned NOT NULL
+  `thread_id` bigint(21) unsigned NOT NULL,
+  `rows_affected` int(11) NOT NULL
 ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
 show fields from mysql.slow_log;
 Field	Type	Null	Key	Default	Extra
@@ -98,6 +99,7 @@ insert_id	int(11)	NO		NULL
 server_id	int(10) unsigned	NO		NULL	
 sql_text	mediumtext	NO		NULL	
 thread_id	bigint(21) unsigned	NO		NULL	
+rows_affected	int(11)	NO		NULL	
 flush logs;
 flush tables;
 SET GLOBAL GENERAL_LOG=ON;
@@ -148,8 +150,8 @@ 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	thread_id
-TIMESTAMP	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	mysql	0	0	1	select sleep(2)	THREAD_ID
+start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected
+TIMESTAMP	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	mysql	0	0	1	select sleep(2)	THREAD_ID	0
 set @@session.long_query_time = @saved_long_query_time;
 alter table mysql.general_log engine=myisam;
 ERROR HY000: You cannot 'ALTER' a log table if logging is enabled
@@ -187,7 +189,8 @@ slow_log	CREATE TABLE `slow_log` (
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
-  `thread_id` bigint(21) unsigned NOT NULL
+  `thread_id` bigint(21) unsigned NOT NULL,
+  `rows_affected` int(11) NOT NULL
 ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
 alter table mysql.general_log engine=myisam;
 alter table mysql.slow_log engine=myisam;
@@ -215,7 +218,8 @@ slow_log	CREATE TABLE `slow_log` (
   `insert_id` int(11) NOT NULL,
   `server_id` int(10) unsigned NOT NULL,
   `sql_text` mediumtext NOT NULL,
-  `thread_id` bigint(21) unsigned NOT NULL
+  `thread_id` bigint(21) unsigned NOT NULL,
+  `rows_affected` int(11) NOT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
 set global general_log='ON';
 set global slow_query_log='ON';
@@ -286,7 +290,8 @@ ON UPDATE CURRENT_TIMESTAMP,
 `insert_id` int(11) NOT NULL,
 `server_id` int(10) unsigned NOT NULL,
 `sql_text` mediumtext NOT NULL,
-`thread_id` BIGINT(21) UNSIGNED NOT NULL
+`thread_id` BIGINT(21) UNSIGNED NOT NULL,
+`rows_affected` int(11) NOT NULL
 ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
 set global general_log='ON';
 set global slow_query_log='ON';
@@ -313,7 +318,7 @@ 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	thread_id
+start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected
 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;
@@ -334,9 +339,9 @@ 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	thread_id
+start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected
 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	thread_id
+start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected
 set global general_log='OFF';
 RENAME TABLE general_log TO general_log2;
 set global slow_query_log='OFF';
@@ -429,10 +434,10 @@ 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	thread_id	seq
-START_TIME	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	test	0	0	1	SELECT "My own slow query", sleep(2)	3	2
-START_TIME	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	test	0	0	1	SELECT "My own slow query", sleep(2)	3	3
-START_TIME	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	test	0	0	1	SELECT "My own slow query", sleep(2)	3	4
+start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected	seq
+START_TIME	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	test	0	0	1	SELECT "My own slow query", sleep(2)	3	0	2
+START_TIME	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	test	0	0	1	SELECT "My own slow query", sleep(2)	3	0	3
+START_TIME	USER_HOST	QUERY_TIME	00:00:00.000000	1	0	test	0	0	1	SELECT "My own slow query", sleep(2)	3	0	4
 SET GLOBAL slow_query_log = 0;
 SET SESSION long_query_time =@saved_long_query_time;
 FLUSH LOGS;
@@ -550,7 +555,7 @@ CREATE procedure `db_17876.archiveSlowLog`()
 BEGIN
 DECLARE start_time, query_time, lock_time CHAR(28);
 DECLARE user_host MEDIUMTEXT;
-DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT;
+DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id, rows_affected INT;
 DECLARE thread_id BIGINT UNSIGNED;
 DECLARE dbname MEDIUMTEXT;
 DECLARE sql_text BLOB;
@@ -565,14 +570,15 @@ 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, thread_id;
+insert_id, server_id, sql_text, thread_id, rows_affected;
 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, thread_id);
+dbname, last_insert_id,  insert_id, server_id, sql_text, thread_id,
+rows_affected);
 END;
 END IF;
 END;