6

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
  • Please run SHOW CREATE TABLE tblname\G on all the tables in the cars database that are listed in the question. – RolandoMySQLDBA Aug 21 '17 at 0:37
  • @RolandoMySQLDBA Done (I've changed some columns names here). – Omri Aug 21 '17 at 8:23
2
+50

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.

  • As for the Hebrew character, I when i run the queries, I get ERROR 1146 (42S02): Table 'mysql.CHARACTER_SETS' doesn't exist. As for both method #1 and #2, I get the same error as in my question. – Omri Aug 23 '17 at 14:53
  • Oh I am sorry. CHARACTER_SETS and COLLATIONS are in the INFORMATION_SCHEMA database. I'll update my answer to reflect that. – RolandoMySQLDBA Aug 23 '17 at 14:56
  • Thanks. Anyway, When i run the Drop Indexes commands, I get InnoDB: FTS Optimize Removing table cars/cars_ftr_a and there is no error in addition, So i can say that the problem is solved? – Omri Aug 23 '17 at 15:06
  • Questions: 1) Does the table still exist ? 2) When you run SHOW CREATE TABLE cars_ftr_a\G, does the fulltext index still exist ? 3) Can you do SELECT COUNT(*) FROM cars_ftr_a; without any error ? 4) If the fulltext index exists, can you do a fulltext search query to test it ? – RolandoMySQLDBA Aug 23 '17 at 15:10
  • 1) It does exist and i get the same result as you posted in your answer. 2) It does exist. 3) Yes, I can. 4) I managed to do a fulltext search query successfully. – Omri Aug 23 '17 at 15:41
0

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?

  • It doesn't work. I get Error in pars0opt.cc: table cars/#sql-3fdc_3 has prefix_len != 0 and InnoDB: FTS Optimize Removing table cars/#sql2-3fdc-3. – Omri Aug 23 '17 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.