Bug #36672 SHOW TABLE STATUS inconsistent on Falcon
Submitted: 12 May 2008 19:42 Modified: 29 Jul 2008 14:46
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:6.0.4 OS:Windows (probably any)
Assigned to: MC Brown CPU Architecture:Any
Tags: qc

[12 May 2008 19:42] Peter Laursen
Description:
while researching on
http://bugs.mysql.com/bug.php?id=36588

I found a small issue with "show table status" on falcon.

I am running this loop inside a Stored Procedure

start transaction;
set innercount = 0;
while innercount <100000 do
insert into tab (t) values (hex(now()));
set innercount = innercount + 1;
end while;
commit;

table is defined like:
create table tab (id integer primary key auto_increment, ts timestamp, t varchar(50))

While nothing is yet committed

"show table status from `test22`where Engine IS NOT NULL;"

.. seems always to return '2' for 'row_count'.  Hovever 'autoincrement' shows the current status of the loop! 

How to repeat:
se above!

Suggested fix:
As long as noting is commited both rowcount and autoincrement should be 0 (zero).
[12 May 2008 19:44] Peter Laursen
show table status etc. as displayed in SQLyog OBJECTS tab.

Attachment: not consistent.jpg (image/jpeg, text), 19.45 KiB.

[12 May 2008 21:42] Ann Harrison
I think this is not a bug, but simply a difference between Falcon
and InnoDB.  The documentation for show table status says that
the InnoDB Rows return is unreliable.  Falcon's is somewhat more
inexact.  The documentation also says "Auto_increment" - "The next 
AUTO_INCREMENT value."  Falcon generates auto-increment values 
using sequences.  When a sequence value is assigned, it is gone,
so Falcon's report on the next auto-increment value is correct,
though, again, different from InnoDB's report.

That said, Falcon's handling of Show Table Status is significantly
different from that of other storage engines and is going to be
an issue with users.
[13 May 2008 10:57] Sveta Smirnova
Thank you for the report.

According to  Auto_increment. Please add

insert into tab (t) values (hex(now()));
select * from tab;

to your test. This will show value of Auto_increment is correct.

As behavior of "Rows" is not documented, I'll reclassify this part of bug as documentation issue.

Use SELECT COUNT(*) to get correct amount of rows in the table.
[13 May 2008 20:01] Peter Laursen
Personally I think this is more than a doc request.

With a large number of rows I can (unwillingly!) accept that 'rowcount' is not 100% accurate.  But when the correct value is 0 (zero) it should not report anything else but 0 (zero).  The inaccuracy between 0 and 2 is *infinitely large* (relatively).
[21 May 2008 13:57] Hakan Küçükyılmaz
Can't repeat with latest Falcon on Linux:

MyISAM:
6.0.6-alpha-debug
[15:57] root@test>select count(*) from tab;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.00 sec)

[15:57] root@test>drop schema test;
Query OK, 1 row affected (0.02 sec)

Falcon:

[15:58] root@test>select count(*) from tab;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (4.86 sec)

[15:58] root@test>drop schema test;
Query OK, 1 row affected (0.15 sec)
[21 May 2008 13:57] Hakan Küçükyılmaz
Sorry, please ignore my last comment. It was intended for Bug#36673.
[29 Jul 2008 14:46] MC Brown
The documentation has been updated to note the behavioral differences between Falcon and other storage engines, both for SHOW TABLE STATUS during transactions, and auto_increment fields.