Issues after schemas migration from mysql 4 to mysql 5.6

Question

Recently I've migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully.

After the import, I went over the log file and i found some issues. Does anyone know what is that and how to fix it?

InnoDB: Error in pars0opt.cc: table cars/cars_ftr_a has prefix_len != 0
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_CONFIG'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_1'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_2'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_3'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_4'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_5'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_6'
InnoDB: in InnoDB data dictionary has unknown flags 40.

Edit (adding create table statement)

Create Table: CREATE TABLE `cars_ftr_a` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `manufacturer` varchar(100) NOT NULL DEFAULT '',
  `model` varchar(255) NOT NULL DEFAULT '',
  `object_type` int(10) unsigned NOT NULL DEFAULT '0',
  `fts_string` text NOT NULL,
  `manufactor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `model_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version_id` int(10) unsigned NOT NULL DEFAULT '0',
  `version` varchar(100) NOT NULL DEFAULT '',
  `group_id` int(10) unsigned NOT NULL DEFAULT '0',
  `group_name` varchar(100) DEFAULT NULL,
  `a` int(10) unsigned DEFAULT NULL,
  `b` varchar(100) NOT NULL DEFAULT '',
  `price` int(10) unsigned NOT NULL DEFAULT '0',
  `c` int(10) unsigned NOT NULL DEFAULT '0',
  `d` varchar(100) NOT NULL DEFAULT '',
  `e` float NOT NULL DEFAULT '0',
  `f` int(10) unsigned NOT NULL DEFAULT '0',
  `g` int(10) unsigned NOT NULL DEFAULT '0',
  `h` int(10) unsigned NOT NULL DEFAULT '0',
  `i` smallint(6) unsigned NOT NULL DEFAULT '0',
  `j` varchar(100) NOT NULL DEFAULT '',
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `l` int(10) unsigned NOT NULL DEFAULT '0',
  `m` int(10) unsigned NOT NULL DEFAULT '0',
  `n` varchar(100) NOT NULL DEFAULT '',
  `o` int(10) unsigned NOT NULL DEFAULT '0',
  `p` text NOT NULL,
  `q` varchar(100) NOT NULL DEFAULT '',
  `r` int(10) unsigned NOT NULL DEFAULT '0',
  `s` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`fts_string`(10)),
  FULLTEXT KEY `fts_string` (`fts_string`)
) ENGINE=InnoDB DEFAULT CHARSET=hebrew

Answer 1

There are two things that came into focus once you posted the table structure

• InnoDB Table has Fulltext Index
• Your Character Set is Hebrew

Would you believe someone asked 7 years ago Does MySql full text search works reasonably with non-Latin languages (Hebrew, Arabic, Japanese…) ???

You should make sure you have that the Hebrew character set in your MySQL installation

mysql> select * from INFORMATION_SCHEMA.CHARACTER_SETS where CHARACTER_SET_NAME='Hebrew';
+--------------------+----------------------+-------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION       | MAXLEN |
+--------------------+----------------------+-------------------+--------+
| hebrew             | hebrew_general_ci    | ISO 8859-8 Hebrew |      1 |
+--------------------+----------------------+-------------------+--------+
1 row in set (0.00 sec)

along with the necessary collations

mysql> select * from INFORMATION_SCHEMA.COLLATIONS where CHARACTER_SET_NAME='Hebrew';
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| hebrew_general_ci | hebrew             | 16 | Yes        | Yes         |       1 |
| hebrew_bin        | hebrew             | 71 |            | Yes         |       1 |
+-------------------+--------------------+----+------------+-------------+---------+
2 rows in set (0.00 sec)

You could try creating a new table with the same structure and loading it.

METHOD #1

CREATE TABLE cars_ftr_a LIKE cars_ftr_a_new;
INSERT INTO cars_ftr_a_new SELECT * FROM cars_ftr_a;
RENAME TABLE cars_ftr_a TO cars_ftr_a_old,cars_ftr_a_new TO cars_ftr_a;

METHOD #2

ALTER TABLE cars_ftr_a ENGINE=InnoDB;

Then, go look into the error log and see if those same warnings reappear. If they do not, you are good to go. You should also look into tuning the InnoDB Fulltext Options.

UPDATE 2017-08-22 23:17 EDT

METHOD #3

Another thing you can try is to drop the Fulltext Index and create it again

ALTER TABLE cars_ftr_a DROP INDEX `fts_string`;
ALTER TABLE cars_ftr_a ADD FULLTEXT `fts_string` (`fts_string`);

Then, go check the logs to see if the same errors came back or not.

UPDATE 2017-08-23 11:04 EDT

METHOD #4

CREATE TABLE cars_ftr_a LIKE cars_ftr_a_tmp;
ALTER TABLE cars_ftr_a_tmp DROP INDEX `fts_string`; 
INSERT INTO cars_ftr_a_tmp SELECT * FROM cars_ftr_a;
RENAME TABLE cars_ftr_a TO cars_ftr_a_xxx,cars_ftr_a_tmp TO cars_ftr_a;
ALTER TABLE cars_ftr_a ADD FULLTEXT `fts_string` (`fts_string`);

Load new table without Fulltext Index. Then create Fulltext Index last.

Answer 2

I have no idea what the warning means, but a similar warning is mentioned at:

http://www.fromdual.com/migration-between-mysql-percona-server-and-mariadb

Quote: All error messages from tables affected by the following message: InnoDB: in InnoDB data dictionary has unknown flags 40/50/52. could be silenced by a run of the OPTIMIZE TABLE command (which can become quite expensive for very big tables).

Have you tried OPTIMIZE TABLE?