|
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:
|
|
|
sanja any update on this ticket?
|
|
kyle.hutchinson Not yes, sorry
|
|
(rr) p thd->get_examined_row_count()
|
$1 = 6468184979087718979
|
|
|
So examened row should be big iint probably
|
|
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.
|
|
|
|
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.
|
|
|
kyle.hutchinsonThe author of the bug (Monty) insist on other fix, so I will do it
|
|
The author want reseting counters instead removing restoring it (so above should be redone)
|
|
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.
|
|
sanja It looks like the existing patch has been approved, do you think this can be included in the next release?
|
|
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.
|
|
the large numbers was result of counting bug. but theoretically they possible
|
huge_value_rows_examined.txt