[MDEV-12437] Import tablespace causes replication to stop Created: 2017-04-03  Updated: 2017-12-11

Status: Open
Project: MariaDB Server
Component/s: Replication, Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Major
Reporter: Rafael Gallastegui Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 1
Labels: upstream

Issue Links:
Relates
relates to MDEV-11658 Simpler, faster IMPORT of InnoDB tables Open

 Description   

I've been experimenting with ALTER TABLE X IMPORT TABLESPACE mostly on MariaDB servers w/o replication as preparation to test the same scenario in a master-slave replication scenario. Most of the tests have been going OK, but when I finally tried it in a master/slave configuration, I saw that the replication is broken, since the slave is trying to execute the IMPORT TABLESPACE statement and it doesn't have the .ibd or .cfg files available to execute the operation. I've been searching all over to see what is the proper way of dealing with the IMPORT TABLESPACE statement in a replication environment but I cannot find anything. The only thing I found is a sentence in a MySQL page where it says that IMPORT TABLESPACE needs to have the file per table setting in both the master and slave set (which I have).

The errors I get in the slave server are like this ones:

2017-03-29 11:34:10 140405862296320 [ERROR] InnoDB: Trying to import a tablespace, but could not open the tablespace file ./RAFAEL_SMALL_ECOH_BAK_X/SETTING_T.ibd
2017-03-29 11:34:10 140405862296320 [Note] InnoDB: Discarding tablespace of table "RAFAEL_SMALL_ECOH_BAK_X"."SETTING_T": Tablespace not found
2017-03-29 11:38:19 140405862296320 [ERROR] Slave SQL: Error 'Table 'RAFAEL_SMALL_ECOH_BAK_X.SETTING_T' doesn't exist in engine' on query. Default database: 'RAFAEL_SMALL_ECOH_BAK_X'. Query: 'alter table SETTING_T import tablespace', Gtid 0-1-160400719, Internal MariaDB error code: 1932
2017-03-29 11:38:19 140405862296320 [Warning] Slave: IO Read error: (2, No such file or directory) Error opening './RAFAEL_SMALL_ECOH_BAK_X/SETTING_T.cfg', will attempt to import without schema verification Error_code: 1810
2017-03-29 11:38:19 140405862296320 [Warning] Slave: ALTER TABLE '"RAFAEL_SMALL_ECOH_BAK_X"."SETTING_T"' IMPORT TABLESPACE failed with error 44 : 'Tablespace not found' Error_code: 1816
2017-03-29 11:38:19 140405862296320 [Warning] Slave: Table 'RAFAEL_SMALL_ECOH_BAK_X.SETTING_T' doesn't exist in engine Error_code: 1932
2017-03-29 11:38:19 140405862296320 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'server1-bin.001326' position 472198041; GTID position '0-1-160400718'

and at this point, the replication stops.

How is this supposed to work? Are we supposed to execute the DISCARD TABLESPACE, wait until it has been replicated in the slave server and copy the .ibd and .cfg file to both servers and execute the IMPORT TABLESPACE? That sounds like a lot of work if you're importing many tables. Or should it have worked by just having the master send the table information to the slave and have the slave import the whole table from the master? Sounds to me that the last scenario is the one that should be the correct one, but it's not working.

Thanks

 Comments   
Comment by Elena Stepanova [ 2017-04-03 ]

I don't know any way to replicate IMPORT TABLESPACE other than actually copying the files. marko, do you?

Comment by Marko Mäkelä [ 2017-04-04 ]

To my knowledge, the InnoDB tablespace import/export does not play well with replication. I do not know if/how anyone uses it with replication.
Some time ago, I wrote down some ideas in MDEV-11658 for improving IMPORT. One of these ideas would be a streaming-friendly version where the raw page data would be written in BASE64 or BASE85 encoding. Maybe an even better alternative could be some syntax that combines CREATE TABLE and INSERT in a single statement, allowing most of the undo and redo logging to be omitted. As long as both the metadata and the data gets written to the binlog, it would work with replication out of the box.

Comment by Rafael Gallastegui [ 2017-04-04 ]

Makes sense. That's what I was assuming that it would happen (that something would be written in the binlog such that the data would be available for the slave servers). The IMPORT TABLESPACE manual page didn't have any limitations for replication. Maybe it needs it?

The reason we're experimenting with this is because we want to copy a database from one system to another every month or so (it's a 50+GB database) and mysqldump seems to be very inefficient (well, not mysqldump itself, but injecting the output of mysqldump into a master/slave replication pair) to the point where the slave sometimes gets behind by more than 10000 seconds (at least). It eventually recovers, but seems like a very demanding on both servers and we were looking for a better way of doing that.

Comment by Marko Mäkelä [ 2017-04-08 ]

There is a way to speed up the data loading: create secondary indexes after the clustered index has been loaded. In this way, the secondary indexes entries could be pre-sorted, and with MariaDB 10.2 (or MySQL 5.7) the bulk loading would be enabled.
In 2009, some years after I implemented "fast create index" for the InnoDB Plugin in MySQL 5.1, MySQL Bug#49120 mysqldump should have flag to delay creating indexes for innodb plugin releases was filed. In 2012, a fix was filed as a contribution Bug#64248 create secondary indexes after inserting rows statements in mysqldump.
To my knowledge, Oracle is replacing mysqldump with a new utility mysqlpump. I am not sure if it supports the delayed creation of indexes. That feature was discussed in the tool design phase.

Comment by Elena Stepanova [ 2017-10-17 ]

Should we treat it as a feature request?

Comment by Marko Mäkelä [ 2017-11-12 ]

Yes, this feels like a feature request to have replication-friendly IMPORT. Or maybe, a duplicate of MDEV-515 (fast bulk insert would automatically be replication-friendly).

Generated at Thu Feb 08 07:57:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.