MySQL Bugs: #108991: util.loadDump fails if table has more than one FULLTEXT index

Bug #108991	util.loadDump fails if table has more than one FULLTEXT index
Submitted:	3 Nov 2022 15:06	Modified:	22 Nov 2022 17:27
Reporter:	Duke Lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell Dump & Load	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a table has more than one FULLTEXT index, util.loadDump fails because it tries to add all of the indexes in one ALTER TABLE statement, which produces an error:

Loading DDL and Data from 'blah' using 4 threads.
Opening dump...
Target is MySQL 8.0.31-0ubuntu0.22.04.1. Dump was produced from MySQL 8.0.31-0ubuntu0.22.04.1
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done       
Executing view DDL - done       
Starting data load
?% (0 bytes / ?), 0.00 B/s, 0 / 1 tables done
ERROR: While recreating indexes for table `db`.`test`: MySQL Error 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time: ALTER TABLE `db`.`test` ADD FULLTEXT KEY `name` (`name`),ADD FULLTEXT KEY `email` (`email`)
ERROR: [Worker002] While recreating indexes for table `db`.`test`: InnoDB presently supports one FULLTEXT index creation at a time
ERROR: Aborting load...                      
Recreating indexes - done       
No data loaded.                              
1 errors and 0 warnings messages were reported during the load.
ERROR: Error loading dump

How to repeat:
create database db;
use db;
create table test (
  id int not null,
  name varchar(100) not null,
  email varchar(100) not null,
  fulltext (name),
  fulltext (email)
);

Dump the data:

mysqlsh -h localhost -u root -p -P33060 -- util dumpInstance "blah" --compatibility=strip_tablespaces --excludeUsers='root'

Then load it into a fresh instance:

mysqlsh -h localhost -u root -p -P33060 -- util loadDump "blah

Suggested fix:
FULLTEXT indexes should be added via individual ALTER TABLE ... ADD FULLTEXT queries

Presumably, the error occurs because --loadIndexes defaults to true and --deferTableIndexes defaults to fulltext, the latter of which produces the error:

"As of 8.0.31, MySQL Shell utilizes MySQL Server's parallel index creation. Previously, the dump loading utilities added indexes sequentially, one at a time. As of this release, all indexes in a table are added simultaneously."

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html#mysql-sh...

Hi Mr. Lee,

Thank you for your bug report.

However, before we proceed, we would like to know whether this bug is a duplicate of the bug #108975 ??

Hi,

This bug is not related to bug #108975. 108975 refers to an error during DDL view creation.

This FULLTEXT index bug is during DDL table creation, particularly when loads are used with deferTableIndexes options. It effectively renders the option useless if there exists a table with more than one FULLTEXT index since the load process breaks with the error described above.

The following causes failures:

--loadIndexes=true --deferTableIndexes=fulltext (both of these values are default values)
--loadIndexes=true --deferTableIndexes=all

The only workaround is to NOT use this feature by turning it off:

--deferTableIndexes=none

Correction to the above:

The only workaround is to NOT use this feature by turning it off:

--deferTableIndexes=off (not "none")

Hi Mr. Lee,

We would also require some data in that table in order to reproduce the behaviour.

If you run the above steps, it will produce the error described. Data isn't required for reproduction.

Hi Mr. Lee,

We are unable to see what is a bug that you are reporting.

MySQL clearly states that only one fulltext index can be created at a time.

Hence, this is intended behaviour, for the time being ......

"MySQL clearly states that only one fulltext index can be created at a time."

Yes, you are exactly right. Did you look at the error output that I posted in original bug report? util.loadDump is trying to create _all_ FULLTEXT indexes in one ALTER statement, which, as you've said, is not allowed by mysql for fulltext indexes.

I'd like to make clear that the error is NOT coming from the example table DDL that I posted above. You _are_ allowed to declare multiple FULLTEXT indexes upon table creation. The error is occurring with util.loadDump due to deferTableIndexes because it's trying to add multiple FULLTEXT indexes with one ALTER statement, which is NOT allowed.

Hi Mr. Lee,

What is a bug number of the original report ????

I meant the original post/comment at the very top of this page.

I'd like to try to make this crystal clear that this is a bug with util.loadDump:

1) User creates a table the contains 2 or more fulltext indexes (no errors thrown)
2) User dumps the data using util.dumpInstance (no errors thrown)
3) User takes the data and restores it onto a separate mysql instance (no errors thrown)
4) util.loadDump defers creation of fulltext indexes due to the default values:
--loadIndexes=true --deferTableIndexes=fulltext (no errors thrown)
5) At the end of the process, util.dumpInstance tries to add the fulltext index (ERROR THROWN)

The reason that step #5 fails is because util.dumpInstance is trying to add the fulltext indexes in a single ALTER statement, which we've already discussed is not legal for mysql server/innodb. Look at the error message at the very top of this page. util.loadDump is trying to run the following query:

ALTER TABLE `db`.`test` ADD FULLTEXT KEY `name` (`name`),ADD FULLTEXT KEY `email` (`email`)

which is not legal. util.loadDump is trying to run a query that is not legal.

Corrections to the above:

"5) At the end of the process, util.dumpInstance tries to add the fulltext index (ERROR THROWN)"

should read:

"5) At the end of the process, util.loadDump tries to add the fulltext index (ERROR THROWN)"

and

"The reason that step #5 fails is because util.dumpInstance"

should read:

"The reason that step #5 fails is because util.loadDump"

Hi Mr. Lee,

Thank you for the clarifications.

As far as we can see, there is nothing stopping you from doing two ALTER TABLE commands and add two FTS indices ......

However, we do understand your position. This report could be treated as a feature request and if that is OK with you, we shall change it to feature request and verify it as such.

Wow, I'm truly in disbelief... Let me try to clarify this again.

util.loadDump has a feature controlled by the deferTableIndexes option where it will initially create tables WITHOUT indexes. This allows the data load step to run faster. After the data load, util.loadDump will then recreate indexes after the data has been loaded.

util.loadDump tries to add indexes back in a SINGLE ALTER statement.

I have ZERO control over what queries util.loadDump runs when recreating the fulltext indexes.

I CANNOT tell util.loadDump to recreate the indexes as two separate ALTER statements.

I'm not the one issuing the ALTER statement that includes multiple fulltext indexes. This statement is coming from the util.loadDump tool itself.

Again, it is UTIL.LOADDUMP that is issuing the illegal ALTER statement when it is trying to recreate the indexes.

I don't know why you seem to keep insisting that I'm the one that's manually issuing this illegal command. The log output that I pasted in the bug report clearly shows that the error is coming from the util.loadDump tool.

Hi Mr. Lee,

We do not insist that you should run ALTER TABLE that we all know that it will fail.

Simply, there are two options.

You either run two valid ALTER TABLE's or we verify this report as a feature request, so that MySQL Shell itself gets a feature to create additional 2 or more FTS indices with a single command.
That is all .....

Hi,

Apologies, my colleague missed the point that this is all done by shell and not by you. Until recently when we say "shell" we were talking about something completely different. 

I reproduced and verified the bug. Thank you for your patience and for this report and reproducible test case.

kind regards

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.32 release notes:

util.loadDump failed if a table in the dump had more than one FULLTEXT index. The following error was returned:

 InnoDB presently supports one FULLTEXT index creation at a time

As of this release, if a table contains more than one FULLTEXT index, each additional index is created using       separate ALTER TABLE statements.

Thank you, Edward.