Skip to content

Again: MySQL error: Specified key was too long; max key length is 767 bytes #597

Closed

Description

@Ramblurr

#455 is back in 3.6.0 (or earlier?) affecting mysql <= 4.6..

mysql <= 4.6 has a max key length of 767 bytes (whereas >= 4.7 has a limit of 3072 bytes)

Caused by: org.polyjdbc.core.exception.SchemaManagerException: [DDL_ERROR] Failed to run DDL:
CREATE INDEX jv_commit_property_property_name_property_value_idx ON jv_commit_property(property_name,property_value(200))
    at org.polyjdbc.core.schema.SchemaManagerImpl.ddl(SchemaManagerImpl.java:91)
    at org.polyjdbc.core.schema.SchemaManagerImpl.create(SchemaManagerImpl.java:52)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

The property value index has a length of 200 here. Should it be 191 instead?

    RelationBuilder relationBuilder = schema.addRelation(tableName.nameWithSchema());
        relationBuilder
            .primaryKey(tableName.localName() + "_pk").using(COMMIT_PROPERTY_COMMIT_FK, COMMIT_PROPERTY_NAME).and()
            .withAttribute().string(COMMIT_PROPERTY_NAME).withMaxLength(190).and() /// <-------
            .withAttribute().string(COMMIT_PROPERTY_VALUE).withMaxLength(600).and(); /// <------
        foreignKey(tableName, COMMIT_PROPERTY_COMMIT_FK, getCommitTableNameWithSchema(), COMMIT_PK, relationBuilder);
        relationBuilder.build();

        columnsIndex(tableName, schema, COMMIT_PROPERTY_COMMIT_FK);

        // Add index prefix length for MySql
        if (dialect instanceof MysqlDialect) {
            columnsIndex(tableName, schema, new IndexedCols(
                    new String[]{COMMIT_PROPERTY_NAME, COMMIT_PROPERTY_VALUE},
                    new int[]{0, 200})); // <--------------
        }
        else {
            columnsIndex(tableName, schema, COMMIT_PROPERTY_NAME, COMMIT_PROPERTY_VALUE);
        }
  1. The col COMMIT_PROPERTY_NAME has max length of 190, and a prefix length of 0 for the index
  2. The col COMMIT_PROPERTY_VALUE has a max length of 600, and a prefix length of 200 for the index.

Why is the prefix length 0 for PROPERTY_NAME? This effectively makes the index useless? Or does 0 mean it indexes the entire length.

But for COMMIT_PROPERTY_VALUE the maximum prefix length is 767 / 4 = 191 in mysql 4.6 and 3072 / 4 = 768 in mysql 4.7

Activity

  1. bartoszwalacik commented on Nov 10, 2017

    @bartoszwalacik
    Member

    effective DDL is:

    CREATE TABLE `jv_commit_property` (
      `property_name` varchar(190) NOT NULL,
      `property_value` varchar(600) DEFAULT NULL,
      `commit_fk` bigint(20) NOT NULL,
      PRIMARY KEY (`commit_fk`,`property_name`),
      KEY `jv_commit_property_commit_fk_idx` (`commit_fk`),
      KEY `jv_commit_property_property_name_property_value_idx` (`property_name`,`property_value`(200)),
      CONSTRAINT `jv_commit_property_commit_fk` FOREIGN KEY (`commit_fk`) REFERENCES `jv_commit` (`commit_pk`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    

    So 0 means no prefix for this col.

    The bug is not back but have never been fixed.
    Fix done in #455 was the move in the right direction but we forgot about indexes.

    I will change JaVers code to generate the following DDL:

    ...
    `property_name` varchar(191) NOT NULL,
    ...
      KEY `jv_commit_property_property_name_property_value_idx` (`property_name`,`property_value`(191))
    ...
    
  2. added a commit that references this issue on Nov 10, 2017
  3. bartoszwalacik commented on Nov 10, 2017

    @bartoszwalacik
    Member
  4. added a commit that references this issue on Nov 12, 2017
  5. bartoszwalacik commented on Nov 17, 2017

    @bartoszwalacik
    Member

    fixed in 3.6.3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions