Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-31945 Support MySQL utf8mb4 (emojis)
  3. SAK-31937

Message bundle table field lengths too long for mysql 5.7 utf8mb4 db

    XMLWordPrintable

    Details

      Description

      Running mysql 5.7 with a default database character set of utf8mb4, the max key length is 3072 bytes = 768 characters, because each character can take up to 4 bytes in utf8mb4 (previously with utf8 this was 1 character = up to 3 bytes).

      However, SAKAI_MESSAGE_BUNDLE is defined with 4 fields each of 255 varchar, with a composite index SMB_SEARCH on all of them:

      https://github.com/sakaiproject/sakai/blob/master/kernel/api/src/main/java/org/sakaiproject/messagebundle/api/MessageBundleProperty.hbm.xml

      So the index cannot be created on startup:

      2016-11-11 15:38:48,480  WARN localhost-startStop-1 org.sakaiproject.springframework.orm.hibernate.AddableSessionFactoryBean - Unsuccessful schema statement: create index SMB_SEARCH on SAKAI_MESSAGE_BUNDLE (BASENAME, MODULE_NAME, LOCALE, PROP_NAME)
      

      java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes

      I'm guessing these fields do not all have to be 255 characters long and could be shortened to keep the index within total allowed length.

      Also the length needs to be defined on the column element, not the property element:

        <property name="baseName" type="string" not-null="true" index="SMB_BASENAME_IDX">
                  <column name="BASENAME" length="150" index="SMB_SEARCH"/>
              </property>
              <property name="moduleName" type="string" not-null="true" index="SMB_MODULE_IDX">
                  <column name="MODULE_NAME" length="150" index="SMB_SEARCH"/>
              </property>
              <property name="locale" type="string" not-null="true" index="SMB_LOCALE_IDX">
                  <column name="LOCALE" length="150" index="SMB_SEARCH"/>
              </property>
              <property name="propertyName" type="string" not-null="true" index="SMB_PROPNAME_IDX">
                  <column name="PROP_NAME" length="150" index="SMB_SEARCH"/>
              </property>
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  smarquard Stephen Marquard
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Git Source Code