SHOW TABLE STATUS is used needlessly

[Baron Schwartz]

In SchemaIterator::_iterate_dbh we have this:

   while ( my $tbl = shift @{$self->{tbls}} ) {
      my $tbl_status;
      if ( $self->{filters}->{'engines'}
           || $self->{filters}->{'ignore-engines'}
           || $self->{keep_tbl_status} )
      {
         my $sql = "SHOW TABLE STATUS FROM " . $q->quote($self->{db})
                 . " LIKE \'$tbl\'";

We should not get the engine from SHOW TABLE STATUS, but from SHOW CREATE TABLE. This is a potentially serious performance impact on the database server.

[Daniel Nichter (daniel-nichter)]

pt-table-sync 2.0+ is using an old version of SchemaIterator. I think this is because I didn't want to mess with the tool's internals. But I'm going to update it now because I can see how to make the most minimal changes. Then I'll update SchemaIterator to avoid SHOW TABLE STATUS, update the module in all tools, and everything should continue to work.

[Daniel Nichter (daniel-nichter)]

Note to self: pt-osc-2.1. is going to conflict with this branch in NibbleIterator.pm because both branches updated that module. I need to keep get_row_estimate() from this branch, and keep everything else from the pt-osc-2.1 branch (which is just bug fixes and refactoring).

[Daniel Nichter (daniel-nichter)]

Summary of changes:

1. SchemaIterator now requires a TableParser
2. For every table, it uses the TableParser to get_create_table(), which is saves as "ddl", then parse(ddl), which it saves as "tbl_struct"
3. Every object data struct it returns, which passes all filters, is like:

{
db => unquoted db name
tbl => unquoted table name
name => Quoter::quote(db, tbl)
ddl => SHOW CREATE TABLE
tbl_struct => parsed ddl
}

4. The newest TableParser is required, so tools with updated SchemaIterator also got an updated TableParser. This updated TableParser can/does replace MySQLDump
5. The same updated modules also need the updated NibbleIterator because previously NibbleIterator used the result of SHOW TABLE STATUS for row_est (row estimate)

[Shahriyar Rzayev (rzayev-sehriyar)]

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