MyIASM to InnoDB suggestion in ACP - Question why some tables are MyIASM

[sibomots]

Scenario.  IPS v 4.x

The Support ACP panel suggests that the database is not using InnoDB  Engine for tables.

I coulda swore that I had done that so I checked.

select table_name, engine
from information_schema.tables
where  table_schema = 'database_name';

Sure enough all of the tables are InnoDB except for a handful:

select table_name, engine
from information_schema.tables
where  table_schema = 'database_name' and
engine not like '%InnoDB';

Yields:

+---------------------+--------+
| table_name          | engine |
+---------------------+--------+
| ibf_ams_content     | MyISAM |
| ibf_calendar_events | MyISAM |
| ibf_posts2          | MyISAM |
+---------------------+--------+
3 rows in set (0.01 sec)

 

The rest are InnoDB:

select  count(*),  engine
from information_schema.tables 
where  table_schema = 'database_name'
group by engine;

+----------+--------+
| count(*) | engine |
+----------+--------+
|      265 | InnoDB |
|        3 | MyISAM |
+----------+--------+

 

That's odd.  I was wondering then when did these three outlier tables get created?  Maybe long after the Upgrade finished?

Doesn't look like it.  The creation dates are in the same frame of time when the whole database was created:

Looking at creation times per table, there's less than day between latest creation date and earliest:

select A.table_name, timestampdiff(hour, create_time, d)
from (    select max(B.create_time) as d
          FROM information_schema.TABLES  B
          where B.table_schema='database_name'
          group by B.create_time) a,
information_schema.TABLES A where  A.table_schema = 'database_name'
group by A.table_name;

Yields

