[Deploying Sakai] MySQL: MyISAM or InnoDB?
Warwick Chapman
warwickchapman at gmail.com
Tue May 7 06:15:26 PDT 2013
Steve
It broke:
2013-05-07 14:55:43,389 WARN http-bio-8080-exec-18
org.sakaiproject.portal.util.ErrorReporter - Bug Report bug-id:
92984805-e6c2-4f69-898e-0bc3c3e8b83f user: null usage-session: null time:
07-May-2013 14:55:43 user comment: null stack trace
org.sakaiproject.portal.api.PortalHandlerException:
java.lang.RuntimeException: SqlService.dbWrite failure
at
org.sakaiproject.portal.charon.handlers.ReLoginHandler.doPost(ReLoginHandler.java:50)
caused by: java.lang.RuntimeException: SqlService.dbWrite failure
at
org.sakaiproject.db.impl.BasicSqlService.dbWrite(BasicSqlService.java:1143)
caused by: java.sql.SQLException: Binary logging not possible. Message:
Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode
'STATEMENT'
I turned binary logging off (which I had enabled in preparation to setup
replication) and it is no longer throwing the error.
Any thoughts?
-- Warwick Bruce Chapman | +27 83 7797 094 | http://warwickchapman.com
On Tue, May 7, 2013 at 1:55 PM, Steve Swinsburg
<steve.swinsburg at gmail.com>wrote:
> Hi Warwick,
>
> You can indeed. Though backup, and arrange a downtime to be on the safe
> side. Those particular tables shouldn't have too much content so probably
> won't take long
>
> ALTER TABLE tablename ENGINE=INNODB;
>
> cheers,
> Steve
>
> On 07/05/2013, at 7:22 PM, Warwick Chapman <warwickchapman at gmail.com>
> wrote:
>
> 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/96e0660c/attachment-0001.html
More information about the production
mailing list