MySQL Bugs: #75802: mysql cmdline behave different then jdbc app with regards to InnoDB max key

Bug #75802	mysql cmdline behave different then jdbc app with regards to InnoDB max key
Submitted:	5 Feb 2015 17:33	Modified:	13 Feb 2015 8:19
Reporter:	Achim Reckeweg	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I am using mysql cmdline client to import a Schema file for database creation.
Everything is fine. The database is created.

For development I do have an ant file that also imports the schema file utilizing the mysql-connector-5.1.34.jar.

This one fails with:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

How to repeat:
Create schema file xyz.mysql:
----
CREATE DATABASE IF NOT EXISTS xyz CHARACTER SET utf8;

    create table xyz.account_group (
        id varchar(128) not null,
        native_identity varchar(322),
        primary key (id)
    ) ENGINE=InnoDB;

      create index actgroup_native_ci on xyz.account_group (native_identity);
----

create ant build.xml
-----
<?xml version="1.0" encoding="UTF-8"?>
<project name="xyz" default="createdb">

  <macrodef name="xyz.sql">
        <attribute name="sql.file"/>
        <sequential>
            <!--<echo>Executing @{sql.file}</echo>-->
            <sql
                    driver="com.mysql.jdbc.Driver"
                    url="jdbc:mysql://localhost?useServerPrepStmts=true&amp;tinyInt1isBit=true&amp;useUnicode=true&amp;characterEncoding=utf8"
                    userid="root"
                    password="xxxx"
                    src="@{sql.file}"
                    print="yes"
                    keepformat="true"
                    escapeprocessing="false"
                    expandproperties="false"
                    >
                <classpath>
                    <pathelement path="${classpath}"/>
                    <fileset dir="/<Path to ant lib with mysql-connector.jar added>">
                        <include name="**/*.jar"/>
                    </fileset>
                </classpath>
            </sql>
        </sequential>
    </macrodef>

    <target name="createdb">
        <xyz.sql sql.file="xyz.mysql"/>
    </target>

</project>

-----

import file using mysql
mysql -u xyz -p < xyz.mysql

drop the database again:
mysqladmin -u xyz -p drop xyz

try to create the db using the ant script:
ant -f build.xml

See the error coming up

Hi Achim,

Thanks for the report.
This is a duplicate of Bug#6604 and neither a c/J nor server bug, but a well-known InnoDB limitation. Please refer http://dev.mysql.com/doc/refman/5.7/en/create-index.html:

"Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes."