Skip to content

Instantly share code, notes, and snippets.

@shield-9
Created November 8, 2014 08:51
Show Gist options
  • Save shield-9/d64bc8bb06bb27845382 to your computer and use it in GitHub Desktop.
Save shield-9/d64bc8bb06bb27845382 to your computer and use it in GitHub Desktop.
Convert WordPress Database from MyISAM to InnoDB (This works for only me!)
ALTER TABLE ew_wp_commentmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_commentmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_commentmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_commentmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_commentmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_commentmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_commentmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_commentmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_comments ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_comments ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_comments ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_comments ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_options ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_options ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_options ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_options ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_options CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_options CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_options CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_options CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_postmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_postmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_postmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_postmeta ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_postmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_postmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_postmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_postmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_posts ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_posts ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_posts ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_posts ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_terms ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_terms ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_terms ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_terms ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_terms CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_terms CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_terms CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_terms CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_term_relationships ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_term_relationships ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_term_relationships ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_term_relationships ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_term_relationships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_term_relationships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_term_relationships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_term_relationships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_term_taxonomy ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_2_term_taxonomy ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_3_term_taxonomy ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_5_term_taxonomy ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
ALTER TABLE ew_wp_term_taxonomy CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_2_term_taxonomy CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_3_term_taxonomy CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE ew_wp_5_term_taxonomy CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
@korobochkin
Copy link

Yep. I have same issue. Its fun that WordPress can't install from the box with utf8mb4 because SQL create queries without row_format option and SQL server set up it to default = Compact and this row_format not work with utf8mb4.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment