[MDEV-19294] Remove TABLESPACE-related commands Created: 2019-04-19  Updated: 2024-02-06  Resolved: 2024-02-06

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Data Definition - Create Table
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Michael Widenius
Resolution: Won't Fix Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-19295 Remove LOGFILE-related commands Open
relates to MDEV-6113 merge 5.7 innodb Closed
relates to MDEV-11426 Remove InnoDB INFORMATION_SCHEMA.FILE... Closed
relates to MDEV-12309 Test migration from MySQL 5.7 with na... Stalled
relates to MDEV-12702 CREATE TABLESPACE in 10.2 Closed
relates to MDEV-19293 Remove information_schema.TABLESPACES Open

 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?

 Comments   
Comment by Geoff Montee (Inactive) [ 2020-12-03 ]

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                                                   |
 
+-----------------------------------------------------+

Comment by Michael Widenius [ 2024-02-06 ]

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.

Comment by Michael Widenius [ 2024-02-06 ]

Needed for MySQL compatibility

Generated at Thu Feb 08 08:50:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.