Jump to content

Community

Converting Last few tables to innodb


Ocean West
 Share

Recommended Posts

536475459_ScreenShot2018-04-24at7_12_03A

So the easiest way to fix this for you (not having MySQL knowledge).....

  • Truncate the core_search_index table in phpmyadmin
  • For each of the tables in your list, open the table up in phpmyadmin
  • In the indexes section, drop all indexes except the primary key (do NOT drop the primary key index)
  • Change each table to InnoDB
  • Go in to your AdminCP and run the support tool. It should tell you that some of your database tables need to be fixed (it should recognize the missing indexes) and allow you to automatically fix the structure or manually copy and paste the queries to fix the structure.
  • Under the search area in the AdminCP click the button to rebuild your search index

 

This is a big of a longer way around what you are after, but if you aren't able to analyze the indexes to figure out which ones to adjust these steps should do the trick.

Link to comment
Share on other sites

thank you for your assistance, its truly appreciated...

I was able to drop the indexes all except primary and convert most of the tables to InnoDB except the following10469513_ScreenShot2018-05-03at6_57_40AM.thumb.png.68d39bb3c0af2a3aeaf662e0f69e6b36.png

I started to run the ACP tool and got this list of queries to run but trying to figure out why so many are going to be converting back to MyISAM even though i have InnoDB as default in my conf_global.php? - should i have my ISP wave their magic wand to make sure that the default engine is INNODB for all new tables first?

CREATE TABLE `core_widgets_new` LIKE `core_widgets`;
ALTER TABLE `core_widgets_new` ENGINE=MyISAM, ADD UNIQUE KEY `widget_key` (`key`(191)), ENGINE=MyISAM, ADD KEY `widget_app` (`app`(191)), ENGINE=MyISAM, ADD KEY `widget_plugin` (`plugin`(191));
INSERT IGNORE INTO `core_widgets_new` SELECT * FROM `core_widgets`;
DROP TABLE `core_widgets`;
RENAME TABLE `core_widgets_new` TO `core_widgets`;
CREATE TABLE `core_search_index_new` LIKE `core_search_index`;
ALTER TABLE `core_search_index_new` ENGINE=MyISAM, ADD UNIQUE KEY `object` (`index_class`(181),`index_object_id`), ENGINE=MyISAM, ADD KEY `item` (`index_class`(181),`index_item_id`), ENGINE=MyISAM, ADD KEY `container` (`index_class`(181),`index_container_id`), ENGINE=MyISAM, ADD KEY `index_date_updated` (`index_date_updated`), ENGINE=MyISAM, ADD KEY `index_date_created` (`index_date_created`), ENGINE=MyISAM, ADD KEY `index_hidden` (`index_hidden`), ENGINE=MyISAM, ADD KEY `index_prune` (`index_item_id`,`index_date_updated`), ENGINE=MyISAM, ADD KEY `index_class_and_object_id` (`index_class`(181),`index_object_id`), ENGINE=MyISAM, ADD KEY `index_class_type_id_hash` (`index_class_type_id_hash`), ENGINE=MyISAM, ADD KEY `index_club_id` (`index_club_id`), ENGINE=MyISAM, ADD KEY `author_lookup` (`index_author`,`index_class`(170),`index_hidden`,`index_date_updated`);
INSERT IGNORE INTO `core_search_index_new` SELECT * FROM `core_search_index`;
DROP TABLE `core_search_index`;
RENAME TABLE `core_search_index_new` TO `core_search_index`;
ALTER TABLE `core_search_index` ENGINE=MyISAM, ADD FULLTEXT KEY `index_content` (`index_content`(123),`index_title`(126));
ALTER TABLE `core_search_index` ENGINE=MyISAM, ADD FULLTEXT KEY `index_title` (`index_title`(250));
CREATE TABLE `core_oauth_server_authorization_codes_new` LIKE `core_oauth_server_authorization_codes`;
ALTER TABLE `core_oauth_server_authorization_codes_new` ENGINE=MyISAM, ADD UNIQUE KEY `authorization_code` (`client_id`,`code`);
INSERT IGNORE INTO `core_oauth_server_authorization_codes_new` SELECT * FROM `core_oauth_server_authorization_codes`;
DROP TABLE `core_oauth_server_authorization_codes`;
RENAME TABLE `core_oauth_server_authorization_codes_new` TO `core_oauth_server_authorization_codes`;
CREATE TABLE `core_login_links_new` LIKE `core_login_links`;
ALTER TABLE `core_login_links_new` ENGINE=MyISAM, ADD UNIQUE KEY `token_identifier` (`token_login_method`,`token_identifier`(181)), ENGINE=MyISAM, ADD KEY `token_member` (`token_member`);
INSERT IGNORE INTO `core_login_links_new` SELECT * FROM `core_login_links`;
DROP TABLE `core_login_links`;
RENAME TABLE `core_login_links_new` TO `core_login_links`;
CREATE TABLE `cms_page_widget_areas_new` LIKE `cms_page_widget_areas`;
ALTER TABLE `cms_page_widget_areas_new` ENGINE=MyISAM, ADD UNIQUE KEY `area_page_area` (`area_page_id`,`area_area`(191));
INSERT IGNORE INTO `cms_page_widget_areas_new` SELECT * FROM `cms_page_widget_areas`;
DROP TABLE `cms_page_widget_areas`;
RENAME TABLE `cms_page_widget_areas_new` TO `cms_page_widget_areas`;
CREATE TABLE `cms_databases_new` LIKE `cms_databases`;
ALTER TABLE `cms_databases_new` ENGINE=MyISAM, ADD UNIQUE KEY `database_key` (`database_key`(191)), ENGINE=MyISAM, ADD KEY `forum_lookup` (`database_forum_record`,`database_forum_forum`,`database_forum_comments`);
INSERT IGNORE INTO `cms_databases_new` SELECT * FROM `cms_databases`;
DROP TABLE `cms_databases`;
RENAME TABLE `cms_databases_new` TO `cms_databases`;
ALTER TABLE `downloads_comments` ENGINE=MyISAM, ADD KEY `comment_fid` (`comment_fid`,`comment_date`), ENGINE=MyISAM, ADD KEY `comment_ip_address` (`comment_ip_address`), ENGINE=MyISAM, ADD KEY `comment_approved` (`comment_open`,`comment_date`), ENGINE=MyISAM, ADD KEY `comment_mid` (`comment_mid`,`comment_date`);
ALTER TABLE `nexus_licensekeys` ADD KEY `lkey_purchase` (`lkey_purchase`), ADD KEY `lkey_member` (`lkey_member`);

