主页 / dba / 问题 / 183706
Accepted
Omri
Asked: 2017-08-18 00:53:35 +0800 CST

模式从 mysql 4 迁移到 mysql 5.6 后的问题

  • 772

最近我将一些模式从实例迁移到mysql 4一个实例和导入操作。经过一些语法调整后，我可以成功导入转储文件。mysql 5.6mysqldump

导入后，我查看了日志文件，发现了一些问题。有谁知道那是什么以及如何解决它？

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.

编辑（添加建表语句）

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
mysql mysql-5.6

2 个回答

  1. Best Answer

    发布表结构后，有两件事成为焦点

    • InnoDB 表有全文索引
    • 您的字符集是希伯来语

    您是否相信 7 年前有人问过 MySql 全文搜索是否适用于非拉丁语言（希伯来语、阿拉伯语、日语……）？？？

    您应该确保在您的 MySQL 安装中设置了希伯来字符集

    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)

    连同必要的整理

    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)

    您可以尝试创建一个具有相同结构的新表并加载它。

    方法#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;

    方法#2

    ALTER TABLE cars_ftr_a ENGINE=InnoDB;

    然后，查看错误日志并查看是否再次出现相同的警告。如果他们不这样做，您就可以开始了。您还应该研究调整 InnoDB Fulltext 选项。

    更新 2017-08-22 23:17 EDT

    方法#3

    您可以尝试的另一件事是删除全文索引并重新创建它

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

    然后，去检查日志，看看是否再次出现相同的错误。

    更新 2017-08-23 11:04 EDT

    方法#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`);

    加载没有全文索引的新表。然后最后创建全文索引。

    • 2

  2. 我不知道警告是什么意思，但在以下位置提到了类似的警告：

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

    Quote：来自受以下消息影响的表的所有错误消息：InnoDB：InnoDB 数据字典中有未知标志 40/50/52。可以通过运行 OPTIMIZE TABLE 命令来静音（对于非常大的表来说，这可能会变得非常昂贵）。

    你试过OPTIMIZE TABLE吗？

    • 0

相关问题