无法将表引擎从MyISAM转换为NDBCLUSTER

Question

在这个帖子中，我遵循了@RolandoMySQLDBA的S提示：mysql，将表从myisam更改为ndb.，但在最后一步我没有成功。

我也有类似的问题，这是我原来的桌子：

CREATE TABLE `df_modules_metadata_values` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `date_added` datetime NOT NULL,
  `date_modified` datetime DEFAULT NULL,
  `uid` mediumint(9) NOT NULL,
  `share_id` mediumint(9) DEFAULT NULL,
  `file_id` mediumint(9) NOT NULL,
  `field_id` mediumint(9) NOT NULL,
  `val` text CHARACTER SET utf8,
  PRIMARY KEY (`id`),
  KEY `date_added` (`date_added`),
  KEY `uid` (`uid`,`file_id`,`field_id`),
  KEY `file_id` (`file_id`,`field_id`),
  KEY `uid_2` (`uid`,`field_id`),
  KEY `val` (`val`(100)),
  KEY `field_id` (`field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

如果我简单地转换它，它将给我这个错误：

mysql> alter table df_modules_metadata_values engine=ndbcluster;
ERROR 1073 (42000): BLOB column 'val' can't be used in key specification with the used table type

以下是我尝试过的：

mysql> show table status where name='df_modules_metadata_values';
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name                       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| df_modules_metadata_values | MyISAM |      10 | Dynamic    |    0 |              0 |           0 | 281474976710655 |         2048 |         0 |              1 | 2017-09-10 18:00:21 | 2017-09-10 18:00:21 | NULL       | utf8_unicode_ci |     NULL |                |         |
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> alter table df_modules_metadata_values rename df_modules_metadata_values_old;
Query OK, 0 rows affected (0.00 sec)

mysql> create table df_modules_metadata_values select * from df_modules_metadata_values_old where 1=2;

Query OK, 0 rows affected, 1 warning (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> alter table df_modules_metadata_values add column val100 char(100) after val;
Query OK, 0 rows affected, 1 warning (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> alter table df_modules_metadata_values add unique index (val100);
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table df_modules_metadata_values engine=ndbcluster;
Query OK, 0 rows affected, 1 warning (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show table status where name='df_modules_metadata_values';
+----------------------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name                       | Engine     | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| df_modules_metadata_values | ndbcluster |      10 | Dynamic    |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL | partitioned    |         |
+----------------------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

现在我很难最后确定你的最后一步，我不明白它们的意思，如何完成“插入到”一步？我们为什么要这么做？

更重要的是，在我的操作中，我可以看到一些警告，我如何检查它们？

谢谢!

Answer 1

• 在看完Warnings: 1之后，做SHOW WARNINGS;；可能会有一些重要的事情。
• 摆脱KEY (val(100))；它实际上是无用的。如果您不需要完全大小的TEXT，那么将其作为一个相当短的VARCHAR，然后有KEY (val)，否则就完全摆脱索引。

Answer 2

转换到InnoDB比转换到NDB容易。你可以用它得到HA。

由于问题似乎是关于建立高可用性设置，所以我建议您使用"InnoDB群集“和”组复制“(而不是NDB)。(请在5.7的在线手册中阅读。)

这将需要在所有表上转换为InnoDB，并且它们具有一个PRIMARY KEY。

这进一步讨论了从MyISAM到InnoDB的转换。