[Deploying Sakai] MySQL: MyISAM or InnoDB?
Warwick Chapman
warwickchapman at gmail.com
Tue May 7 02:22:14 PDT 2013
Steve
I have just done a check on the engine type for all the tables in our Sakai
DB and the result follows. Can I safely convert all the MyISAM's to InnoDB
without failure?:
mysql> select table_name,engine from information_schema.tables where
table_schema="sakai";
+--------------------------------+--------+
| table_name | engine |
+--------------------------------+--------+
| ANNOUNCEMENT_CHANNEL | InnoDB |
| ANNOUNCEMENT_MESSAGE | InnoDB |
| ASN_AP_ITEM_ACCESS_T | InnoDB |
| ASN_AP_ITEM_T | InnoDB |
| ASN_MA_ITEM_T | InnoDB |
| ASN_NOTE_ITEM_T | InnoDB |
| ASN_SUP_ATTACH_T | InnoDB |
| ASN_SUP_ITEM_T | InnoDB |
| ASSIGNMENT_ASSIGNMENT | InnoDB |
| ASSIGNMENT_CONTENT | InnoDB |
| ASSIGNMENT_SUBMISSION | InnoDB |
| BBB_MEETING | InnoDB |
| BBB_MEETING_PARTICIPANT | InnoDB |
| CALENDAR_CALENDAR | InnoDB |
| CALENDAR_EVENT | InnoDB |
| CHAT2_CHANNEL | InnoDB |
| CHAT2_MESSAGE | InnoDB |
| CITATION_CITATION | InnoDB |
| CITATION_COLLECTION | InnoDB |
| CITATION_SCHEMA | InnoDB |
| CITATION_SCHEMA_FIELD | InnoDB |
| CMN_TYPE_T | InnoDB |
| CM_ACADEMIC_SESSION_T | InnoDB |
| CM_COURSE_SET_CANON_ASSOC_T | InnoDB |
| CM_COURSE_SET_OFFERING_ASSOC_T | InnoDB |
| CM_CROSS_LISTING_T | InnoDB |
| CM_ENROLLMENT_SET_T | InnoDB |
| CM_ENROLLMENT_T | InnoDB |
| CM_MEETING_T | InnoDB |
| CM_MEMBERSHIP_T | InnoDB |
| CM_MEMBER_CONTAINER_T | InnoDB |
| CM_OFFICIAL_INSTRUCTORS_T | InnoDB |
| CM_SEC_CATEGORY_T | InnoDB |
| CONTENT_COLLECTION | InnoDB |
| CONTENT_DROPBOX_CHANGES | InnoDB |
| CONTENT_RESOURCE | InnoDB |
| CONTENT_RESOURCE_BODY_BINARY | InnoDB |
| CONTENT_RESOURCE_DELETE | InnoDB |
| CONTENT_TYPE_REGISTRY | InnoDB |
| DIRECTORYEDITOR_FIELDS | MyISAM |
| EMAIL_TEMPLATE_ITEM | InnoDB |
| ENTITY_PROPERTIES | InnoDB |
| ENTITY_TAG_APPLICATIONS | InnoDB |
| GB_CATEGORY_T | InnoDB |
| GB_COMMENT_T | InnoDB |
| GB_GRADABLE_OBJECT_T | InnoDB |
| GB_GRADEBOOK_T | InnoDB |
| GB_GRADE_MAP_T | InnoDB |
| GB_GRADE_RECORD_T | InnoDB |
| GB_GRADE_TO_PERCENT_MAPPING_T | InnoDB |
| GB_GRADING_EVENT_T | InnoDB |
| GB_GRADING_SCALE_GRADES_T | InnoDB |
| GB_GRADING_SCALE_PERCENTS_T | InnoDB |
| GB_GRADING_SCALE_T | InnoDB |
| GB_LETTERGRADE_MAPPING | InnoDB |
| GB_LETTERGRADE_PERCENT_MAPPING | InnoDB |
| GB_PERMISSION_T | InnoDB |
| GB_PROPERTY_T | InnoDB |
| GB_SPREADSHEET_T | InnoDB |
| MAILARCHIVE_CHANNEL | InnoDB |
| MAILARCHIVE_MESSAGE | InnoDB |
| MFR_AREA_T | InnoDB |
| MFR_ATTACHMENT_T | InnoDB |
| MFR_DATE_RESTRICTIONS_T | InnoDB |
| MFR_EMAIL_NOTIFICATION_T | InnoDB |
| MFR_HIDDEN_GROUPS_T | MyISAM |
| MFR_LABEL_T | InnoDB |
| MFR_MEMBERSHIP_ITEM_T | InnoDB |
| MFR_MESSAGE_T | InnoDB |
| MFR_OPEN_FORUM_T | InnoDB |
| MFR_PERMISSION_LEVEL_T | InnoDB |
| MFR_PRIVATE_FORUM_T | InnoDB |
| MFR_PVT_MSG_USR_T | InnoDB |
| MFR_SYNOPTIC_ITEM | InnoDB |
| MFR_TOPIC_T | InnoDB |
| MFR_UNREAD_STATUS_T | InnoDB |
| POLL_OPTION | InnoDB |
| POLL_POLL | InnoDB |
| POLL_VOTE | InnoDB |
| PROFILE_COMPANY_PROFILES_T | InnoDB |
| PROFILE_EXTERNAL_INTEGRATION_T | InnoDB |
| PROFILE_FRIENDS_T | InnoDB |
| PROFILE_GALLERY_IMAGES_T | InnoDB |
| PROFILE_IMAGES_EXTERNAL_T | InnoDB |
| PROFILE_IMAGES_OFFICIAL_T | InnoDB |
| PROFILE_IMAGES_T | InnoDB |
| PROFILE_KUDOS_T | InnoDB |
| PROFILE_MESSAGES_T | InnoDB |
| PROFILE_MESSAGE_PARTICIPANTS_T | InnoDB |
| PROFILE_MESSAGE_THREADS_T | InnoDB |
| PROFILE_PREFERENCES_T | InnoDB |
| PROFILE_PRIVACY_T | InnoDB |
| PROFILE_SOCIAL_INFO_T | InnoDB |
| PROFILE_STATUS_T | InnoDB |
| PROFILE_WALL_ITEMS_T | MyISAM |
| PROFILE_WALL_ITEM_COMMENTS_T | MyISAM |
| QRTZ_BLOB_TRIGGERS | InnoDB |
| QRTZ_CALENDARS | InnoDB |
| QRTZ_CRON_TRIGGERS | InnoDB |
| QRTZ_FIRED_TRIGGERS | InnoDB |
| QRTZ_JOB_DETAILS | InnoDB |
| QRTZ_JOB_LISTENERS | InnoDB |
| QRTZ_LOCKS | InnoDB |
| QRTZ_PAUSED_TRIGGER_GRPS | InnoDB |
| QRTZ_SCHEDULER_STATE | InnoDB |
| QRTZ_SIMPLE_TRIGGERS | InnoDB |
| QRTZ_TRIGGERS | InnoDB |
| QRTZ_TRIGGER_LISTENERS | InnoDB |
| SAKAI_ALIAS | InnoDB |
| SAKAI_ALIAS_PROPERTY | InnoDB |
| SAKAI_CLUSTER | InnoDB |
| SAKAI_DIGEST | InnoDB |
| SAKAI_EVENT | InnoDB |
| SAKAI_EVENT_DELAY | InnoDB |
| SAKAI_LOCKS | InnoDB |
| SAKAI_MESSAGE_BUNDLE | InnoDB |
| SAKAI_NOTIFICATION | InnoDB |
| SAKAI_PERSON_META_T | InnoDB |
| SAKAI_PERSON_T | InnoDB |
| SAKAI_POSTEM_GRADEBOOK | InnoDB |
| SAKAI_POSTEM_HEADINGS | InnoDB |
| SAKAI_POSTEM_STUDENT | InnoDB |
| SAKAI_POSTEM_STUDENT_GRADES | InnoDB |
| SAKAI_PREFERENCES | InnoDB |
| SAKAI_PRESENCE | InnoDB |
| SAKAI_PRIVACY_RECORD | InnoDB |
| SAKAI_REALM | InnoDB |
| SAKAI_REALM_FUNCTION | InnoDB |
| SAKAI_REALM_PROPERTY | InnoDB |
| SAKAI_REALM_PROVIDER | InnoDB |
| SAKAI_REALM_RL_FN | InnoDB |
| SAKAI_REALM_RL_GR | InnoDB |
| SAKAI_REALM_ROLE | InnoDB |
| SAKAI_REALM_ROLE_DESC | InnoDB |
| SAKAI_SESSION | InnoDB |
| SAKAI_SITE | InnoDB |
| SAKAI_SITE_GROUP | InnoDB |
| SAKAI_SITE_GROUP_PROPERTY | InnoDB |
| SAKAI_SITE_PAGE | InnoDB |
| SAKAI_SITE_PAGE_PROPERTY | InnoDB |
| SAKAI_SITE_PROPERTY | InnoDB |
| SAKAI_SITE_TOOL | InnoDB |
| SAKAI_SITE_TOOL_PROPERTY | InnoDB |
| SAKAI_SITE_USER | InnoDB |
| SAKAI_SYLLABUS_ATTACH | InnoDB |
| SAKAI_SYLLABUS_DATA | InnoDB |
| SAKAI_SYLLABUS_ITEM | InnoDB |
| SAKAI_USER | InnoDB |
| SAKAI_USER_ID_MAP | InnoDB |
| SAKAI_USER_PROPERTY | InnoDB |
| SAM_ANSWERFEEDBACK_T | InnoDB |
| SAM_ANSWER_T | InnoDB |
| SAM_ASSESSACCESSCONTROL_T | InnoDB |
| SAM_ASSESSEVALUATION_T | InnoDB |
| SAM_ASSESSFEEDBACK_T | InnoDB |
| SAM_ASSESSMENTBASE_T | InnoDB |
| SAM_ASSESSMENTGRADING_T | InnoDB |
| SAM_ASSESSMETADATA_T | InnoDB |
| SAM_ATTACHMENT_T | InnoDB |
| SAM_AUTHZDATA_T | InnoDB |
| SAM_FAVORITECOLCHOICESITEM_T | InnoDB |
| SAM_FAVORITECOLCHOICES_T | InnoDB |
| SAM_FUNCTIONDATA_T | InnoDB |
| SAM_GRADINGATTACHMENT_T | InnoDB |
| SAM_GRADINGSUMMARY_T | InnoDB |
| SAM_ITEMFEEDBACK_T | InnoDB |
| SAM_ITEMGRADING_T | InnoDB |
| SAM_ITEMMETADATA_T | InnoDB |
| SAM_ITEMTEXT_T | InnoDB |
| SAM_ITEM_T | InnoDB |
| SAM_MEDIA_T | InnoDB |
| SAM_PUBLISHEDACCESSCONTROL_T | InnoDB |
| SAM_PUBLISHEDANSWERFEEDBACK_T | InnoDB |
| SAM_PUBLISHEDANSWER_T | InnoDB |
| SAM_PUBLISHEDASSESSMENT_T | InnoDB |
| SAM_PUBLISHEDATTACHMENT_T | InnoDB |
| SAM_PUBLISHEDEVALUATION_T | InnoDB |
| SAM_PUBLISHEDFEEDBACK_T | InnoDB |
| SAM_PUBLISHEDITEMFEEDBACK_T | InnoDB |
| SAM_PUBLISHEDITEMMETADATA_T | InnoDB |
| SAM_PUBLISHEDITEMTEXT_T | InnoDB |
| SAM_PUBLISHEDITEM_T | InnoDB |
| SAM_PUBLISHEDMETADATA_T | InnoDB |
| SAM_PUBLISHEDSECTIONMETADATA_T | InnoDB |
| SAM_PUBLISHEDSECTION_T | InnoDB |
| SAM_PUBLISHEDSECUREDIP_T | InnoDB |
| SAM_QUALIFIERDATA_T | InnoDB |
| SAM_QUESTIONPOOLACCESS_T | InnoDB |
| SAM_QUESTIONPOOLITEM_T | InnoDB |
| SAM_QUESTIONPOOL_T | InnoDB |
| SAM_SECTIONMETADATA_T | InnoDB |
| SAM_SECTION_T | InnoDB |
| SAM_SECUREDIP_T | InnoDB |
| SAM_STUDENTGRADINGSUMMARY_T | InnoDB |
| SAM_TYPE_T | InnoDB |
| SCHEDULER_DELAYED_INVOCATION | InnoDB |
| SITEASSOC_CONTEXT_ASSOCIATION | InnoDB |
| SMS_ACCOUNT | InnoDB |
| SMS_CONFIG | InnoDB |
| SMS_MESSAGE | InnoDB |
| SMS_TASK | InnoDB |
| SMS_TRANSACTION | InnoDB |
| SSQ_ANSWER | InnoDB |
| SSQ_QUESTION | InnoDB |
| SSQ_SITETYPE_QUESTIONS | InnoDB |
| SSQ_USER_ANSWER | InnoDB |
| SST_EVENTS | InnoDB |
| SST_JOB_RUN | InnoDB |
| SST_PREFERENCES | InnoDB |
| SST_PRESENCES | InnoDB |
| SST_REPORTS | InnoDB |
| SST_RESOURCES | InnoDB |
| SST_SERVERSTATS | MyISAM |
| SST_SITEACTIVITY | InnoDB |
| SST_SITEVISITS | InnoDB |
| SST_USERSTATS | MyISAM |
| TAGGABLE_LINK | InnoDB |
| URL_RANDOMISED_MAPPINGS_T | InnoDB |
| VALIDATIONACCOUNT_ITEM | InnoDB |
| content_resource_lock | InnoDB |
| dw_assignment_status | MyISAM |
| dw_content_resource_lock | MyISAM |
| dw_guidance | MyISAM |
| dw_guidance_item | MyISAM |
| dw_guidance_item_file | MyISAM |
| dw_matrix | MyISAM |
| dw_matrix_cell | MyISAM |
| dw_metaobj_form_def | MyISAM |
| dw_pres_itemdef_mimetype | MyISAM |
| dw_presentation | MyISAM |
| dw_presentation_comment | MyISAM |
| dw_presentation_item | MyISAM |
| dw_presentation_item_def | MyISAM |
| dw_presentation_item_property | MyISAM |
| dw_presentation_layout | MyISAM |
| dw_presentation_log | MyISAM |
| dw_presentation_page | MyISAM |
| dw_presentation_page_item | MyISAM |
| dw_presentation_page_region | MyISAM |
| dw_presentation_template | MyISAM |
| dw_resource | MyISAM |
| dw_resource_collection | MyISAM |
| dw_review_items | MyISAM |
| dw_scaffolding | MyISAM |
| dw_scaffolding_cell | MyISAM |
| dw_scaffolding_cell_evaluators | MyISAM |
| dw_scaffolding_criteria | MyISAM |
| dw_scaffolding_levels | MyISAM |
| dw_session | MyISAM |
| dw_site_users | MyISAM |
| dw_sites | MyISAM |
| dw_template_file_ref | MyISAM |
| dw_users | MyISAM |
| dw_wizard | MyISAM |
| dw_wizard_category | MyISAM |
| dw_wizard_completed | MyISAM |
| dw_wizard_completed_category | MyISAM |
| dw_wizard_completed_page | MyISAM |
| dw_wizard_page | MyISAM |
| dw_wizard_page_attachments | MyISAM |
| dw_wizard_page_def | MyISAM |
| dw_wizard_page_def_add_forms | MyISAM |
| dw_wizard_page_forms | MyISAM |
| dw_wizard_page_sequence | MyISAM |
| dw_wizard_style | MyISAM |
| dw_wizard_support_item | MyISAM |
| dw_workflow_parent | MyISAM |
| hibernate_unique_key | InnoDB |
| lesson_builder_comments | InnoDB |
| lesson_builder_groups | InnoDB |
| lesson_builder_items | InnoDB |
| lesson_builder_log | InnoDB |
| lesson_builder_pages | InnoDB |
| lesson_builder_student_pages | InnoDB |
| lti_content | MyISAM |
| lti_mapping | MyISAM |
| lti_tools | MyISAM |
| melete_cc_license | InnoDB |
| melete_course_module | InnoDB |
| melete_license | InnoDB |
| melete_module | InnoDB |
| melete_module_shdates | InnoDB |
| melete_resource | InnoDB |
| melete_section | InnoDB |
| melete_section_resource | InnoDB |
| melete_site_preference | InnoDB |
| melete_user_preference | InnoDB |
| metaobj_form_def | InnoDB |
| osp_authz_simple | InnoDB |
| osp_completed_wiz_category | InnoDB |
| osp_completed_wizard | InnoDB |
| osp_completed_wizard_page | InnoDB |
| osp_guidance | InnoDB |
| osp_guidance_item | InnoDB |
| osp_guidance_item_file | InnoDB |
| osp_help_glossary | InnoDB |
| osp_help_glossary_desc | InnoDB |
| osp_list_config | InnoDB |
| osp_matrix | InnoDB |
| osp_matrix_cell | InnoDB |
| osp_matrix_label | InnoDB |
| osp_portal_category_pages | InnoDB |
| osp_portal_site_type | InnoDB |
| osp_portal_special_sites | InnoDB |
| osp_portal_tool_category | InnoDB |
| osp_portal_tool_functions | InnoDB |
| osp_portal_tool_type | InnoDB |
| osp_pres_itemdef_mimetype | InnoDB |
| osp_presentation | InnoDB |
| osp_presentation_comment | InnoDB |
| osp_presentation_item | InnoDB |
| osp_presentation_item_def | InnoDB |
| osp_presentation_item_property | InnoDB |
| osp_presentation_layout | InnoDB |
| osp_presentation_log | InnoDB |
| osp_presentation_page | InnoDB |
| osp_presentation_page_item | InnoDB |
| osp_presentation_page_region | InnoDB |
| osp_presentation_template | InnoDB |
| osp_review | InnoDB |
| osp_scaffolding | InnoDB |
| osp_scaffolding_attachments | InnoDB |
| osp_scaffolding_cell | InnoDB |
| osp_scaffolding_cell_form_defs | InnoDB |
| osp_scaffolding_criteria | InnoDB |
| osp_scaffolding_form_defs | InnoDB |
| osp_scaffolding_levels | InnoDB |
| osp_site_tool | InnoDB |
| osp_style | InnoDB |
| osp_template_file_ref | InnoDB |
| osp_wiz_page_attachment | InnoDB |
| osp_wiz_page_def_attachments | InnoDB |
| osp_wiz_page_form | InnoDB |
| osp_wizard | InnoDB |
| osp_wizard_category | InnoDB |
| osp_wizard_page | InnoDB |
| osp_wizard_page_def | InnoDB |
| osp_wizard_page_sequence | InnoDB |
| osp_workflow | InnoDB |
| osp_workflow_item | InnoDB |
| osp_workflow_parent | InnoDB |
| report_xsl_file | InnoDB |
| reports_def_xml | InnoDB |
| reports_param | InnoDB |
| reports_report | InnoDB |
| reports_result | InnoDB |
| rwikicurrentcontent | InnoDB |
| rwikihistory | InnoDB |
| rwikihistorycontent | InnoDB |
| rwikiobject | InnoDB |
| rwikipagemessage | InnoDB |
| rwikipagepresence | InnoDB |
| rwikipagetrigger | InnoDB |
| rwikipreference | InnoDB |
| rwikiproperties | InnoDB |
| scheduler_trigger_events | InnoDB |
| search_journal | InnoDB |
| search_node_status | InnoDB |
| search_segments | InnoDB |
| search_transaction | InnoDB |
| searchbuilderitem | InnoDB |
| searchwriterlock | InnoDB |
+--------------------------------+--------+
-- Warwick Bruce Chapman | +27 83 7797 094 | http://warwickchapman.com
On Mon, Aug 1, 2011 at 1:00 PM, Steve Swinsburg
<steve.swinsburg at gmail.com>wrote:
> Hi Wolfgang,
>
> Yes you should be using InnoDB. We successfully converted ours over about
> a year ago by dumping the database and reimporting ensuring the tables
> were being created as InnoDB.
>
> cheers,
> Steve
>
> On 01/08/2011, at 7:00 PM, Wolfgang Rohregger wrote:
>
> > Hi,
> >
> > I've had troubles running the Sakai 2.8.0 DB upgrade scripts for MySQL.
> After doing some research, there have been at least some indicators that
> parts, if not the whole, database enginge of MySQL might have been switched
> over to InnoDB.
> >
> > Is InnoDB now used for all Sakai tables and should I migrate the old
> MyISAM tables to InnoDB?
> >
> > Cheers,
> >
> > Wolfgang
> > _______________________________________________
> > production mailing list
> > production at collab.sakaiproject.org
> > http://collab.sakaiproject.org/mailman/listinfo/production
> >
> > TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20130507/cffe62f1/attachment-0001.html
More information about the production
mailing list