MySQL Bugs: #13041: Wrong row count in information

Bug #13041	Wrong row count in information_schema.tables for innodb table
Submitted:	7 Sep 2005 14:35	Modified:	26 Sep 2005 14:03
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.11-beta-nt-max	OS:	Windows (WinXP sp2)
Assigned to:		CPU Architecture:	Any

Description:
Row count for InnoDB table in INFORMATION_SCHEMA.TABLES is wrong and gets altered by OPTIMIZE TABLE.

How to repeat:
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.0.11-beta-nt-max |
+--------------------+
1 row in set (0.02 sec)

mysql> select count(*) from prodotti;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.02 sec)

mysql> create table prodotti_archive engine=archive as select * from prodotti;
Query OK, 1000 rows affected (0.25 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> select table_name, engine, round(data_length/1024/1024,2) total_size_mb,
table_rows from information_schema.tables where table_schema = 'southwind' and t
able_name like 'prodotti%';
+------------------+---------+---------------+------------+
| table_name       | engine  | total_size_mb | table_rows |
+------------------+---------+---------------+------------+
| prodotti         | InnoDB  |          0.14 |        963 |
| prodotti_archive | ARCHIVE |          0.04 |       1000 |
+------------------+---------+---------------+------------+
2 rows in set (0.17 sec)

(you can see that the count is wrong for table "prodotti") then I issued an "optimize table"

mysql> optimize table prodotti;
+--------------------+----------+----------+----------+
| Table              | Op       | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| southwind.prodotti | optimize | status   | OK       |
+--------------------+----------+----------+----------+
1 row in set (0.45 sec)

mysql> select table_name, engine, round(data_length/1024/1024,2) total_size_mb,
table_rows from information_schema.tables where table_schema = 'southwind' and t
able_name like 'prodotti%';
+------------------+---------+---------------+------------+
| table_name       | engine  | total_size_mb | table_rows |
+------------------+---------+---------------+------------+
| prodotti         | InnoDB  |          0.11 |        547 |
| prodotti_archive | ARCHIVE |          0.04 |       1000 |
+------------------+---------+---------------+------------+
2 rows in set (0.01 sec)

The count has changed but it's wrong again.

DROP TABLE IF EXISTS `southwind`.`prodotti`;
CREATE TABLE `prodotti` (
  `IDProdotto` int(10) NOT NULL default '0',
  `NomeProdotto` varchar(40) NOT NULL,
  `IDFornitore` int(10) NOT NULL default '0',
  `IDCategoria` int(10) NOT NULL default '0',
  `Quantit_PerUnit` varchar(30) default NULL,
  `PrezzoUnitario` decimal(19,4) default NULL,
  `Scorte` smallint(5) default NULL,
  `Quantit_Ordinata` smallint(5) default NULL,
  `LivelloDiRiordino` smallint(5) default NULL,
  `Sospeso` char(1) NOT NULL default '',
  PRIMARY KEY  (`IDProdotto`,`IDFornitore`,`IDCategoria`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 11264 kB; (`IDCategoria`) REFER `southwind/cate';

I tried to repeat your test case on 5.0.12-nt, but with no luck:

mysql> CREATE TABLE `prodotti` (
    ->   `IDProdotto` int(10) NOT NULL default '0',
    ->   `NomeProdotto` varchar(40) NOT NULL,
    ->   `IDFornitore` int(10) NOT NULL default '0',
    ->   `IDCategoria` int(10) NOT NULL default '0',
    ->   `Quantit_PerUnit` varchar(30) default NULL,
    ->   `PrezzoUnitario` decimal(19,4) default NULL,
    ->   `Scorte` smallint(5) default NULL,
    ->   `Quantit_Ordinata` smallint(5) default NULL,
    ->   `LivelloDiRiordino` smallint(5) default NULL,
    ->   `Sospeso` char(1) NOT NULL default '',
    ->   PRIMARY KEY  (`IDProdotto`,`IDFornitore`,`IDCategoria`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.81 sec)

mysql> insert into prodotti(`IDProdotto`, `NomeProdotto`, `IDFornitore`, `IDCategoria`) values(1, '1', 1, 1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into prodotti(`IDProdotto`, `NomeProdotto`, `IDFornitore`, `IDCategoria`) values(1, '1', 1, 2);
Query OK, 1 row affected (0.05 sec)

mysql> insert into prodotti(`IDProdotto`, `NomeProdotto`, `IDFornitore`, `IDCategoria`) values(1, '1', 1, 3);
Query OK, 1 row affected (0.04 sec)

mysql> insert into prodotti(`IDProdotto`, `NomeProdotto`, `IDFornitore`, `IDCategoria`) values(2, '1', 1, 1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into prodotti(`IDProdotto`, `NomeProdotto`, `IDFornitore`, `IDCategoria`) values(2, '1', 1, 2);
Query OK, 1 row affected (0.04 sec)

mysql> insert into prodotti(`IDProdotto`, `NomeProdotto`, `IDFornitore`, `IDCategoria`) values(2, '1', 1, 3);
Query OK, 1 row affected (0.04 sec)

mysql> select count(*) from prodotti;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.02 sec)

mysql> create table prodotti_archive engine=archive as select * from prodotti;
Query OK, 6 rows affected (0.20 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select table_name, engine, round(data_length/1024/1024,2) total_size_mb,
    -> table_rows from information_schema.tables where table_schema = 'test' and table_name like 'prodotti%';
+------------------+---------+---------------+------------+
| table_name       | engine  | total_size_mb | table_rows |
+------------------+---------+---------------+------------+
| prodotti         | InnoDB  | 0.02          |          6 |
| prodotti_archive | ARCHIVE | 0.00          |          6 |
+------------------+---------+---------------+------------+
2 rows in set (0.23 sec)

mysql> optimize table prodotti;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.prodotti | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.42 sec)

mysql> select table_name, engine, round(data_length/1024/1024,2) total_size_mb,
    -> table_rows from information_schema.tables where table_schema = 'test' and table_name like 'prodotti%';
+------------------+---------+---------------+------------+
| table_name       | engine  | total_size_mb | table_rows |
+------------------+---------+---------------+------------+
| prodotti         | InnoDB  | 0.02          |          6 |
| prodotti_archive | ARCHIVE | 0.00          |          6 |
+------------------+---------+---------------+------------+
2 rows in set (0.01 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

So, it looks like you have to describe the actions with this table (before optimizing) more precisely, or upload the real data, or provide any other additinal information on how to repeat the problem.

Probably due to wrong database update