[MDEV-19294] Remove TABLESPACE-related commands - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Fix Version/s: N/A
Component/s: Data Definition - Alter Table, Data Definition - Create Table
Labels:
None

Description

MariaDB Server's grammar supports the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. These statements seem to have been inherited from MySQL NDB Cluster.

MySQL 5.7 supports these statements for InnoDB as well, but we chose not to merge that support in MariaDB 10.2.

https://mariadb.com/kb/en/library/alter-tablespace/

https://mariadb.com/kb/en/library/create-tablespace/

https://mariadb.com/kb/en/library/drop-tablespace/

The MySQL-compatible statements will actually still succeed on MariaDB, but will throw a warning. e.g.:

MariaDB [db1]> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [db1]> SHOW WARNINGS;

+---------+------+------------------------------------------------------------------------------------------------+

| Level   | Code | Message                                                                                        |

+---------+------+------------------------------------------------------------------------------------------------+

| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' |

+---------+------+------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Is there any reason to keep these in MariaDB?

Attachments

Issue Links

relates to

MDEV-19295 Remove LOGFILE-related commands

Open

MDEV-6113 merge 5.7 innodb

Closed

MDEV-11426 Remove InnoDB INFORMATION_SCHEMA.FILES implementation

Closed

MDEV-12309 Test migration from MySQL 5.7 with named tablespaces to MariaDB 10.2

Stalled

MDEV-12702 CREATE TABLESPACE in 10.2

Closed

MDEV-19293 Remove information_schema.TABLESPACES

Open

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) created issue - 2019-04-19 20:07

Geoff Montee (Inactive) made changes - 2019-04-19 20:07

Field	Original Value	New Value
Link		This issue relates to MDEV-19293 [ MDEV-19293 ]

Geoff Montee (Inactive) made changes - 2019-04-19 20:07

Link

This issue relates to ~~MDEV-12702~~ [ ~~MDEV-12702~~ ]

Geoff Montee (Inactive) made changes - 2019-04-19 20:07

Link

This issue relates to ~~MDEV-11426~~ [ ~~MDEV-11426~~ ]

Geoff Montee (Inactive) made changes - 2019-04-19 20:07

Link

This issue relates to MDEV-12309 [ MDEV-12309 ]

Geoff Montee (Inactive) made changes - 2019-04-19 20:07

Link

This issue relates to ~~MDEV-6113~~ [ ~~MDEV-6113~~ ]

Geoff Montee (Inactive) made changes - 2019-04-19 20:16

Description

MariaDB Server's grammar supports the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. These statements seem to have been inherited from MySQL NDB Cluster.

MySQL 5.7 supports these statements for InnoDB as well, but we chose not to merge that support in MariaDB 10.2. The MySQL-compatible statements will actually still succeed on MariaDB, but will throw a warning. e.g.:

{noformat}
MariaDB [db1]> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [db1]> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{noformat}

Is there any reason to keep these in MariaDB?

MariaDB Server's grammar supports the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. These statements seem to have been inherited from MySQL NDB Cluster.

MySQL 5.7 supports these statements for InnoDB as well, but we chose not to merge that support in MariaDB 10.2.

https://mariadb.com/kb/en/library/alter-tablespace/

https://mariadb.com/kb/en/library/create-tablespace/

https://mariadb.com/kb/en/library/drop-tablespace/

The MySQL-compatible statements will actually still succeed on MariaDB, but will throw a warning. e.g.:

{noformat}
MariaDB [db1]> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [db1]> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{noformat}

Is there any reason to keep these in MariaDB?

Geoff Montee (Inactive) made changes - 2019-04-19 20:35

Link

This issue relates to MDEV-19295 [ MDEV-19295 ]

Geoff Montee (Inactive) made changes - 2020-12-03 18:47

Assignee

Sergei Golubchik [ serg ]

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Geoff Montee (Inactive) added a comment - 2020-12-03 18:55

The CREATE TABLESPACE privilege is also present, even though it does not apply to MariaDB.

It is present in multiple places.

It is present in SHOW PRIVILEGES:

MariaDB [(none)]> SHOW PRIVILEGES;

+--------------------------+---------------------------------------+--------------------------------------------------------------------+

| Privilege                | Context                               | Comment                                                            |

+--------------------------+---------------------------------------+--------------------------------------------------------------------+

...

| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |

...

+--------------------------+---------------------------------------+--------------------------------------------------------------------+

It is present in mysql.user:

MariaDB [(none)]> SELECT Create_tablespace_priv FROM mysql.user;

+------------------------+

| Create_tablespace_priv |

+------------------------+

| N                      |

| Y                      |

| Y                      |

+------------------------

And it is present in the bitmask used in mysql.global_priv, which is referenced by mysql.user:

MariaDB [(none)]> SELECT IF(json_value(Priv,'$.access') & 536870912,'Y','N') FROM mysql.global_priv;

+-----------------------------------------------------+

| IF(json_value(Priv,'$.access') & 536870912,'Y','N') |

+-----------------------------------------------------+

| N                                                   |

| Y                                                   |

| Y                                                   |

+-----------------------------------------------------+

And if you GRANT ALL to a user, then they will get this privilege:

MariaDB [(none)]> SELECT IF(json_value(Priv,'$.access') & 536870912,'Y','N') FROM mysql.global_priv WHERE User='my_user' AND Host='localhost';

+-----------------------------------------------------+

| IF(json_value(Priv,'$.access') & 536870912,'Y','N') |

+-----------------------------------------------------+

| Y                                                   |

+-----------------------------------------------------+

