Bug #76846 No of rows for table showing differntly

• Submitted: 27 Apr 2015 7:12
• Modified: 14 May 2015 11:04
• Reporter: Arun Pandi
• Status: Can't repeat
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.6.12-log
• OS: Windows
• CPU Architecture: Any

[27 Apr 2015 7:12] Arun Pandi

Description:
I am trying to learn the table partitions and partitions type. 
I have created below table.
CREATE TABLE `players_key` (
  `id` int(10) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mobile` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `skype_id` varchar(255) DEFAULT NULL,
  `facebook_id` varchar(255) DEFAULT NULL,
  `twitter_id` varchar(255) DEFAULT NULL,
  `linkedin_id` varchar(255) DEFAULT NULL,
  `zip` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `web_site` varchar(255) DEFAULT NULL,
  `height` varchar(255) DEFAULT NULL,
  `weight` varchar(255) DEFAULT NULL,
  `profile_status` varchar(255) DEFAULT NULL,
  `address` text,
  `about_me` text,
  `gender` enum('m','f') DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `smoking` enum('y','n','p') DEFAULT NULL,
  `drinking` enum('y','n','p') DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `status` enum('1','0') DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `player_types_id` int(11) DEFAULT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ip_address` varchar(50) DEFAULT NULL,
  `activation_code` varchar(255) DEFAULT NULL,
  `remember_me_token` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (id)
PARTITIONS 10 */

And, i inserted around 250 records repeatedly in the table using while loop. 
Now, i just tried to check the number of records available in the table. In information schema and explain statements returns 17763105 records. 
But, count(*) and max(auto increment id)  gives 19362408. Which one is right? It making me confuse. I can't check it manually because it have more records. 

SELECT information_schema.tables.*
  FROM information_schema.tables 
 WHERE table_schema = <'db'>
   AND table_name = 'players_key';
/*   
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
def	            <db>	            players_key	BASE TABLE	InnoDB	10	      Compact	17763105	296	            5274337280	0	            0	            51380224	\N	            \N	      \N	      \N	      latin1_swedish_ci	\N	      partitioned	
*/

SELECT COUNT(*) FROM players_key;
/*
count(*)
19362408
*/

SELECT MAX(id) FROM players_key;
/*
max(id)
19362408
*/

How to repeat:
1) create a table using key partitioning. 
2) insert more than 1 crore records. 
and test the queries i have given.

[14 May 2015 11:04] MySQL Verification Team

Hello Arun,

Thank you for the report.
MySQL version 5.6.12 is very old and many bugs were fixed since. Please upgrade to current version 5.6.24, try with it and inform us if problem persists. I see similar issue reported Bug #69168 - which was again reported against 5.6.11/5.6.12 and fixed in 5.6.14.

Thank you for your interest in MySQL.

Thanks,
Umesh