mysql, changing tables from myisam to ndb

Question

Gents, Converting a table from myisam to ndb via:

ALTER TABLE piwik_log_profiling ENGINE NDBCLUSTER;

This will fail with:

BLOB column 'query' can't be used in key specification with the used table type

Now, if the 'query' field is changed from text to varchar, then I get the following:

Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Original table:

| log_profiling_backup | CREATE TABLE `log_profiling_backup` (
`query` text NOT NULL,
`count` int(10) unsigned DEFAULT NULL,
`sum_time_ms` float DEFAULT NULL,
UNIQUE KEY `query` (`query`(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

Answer 1

Maybe you could try the following:

ALTER TABLE piwik_log_profiling RENAME piwik_log_profiling_old;
CREATE TABLE piwik_log_profiling SELECT * FROM piwik_log_profiling_old WHERE 1=2;
ALTER TABLE piwik_log_profiling ADD COLUMN query100 CHAR(100) NOT NULL AFTER query;
ALTER TABLE piwik_log_profiling ADD UNIQUE INDEX (query100);
ALTER TABLE piwik_log_profiling ENGINE=NDBCLUSTER;

If it works up to now, them do this

INSERT INTO piwik_log_profiling (query)
SELECT query,LEFT(query,100),`count`,sum_time_ms
FROM piwik_log_profiling_old;

Based on this, your queries will have to use query100 instead of query in its WHERE clauses.

Please let us all know if this worked !!!