Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
It would be useful if EXPLAIN supported ALTER TABLE and/or other DDL statements.
For example, something like this:
EXPLAIN ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE;
|
And probably also something like this:
EXPLAIN EXTENDED ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE;
|
The output should probably include:
- What algorithm will be used (i.e. COPY, INPLACE, NOCOPY, INSTANT).
- Whether the table will be rebuilt.
- Whether the table will be locked.
- If the table is locked, what kind of lock will be used.
- Whether the table's ROW_FORMAT will be changed.
- For InnoDB, whether the table will use the non-canonical format that is required to implement some instant alter operations.
- For InnoDB, whether the table will be in the system tablespace or in a file-per-table tablespace.
The more basic pieces of information could probably be output by EXPLAIN, and the more detailed pieces of information could probably be output by EXPLAIN EXTENDED.
Attachments
Issue Links
- relates to
-
MDEV-27294 Implement EXPLAIN for SHOW
-
- Open
-
-
SAMU-274 Failed to load
- is implemented by
-
SAMU-269 Failed to load
- mentioned in
-
Page Failed to load
Activity
Hi marko,
Accurate information about ROW_FORMAT is returned by SHOW TABLE STATUS; the format used by InnoDB may differ from what is in the .frm file.
SHOW TABLE STATUS would provide the table's current ROW_FORMAT value. If we support EXPLAIN for ALTER TABLE, then I was thinking that EXPLAIN should ask the storage engine whether the ROW_FORMAT of the table will be changed by the ALTER TABLE. It is not always obvious. For example, some users may not expect the row format of the table to change in the following case, if they were not aware that the value of innodb_default_row_format has been changed, or if they were not aware that the FORCE keyword could cause the ROW_FORMAT to change:
MariaDB [db1]> SET GLOBAL innodb_default_row_format='DYNAMIC';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [db1]> CREATE OR REPLACE TABLE tab (id int primary key);
|
Query OK, 0 rows affected (0.013 sec)
|
|
|
MariaDB [db1]> SELECT NAME, ROW_FORMAT
|
-> FROM information_schema.INNODB_SYS_TABLES
|
-> WHERE NAME = 'db1/tab';
|
+---------+------------+
|
| NAME | ROW_FORMAT |
|
+---------+------------+
|
| db1/tab | Dynamic |
|
+---------+------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [db1]> SET GLOBAL innodb_default_row_format='COMPACT';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [db1]> ALTER TABLE tab ADD COLUMN c varchar(50), FORCE;
|
Query OK, 0 rows affected (0.018 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [db1]> SELECT NAME, ROW_FORMAT
|
-> FROM information_schema.INNODB_SYS_TABLES
|
-> WHERE NAME = 'db1/tab';
|
+---------+------------+
|
| NAME | ROW_FORMAT |
|
+---------+------------+
|
| db1/tab | Compact |
|
+---------+------------+
|
1 row in set (0.000 sec)
|
| Field | Original Value | New Value |
|---|---|---|
| Description |
It would be useful if EXPLAIN supported ALTER TABLE and/or other DDL statements. i.e. so that something like this would work:
{noformat} ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE; {noformat} The output should probably include: * What algorithm will be used (i.e. COPY, INPLACE, NOCOPY, INSTANT). * Whether the table will be rebuilt. * Whether the table will be locked. * If the table is locked, what kind of lock will be used. * Whether the table's ROW_FORMAT will be changed. * For InnoDB, whether the table will use the non-canonical format that is required to implement some instant alter operations. * For InnoDB, whether the table will be in the system tablespace or in a file-per-table tablespace. |
It would be useful if EXPLAIN supported ALTER TABLE and/or other DDL statements.
For example, something like this: {noformat} EXPLAIN ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE; {noformat} And probably also something like this: {noformat} EXPLAIN EXTENDED ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE; {noformat} The output should probably include: * What algorithm will be used (i.e. COPY, INPLACE, NOCOPY, INSTANT). * Whether the table will be rebuilt. * Whether the table will be locked. * If the table is locked, what kind of lock will be used. * Whether the table's ROW_FORMAT will be changed. * For InnoDB, whether the table will use the non-canonical format that is required to implement some instant alter operations. * For InnoDB, whether the table will be in the system tablespace or in a file-per-table tablespace. The more basic pieces of information could probably be output by {{EXPLAIN}}, and the more detailed pieces of information could probably be output by {{EXPLAIN EXTENDED}}. |
| Link | This issue relates to MDEV-20702 [ MDEV-20702 ] |
| Component/s | Data Definition - Alter Table [ 14927 ] | |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Key | MDEV-20508 | MENT-404 |
| Project | MariaDB Server [ 10000 ] | MariaDB Enterprise [ 11500 ] |
Hi, sorry to spam you, but please add a fixVersion to those MENT issues.
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Workflow | MariaDB v3 [ 99458 ] | MariaDB v4 [ 135778 ] |
| Component/s | Data Definition - Alter Table [ 10114 ] | |
| Component/s | Data Definition - Alter Table [ 14927 ] | |
| Key | MENT-404 | MDEV-30340 |
| Issue Type | New Feature [ 2 ] | Task [ 3 ] |
| Project | MariaDB Enterprise [ 11500 ] | MariaDB Server [ 10000 ] |
| Labels | synchronization |
| Labels | synchronization |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Remote Link | This issue links to "SAMU-269 (Jira)" [ 37266 ] |
| Remote Link | This issue links to "SAMU-274 (Jira)" [ 37269 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37275 ] |
Most of this information will be delivered by storage engines via the existing virtual member function handler::check_if_supported_inplace_alter() already.
Accurate information about ROW_FORMAT is returned by SHOW TABLE STATUS; the format used by InnoDB may differ from what is in the .frm file.
As far as I can tell, the only question that the handler API cannot currently answer is whether a table is in the non-canonical format that was introduced by
MDEV-11369(instant ADD COLUMN) and extended inMDEV-15562. Perhaps we should extend SHOW TABLE STATUS with this information, and also let handler::check_if_supported_inplace_alter() assign a flag that informs whether the table would be in non-canonical format after the operation.Note that an ALGORITHM=NOCOPY operation may convert a table to non-canonical format, even though the complete operation is not ALGORITHM=INSTANT. Also, the table may already be in non-canonical format, and as a result of ALTER, it can either be in canonical or non-canonical format. For example, if the table would be rebuilt due to the FORCE keyword, or due to changing the data type of a column, the table would always be in the canonical format.
The changes to InnoDB should be minimal (doable in a few hours, once the storage engine interface changes have been implemented in a development branch). The bulk of this work should be done outside storage engines.