Geoff Montee (Inactive) added a comment - 2020-12-03 18:55 The CREATE TABLESPACE privilege is also present, even though it does not apply to MariaDB. It is present in multiple places. It is present in SHOW PRIVILEGES : MariaDB [(none)]> SHOW PRIVILEGES ; + --------------------------+---------------------------------------+--------------------------------------------------------------------+ | Privilege | Context | Comment | + --------------------------+---------------------------------------+--------------------------------------------------------------------+ ... | Create tablespace | Server Admin | To create / alter / drop tablespaces | ... + --------------------------+---------------------------------------+--------------------------------------------------------------------+ It is present in mysql.user : MariaDB [(none)]> SELECT Create_tablespace_priv FROM mysql. user ; + ------------------------+ | Create_tablespace_priv | + ------------------------+ | N | | Y | | Y | + ------------------------ And it is present in the bitmask used in mysql.global_priv , which is referenced by mysql.user : MariaDB [(none)]> SELECT IF (json_value(Priv, '$.access' ) & 536870912, 'Y' , 'N' ) FROM mysql.global_priv; + -----------------------------------------------------+ | IF (json_value(Priv, '$.access' ) & 536870912, 'Y' , 'N' ) | + -----------------------------------------------------+ | N | | Y | | Y | + -----------------------------------------------------+ And if you GRANT ALL to a user, then they will get this privilege: MariaDB [(none)]> SELECT IF (json_value(Priv, '$.access' ) & 536870912, 'Y' , 'N' ) FROM mysql.global_priv WHERE User = 'my_user' AND Host= 'localhost' ; + -----------------------------------------------------+ | IF (json_value(Priv, '$.access' ) & 536870912, 'Y' , 'N' ) | + -----------------------------------------------------+ | Y | + -----------------------------------------------------+

Ralf Gebhardt made changes - 2021-01-28 10:34

Fix Version/s

10.4 [ 22408 ]

Ralf Gebhardt made changes - 2021-07-21 09:06

Assignee

Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]

Sergei Golubchik made changes - 2021-12-06 21:21

Workflow

MariaDB v3 [ 96247 ]

MariaDB v4 [ 131067 ]

AirFocus made changes - 2022-08-09 16:10

Description

MariaDB Server's grammar supports the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. These statements seem to have been inherited from MySQL NDB Cluster.

MySQL 5.7 supports these statements for InnoDB as well, but we chose not to merge that support in MariaDB 10.2.

https://mariadb.com/kb/en/library/alter-tablespace/

https://mariadb.com/kb/en/library/create-tablespace/

https://mariadb.com/kb/en/library/drop-tablespace/

The MySQL-compatible statements will actually still succeed on MariaDB, but will throw a warning. e.g.:

{noformat}
MariaDB [db1]> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [db1]> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{noformat}

Is there any reason to keep these in MariaDB?

MariaDB Server's grammar supports the CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE statements. These statements seem to have been inherited from MySQL NDB Cluster.

MySQL 5.7 supports these statements for InnoDB as well, but we chose not to merge that support in MariaDB 10.2.

https://mariadb.com/kb/en/library/alter\-tablespace/

https://mariadb.com/kb/en/library/create\-tablespace/

https://mariadb.com/kb/en/library/drop\-tablespace/

The MySQL\-compatible statements will actually still succeed on MariaDB, but will throw a warning. e.g.:

{noformat}
MariaDB [db1]> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [db1]> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 1478 | Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
{noformat}

Is there any reason to keep these in MariaDB?

Ralf Gebhardt made changes - 2023-07-20 14:55

Assignee

Michael Widenius [ monty ]

Julien Fritsch made changes - 2023-11-30 16:30

Issue Type

Task [ 3 ]

New Feature [ 2 ]

Ralf Gebhardt made changes - 2024-02-01 14:14

Issue Type

New Feature [ 2 ]

Task [ 3 ]

Michael Widenius added a comment - 2024-02-06 14:14

For MySQL compatibility, we have to keep the syntax (and give warnings).
We start to get more and more users who are asking for more MySQL compatibility (to be able to run MySQL instances unchanged in MariaDB) and we have work with them regarding this!

I did remove in 2021 ALTER TABLESPACE, DROP TABLESPACE and the 'alter_tablespace' status variable.
(I don't remember why I did this, probably someone asked me to do that)
If there is demand, we may have to add back the ALTER TABLESPACE and DROP TABLESPACE as 'compatible commands that does nothing.

Michael Widenius added a comment - 2024-02-06 14:14 For MySQL compatibility, we have to keep the syntax (and give warnings). We start to get more and more users who are asking for more MySQL compatibility (to be able to run MySQL instances unchanged in MariaDB) and we have work with them regarding this! I did remove in 2021 ALTER TABLESPACE, DROP TABLESPACE and the 'alter_tablespace' status variable. (I don't remember why I did this, probably someone asked me to do that) If there is demand, we may have to add back the ALTER TABLESPACE and DROP TABLESPACE as 'compatible commands that does nothing.

Michael Widenius added a comment - 2024-02-06 14:15

Needed for MySQL compatibility

Michael Widenius added a comment - 2024-02-06 14:15 Needed for MySQL compatibility

Michael Widenius made changes - 2024-02-06 14:15

Fix Version/s		N/A [ 14700 ]
Resolution		Won't Fix [ 2 ]
Status	Open [ 1 ]	Closed [ 6 ]

Jira Automation (IT) made changes - 2024-07-04 04:03

Zendesk Related Tickets

194091

People

Assignee:: Michael Widenius

Reporter:: Geoff Montee (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2019-04-19 20:07

Updated:: 2025-02-26 17:32

Resolved:: 2024-02-06 14:15

Git Integration

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