← Back to team overview

maria-discuss team mailing list archive

Re: ALTER TABLE ENIGNE=InnoDB performance

 

Hi Conor,

There is a bug report for the error messages now:

https://jira.mariadb.org/browse/MDEV-14941
MDEV-14941 Timeouts on persistent statistics tables after upgrade

On Sat, Jan 13, 2018 at 1:30 AM, Conor Murphy
<conor_mark_murphy@xxxxxxxxxxx> wrote:
> However, the whole exercise has reached a dead-end as we need to be able to
> replicate the database and as far as I can tell we need "ALTER TABLE t
> DISCARD PARTITION ALL TABLESPACE" which isn't available.

MariaDB is indeed missing the ALTER TABLE t DISCARD|IMPORT
PARTITION…TABLESPACE syntax.
MariaDB is also missing the "MySQL 5.7 native InnoDB partitioning"
which probably means that their code change cannot be easily ported.

The following commit in MySQL 5.7 added the the syntax:

commit 9c4d999381bb8bfcc48921c0ece378f877e47bb3
Author: Mattias Jonsson <mattias.jonsson@xxxxxxxxxx>
Date:   Thu Feb 6 14:02:06 2014 +0100

    WL#6868: Support transportable tablespaces for single innodb partition.

    (Based on WL#6867.)

    Made alter_info.partition_names a List<String> instead of List<char>,
    so it is the same as table_list.partition_names.

    Added syntax support for:
    ALTER TABLE t DISCARD PARTITION <list_of_parts> TABLESPACE
    ALTER TABLE t IMPORT PARTITION <list_of_parts> TABLESPACE
    which will only DISCARD/IMPORT the listed partitions.

    Note that FLUSH FOR EXPORT still works on table level!

The following is the merge commit to MySQL 5.7 (which was called
mysql-trunk at that time).

commit 6be6f3e3820183f315c153dafa12465e6832851d
Merge: d168602867c e7fc4dce3ef
Author: Mattias Jonsson <mattias.jonsson@xxxxxxxxxx>
Date:   Wed Feb 26 10:32:57 2014 +0100

    WL#6867: Support transportable tablespaces for partitioned innodb tables.

    Merge into mysql-trunk.

I would not be keen to update the DISCARD/IMPORT TABLESPACE in
MariaDB, unless a customer is asking for it. I would rather have
faster ALTER TABLE…ALGORITHM=COPY (MDEV-11415) and bulk inserts
(MDEV-515) and more easily transportable InnoDB files (just FLUSH
TABLES…FOR EXPORT, copy the files, and let the server discover them,
similar to how it works with MyISAM). The DISCARD/IMPORT feels like a
crude hack to me. DISCARD is breaking referential integrity of the
data dictionary, and the adjustments during IMPORT (which could be
avoided with some file format changes) are neither atomic nor
crash-safe.
-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

DON’T MISS

M|18

MariaDB User Conference

February 26 - 27, 2018

New York City

https://m18.mariadb.com/


Follow ups

References