| Bug #69119 | Wrong FOUND_ROWS() on MySQL 5.6.11 | ||
|---|---|---|---|
| Submitted: | 2 May 2013 2:51 | Modified: | 3 Jun 2013 14:26 |
| Reporter: | 靖博 今野 | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.6.11 | OS: | Linux (CentOS 6.4) |
| Assigned to: | Tor Didriksen | CPU Architecture: | Any |
| Tags: | found_rows, regression | ||
[3 May 2013 3:27]
Piotr Tomasik
I can confirm that I still have FOUND_ROWS Problem on innoDB as well. Mysql 5.6.11 OSX Downgraded to 5.5.* and it was ok again
[3 May 2013 11:01]
MySQL Verification Team
Hello, Thank you for the report. Verified as described. Thanks, Umesh
[3 May 2013 11:03]
MySQL Verification Team
######### Affected mysql-5.6.11
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.6.11-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test_found_rows_myisam (
-> pkey char(11) NOT NULL,
-> ivalue smallint(6) NOT NULL,
-> PRIMARY KEY (`pkey`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
mysql>
mysql> INSERT INTO test_found_rows_myisam
-> VALUES
-> ('4548870', -10000),
-> ('4600641', -10000),
-> ('4718998', -10000),
-> ('4737258', -10000),
-> ('4737259', -10000),
-> ('4769408', -10000),
-> ('4806434', -10000),
-> ('4891119', -10000),
-> ('4897306', -10000),
-> ('4982112', -10000),
-> ('5489466', -10000),
-> ('5531955', -10000),
-> ('5837345', -10000),
-> ('6069196', -10000),
-> ('6756422', -10000),
-> ('6839510', -10000),
-> ('7082263', -10000),
-> ('7087671', -10000),
-> ('7352152', -10000);
Query OK, 19 rows affected (0.13 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
-> FROM test_found_rows_myisam
-> ORDER BY ivalue DESC, pkey DESC
-> LIMIT 0,10;
+---------+--------+
| pkey | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.01 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 10 |
+--------------+
1 row in set (0.06 sec)
mysql>
mysql> CREATE TABLE test_found_rows_innodb (
-> pkey char(11) NOT NULL,
-> ivalue smallint(6) NOT NULL,
-> PRIMARY KEY (`pkey`)
-> ) ENGINE=Innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> INSERT INTO test_found_rows_innodb
-> VALUES
-> ('4548870', -10000),
-> ('4600641', -10000),
-> ('4718998', -10000),
-> ('4737258', -10000),
-> ('4737259', -10000),
-> ('4769408', -10000),
-> ('4806434', -10000),
-> ('4891119', -10000),
-> ('4897306', -10000),
-> ('4982112', -10000),
-> ('5489466', -10000),
-> ('5531955', -10000),
-> ('5837345', -10000),
-> ('6069196', -10000),
-> ('6756422', -10000),
-> ('6839510', -10000),
-> ('7082263', -10000),
-> ('7087671', -10000),
-> ('7352152', -10000);
Query OK, 19 rows affected (0.08 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
-> FROM test_found_rows_innodb
-> ORDER BY ivalue DESC, pkey DESC
-> LIMIT 0,10;
+---------+--------+
| pkey | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.01 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 19 |
+--------------+
1 row in set (0.01 sec)
mysql>
[3 May 2013 11:04]
MySQL Verification Team
######### not affected mysql-5.5.31
[root@ushastry mysql-5.5.31]# bin/mysql -u root -p --port=3306 --protocol=TCP test
Server version: 5.5.31-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE test_found_rows_myisam (
-> pkey char(11) NOT NULL,
-> ivalue smallint(6) NOT NULL,
-> PRIMARY KEY (`pkey`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO test_found_rows_myisam
-> VALUES
-> ('4548870', -10000),
-> ('4600641', -10000),
-> ('4718998', -10000),
-> ('4737258', -10000),
-> ('4737259', -10000),
-> ('4769408', -10000),
-> ('4806434', -10000),
-> ('4891119', -10000),
-> ('4897306', -10000),
-> ('4982112', -10000),
-> ('5489466', -10000),
-> ('5531955', -10000),
-> ('5837345', -10000),
-> ('6069196', -10000),
-> ('6756422', -10000),
-> ('6839510', -10000),
-> ('7082263', -10000),
-> ('7087671', -10000),
-> ('7352152', -10000);
Query OK, 19 rows affected (0.00 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
-> FROM test_found_rows_myisam
-> ORDER BY ivalue DESC, pkey DESC
-> LIMIT 0,10;
+---------+--------+
| pkey | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 19 |
+--------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> CREATE TABLE test_found_rows_innodb (
-> pkey char(11) NOT NULL,
-> ivalue smallint(6) NOT NULL,
-> PRIMARY KEY (`pkey`)
-> ) ENGINE=Innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO test_found_rows_innodb
-> VALUES
-> ('4548870', -10000),
-> ('4600641', -10000),
-> ('4718998', -10000),
-> ('4737258', -10000),
-> ('4737259', -10000),
-> ('4769408', -10000),
-> ('4806434', -10000),
-> ('4891119', -10000),
-> ('4897306', -10000),
-> ('4982112', -10000),
-> ('5489466', -10000),
-> ('5531955', -10000),
-> ('5837345', -10000),
-> ('6069196', -10000),
-> ('6756422', -10000),
-> ('6839510', -10000),
-> ('7082263', -10000),
-> ('7087671', -10000),
-> ('7352152', -10000);
Query OK, 19 rows affected (0.14 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
-> FROM test_found_rows_innodb
-> ORDER BY ivalue DESC, pkey DESC
-> LIMIT 0,10;
+---------+--------+
| pkey | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 19 |
+--------------+
1 row in set (0.00 sec)
mysql>
[3 May 2013 11:20]
MySQL Verification Team
## Works in 5.6.10
Server version: 5.6.10-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE test_found_rows_myisam (
-> pkey char(11) NOT NULL,
-> ivalue smallint(6) NOT NULL,
-> PRIMARY KEY (`pkey`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO test_found_rows_myisam
-> VALUES
-> ('4548870', -10000),
-> ('4600641', -10000),
-> ('4718998', -10000),
-> ('4737258', -10000),
-> ('4737259', -10000),
-> ('4769408', -10000),
-> ('4806434', -10000),
-> ('4891119', -10000),
-> ('4897306', -10000),
-> ('4982112', -10000),
-> ('5489466', -10000),
-> ('5531955', -10000),
-> ('5837345', -10000),
-> ('6069196', -10000),
-> ('6756422', -10000),
-> ('6839510', -10000),
-> ('7082263', -10000),
-> ('7087671', -10000),
-> ('7352152', -10000);
Query OK, 19 rows affected (0.00 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
-> FROM test_found_rows_myisam
-> ORDER BY ivalue DESC, pkey DESC
-> LIMIT 0,10;
+---------+--------+
| pkey | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.00 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 19 |
+--------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test_found_rows_innodb (
-> pkey char(11) NOT NULL,
-> ivalue smallint(6) NOT NULL,
-> PRIMARY KEY (`pkey`)
-> ) ENGINE=Innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> INSERT INTO test_found_rows_innodb
-> VALUES
-> ('4548870', -10000),
-> ('4600641', -10000),
-> ('4718998', -10000),
-> ('4737258', -10000),
-> ('4737259', -10000),
-> ('4769408', -10000),
-> ('4806434', -10000),
-> ('4891119', -10000),
-> ('4897306', -10000),
-> ('4982112', -10000),
-> ('5489466', -10000),
-> ('5531955', -10000),
-> ('5837345', -10000),
-> ('6069196', -10000),
-> ('6756422', -10000),
-> ('6839510', -10000),
-> ('7082263', -10000),
-> ('7087671', -10000),
-> ('7352152', -10000);
Query OK, 19 rows affected (0.01 sec)
Records: 19 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue
-> FROM test_found_rows_innodb
-> ORDER BY ivalue DESC, pkey DESC
-> LIMIT 0,10;
+---------+--------+
| pkey | ivalue |
+---------+--------+
| 7352152 | -10000 |
| 7087671 | -10000 |
| 7082263 | -10000 |
| 6839510 | -10000 |
| 6756422 | -10000 |
| 6069196 | -10000 |
| 5837345 | -10000 |
| 5531955 | -10000 |
| 5489466 | -10000 |
| 4982112 | -10000 |
+---------+--------+
10 rows in set (0.01 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 19 |
+--------------+
1 row in set (0.00 sec)
mysql>
[4 May 2013 10:54]
MySQL Verification Team
Seems I filed a duplicate by mistake (but my testcase is nicer :) http://bugs.mysql.com/bug.php?id=69144
[4 May 2013 10:57]
Piotr Tomasik
@ Shane Bester At least you proved that it was also a problem on innodb and not specifically myisam
[8 May 2013 9:05]
Tor Didriksen
oops, this is a regression caused by the patch for Bug#68458 Wrong count from FOUND_ROWS() on MySQL 5.6
[30 May 2013 5:12]
Erlend Dahl
Bug#69166 was marked as a duplicate.
[3 Jun 2013 14:26]
Paul DuBois
Noted in 5.6.12, 5.7.2 changelogs. FOUND_ROWS() could return an incorrect value if the preceding query used filesort.
Description: FOUND_ROWS() return is wrong count. The innoDB is OK, but It does not work on MyISAM. How to repeat: mysql> use test; Database changed mysql> mysql> CREATE TABLE test_found_rows ( -> pkey char(11) NOT NULL, -> ivalue smallint(6) NOT NULL, -> PRIMARY KEY (`pkey`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 -> ; INSERT INTO test_found_rows VALUES ('4548870', -10000), ('4600641', -10000), ('4718998', -10000), ('4737258', -10000), ('4737259', -10000), ('4769408', -10000), ('4806434', -10000), ('4891119', -10000), ('4897306', -10000), ('4982112', -10000), ('5489466', -10000), ('5531955', -10000), ('5837345', -10000), ('6069196', -10000), ('6756422', -10000), Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO test_found_rows -> VALUES -> ('4548870', -10000), -> ('4600641', -10000), -> ('4718998', -10000), -> ('4737258', -10000), -> ('4737259', -10000), -> ('4769408', -10000), -> ('4806434', -10000), -> ('4891119', -10000), -> ('4897306', -10000), -> ('4982112', -10000), -> ('5489466', -10000), -> ('5531955', -10000), -> ('5837345', -10000), -> ('6069196', -10000), -> ('6756422', -10000), -> ('6839510', -10000), -> ('7082263', -10000), -> ('7087671', -10000), -> ('7352152', -10000) -> ; Query OK, 19 rows affected (0.00 sec) Records: 19 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM test_found_rows; +---------+--------+ | pkey | ivalue | +---------+--------+ | 4548870 | -10000 | | 4600641 | -10000 | | 4718998 | -10000 | | 4737258 | -10000 | | 4737259 | -10000 | | 4769408 | -10000 | | 4806434 | -10000 | | 4891119 | -10000 | | 4897306 | -10000 | | 4982112 | -10000 | | 5489466 | -10000 | | 5531955 | -10000 | | 5837345 | -10000 | | 6069196 | -10000 | | 6756422 | -10000 | | 6839510 | -10000 | | 7082263 | -10000 | | 7087671 | -10000 | | 7352152 | -10000 | +---------+--------+ 19 rows in set (0.00 sec) mysql> mysql> mysql> mysql> SELECT SQL_CALC_FOUND_ROWS pkey ,ivalue -> FROM test_found_rows -> ORDER BY ivalue DESC, pkey DESC -> LIMIT 0,10 -> ; +---------+--------+ | pkey | ivalue | +---------+--------+ | 7352152 | -10000 | | 7087671 | -10000 | | 7082263 | -10000 | | 6839510 | -10000 | | 6756422 | -10000 | | 6069196 | -10000 | | 5837345 | -10000 | | 5531955 | -10000 | | 5489466 | -10000 | | 4982112 | -10000 | +---------+--------+ 10 rows in set (0.00 sec) mysql> mysql> mysql> SELECT FOUND_ROWS(); +--------------+ | FOUND_ROWS() | +--------------+ | 10 | +--------------+ 1 row in set (0.00 sec)