+-------------------------------------------+------+
| table_name                                | Diff |
+-------------------------------------------+------+
| ibf_core_files                            |  -20 |
| ibf_core_share_links                      |  -20 |
| ibf_core_sys_lang_words                   |  -20 |
| ibf_core_theme_templates                  |  -20 |
| ibf_downloads_downloads                   |  -20 |
| ibf_forums_topics                         |  -20 |
| ibf_core_profanity_filters                |  -20 |
| ibf_core_soft_delete_log                  |  -20 |
| ibf_core_tags                             |  -20 |
| ibf_core_voters                           |  -20 |
| ibf_downloads_files_records               |  -20 |
| ibf_calendar_event_reviews                |  -20 |
| ibf_core_admin_logs                       |  -20 |
| ibf_core_members_warn_logs                |  -20 |
| ibf_forums_archive_posts                  |  -20 |
| ibf_calendar_venues                       |  -20 |
| ibf_core_item_markers                     |  -20 |
| ibf_core_member_ranks                     |  -20 |
| ibf_core_message_topics                   |  -20 |
| ibf_core_pfields_data                     |  -20 |
| ibf_core_sessions                         |  -20 |
| ibf_core_sys_lang                         |  -20 |
| ibf_downloads_comments                    |  -20 |
| ibf_forums_topic_mmod                     |  -20 |
| ibf_core_clubs_node_map                   |  -20 |
| ibf_core_notification_preferences         |  -20 |
| ibf_core_sys_social_groups                |  -20 |
| ibf_downloads_files_pending               |  -20 |
| ibf_gallery_albums                        |  -20 |
| ibf_calendar_event_reminders              |  -20 |
| ibf_core_admin_login_logs                 |  -20 |
| ibf_core_members_warn_actions             |  -20 |
| ibf_core_search_index                     |  -20 |
| ibf_gallery_images_uploads                |  -20 |
| ibf_calendar_import_map                   |  -20 |
| ibf_core_member_history                   |  -20 |
| ibf_core_message_topic_user_map           |  -20 |
| ibf_core_pfields_content                  |  -20 |
| ibf_core_seo_meta                         |  -20 |
| ibf_core_sys_cp_sessions                  |  -20 |
| ibf_core_theme_settings_fields            |  -20 |
| ibf_downloads_cfields                     |  -20 |
| ibf_forums_question_ratings               |  -20 |
| ibf_core_applications                     |  -20 |
| ibf_core_notification_defaults            |  -20 |
| ibf_core_polls                            |  -20 |
| ibf_core_sys_social_group_members         |  -20 |
| ibf_core_validating                       |  -20 |
| ibf_downloads_files_notify                |  -20 |
| ibf_core_dev                              |  -20 |
| ibf_core_oauth_authorize_prompts          |  -20 |
| ibf_core_question_and_answer              |  -20 |
| ibf_core_tasks                            |  -20 |
| ibf_downloads_sessions                    |  -20 |
| ibf_gallery_images                        |  -20 |
| ibf_calendar_event_comments               |  -20 |
| ibf_calendar_import_feeds                 |  -20 |
| ibf_core_announcements                    |  -20 |
| ibf_core_bulk_mail                        |  -20 |
| ibf_core_error_logs                       |  -20 |
| ibf_core_message_posts                    |  -20 |
| ibf_core_permission_index                 |  -20 |
| ibf_core_rc_reports                       |  -20 |
| ibf_core_sys_conf_settings                |  -20 |
| ibf_core_theme_resources                  |  -20 |
| ibf_forums_posts                          |  -20 |
| ibf_core_follow                           |  -20 |
| ibf_core_member_status_updates            |  -20 |
| ibf_core_modules                          |  -20 |
| ibf_core_upgrade_history                  |  -20 |
| ibf_downloads_files                       |  -20 |
| ibf_gallery_album_comments                |  -20 |
| ibf_core_acp_tab_order                    |  -20 |
| ibf_core_groups                           |  -20 |
| ibf_core_spam_service_log                 |  -20 |
| ibf_core_tags_perms                       |  -20 |
| ibf_downloads_reviews                     |  -20 |
| ibf_gallery_comments                      |  -20 |
| ibf_calendar_calendars                    |  -20 |
| ibf_calendar_events                       |  -20 |
| ibf_core_banfilters                       |  -20 |
| ibf_core_emoticons                        |  -20 |
| ibf_core_incoming_emails                  |  -20 |
| ibf_core_rc_index                         |  -20 |
| ibf_core_theme_css                        |  -20 |
| ibf_downloads_categories                  |  -20 |
| ibf_forums_forums                         |  -20 |
| ibf_core_files_temp                       |  -20 |
| ibf_core_member_status_replies            |  -20 |
| ibf_core_reputation_index                 |  -20 |
| ibf_core_themes                           |  -20 |
| ibf_downloads_filebackup                  |  -20 |
| ibf_core_attachments                      |  -20 |
| ibf_core_tags_cache                       |  -20 |
| ibf_gallery_categories                    |  -20 |
| ibf_calendar_event_rsvp                   |  -20 |
| ibf_core_admin_permission_rows            |  -20 |
| ibf_core_mail_error_logs                  |  -20 |
| ibf_core_members_warn_reasons             |  -20 |
| ibf_core_oauth_server_authorization_codes |  -20 |
| ibf_core_rc_comments                      |  -20 |
| ibf_forums_archive_rules                  |  -20 |
| ibf_gallery_bandwidth                     |  -19 |
| ibf_gallery_reviews                       |  -19 |
| ibf_gallery_album_reviews                 |  -19 |
| ibf_forums_answer_ratings                 |  -18 |
| ibf_forums_view_method                    |  -18 |
| ibf_core_achievements_log                 |  -17 |
| ibf_core_api_logs                         |  -17 |
| ibf_core_clubs                            |  -17 |
| ibf_core_item_member_map                  |  -17 |
| ibf_core_member_recognize                 |  -17 |
| ibf_core_moderator_logs                   |  -17 |
| ibf_core_pfields_groups                   |  -17 |
| ibf_core_referrals                        |  -17 |
| ibf_core_acp_notifications_preferences    |  -17 |
| ibf_core_content_featured                 |  -17 |
| ibf_core_googleauth_used_codes            |  -17 |
| ibf_core_members_features                 |  -17 |
| ibf_core_rss_import                       |  -17 |
| ibf_core_automatic_moderation_types       |  -17 |
| ibf_core_editor_stored_replies            |  -17 |
| ibf_core_ignored_users                    |  -17 |
| ibf_core_login_methods                    |  -17 |
| ibf_core_oauth_server_access_tokens       |  -17 |
| ibf_core_ratings                          |  -17 |
| ibf_core_search_index_item_map            |  -17 |
| ibf_core_store                            |  -17 |
| ibf_core_api_keys                         |  -17 |
| ibf_core_club_pages                       |  -17 |
| ibf_core_referral_banners                 |  -17 |
| ibf_core_acp_notifications                |  -17 |
| ibf_core_geoip_cache                      |  -17 |
| ibf_core_members_feature_seen             |  -17 |
| ibf_core_post_before_registering          |  -17 |
| ibf_core_rss_export                       |  -17 |
| ibf_core_social_promote_sharers           |  -17 |
| ibf_core_view_updates                     |  -17 |
| ibf_core_automatic_moderation_rules       |  -17 |
| ibf_core_edit_history                     |  -17 |
| ibf_core_login_links                      |  -17 |
| ibf_core_oauth_clients                    |  -17 |
| ibf_core_queue                            |  -17 |
| ibf_core_statistics                       |  -17 |
| ibf_core_anonymous_posts                  |  -17 |
| ibf_core_ips_bulletins                    |  -17 |
| ibf_core_reactions                        |  -17 |
| ibf_core_acp_notifcations_dismissals      |  -17 |
| ibf_core_clubs_memberships                |  -17 |
| ibf_core_follow_count_cache               |  -17 |
| ibf_core_javascript                       |  -17 |
| ibf_core_members                          |  -17 |
| ibf_core_social_promote_content           |  -17 |
| ibf_core_acronyms                         |  -17 |
| ibf_core_automatic_moderation_pending     |  -17 |
| ibf_core_members_logins                   |  -17 |
| ibf_core_saved_charts                     |  -17 |
| ibf_core_spam_whitelist                   |  -17 |
| ibf_core_member_badges                    |  -17 |
| ibf_core_security_questions               |  -17 |
| ibf_core_achievements_rules               |  -17 |
| ibf_core_api_webhooks                     |  -17 |
| ibf_core_clubs_fieldvalues                |  -17 |
| ibf_core_item_statistics_cache            |  -17 |
| ibf_core_points_log                       |  -17 |
| ibf_core_reputation_leaderboard_history   |  -17 |
| ibf_core_social_promote                   |  -17 |
| ibf_core_attachments_map                  |  -17 |
| ibf_core_deletion_log                     |  -17 |
| ibf_core_log                              |  -17 |
| ibf_core_members_known_ip_addresses       |  -17 |
| ibf_core_notifications_pwa_queue          |  -17 |
| ibf_core_profile_steps                    |  -17 |
| ibf_core_s3_deletions                     |  -17 |
| ibf_core_advertisements                   |  -17 |
| ibf_core_marketplace_tokens               |  -17 |
| ibf_core_menu                             |  -17 |
| ibf_core_output_cache                     |  -17 |
| ibf_core_security_answers                 |  -17 |
| ibf_core_streams                          |  -17 |
| ibf_core_achievements_log_milestones      |  -17 |
| ibf_core_api_webhook_fires                |  -17 |
| ibf_core_clubs_fields                     |  -17 |
| ibf_core_item_redirect                    |  -17 |
| ibf_core_moderators                       |  -17 |
| ibf_core_plugins                          |  -17 |
| ibf_core_sitemap                          |  -17 |
| ibf_core_content_meta                     |  -17 |
| ibf_core_group_promotions                 |  -17 |
| ibf_core_members_known_devices            |  -17 |
| ibf_core_notifications_pwa_keys           |  -17 |
| ibf_core_profile_completion               |  -17 |
| ibf_core_rss_imported                     |  -17 |
| ibf_core_solved_index                     |  -17 |
| ibf_core_badges                           |  -17 |
| ibf_core_email_templates                  |  -17 |
| ibf_core_image_proxy                      |  -17 |
| ibf_core_search_index_tags                |  -17 |
| ibf_core_stream_subscriptions             |  -17 |
| ibf_core_notifications                    |  -16 |
| ibf_core_theme_settings_values            |  -16 |
| ibf_core_leaders                          |  -16 |
| ibf_core_tasks_log                        |  -16 |
| ibf_core_cache                            |  -16 |
| ibf_core_file_logs                        |  -16 |
| ibf_core_hooks                            |  -16 |
| ibf_core_login_handlers                   |  -16 |
| ibf_core_widgets                          |  -16 |
| ibf_core_widget_trash                     |  -16 |
| ibf_core_acp_search_index                 |  -16 |
| ibf_core_leaders_groups                   |  -14 |
| ibf_core_theme_conflict                   |  -14 |
| ibf_core_file_storage                     |  -14 |
| ibf_core_reputation_levels                |  -14 |
| ibf_core_widget_areas                     |  -14 |
| ibf_core_theme_content_history            |  -14 |
| ibf_ipbwiki_group_rights                  |    0 |
| ibf_subscriptions                         |    0 |
| ibf_blog_authmembers                      |    0 |
| ibf_core_archive_restore                  |    0 |
| ibf_links_ratings                         |    0 |
| ibf_blog_tracker_queue                    |    0 |
| ibf_custom_pages                          |    0 |
| ibf_stats                                 |    0 |
| ibf_ams_content                           |    0 |
| ibf_ipbwiki_group                         |    0 |
| ibf_subscription_trans                    |    0 |
| ibf_bbcode_mediatag                       |    0 |
| ibf_core_archive_log                      |    0 |
| ibf_links_comments                        |    0 |
| ibf_blog_tracker                          |    0 |
| ibf_core_hooks_files                      |    0 |
| ibf_custom_bbcode                         |    0 |
| ibf_rc_reports_index                      |    0 |
| ibf_ams_categories                        |    0 |
| ibf_ipbwiki_discussion_topic              |    0 |
| ibf_subscription_methods                  |    0 |
| ibf_ams_watchlists                        |    0 |
| ibf_links_cats                            |    0 |
| ibf_blog_read                             |    0 |
| ibf_posts2                                |    0 |
| ibf_ams_attachments                       |    0 |
| ibf_core_inline_messages                  |    0 |
| ibf_downloads_ccontent                    |    0 |
| ibf_ipbwiki_conf                          |    0 |
| ibf_subscription_logs                     |    0 |
| ibf_ams_upgrade_history                   |    0 |
| ibf_core_sys_settings_titles              |    0 |
| ibf_links                                 |    0 |
| x_utf_ibf_convert_session_tables          |    0 |
| ibf_blog_headers                          |    0 |
| ibf_message_text                          |    0 |
| ibf_ams_articles                          |    0 |
| ibf_installed_mods                        |    0 |
| ibf_subscription_extra                    |    0 |
| ibf_ams_rating                            |    0 |
| ibf_core_sys_login                        |    0 |
| ibf_ipbwiki_skin                          |    0 |
| x_utf_ibf_convert_session                 |    0 |
| ibf_blog_bookmarks                        |    0 |
| ibf_core_like_cache                       |    0 |
| ibf_downloads_ip2ext                      |    0 |
| ibf_member_awards                         |    0 |
| ibf_blog_upgrade_history                  |    0 |
| ibf_custom_sidebar_blocks                 |    0 |
| ibf_images                                |    0 |
| ibf_subscription_currency                 |    0 |
| ibf_ams_logs                              |    0 |
+-------------------------------------------+------+

 

