MySQL Bugs: #105450: There is a problem when calling the system table in stored function

Bug #105450	There is a problem when calling the system table in stored function
Submitted:	4 Nov 2021 2:49	Modified:	22 Jan 2022 15:25
Reporter:	yi qian	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.25, 8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression, stored function; information_schema.tables

Description:
When calling the statistics of system table information_schema.tables in the storage function, it is consistent with the actual situation when the current link is only called for the first time.

How to repeat:
1.Prepare data:
create database testdb;

create table `table1`  (   `org_id` int(8) not null auto_increment,   `org_code` varchar(16) not null,primary key (`org_id`));

insert into table1 values(NULL,'1');
insert into table1 values(NULL,'2');

2.Let the system table update in real time:
set session information_schema_stats_expiry = 0;

3.Create storage function call table information_schema.tables:
SET GLOBAL log_bin_trust_function_creators = 1;

delimiter /

create function fun1(table_name varchar(64)) returns int(11)
begin
declare dbname varchar(32) default 'testdb';
declare nextid int(11);
select database() into dbname;
select max(AUTO_INCREMENT) into nextid from information_schema.tables t where table_schema=dbname and t.table_name=lower(table_name);
return nextid;
end;
/

delimiter ;

4.First query:
mysql> select max(AUTO_INCREMENT) from information_schema.tables t where table_schema='testdb' and t.table_name=lower('table1');
+---------------------+
| max(AUTO_INCREMENT) |
+---------------------+
|                   3 |
+---------------------+
1 row in set (0.00 sec)

mysql> select fun1('table1');
+----------------+
| fun1('table1') |
+----------------+
|              3 |
+----------------+
1 row in set (0.01 sec)

5.Insert data and second query:
mysql> insert into table1 values(NULL,'1');

mysql> select max(AUTO_INCREMENT) from information_schema.tables t where table_schema='testdb' and t.table_name=lower('table1');
+---------------------+
| max(AUTO_INCREMENT) |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.00 sec)

mysql> select fun1('table1');
+----------------+
| fun1('table1') |
+----------------+
|              3 |
+----------------+
1 row in set (0.01 sec)

Hello yi qian,

Thank you for the report and test case.
Verified as described with 8.0.27 build.

regards,
Umesh

Can up update the synopsis/tags to say 'stored function'.  The reporter's original language may not be not english.

change storage function to stored function

Posted by developer:
 
Fixed as of the upcoming 8.0.29 release:

An INFORMATION_SCHEMA.FILES or INFORMATION_SCHEMA.TABLES query executed
by prepared statement or stored function returned stale data. Cached data
was reported after the data had changed.