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: | |
Category: | MySQL Server: Falcon storage engine | Severity: | S3 (Non-critical) |
Version: | 6.0-BK | OS: | Any |
Assigned to: | Vladislav Vaintroub | CPU Architecture: | Any |
[9 Apr 2008 14:49]
Philip Stoev
[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.