0

I followed @RolandoMySQLDBA 's tips in this thread: mysql, changing tables from myisam to ndb But I didn't succeed on the last step.

I have a similar problem, here's my original table:

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;

If I simply convert it, it will give me this error:

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

Here is what I tried:

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)

Now I have difficulty to finalize your last step, I can't understand the meaning of them, how to finish the "insert into" step? And why we need to do that?

What's more, during my operations, I could see some warnings, how can I check them?

Thanks!

0
  • After seeing Warnings: 1, do SHOW WARNINGS;; there could be something important.

  • Get rid of KEY (val(100)); it is virtually useless anyway. If you don't need the full size of TEXT, make it a reasonably short VARCHAR, then have KEY (val), else get rid of the index completely.

  • The warning says: Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK'. Can we ignore it safety? – NeilWang Sep 13 '17 at 2:49
  • Looks like there is a PK?? I don't understand. – Rick James Sep 13 '17 at 3:11
  • Thank you, I tried to keep all the types as original as possible. But it seems if I set val to text, I can't add key(val). – NeilWang Sep 14 '17 at 8:57
  • Let's see the query that would make use of KEY(val(100)). – Rick James Sep 14 '17 at 14:07
  • Sorry, how to do that? – NeilWang Sep 15 '17 at 21:43
0

Conversion to InnoDB is easier than to NDB. And you can get HA with it.

Since the question seems to be about making a High Availability setups, I recommend (instead of NDB) that you go with "InnoDB Cluster" and "Group Replication". (Read up on them in the online manual for 5.7.)

That will require conversion to InnoDB on all of your tables and that they have a PRIMARY KEY.

This further discusses conversion from MyISAM to InnoDB.

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.