Thanks.

Link to comment
Share on other sites

Yes, you should get your hosting provider to ensure InnoDB is the default engine in your environment. You can adjust the engine in the queries yourself before running them if you want, however the calculations for the multi-column indexes may be off and result in the same SQL errors you previously faced.

Three of those tables in your screenshot have 0 rows and are not critical to being able to access the AdminCP, so if you're having trouble with them you could just drop the table entirely and then run the support tool again (which will give you the create table statements to use). Also, if you drop core_sitemap it will automatically repopulate as well if that is the easiest route for you to go.

Link to comment
Share on other sites

thank you that worked great there is one table that just won't convert i've tried setting it to INNODB manually and or convert after it is recreated. 

Not sure if it is a bug (opportunity for improvement)  or not but shouldn't this code generation default the ENGINE to that which is found in my global conf? 

 

CREATE TABLE `cms_database_fields_thumbnails` (
	`thumb_original_location` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' ,
	`thumb_location` VARCHAR (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' ,
	`thumb_field_id` INT (10) NOT NULL DEFAULT 0 ,
	`thumb_database_id` INT (10) NOT NULL DEFAULT 0 ,
	`thumb_record_id` INT (10) UNSIGNED NOT NULL DEFAULT 0 ,
	PRIMARY KEY (`thumb_original_location`(250))
)
ENGINE MyISAM CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 

 

Link to comment
Share on other sites

We don't really look at a default engine setting in conf_global.php in 4.x. Instead, we use the default engine specified at the server level, which is why I suggested contacting your host and asking them to change which engine is the default.

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

We use technologies, such as cookies, to customise content and advertising, to provide social media features and to analyse traffic to the site. We also share information about your use of our site with our trusted social media, advertising and analytics partners. See more about cookies and our Privacy Policy