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

No progress report for online DDL of InnoDB tables

    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

              • Assignee:
                jplindst Jan Lindström
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: