Description:
After a prepare stmt is declared, switch to a new database and run the stmt,the result is still result in the last DB.
actually I know maybe it is not a issue, because the prepare-stmt is a global action.
but what I am worried is such a scenerio:
0. there are two databases , and there are some tables with the same name and table definition in the two databases.
1. the customer prepare a stmt in DB1
2. the customer switch to DB2(use DB2 in the same session)
3. execute stmt
4. the customer maybe would like to use the results obtained in step 3 to process customer services. then ther will be something wrong with the services.
so I think it would be more reasonable to add a description of this scenario in the documentation.
How to repeat:
we need to start 2 session to repeat this secnerio.
session 1:
create database session_plan_cache;
use session_plan_cache;
CREATE TABLE `t_general_test` (
`id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`client` char(4) DEFAULT NULL,
`bin_id` binary(16) NOT NULL,
`t_bit16` bit(16) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`col1` decimal(10,2) DEFAULT NULL,
`text` blob,
`tex` text,
`sex` enum('boy','girl','secret') DEFAULT 'secret'
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t_general_test values(null,null,null,123,0,now(),0,'textsefwefwef','fwgfwergwegf','boy');
insert into t_general_test values(null,null,null,123,0,now(),0,null,null,'boy');
insert into t_general_test values(0,'NULL','NULL',123,0,now(),0,'textsefwefwef','fwgfwergwegf','boy');
insert into t_general_test values(0,'NULL','NULL',123,0,now(),0,'','','girl');
insert into t_general_test values(255,'NULL','NULL',123,0,now(),0,'textsefwefwef','fwgfwergwegf','boy');
insert into t_general_test values(100,'NULL','NULL',123,0,now(),0,'','','girl');
insert into t_general_test values(255,'NULL','NULL',123,2,now(),0,'textsefwefwef','fwgfwergwegf','boy');
insert into t_general_test values(100,'NULL','NULL',123,2,now(),0,'','','girl');
insert into t_general_test select * from t_general_test;
set @sql_text1='select count(*) from t_general_test t where id<1;';
PREPARE stmt1 FROM @sql_text1;
EXECUTE stmt1;
EXECUTE stmt1;
start another db-conn , start session 2:
create database session_plan_cache1;
use session_plan_cache1;
CREATE TABLE `t_general_test` (
`id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`client` char(4) DEFAULT NULL,
`bin_id` binary(16) NOT NULL,
`t_bit16` bit(16) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`col1` decimal(10,2) DEFAULT NULL,
`text` blob,
`tex` text,
`sex` enum('boy','girl','secret') DEFAULT 'secret'
) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
back to session 1:
use session_plan_cache1;
--the query will retun 4, because it is conned to session_plan_cache actually.
EXECUTE stmt1;
Suggested fix:
I think it would be more reasonable to add a description of this scenario in the documentation.
To remind the user that prepare stmt is a global operation.
Even if you switch to a new database in a session, execute stmt still returns the result of the original database.
Description: After a prepare stmt is declared, switch to a new database and run the stmt,the result is still result in the last DB. actually I know maybe it is not a issue, because the prepare-stmt is a global action. but what I am worried is such a scenerio: 0. there are two databases , and there are some tables with the same name and table definition in the two databases. 1. the customer prepare a stmt in DB1 2. the customer switch to DB2(use DB2 in the same session) 3. execute stmt 4. the customer maybe would like to use the results obtained in step 3 to process customer services. then ther will be something wrong with the services. so I think it would be more reasonable to add a description of this scenario in the documentation. How to repeat: we need to start 2 session to repeat this secnerio. session 1: create database session_plan_cache; use session_plan_cache; CREATE TABLE `t_general_test` ( `id` int DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `client` char(4) DEFAULT NULL, `bin_id` binary(16) NOT NULL, `t_bit16` bit(16) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `col1` decimal(10,2) DEFAULT NULL, `text` blob, `tex` text, `sex` enum('boy','girl','secret') DEFAULT 'secret' ) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; insert into t_general_test values(null,null,null,123,0,now(),0,'textsefwefwef','fwgfwergwegf','boy'); insert into t_general_test values(null,null,null,123,0,now(),0,null,null,'boy'); insert into t_general_test values(0,'NULL','NULL',123,0,now(),0,'textsefwefwef','fwgfwergwegf','boy'); insert into t_general_test values(0,'NULL','NULL',123,0,now(),0,'','','girl'); insert into t_general_test values(255,'NULL','NULL',123,0,now(),0,'textsefwefwef','fwgfwergwegf','boy'); insert into t_general_test values(100,'NULL','NULL',123,0,now(),0,'','','girl'); insert into t_general_test values(255,'NULL','NULL',123,2,now(),0,'textsefwefwef','fwgfwergwegf','boy'); insert into t_general_test values(100,'NULL','NULL',123,2,now(),0,'','','girl'); insert into t_general_test select * from t_general_test; set @sql_text1='select count(*) from t_general_test t where id<1;'; PREPARE stmt1 FROM @sql_text1; EXECUTE stmt1; EXECUTE stmt1; start another db-conn , start session 2: create database session_plan_cache1; use session_plan_cache1; CREATE TABLE `t_general_test` ( `id` int DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `client` char(4) DEFAULT NULL, `bin_id` binary(16) NOT NULL, `t_bit16` bit(16) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `col1` decimal(10,2) DEFAULT NULL, `text` blob, `tex` text, `sex` enum('boy','girl','secret') DEFAULT 'secret' ) ENGINE=Dstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; back to session 1: use session_plan_cache1; --the query will retun 4, because it is conned to session_plan_cache actually. EXECUTE stmt1; Suggested fix: I think it would be more reasonable to add a description of this scenario in the documentation. To remind the user that prepare stmt is a global operation. Even if you switch to a new database in a session, execute stmt still returns the result of the original database.