The results are per each table in the DB, show the difference between that table's creation time and the max creation time overall in that database, less than 24hrs.  Definitely during the process of initial upgrade.
 

The point is that these three tables were created by the IPS SW during the upgrade process in the MyIASM engine while the rest of the tables were created in InnoDB engine.

I cannot figure out why these tables were special, and since created by the IPS SW why these three were created with the MyIASM engine while the rest InnoDB.

 

Edited June 23, 2022 by sibomots

[Ryan Ashbrook]

The only table that would have been created during upgrade would have been the calendar_events table - and technically, it wasn't actually created but rather renamed from the old cal_events table for the Calendar application.

I am unsure of the other two tables - the first one looks like a third party application, and the second one looks to have been a copy of the posts table (which was renamed to forums_posts). Searching through both IPS4 and IP.Board 3, and there is no reference at all to any "posts2" table.

Regardless of where they came from, it is fine to simply change them to InnoDB.

[sibomots]

1 minute ago, Ryan Ashbrook said:

The only table that would have been created during upgrade would have been the calendar_events table - and technically, it wasn't actually created but rather renamed from the old cal_events table for the Calendar application.

I am unsure of the other two tables - the first one looks like a third party application, and the second one looks to have been a copy of the posts table (which was renamed to forums_posts). Searching through both IPS4 and IP.Board 3, and there is no reference at all to any "posts2" table.

Regardless of where they came from, it is fine to simply change them to InnoDB.

That's sorta what I just realized after I posted.  The conjecture I have is that in the legacy schema those three tables existed and they had (for some reason been in MyIASM) so when I re-created the DB they (just as expected) got created just as the mysqldump instructed them to be.

Hence why they are MyIASM.  

My bad.  I'll take care of it.

Thanks for the insight.

 

In terms of the other recommendations, they are:

1. Fixed to Dynamic row type
2. MyIASM to InnoDB
3. and UTF8MB4

I don't remember in MySQL which order of those makes the most sense efficiency wise.

I am thinking it makes more sense to do:

1. MyIASM to InnoDB first,
2. then Fixed to Dyamic
3. then to UTF8MB4

MySQL 5.7

 

[Ryan Ashbrook]

At the end of the day, I'm not sure it really matters, so whichever you feel is best will work fine.

[Marc Stridgen]

It would also be best to check the default table type on your mysql instance is set to INNODB, so you dont have any come up in the future