Elena Stepanova added a comment - 2023-03-19 20:50 Same as attached, in MTR form: --source include/have_innodb.inc   set @log_output.save= @@log_output, @slow_log.save= @@slow_query_log; set global log_output= 'TABLE' , slow_query_log= ON ; set long_query_time= 0.000001; create database db; use db;   --delimiter // CREATE OR REPLACE FUNCTION `get_id`(`INPUT_STRING` VARCHAR (50), `DECRYPT_KEY` CHAR (20)) RETURNS int (11) BEGIN DECLARE REQUEST_ID INT ; SET REQUEST_ID = IF ( ( LOCATE( '#$#$#$ LHP_ID_' , INPUT_STRING) ) > 0 AND ( INPUT_STRING REGEXP DECRYPT_KEY )>0, ( SPLIT_STRING( SUBSTRING_INDEX( SUBSTRING_INDEX(INPUT_STRING, '#$#$#$ LHP_ID_' , -1), '#$#$#$' , 1 ), '-' , 2 ) ), NULL ) ; SET REQUEST_ID= IF ( ISNULL (REQUEST_ID) OR (REQUEST_ID= '' ) OR (REQUEST_ID=0), NULL , REQUEST_ID   ); RETURN (REQUEST_ID) ; END //   CREATE OR REPLACE FUNCTION `SPLIT_STRING`(`str` VARCHAR (255), `delim` VARCHAR (12), `pos` INT ) RETURNS varchar (255) CHARSET utf8mb4 RETURN REPLACE ( SUBSTRING ( SUBSTRING_INDEX(str , delim , pos) , CHAR_LENGTH( SUBSTRING_INDEX(str , delim , pos - 1) ) + 1 ) , delim , '' ) //   CREATE TABLE `tab1` ( `ID` int (11) NOT NULL AUTO_INCREMENT, `NAME_F` varchar (50) DEFAULT NULL , PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 //   CREATE TABLE `tab2` ( `ID` int (11) NOT NULL AUTO_INCREMENT, `TAB1_ID` int (11) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 //     for i in 1..100 do insert into tab1 values ( null , 'qwerty' ); end for ; // for i in 1..1000 do insert into tab2 values ( null ,round(rand()*10000)); end for ; //   --delimiter ;   SELECT get_id( CAST (aes_decrypt(tab1.NAME_F, 'V41iNM0n4' ) AS char ), 'KM_ID_PL' ) as get_string, ( CASE WHEN ( SELECT ID FROM tab2 where tab2.TAB1_ID = tab1.ID LIMIT 1) IS NULL THEN 0 ELSE 1 END ) AS IS_ATT FROM tab1 ORDER BY 2 DESC LIMIT 2;   set global log_output= @log_output.save, slow_query_log= @slow_log.save; drop database db; 10.4 99ee200b 2023-03-19 22:50:15 9 [ERROR] Failed to write to mysql.slow_log:

Kyle Hutchinson added a comment - 2023-07-12 16:07 sanja any update on this ticket?

Oleksandr Byelkin added a comment - 2023-07-13 05:58 kyle.hutchinson Not yes, sorry

Oleksandr Byelkin added a comment - 2023-07-13 07:46 (rr) p thd->get_examined_row_count() $1 = 6468184979087718979

Oleksandr Byelkin added a comment - 2023-07-13 07:53 So examened row should be big iint probably

Oleksandr Byelkin added a comment - 2023-07-14 06:55 - edited commit 845fbe9ce01fac29c446b4e898b2242ec814ade8 (HEAD -> bb-10.4-MDEV-30820, origin/bb-10.4-MDEV-30820) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Thu Jul 13 13:39:54 2023 +0200   MDEV-30820 slow log Rows_examined out of range Fix row counters to be able to get any possible value.

Oleksandr Byelkin added a comment - 2023-07-19 14:15 - edited commit 55a27bac80ca12999f1239c96cb6f5d2e0a3f00c Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Thu Jul 13 13:39:54 2023 +0200   MDEV-30820 slow log Rows_examined out of range Fix row counters to be able to get any possible value.

Oleksandr Byelkin added a comment - 2023-08-11 11:27 kyle.hutchinson The author of the bug (Monty) insist on other fix, so I will do it

Oleksandr Byelkin added a comment - 2023-08-11 11:28 The author want reseting counters instead removing restoring it (so above should be redone)

Michael Widenius added a comment - 2023-09-08 13:33 Short recap: For sub statements we reset the counters and calculate only what happens in the sub statement. This is fine. Sub statements only shows up in the slow query log if LOG_SLOW_DISABLE_SP is not set. For the top statement, we collect counters for the top statement + all included sub statements. One could argue that for the top statement we should not print the sub statement status (except if LOG_SLOW_DISABLE_SP is set). Historically we have printed everything for the top statement and I think this is fine for now. It may cause confusion if the number of statistics changes just because a user changes the value of LOG_SLOW_DISABLE_SP (by default it is set). This means that your current patch is fine as it only extends the counters. What should probably be done in the documentation is to clearly state that slow query log statistics for a statement always includes all statistics for all called stored procedures or stored functions.

Kyle Hutchinson added a comment - 2023-09-20 16:13 sanja It looks like the existing patch has been approved, do you think this can be included in the next release?

Daniel Black added a comment - 2023-09-21 10:02 sanja I wouldn't just yet, looking at the figure: --- /home/dan/repos/mariadb-server-10.4/mysql-test/main/log_slow_debug.result 2023-09-21 19:35:56.918844459 +1000 +++ /home/dan/repos/mariadb-server-10.4/mysql-test/main/log_slow_debug.reject 2023-09-21 19:43:41.965315498 +1000 @@ -246,6 +246,12 @@ 1 0 2 0 SET GLOBAL debug_dbug=@old_dbug; +SELECT sql_text,rows_examined FROM mysql.slow_log; +sql_text rows_examined +SET SESSION long_query_time= 0 0 +SET GLOBAL debug_dbug="+d,debug_huge_number_of_examined_rows" 0 +SELECT * FROM tab_MDEV_30820 ORDER BY 1 4 +SET GLOBAL debug_dbug=@old_dbug 0 SET @@long_query_time= @old_long_query_time; SET @@global.log_output= @old_log_output; SET @@global.slow_query_log= @old_slow_query_log; It doesn't appear that the very large number ended up in the logs. I foolishly started https://github.com/MariaDB/server/pull/2767 before seeing this commit thinking all the datatypes needed to be ulonglong. I get the same mtr result however.

Oleksandr Byelkin added a comment - 2023-09-22 12:02 the large numbers was result of counting bug. but theoretically they possible