MySQL Bugs: #42596: select count(*)

Bug #42596	select count(*)
Submitted:	4 Feb 2009 15:21	Modified:	5 Mar 2009 10:56
Reporter:	Andrea Villardino	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.30-community	OS:	Linux (CentOS release 5.2 (Final))
Assigned to:		CPU Architecture:	Any
Tags:	count, SELECT

Description:
I executed:
truncate table LOGINTRACE;
Query OK, 0 rows affected (0.02 sec)

Then:
select count(*) from LOGINTRACE;
+----------+
| count(*) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

Then:
select*from LOGINTRACE;
Empty set (0.00 sec)

Then:
select count(LOGIN) from LOGINTRACE;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Then again:
select count(*) from LOGINTRACE;
+----------+
| count(*) |
+----------+
|       36 |
+----------+
1 row in set (0.00 sec)

How to repeat:
I noticed this strange behaviour after a load test, which stressed the database.

Suggested fix:
Workaround: Insert new rows in the table and the count begins to count right.

Thank you for a problem report. Please, send the results of:

show create table LOGINTRACE\G

Any ideas on how to repeat this problem are also welcomed.

show create table LOGINTRACE\G
*************************** 1. row ***************************
       Table: LOGINTRACE
Create Table: CREATE TABLE `LOGINTRACE` (
  `IDFORN` int(11) NOT NULL,
  `LOGIN` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `ACTIVITYID` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `LOGINTIME` datetime NOT NULL,
  PRIMARY KEY (`IDFORN`,`LOGIN`,`ACTIVITYID`,`LOGINTIME`),
  KEY `IX_LOGINTRACE_IDFORN` (`IDFORN`),
  KEY `IX_LOGINTRACE_LOGIN` (`LOGIN`),
  KEY `IX_LOGINTRACE_ACTIVITYID` (`ACTIVITYID`),
  KEY `IX_LOGINTRACE_IDFLOG` (`IDFORN`,`LOGIN`),
  CONSTRAINT `LOGINTRACE_ibfk_1` FOREIGN KEY (`ACTIVITYID`) REFERENCES `ATTIVITA` (`IDATTIVITA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Please, upload also your error log (compressed), at least from the moment just before TRUNCATE.

Clients are java applications running on JBOSS 4.0.4. The driver I'm using is JDBC mysql-connector-java-5.1.7-bin.jar.
I suppose that the messages "Aborted connection" you can see in the mysql error log are caused by not properly ended connections from developers, because they never complained about that during their work, even if I warned them to tell if they see any problem about connection to database.

Thank you for the feedback.

This looks like duplicate of bug #40386. Please check if you have query cache turned on.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".