0

How can I fix conversion error from InnoDB to MyISAM for these tables?

MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
MariaDB [db_name]> ALTER TABLE wp_postmeta ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
+-------------------+------------+-------------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                                              | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+-------------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY                                               |            1 | ID             | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name                                             |            1 | post_name      | A         |      419351 |      191 | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            1 | post_type      | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            2 | post_status    | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            3 | post_date      | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | type_status_date                                      |            4 | ID             | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_parent                                           |            1 | post_parent    | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_author                                           |            1 | post_author    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_modified                   |            1 | post_modified  | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_post_type  |            1 | post_type      | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_post_type  |            2 | post_status    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_post_type  |            3 | post_mime_type | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_passw_post_statu |            1 | post_type      | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_passw_post_statu |            2 | post_password  | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_passw_post_statu |            3 | post_status    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_id         |            1 | post_type      | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_id         |            2 | post_status    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_type_post_statu_id         |            3 | ID             | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | wp_posts_idx_post_date                       |            1 | post_date      | A         |      419351 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+-------------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field                 | Type                | Null | Key | Default             | Extra          |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   |     | NULL                |                |
| post_title            | text                | NO   |     | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(255)        | NO   |     |                     |                |
| post_name             | varchar(200)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext            | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+-----------------------+---------------------+------+-----+---------------------+----------------+
+----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                | Non_unique | Key_name                                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_postmeta |          0 | PRIMARY                                 |            1 | meta_id     | A         |     3777303 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | post_id                                 |            1 | post_id     | A         |     1259101 |     NULL | NULL   |      | BTREE      |         |               |
| wp_postmeta |          1 | wp_postme_idx_meta_key_post_id |            1 | meta_key    | A         |       23906 |     NULL | NULL   | YES  | BTREE      |         |               |
| wp_postmeta |          1 | wp_postme_idx_meta_key_post_id |            2 | post_id     | A         |     3777303 |     NULL | NULL   |      | BTREE      |         |               |
+----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
  • 2
    Hi and welcome to the forum! Check here and here - InnoDBs index limit is bigger than MyISAM's! You obviously have indexes which are too big for MyISAM. What I'd like to know is why on earth are you moving to an engine which is deprecated? You'll have to redesign your system what's more! – Vérace May 11 at 4:04
  • @Vérace lol, hi, thx a lot. i cant deal with the required ram amount of the innodb, 6.8gb innodb size requires much more space to store it in the memory which i cant handle to pay right now. Using a server with 3.7gb memory. – execash May 11 at 4:09
  • 1
    the entire database doesn't need to go to innodb buffer pool, only the active stuff that is frequently used/modified. MyISAM will work the same way with the inactive pages not being in the linux kernel page cache. – danblack May 11 at 4:34
  • 1
    Maybe your meta_key can be reduced to the maximum size used (also the smallest character set used). Can you use SHOW CREATE TABLE {tablename} as this includes datatypes in an easier to read format. – danblack May 11 at 4:36
  • 1
    SHOW CREATE TABLE is more descriptive than DESCRIBE. – Rick James May 11 at 4:39
2

NO! That's the wrong solution to a memory issue.

The buffer_pool is a "cache"; it does not have to be bigger than the dataset.

InnoDB is faster than MyISAM; this point, alone, may doom your conversion to disaster.

The 1000 comes from 4*255, where the 4 comes from using ut8mb4 (good) and WP's default of using VARCHAR(255) (not good). Check your data, probably you come no where near to using 255 characters in any of those columns. Or maybe sometimes you have exceed 255 and had the data silently truncated!.

Here are tips on speeding up postmeta and making it smaller: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Another bulky thing that WP unnecessarily does: BIGINT (8 bytes) for lots of columns. A 4-byte INT (2 billion max) would save a lot of space. (But that is rather tedious to fix.

For the main performance boost (Caveat: I have not verified it):

ALTER TABLE wp_postmeta
    DROP PRIMARY KEY,
    ADD PRIMARY KEY(post_id, meta_key),
    ADD INDEX(meta_id) ;
| improve this answer | |
  • How can i convert CREATE TABLE wp_postmeta ( meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, post_id bigint(20) unsigned NOT NULL DEFAULT '0', meta_key varchar(255) DEFAULT NULL, meta_value longtext, PRIMARY KEY (meta_id), KEY post_id (post_id), KEY meta_key (meta_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; to this CREATE TABLE wp_postmeta ( post_id BIGINT UNSIGNED NOT NULL, meta_key VARCHAR(255) NOT NULL, meta_value LONGTEXT NOT NULL, PRIMARY KEY(post_id, meta_key), INDEX(meta_key) ) ENGINE=InnoDB; – execash May 11 at 5:00
  • Do a dump. Change all the engines that you want - substitute Engine=MyISAM for Engine=InnoDB for the relevant tables and then restore the (modified) dump! However, you have been told that this change is not a good idea - very few databases fit entirely in RAM - they're not meant to and systems such as MySQL have been designed with this in mind! – Vérace May 11 at 5:09
  • @execash - I added the ALTER. – Rick James May 11 at 5:22
  • @Vérace - his comment is about the change to postmeta, not about switching to MyISAM. – Rick James May 11 at 5:24
  • Ooops! Thanks for the heads up! – Vérace May 11 at 5:29

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.