Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8471

No progress report for online DDL of InnoDB tables

    XMLWordPrintable

Details

    Description

      Progress reports for online DDL on InnoDB tables does not appear to be working, even after the fix for MDEV-8179.

      For example, if I have this table:

      CREATE TABLE db1.test_table (
         id INT AUTO_INCREMENT PRIMARY KEY,
         file BLOB
      );

      And then I execute this:

      ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL;

      The progress printed to the terminal is stuck at 25% of stage 1, and SHOW PROCESSLIST is stuck at 0%:

      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State          | Info                                                           | Progress |
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   22 | altering table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL |    0.000 |
      |  7 | root | localhost | NULL | Query   |    0 | init           | SHOW PROCESSLIST                                               |    0.000 |
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State          | Info                                                           | Progress |
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   23 | altering table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL |    0.000 |
      |  7 | root | localhost | NULL | Query   |    0 | init           | SHOW PROCESSLIST                                               |    0.000 |
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State          | Info                                                           | Progress |
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   25 | altering table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL |    0.000 |
      |  7 | root | localhost | NULL | Query   |    0 | init           | SHOW PROCESSLIST                                               |    0.000 |
      +----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
      2 rows in set (0.00 sec)

      However, if I perform an operation that forces a table copy, such as changing a column's data type, I get a progress report:

      ALTER TABLE db1.test_table MODIFY COLUMN file longblob;

      The progress report:

      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   36 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   48.527 |
      |  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   37 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   49.667 |
      |  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   38 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   50.787 |
      |  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> SHOW PROCESSLIST;
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      | Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      |  6 | root | localhost | NULL | Query   |   39 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   52.329 |
      |  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
      +----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
      2 rows in set (0.01 sec)

      Should progress reporting work for online ALTER TABLE in 10.0? I see no mention that it shouldn't in the KB:

      https://mariadb.com/kb/en/mariadb/progress-reporting/

      https://mariadb.com/kb/en/mariadb/alter-table/#progress-reports

      Attachments

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.