Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
share|improve this question

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 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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.