Description:
Hi!
We have an InnoDB table with few FULLTEXT indexes. Tricky part that one column belong to both indexes (e.g. lets call it "field3" in my example).
If you will try to do something like this, it will work fine:
mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3";
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
----------
But if you will try to do minor version upgrade, like:
- Upgrade from 5.7.21 to 5.7.24
- Run "mysql_upgrade"
- Try same query again - it will fail now:
mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3";
ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time
----------
And if you will try mysqlcheck - it reports that all is fine:
root@ddeca2dcd8d8:/# mysqlcheck -c -e -uroot -proot --databases testDB
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
testDB.testTable OK
----------
The only possible way on how to make it work again is to do DB dump/restore or execute "mysqlcheck -o" which will recreate indexes.
How to repeat:
1. Check current MySQL version and create testDB:
mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.21 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.21 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
mysql> CREATE DATABASE testDB;
Query OK, 1 row affected (0.00 sec)
mysql> use testDB;
Database changed
mysql> CREATE TABLE `testTable` (
-> `field1` int(11) NOT NULL,
-> `field2` varchar(100) NOT NULL,
-> `field3` varchar(100) NOT NULL,
-> `field4` varchar(100) NOT NULL,
-> PRIMARY KEY (`field1`),
-> FULLTEXT KEY `idx1` (`field2`,`field3`),
-> FULLTEXT KEY `idx2` (`field3`,`field4`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.10 sec)
----------
2. Verify that ALTER TABLE works:
mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3";
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
----------
3. Upgrade MySQL to never version, like 5.7.24
4. Run "mysql_upgrade"
5. Indicate that everything was OK:
root@ddeca2dcd8d8:/# mysql_upgrade -uroot -proot
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
sys.sys_config OK
testDB.testTable OK
Upgrade process completed successfully.
----------
6. Verify new MySQL version:
mysql> SHOW VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.24 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.24 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
----------
7. Run manual DB check just in case:
root@ddeca2dcd8d8:/# mysqlcheck -c -e -uroot -proot --databases testDB
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
testDB.testTable OK
----------
8. Try to execute same ALTER TABLE query again:
mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3";
ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time
Description: Hi! We have an InnoDB table with few FULLTEXT indexes. Tricky part that one column belong to both indexes (e.g. lets call it "field3" in my example). If you will try to do something like this, it will work fine: mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3"; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 ---------- But if you will try to do minor version upgrade, like: - Upgrade from 5.7.21 to 5.7.24 - Run "mysql_upgrade" - Try same query again - it will fail now: mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3"; ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time ---------- And if you will try mysqlcheck - it reports that all is fine: root@ddeca2dcd8d8:/# mysqlcheck -c -e -uroot -proot --databases testDB mysqlcheck: [Warning] Using a password on the command line interface can be insecure. testDB.testTable OK ---------- The only possible way on how to make it work again is to do DB dump/restore or execute "mysqlcheck -o" which will recreate indexes. How to repeat: 1. Check current MySQL version and create testDB: mysql> SHOW VARIABLES LIKE '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.21 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.21 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 8 rows in set (0.00 sec) mysql> CREATE DATABASE testDB; Query OK, 1 row affected (0.00 sec) mysql> use testDB; Database changed mysql> CREATE TABLE `testTable` ( -> `field1` int(11) NOT NULL, -> `field2` varchar(100) NOT NULL, -> `field3` varchar(100) NOT NULL, -> `field4` varchar(100) NOT NULL, -> PRIMARY KEY (`field1`), -> FULLTEXT KEY `idx1` (`field2`,`field3`), -> FULLTEXT KEY `idx2` (`field3`,`field4`) -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.10 sec) ---------- 2. Verify that ALTER TABLE works: mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3"; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 ---------- 3. Upgrade MySQL to never version, like 5.7.24 4. Run "mysql_upgrade" 5. Indicate that everything was OK: root@ddeca2dcd8d8:/# mysql_upgrade -uroot -proot mysql_upgrade: [Warning] Using a password on the command line interface can be insecure. Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK The sys schema is already up to date (version 1.5.1). Checking databases. sys.sys_config OK testDB.testTable OK Upgrade process completed successfully. ---------- 6. Verify new MySQL version: mysql> SHOW VARIABLES LIKE '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.7.24 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.24 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ 8 rows in set (0.00 sec) ---------- 7. Run manual DB check just in case: root@ddeca2dcd8d8:/# mysqlcheck -c -e -uroot -proot --databases testDB mysqlcheck: [Warning] Using a password on the command line interface can be insecure. testDB.testTable OK ---------- 8. Try to execute same ALTER TABLE query again: mysql> ALTER TABLE `testTable` MODIFY COLUMN `field3` varchar(100) NOT NULL COMMENT "Field3"; ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time