| Bug #89214 | The SELECT will deadlock in the stored procedure, if the result set is empty. | ||
|---|---|---|---|
| Submitted: | 12 Jan 2018 19:30 | Modified: | 3 Oct 2018 1:40 |
| Reporter: | two pg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S2 (Serious) |
| Version: | 5.7,5.7.17,5.7.20,5.7.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Jan 2018 19:37]
two pg
source code
Attachment: mysql-bug-data-89214.zip (application/x-zip-compressed, text), 1.89 KiB.
[17 Jan 2018 14:26]
MySQL Verification Team
Hi! Thank you for your report. We need to know whether you observe this phenomena if you do not use prepared statements ??? Also, we need to know whether your program gets stuck in this line: if (mysql_stmt_execute(stmt)) Next, stored procedures return two or more result sets. And I do not see where do you clear both result sets in your code. Last, but not least, what is shown in the processlist when your program gets stuck ????
[19 Jan 2018 14:35]
two pg
Yes, we are stuck in this line if result set is empty. if (mysql_stmt_execute(stmt)) This Bug only happen in the use of mysql_stmt_*, does not occur in the use of mysql_real_*. Must use mysql client 5.7.x and mysql server 5.7.x to repeat. Other versions do not have this problem. This is just a test code, eliminating a lot of detail. We found this problem in our asynchronous mysql connection library as we upgraded mysql 5.5 / 5.6 to mysql 5.7. mysql> show processlist; +----+------+--------------------+-------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+--------------------+-------+---------+------+----------+------------------+ | 6 | root | DESKTOP-7700:50460 | NULL | Sleep | 164 | | NULL | | 7 | root | localhost:50468 | appdb | Sleep | 304 | | NULL | | 12 | root | localhost:50511 | NULL | Query | 0 | starting | show processlist | +----+------+--------------------+-------+---------+------+----------+------------------+ 3 rows in set (0.00 sec)
[23 Jan 2018 13:19]
MySQL Verification Team
Hi! Please, clear out one more detail. You wrote that you use "our asynchronous MySQL connection library". Does it mean that you do not use our library ??? If that is the case, we can not debug your problem, since it could be related to your library behaviour. Thanks in advance !!!!
[23 Jan 2018 13:39]
MySQL Verification Team
Hi! To make things simpler, can you repeat this problem with our own libmysqlclient library ???
[23 Jan 2018 14:05]
MySQL Verification Team
Use the latest libmysqlclient library that comes with our 5.7 package.
[24 Jan 2018 0:01]
two pg
No, we use libmysqlclient, version 5.7.21
[24 Jan 2018 0:01]
two pg
new source code
Attachment: mysql-bug-data2-89214.zip (application/x-zip-compressed, text), 4.82 KiB.
[24 Jan 2018 0:04]
two pg
source code is copy from https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-execute.html
[24 Jan 2018 0:06]
two pg
5.6.39 passed the test. log-5.6.39: E:\TestMysql\x64\Release\TestMysql.exe MYSQL server version: 5.6.39-log MYSQL client version: 5.6.39 ----------------Test5---------------- ------------------------------------- sql = call SP_GET_TEST_TABLE(1) ---------------begin----------------- prepare, SELECT successful total parameters in SELECT: 0 total columns in SELECT statement: 4 Fetching results ... Query Finished ... ----------------end------------------
[24 Jan 2018 0:14]
two pg
5.7.21 Test failed, stuck in mysql_stmt_execute(). E:\TestMysql\x64\Release\TestMysql.exe MYSQL server version: 5.7.21-log MYSQL client version: 5.7.21 ----------------Test5---------------- ------------------------------------- sql = call SP_GET_TEST_TABLE(1) ---------------begin----------------- prepare, SELECT successful total parameters in SELECT: 0 ^C E:\TestMysql\x64\Release>
[24 Jan 2018 0:22]
two pg
Passed the test. E:\TestMysql\x64\Release\TestMysql.exe MYSQL server version: 5.7.21-log MYSQL client version: 5.6.39 ----------------Test5---------------- ------------------------------------- sql = call SP_GET_TEST_TABLE(1) ---------------begin----------------- prepare, SELECT successful total parameters in SELECT: 0 total columns in SELECT statement: 4 Fetching results ... Query Finished ... ----------------end------------------
[24 Jan 2018 0:23]
two pg
Passed the test. E:\TestMysql\x64\Release\TestMysql.exe MYSQL server version: 5.6.39-log MYSQL client version: 5.7.21 ----------------Test5---------------- ------------------------------------- sql = call SP_GET_TEST_TABLE(1) ---------------begin----------------- prepare, SELECT successful total parameters in SELECT: 0 total columns in SELECT statement: 4 Fetching results ... Query Finished ... ----------------end------------------
[24 Jan 2018 1:00]
two pg
This problem is very easy to repeat, You must use mysql server 5.7.x and libmysqlclient 5.7.x. We tested 5.7.11, 5.7.13, 5.7.17, 5.7.20, 5.7.21, all can find this problem. The main flow of our asynchronous library is similar to this code. https://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-execute.html see: https://bugs.mysql.com/file.php?id=26408&bug_id=89214
[24 Jan 2018 16:02]
MySQL Verification Team
Hi! Actually, I get a bit different results. Yes, the program prints out to stdout the same status messages: stmt_prepare begin stmt_prepare successful stmt_execute begin But, processlist is different: +----+--------+-----------------+-------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+-----------------+-------+---------+------+----------+------------------+ | 5 | sinisa | localhost:51017 | appdb | Sleep | 204 | | NULL | | 8 | sinisa | localhost | NULL | Query | 0 | starting | show processlist | +----+--------+-----------------+-------+---------+------+----------+------------------+ is that the same that you see ???? A long sleep ......
[25 Jan 2018 3:28]
two pg
No different, it's the same. please check the previous comment.
[25 Jan 2018 13:23]
MySQL Verification Team
Hi! I tried recoding your test case in several ways, but it never worked. Verified as reported.
[3 Oct 2018 1:40]
Paul DuBois
Posted by developer: Fixed in 5.7.25, 8.0.14. When executing a prepared statement with a procedure call with the CURSOR_TYPE_READ_ONLY flag set, the client library hung if the procedure performed a SELECT that returned an empty result set.
Description: // main.cpp #include <string.h> #include <mysql.h> #define SQLTEXT "call SP_GET_ACCOUNT_NAME(1)" int main(int argc, char **argv) { MYSQL *mysql; mysql = mysql_init(NULL); if (mysql == NULL) { fprintf(stderr, " mysql_init() error.\n"); exit(1); } if (!mysql_real_connect(mysql, "127.0.0.1", "root", "root", "appdb", 3306, NULL, 0)) { fprintf(stderr, " mysql_real_connect() error.\n"); exit(1); } MYSQL_STMT *stmt; stmt = mysql_stmt_init(mysql); if (!stmt) { fprintf(stderr, " mysql_stmt_init(), out of memory\n"); exit(1); } unsigned long type = (unsigned long)CURSOR_TYPE_READ_ONLY; unsigned long prefetch_rows = 64; mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void*)&type); if (mysql_stmt_attr_set(stmt, STMT_ATTR_PREFETCH_ROWS, (void*)&prefetch_rows)) { fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(1); } fprintf(stdout, "stmt_prepare begin\n"); if (mysql_stmt_prepare(stmt, SQLTEXT, strlen(SQLTEXT))) { fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(1); } fprintf(stdout, "stmt_prepare successful\n"); fprintf(stdout, "stmt_execute begin\n"); // BUG: deadlock if (mysql_stmt_execute(stmt)) { fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(1); } fprintf(stdout, "stmt_execute successful\n"); if (mysql_stmt_close(stmt)) { fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(1); } mysql_close(mysql); return 0; } -- ------------------------------------------------------- CREATE DATABASE IF NOT EXISTS `appdb` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `appdb`; -- MySQL dump 10.13 Distrib 5.7.17, for Win64 (x86_64) -- -- Host: 192.168.0.10 Database: appdb -- ------------------------------------------------------ -- Server version 5.7.20-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `account` -- DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `account` ( `uid` bigint(20) unsigned NOT NULL, `name` varchar(64) NOT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `account` -- LOCK TABLES `account` WRITE; /*!40000 ALTER TABLE `account` DISABLE KEYS */; /*!40000 ALTER TABLE `account` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping routines for database 'appdb' -- /*!50003 DROP PROCEDURE IF EXISTS `SP_GET_ACCOUNT_NAME` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE `SP_GET_ACCOUNT_NAME`(IN `puid` BIGINT unsigned) BEGIN SELECT `name` FROM `account` WHERE uid = puid; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-01-13 3:18:23 How to repeat: 1. libmysql version 5.7.x 2. mysql version 5.7.x 3. mysql on linux or windows