MySQL Bugs: #35939: Drift in Falcon row count reported by SHOW TABLE STATUS

Bug #35939	Drift in Falcon row count reported by SHOW TABLE STATUS
Submitted:	9 Apr 2008 16:49	Modified:	8 Jan 11:31
Reporter:	Philip Stoev
Status:	Closed
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.0-BK	OS:	Any
Assigned to:	Vladislav Vaintroub	Target Version:
Triage:	D3 (Medium)

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.

Test case for bug 35939

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

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.

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)

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";

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.

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*.

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.

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.

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.

Fix is in version 6.0.7

Pushed into 6.0.6-alpha  (revid:vvaintroub@mysql.com-20080731100430-2jwj8h1hhyk8lfxk)
(version source revid:hakan@mysql.com-20080716160034-sdexuyp3qow7zlc6) (pib:3)

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.