pt-online-schema-change fails when table is empty

I tested with pt-osc 2.1.2 and mysql 5.1.63-rel13.4 Percona Server (GPL), 13.4, Revision 443, but couldn't reproduce it it. (tested with full table, and then truncated and tested with both, even with innodb_stats_auto_update = OFF).

However, from what I checked in the code, you should be able to alleviate this --nocheck-plan for those tables.

i havent deleted the table using truncate but using DELETE FROM, maybe this behaves a bit differently in that manner.

True, EXPLAIN row estimates can be off by a bit, but 4,686,227 vs. 0 is a pretty big difference. Maybe XtraDB allows a wider margin of error, but I can't reproduce this either with regular InnoDB. Perhaps something else is causing this problem.

Moritz, if you can reproduce the problem, can you run with PTDEBUG=1 and attach the output please?

I vaguely recall some DELETE performance optimizations that might be a factor, so I'll ask the XtraDB developers what they think about this.

Daniel,
no problem. I have attached the output with PTDEBUG enabled. Hope it helps!

Moritz, thanks for the debug. Vadim said that DELETE shouldn't cause a statistics problem like this, else it's a variable setting or a bug. From the debug, we see:

# rows => '6031531',

So MySQL is definitely reporting that many rows, which causes the tool to do:

SELECT /*!40001 SQL_NO_CACHE */ `post_id` FROM `database`.`big_test_table` FORCE INDEX(`PRIMARY`) ORDER BY `post_id` LIMIT 1 /*first lower boundary*/

But no rows are returned, which causes the crash because the tool expects rows at this point. I can make the tool handle when that SQL returns no rows (i.e. if there's no first row, then there's no rows at all), but I'd also like to find out why MySQL is so wrong about the row count.

What is the value of innodb_stats_auto_update on your system? And while we're at it, what's the row for the table in question from INFORMATION_SCHEMA.INNODB_TABLE_STATS?

Daniel,
thanks for working on this.

The metrics you requested:

Variable_name: innodb_stats_auto_update
        Value: 1

        rows: 5024984
  clust_size: 200960
  other_size: 53779
    modified: 170620

(When i reproduced the problem yesterday i did that on a copy of that table, i deleted the copy therefore i can only provide you with stats from original. The original problem however occured on the original, i conclude it does not make any difference)

Moritz,

Thanks for the values. They may explain why the server hasn't update the stats yet. According to http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_stats.html?id=percona-server:features:innodb_stats

"""
If the value of modified column exceeds “rows / 16” or 2000000000, the statistics recalculation is done when innodb_stats_auto_update == 1.
"""

5024984 / 16 = 314,061.5 which is < 170620 (modified), so no update. I'll have Vadim double check my reading of this. If correct, it at least explains why MySQL/XtraDB is behaving this way.

Moritz,

Could you download the latest pt-osc from trunk (download link at http://bazaar.launchpad.net/~percona-toolkit-dev/percona-toolkit/2.1/view/head:/bin/pt-online-schema-change) and try again please? It should say "<table> is empty, no rows to copy." and then proceed to work normally.

Since I can't reproduce this locally, I wasn't able to write a test case, but the new code is pretty simple and it doesn't break any of the existing tests.

Daniel,

i verified your fix, for my specific test case it does work now.
Thanks again!

Excellent, thanks Moritz.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-550