Converting Last few tables to innodb

[Ocean West]

i can't seem get these last remaining tables to be InnoDB with out getting the error - any suggestions?

[Bluto]

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

Hope this helps.

[bfarber]

The error message is indicating that the size of an index on the MyISAM tables is too large for InnoDB. You can edit the indexes on those tables to reduce their size (how many chars of each column are indexed) and then convert the table.

[Ocean West]

i am not clear how to do that is there an SQL statement that can do that? 

 

[bfarber]

14 hours ago, Ocean West said:

i am not clear how to do that is there an SQL statement that can do that? 

 

No, this requires someone with MySQL knowledge to log in to the MySQL server and perform the actions manually.

[Ocean West]

I am hoping someone here with MySQL knowledge would share, this knowledge. 

 

 

[bfarber]

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.

[Ocean West]

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 following

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.

[bfarber]

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.

[Ocean West]

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 

 

[bfarber]

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.