Bug #19417 Error Querying the information schema

• Submitted: 28 Apr 2006 8:19
• Modified: 30 Apr 2006 1:08
• Reporter: Dennis George
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.20a-nt
• OS: Windows (Windows XP SP2 german)
• CPU Architecture: Any

[28 Apr 2006 8:19] Dennis George

Description:
Hello,

The following query returns different information about the rows using in
a table   (Database: Information_schema, Table: tables, column:
table_rows)

No other client is connected to the server. I am using the commandline
interface. 

Server-version: 5.0.20a-nt, OS: WindowsXP german SP2
All Tables in the database are innodb.

The database contains views, funcitons and triggers according to the
exercises of the "mysql5 for  developers" training. 
Another student had the same error but not everyone.

First Trigger on the Database Country:

CREATE DEFINER='root@localhost' TRIGGER `world2`.`tr1` AFTER DELETE ON `world2`.`country`
  FOR EACH ROW BEGIN
delete from city where countrycode = OLD.code;
delete from countrylanguage where countrycode = OLD.code;
end;

second trigger:

CREATE DEFINER='root@localhost' TRIGGER `world2`.`tr2` AFTER UPDATE ON `world2`.`country`
  FOR EACH ROW begin
update city set countrycode = NEW.code where countrycode = OLD.code;
update countrylanguage set countrycode = NEW.code where countrycode = OLD.code;
end;

Here are the results:

mysql> select table_rows from tables where table_name = 'Country'
and table_schema='world2';

+------------+
| table_rows |
+------------+
| 253        |
+------------+
1 row in set (0.00 sec)

mysql> select table_rows from tables where table_name = 'Country'
and table_schema='world2';

+------------+
| table_rows |
+------------+
| 236        |
+------------+
1 row in set (0.00 sec)

mysql> select table_rows from tables where table_name = 'Country'
and table_schema='world2';
+------------+
| table_rows |
+------------+
| 196        |
+------------+
1 row in set (0.00 sec)

mysql> select table_rows from tables where table_name = 'Country'
and table_schema='world2';

+------------+
| table_rows |
+------------+
| 253        |
+------------+
1 row in set (0.00 sec)

mysql> select table_rows from tables where table_name = 'Country'
and table_schema='world2';
+------------+
| table_rows |
+------------+
| 236        |
+------------+
1 row in set (0.00 sec)

Greetings,

Dennis George
Grünstrasse 44, 41199 Mönchengladbach
Germany
eMail: [e-mail]

How to repeat:
see description

Suggested fix:
no

[28 Apr 2006 8:25] Dennis George

SQL-Dump of the Database with triggers ....

Attachment: world_with_triggers.zip (application/x-zip-compressed, text), 78.29 KiB.

[30 Apr 2006 1:08] MySQL Verification Team

Thank you for the bug report. Please read below regarding restrictions
with the InnoDB engine:

http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

#

SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except
for the physical size reserved by the table. The row count is only a rough
estimate used in SQL optimization.
#

InnoDB does not keep an internal count of rows in a table. (In practice, this
would be somewhat complicated due to multi-versioning.) To process a
SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table,
which takes some time if the index is not entirely in the buffer pool.
To get a fast count, you have to use a counter table you create yourself and
let your application update it according to the inserts and deletes it does.
If your table does not change often, using the MySQL query cache is a
good solution. SHOW TABLE STATUS also can be used if an approximate row count
is sufficient. See Section 14.2.11, “InnoDB Performance Tuning Tips”. 

c:\mysql\bin>mysql -uroot information_schema
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.20a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select table_rows from tables where table_name = 'Country'
    -> and table_schema='world2';
+------------+
| table_rows |
+------------+
| 231        |
+------------+
1 row in set (0.00 sec)

mysql> select table_rows from tables where table_name = 'Country'
    -> and table_schema='world2';
+------------+
| table_rows |
+------------+
| 237        |
+------------+
1 row in set (0.02 sec)

mysql> alter table world2.country engine=MyISAM;
Query OK, 238 rows affected (0.14 sec)
Records: 238  Duplicates: 0  Warnings: 0

mysql> select table_rows from tables where table_name = 'Country'
    -> and table_schema='world2';
+------------+
| table_rows |
+------------+
| 238        |
+------------+
1 row in set (0.02 sec)

mysql> select table_rows from tables where table_name = 'Country'
    -> and table_schema='world2';
+------------+
| table_rows |
+------------+
| 238        |
+------------+
1 row in set (0.00 sec)

mysql> select table_rows from tables where table_name = 'Country'
    -> and table_schema='world2';
+------------+
| table_rows |
+------------+
| 238        |
+------------+
1 row in set (0.00 sec)

mysql>