sibomots Posted June 23, 2022 Posted June 23, 2022 (edited) 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
Solution Ryan Ashbrook Posted June 23, 2022 Solution Posted June 23, 2022 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 Posted June 23, 2022 Author Posted June 23, 2022 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: Fixed to Dynamic row type MyIASM to InnoDB 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: MyIASM to InnoDB first, then Fixed to Dyamic then to UTF8MB4 MySQL 5.7
Ryan Ashbrook Posted June 23, 2022 Posted June 23, 2022 At the end of the day, I'm not sure it really matters, so whichever you feel is best will work fine.
Marc Posted June 24, 2022 Posted June 24, 2022 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
Recommended Posts