Bug #35939 Drift in Falcon row count reported by SHOW TABLE STATUS
Submitted: 9 Apr 2008 14:49 Modified: 8 Jan 2009 10:31
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0-BK OS:Any
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Triage: D3 (Medium)

[9 Apr 2008 14:49] Philip Stoev
Description:
When executing a workload involving creating and deleting records into a  table via triggers, SHOW TABLE STATUS will show an increasing number of records in that table, even though the table never has any rows (they are inserted with a BEFORE INSERT trigger and then immediately deleted using an AFTER INSERT trigger).

Over time, this will cause bad optimizer decisions. It appears that there is no way to fix this, since Falcon does not support OPTIMIZE TABLE.

How to repeat:
Test case will follow shortly.
[9 Apr 2008 15:09] Philip Stoev
Test case for bug 35939

Attachment: bug35939.zip (application/x-zip-compressed, text), 1.01 KiB.

[9 Apr 2008 15:16] Philip Stoev
To reproduce, please unpack the zip file so that the .txt files are placed in mysql-test and the .test files are placed in mysql-test/t. Then issue:

$ perl ./mysql-test-run.pl --stress --stress-init-file=bug35939_init.txt \
--stress-test-file=bug35939_run.txt --stress-test-duration=48200 \
--stress-threads=10 --skip-ndb

While it is running, please run a SHOW TABLE STATUS in a separate window. You will see that the count increases for both tables, even though inter1_log does not aquire any new records.

It appears that if the server is restarted or ALTER is issued, the row count resets to the correct value (zero or near-zero). However fixing things this way means forcing a service disruption.
[9 Apr 2008 17:00] Kevin Lewis
There is a disconnect here between show table status and what is really in the table.  After running the insert 11000 times;

mysql> show table status;
+------------+--------+---------+------------+-------+
| Name       | Engine | Version | Row_format | Rows  |
+------------+--------+---------+------------+-------+
| inter1     | Falcon |      10 | Dynamic    | 11000 |
| inter1_log | Falcon |      10 | Fixed      | 11000 |
+------------+--------+---------+------------+-------+
5 rows in set (0.00 sec)

mysql> select * from inter1_log;
Empty set (0.03 sec)

mysql> select count(*) from inter1;
+----------+
| count(*) |
+----------+
|    11000 |
+----------+
1 row in set (0.09 sec)
[29 Jul 2008 19:27] Philip Stoev
Here is a non-concurrent test expressed in Perl. If autocommit is used, then the reported row count is correct. Only if explicit transactions are used then the row count is wrong:

use strict;
use DBI;

my $dbh = DBI->connect('dbi:mysql:user=root:host=127.0.0.1:port=9306:database=test');

$dbh->do("DROP TABLE t1, t2");

$dbh->do("
        CREATE TABLE IF NOT EXISTS t1 (
                uuid CHAR(36) NOT NULL
        ) ENGINE = Falcon
");

$dbh->do("
        CREATE TABLE IF NOT EXISTS t2 (
                uuid CHAR(36) NOT NULL
        ) ENGINE = Falcon
");

$dbh->do("
        CREATE TRIGGER before_insert BEFORE INSERT ON t1
        FOR EACH ROW INSERT INTO t2 (uuid) VALUES (NEW.uuid)
");

$dbh->do("
        CREATE TRIGGER after_insert AFTER INSERT ON t1
        FOR EACH ROW DELETE FROM t2 WHERE uuid = NEW.uuid
");

foreach my $i (1..1000) {
        $dbh->do("SET AUTOCOMMIT=OFF");
        $dbh->do("START TRANSACTION");
        $dbh->do("INSERT INTO t1 (uuid) VALUES (UUID())");
        $dbh->do("COMMIT");
}

my $rows = $dbh->selectrow_array("
        SELECT TABLE_ROWS
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_NAME = 't2'
");

print "Rows are $rows\n";
[29 Jul 2008 22:19] Vladislav Vaintroub
I run the script on both Windows and Ubuntu, and it always reports 1000 rows.
moreover , I tried to increase the loop, put "SET AUTOCOMMIT=OFF" outside of the loop, 2 threads instead of 1, larger transaction (100/1000 inserts at a time) there result was always consistent.

So I can't reproduce the described behavior.
[30 Jul 2008 11:46] Philip Stoev
Vlad,

1000 rows is the *wrong* answer for table t2. This table has a row in it inserted by a BEFORE INSERT trigger, and this row is immediately deleted by an AFTER INSERT trigger. So the correct number of rows for this table is zero, which is what is shown when you do a SELECT COUNT(*). However, SHOW TABLE STATUS reports 1000 rows, which is *incorrect*.
[30 Jul 2008 15:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50732

2761 Vladislav Vaintroub	2008-07-30
      Bug#35939 - Drift in Falcon row count reported by SHOW TABLE STATUS
      The problem is that during recalculation of cardinality during commit. If a record was inserted and
      deleted in the same transaction, it was counted as added and cardinality would be incremented.
      
      This patch addresses this issue by ensuring table cardinality does not change if such a record
      is encountered.
[30 Jul 2008 21:16] Kevin Lewis
The code change looks good.  But the test case could use a couple minor tweeks, which I mention in the commit email.  OK to push.
[31 Jul 2008 10:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50767

2761 Vladislav Vaintroub	2008-07-31
      Bug#35939 - Drift in Falcon row count reported by SHOW TABLE STATUS
      The problem is that during recalculation of cardinality during commit. 
      If a record was inserted and deleted in the same transaction, it was 
      counted as new and cardinality would be incremented.
            
      This patch addresses this issue. Now table cardinality does not 
      change when such a record is encountered.
[22 Aug 2008 17:52] Kevin Lewis
Fix is in version 6.0.7
[14 Sep 2008 1:47] Bugs System
Pushed into 6.0.6-alpha  (revid:vvaintroub@mysql.com-20080731100430-2jwj8h1hhyk8lfxk) (version source revid:hakan@mysql.com-20080716160034-sdexuyp3qow7zlc6) (pib:3)
[8 Jan 2009 10:31] MC Brown
A note has been added to the 6.0.7 changelog: 

When using both an INSERT BEFORE trigger to create a row and AFTER INSERT trigger to delete the same row on a FALCON table, the record count as reported by SHOW TABLE STATUS could get out of sync with the actual record contents. This was caused by the changes now being correctly updated in the table status information.