MySQL Bugs: #71377: too larege examined_row_count prevents logging mysql.slow

Bug #71377	too larege examined_row_count prevents logging mysql.slow_log
Submitted:	14 Jan 2014 17:31	Modified:	17 Jan 2014 7:29
Reporter:	xiaobin lin (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.1+, 5.6.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	examined_row_count, INT_MAX32, slow_log

Description:
The conditions bellow will prevent mysqld logging a slow query, and print in errlog  "[ERROR] Failed to write to mysql.slow_log: "

  1)a slow query whose query time > long_query_time
  2) this query examines more than INT_MAX32 rows
  3) the slow_log is on and log_output='TABLE'

How to repeat:
assume there is a table T with more than INT_MAX32 rows.

set global slow_query_log=on;
set long_query_time=1;
select * from T limit INT_MAX32,1.

Suggested fix:
It is because the field 'rows_examined' in  mysql.slow_log defined as int,
but in when store it ,longlong is used. 
   "table->field[5]->store((longlong) thd->examined_row_count, TRUE)"

We could change the action while mysql_install_db, define the row_examined field as bigint.

PS: The output in error log is unfriendly too,which cost me some time to find the reason.  Another suggestion is to add detail information for every error case here.

Hello Xiaobin,

Thank you for the bug report.
Verified as described on 5.6.15.

Thanks,
Umesh

// How to repeat
// Time consuming stuff 

1. All we need is a huge table to repeat this:

// disable bin logging

use test;
drop table if exists keyvalue;
CREATE TABLE `keyvalue` (
  `id` tinyint(1) NOT NULL
  ) ENGINE=myisam charset=latin1;

set @id:=0;
insert into `keyvalue` values ('1'),('1'),('1'),('1');
insert into `keyvalue`(`id`) select '1' from `keyvalue` k1, `keyvalue` k2, `keyvalue` k3, `keyvalue` k4,`keyvalue` k5,`keyvalue` k6, `keyvalue` k7, `keyvalue` k8, `keyvalue` k9,`keyvalue` k0,`keyvalue` ka, `keyvalue` kb, `keyvalue` kc, `keyvalue` kd, `keyvalue` ke, `keyvalue` kf, `keyvalue` kg,`keyvalue` kh limit 2147483650;  

set global slow_query_log=on;
set global long_query_time=1;
set global log_output='TABLE';

select * from keyvalue limit 2147483649,1;
select * from mysql.slow_log\G
drop table if exists keyvalue;

// Check error log

2014-01-17 13:11:49 6150 [ERROR] Failed to write to mysql.slow_log:

// Just inspect the table structure of mysql.slow_log and notice rows_examined column's data type INT - which means any efforts to insert a value of >2147483647 results
into of range error..

mysql> desc mysql.slow_log;
+----------------+---------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                | Null | Key | Default           | Extra                       |
+----------------+---------------------+------+-----+-------------------+-----------------------------+
| start_time     | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update 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)        | NO   |     | NULL              |                             |
| last_insert_id | int(11)             | NO   |     | NULL              |                             |
| 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              |                             |
+----------------+---------------------+------+-----+-------------------+-----------------------------+

PS: row_sent should change too. 

diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql
index 8e92462..71004cb 100644
--- a/scripts/mysql_fix_privilege_tables.sql
+++ b/scripts/mysql_fix_privilege_tables.sql
@@ -90,7 +90,7 @@ DROP PREPARE stmt;
 
 -- Create slow_log if CSV is enabled.
 
-SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0');
+SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent BIGINT NOT NULL, rows_examined BIGINT NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0');
 
 PREPARE stmt FROM @str;
 EXECUTE stmt;
diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql
index 80cba6e..a2a9c02 100644
--- a/scripts/mysql_system_tables.sql
+++ b/scripts/mysql_system_tables.sql
@@ -90,7 +90,7 @@ DROP PREPARE stmt;
 
 -- Create slow_log if CSV is enabled.
 
-SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0');
+SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent BIGINT NOT NULL, rows_examined BIGINT NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER UNSIGNED NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0');
 
 PREPARE stmt FROM @str;
 EXECUTE stmt;