Description:
The value of row_count() does not reset after a DROP TABLE in a procedure. This value increase after each iteration of the procedure.
mysql> delimiter //
mysql> CREATE DEFINER='root'@'%' PROCEDURE test_procedure()
-> begin
-> drop table if exists tmptb;
-> create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
-> select row_count();
-> select count(*) from tbtest;
-> drop table if exists tmptb;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call test_procedure;
+-------------+
| row_count() |
+-------------+
| 1000 |
+-------------+
1 row in set (0.04 sec)
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> call test_procedure;
+-------------+
| row_count() |
+-------------+
| 2000 |
+-------------+
1 row in set (0.03 sec)
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.07 sec)
mysql> call test_procedure;
+-------------+
| row_count() |
+-------------+
| 3000 |
+-------------+
1 row in set (0.02 sec)
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.10 sec)
If execute these statements without a Procedure, the value of row_count() is reset after each DROP TABLE
mysql> drop table if exists tmptb;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
Query OK, 1000 rows affected (0.06 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 1000 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(*) from tbtest;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.02 sec)
mysql> drop table if exists tmptb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
Query OK, 1000 rows affected (0.02 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 1000 |
+-------------+
1 row in set (0.00 sec)
mysql> select count(*) from tbtest;
+----------+
| count(*) |
+----------+
| 1000 |
+----------+
1 row in set (0.00 sec)
mysql> drop table if exists tmptb;
Query OK, 0 rows affected (0.01 sec)
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
How to repeat:
Create this table and this procedure
mysql> show create table tbtest\G
*************************** 1. row ***************************
Table: tbtest
Create Table: CREATE TABLE `tbtest` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
show create procedure test_procedure\G
*************************** 1. row ***************************
Procedure: test_procedure
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`()
begin
drop table if exists tmptb;
create table tmptb (primary key (id)) engine=innodb as select * from tbtest;
select row_count();
select count(*) from tbtest;
drop table if exists tmptb;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Then call the procedure multiple times in the same session.
Suggested fix:
Reset the value of row_count() after each drop table when it is called in a procedure
Description: The value of row_count() does not reset after a DROP TABLE in a procedure. This value increase after each iteration of the procedure. mysql> delimiter // mysql> CREATE DEFINER='root'@'%' PROCEDURE test_procedure() -> begin -> drop table if exists tmptb; -> create table tmptb (primary key (id)) engine=innodb as select * from tbtest; -> select row_count(); -> select count(*) from tbtest; -> drop table if exists tmptb; -> end// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call test_procedure; +-------------+ | row_count() | +-------------+ | 1000 | +-------------+ 1 row in set (0.04 sec) +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.04 sec) Query OK, 0 rows affected (0.05 sec) mysql> call test_procedure; +-------------+ | row_count() | +-------------+ | 2000 | +-------------+ 1 row in set (0.03 sec) +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.05 sec) Query OK, 0 rows affected (0.07 sec) mysql> call test_procedure; +-------------+ | row_count() | +-------------+ | 3000 | +-------------+ 1 row in set (0.02 sec) +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.10 sec) If execute these statements without a Procedure, the value of row_count() is reset after each DROP TABLE mysql> drop table if exists tmptb; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table tmptb (primary key (id)) engine=innodb as select * from tbtest; Query OK, 1000 rows affected (0.06 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> select row_count(); +-------------+ | row_count() | +-------------+ | 1000 | +-------------+ 1 row in set (0.00 sec) mysql> select count(*) from tbtest; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.02 sec) mysql> drop table if exists tmptb; Query OK, 0 rows affected (0.00 sec) mysql> create table tmptb (primary key (id)) engine=innodb as select * from tbtest; Query OK, 1000 rows affected (0.02 sec) Records: 1000 Duplicates: 0 Warnings: 0 mysql> select row_count(); +-------------+ | row_count() | +-------------+ | 1000 | +-------------+ 1 row in set (0.00 sec) mysql> select count(*) from tbtest; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec) mysql> drop table if exists tmptb; Query OK, 0 rows affected (0.01 sec) mysql> select row_count(); +-------------+ | row_count() | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) How to repeat: Create this table and this procedure mysql> show create table tbtest\G *************************** 1. row *************************** Table: tbtest Create Table: CREATE TABLE `tbtest` ( `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) show create procedure test_procedure\G *************************** 1. row *************************** Procedure: test_procedure sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`() begin drop table if exists tmptb; create table tmptb (primary key (id)) engine=innodb as select * from tbtest; select row_count(); select count(*) from tbtest; drop table if exists tmptb; end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Then call the procedure multiple times in the same session. Suggested fix: Reset the value of row_count() after each drop table when it is called in a procedure