3

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 |

migrated from serverfault.com Feb 28 '12 at 4:14

This question came from our site for system and network administrators.

  • Changing the index to 'normal' fixes this... but... is that the safest thing to do? I am not a mysql expert. :) Thanks! – Clustermagnet Feb 27 '12 at 23:49
  • I guess the question here is... whats the safest change that you can do to the table to be ndb-compliant :) THANKS! – Clustermagnet Feb 27 '12 at 23:50
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 !!!

Your Answer

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