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

[4 Nov 2021 2:49] yi qian
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)
[4 Nov 2021 6:30] MySQL Verification Team
Hello yi qian,

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

regards,
Umesh
[4 Nov 2021 19:19] Justin Swanhart
Can up update the synopsis/tags to say 'stored function'.  The reporter's original language may not be not english.
[5 Nov 2021 3:41] yi qian
change storage function to stored function
[22 Jan 2022 15:25] Daniel Price
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.