com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length [...]

Hibernate JIRA | Chris Wilson | 7 years ago
  1. 0

    The attached test case demonstrates the problem. Our situation is: We have four tables in this minimal test case: projects, sites, project_sites and site_cost. project_sites is linked to both projects and sites by a unique key spanning both, defined as a <properties> element in the Hibernate mapping. The <property> elements inside this <properties> name many-to-one associations, not stand-alone <property> elements. See below for justification. site_cost references this unique key using a project and a site as well, so it does not reference the primary key of project_sites, as that would duplicate data (site_id) and allow conflicts in our real-world code, where the site_id column is used in many other associations as well. Please bear with us on this database structure, as it's used for sharding and replicating tables between offline nodes based on site_ids. We generate our schema from the Hibernate mappings to allow database portability, using Hibernate's SchemaExport. The generation fails when adding the constraint to site_cost, because it incorrectly tries to use the database field names "site" and "project" (which don't exist, but are the names of the associations) in building the constraint, instead of "site_id" and "project_id" which do exist. The failing statement is: alter table site_cost add index FKE02EFDC52F2411E6 (project_id, site_id), add constraint FKE02EFDC52F2411E6 foreign key (project_id, site_id) references project_site (project, site) Which results in the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length [...] at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:383) at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:341) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:262) at org.aptivate.hibernate.test.ReferencePropertiesTest.testReferenceToProperties(ReferencePropertiesTest.java:63) [...] One way to work around this is to change the <properties> in ProjectSite.hbm.xml to reference individual properties instead of many-to-one relationships, and add those properties to the mapping and the POJO class. However, I don't wish to add a useless and dangerous API (to directly set to my POJOs. It should be possible to use the many-to-one mapping by extracting the column name(s) from it, and adding them to the unique index, but I guess Hibernate does not support that at present. The documentation is not clear on whether it should support it or not. Perhaps it's implied that a properties > property.name should name an actual property and not a many-to-one association, but it appears sparsely documented to me, and this would definitely be a useful feature for us. I'm sorry for not (yet) providing a patch myself, but I'm pretty new to Hibernate and not familiar with the code, and I currently can't build it due to the number of dependencies (and not being good with Maven).

    Hibernate JIRA | 7 years ago | Chris Wilson
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length [...]
  2. 0

    The attached test case demonstrates the problem. Our situation is: We have four tables in this minimal test case: projects, sites, project_sites and site_cost. project_sites is linked to both projects and sites by a unique key spanning both, defined as a <properties> element in the Hibernate mapping. The <property> elements inside this <properties> name many-to-one associations, not stand-alone <property> elements. See below for justification. site_cost references this unique key using a project and a site as well, so it does not reference the primary key of project_sites, as that would duplicate data (site_id) and allow conflicts in our real-world code, where the site_id column is used in many other associations as well. Please bear with us on this database structure, as it's used for sharding and replicating tables between offline nodes based on site_ids. We generate our schema from the Hibernate mappings to allow database portability, using Hibernate's SchemaExport. The generation fails when adding the constraint to site_cost, because it incorrectly tries to use the database field names "site" and "project" (which don't exist, but are the names of the associations) in building the constraint, instead of "site_id" and "project_id" which do exist. The failing statement is: alter table site_cost add index FKE02EFDC52F2411E6 (project_id, site_id), add constraint FKE02EFDC52F2411E6 foreign key (project_id, site_id) references project_site (project, site) Which results in the error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length [...] at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:383) at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:341) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:262) at org.aptivate.hibernate.test.ReferencePropertiesTest.testReferenceToProperties(ReferencePropertiesTest.java:63) [...] One way to work around this is to change the <properties> in ProjectSite.hbm.xml to reference individual properties instead of many-to-one relationships, and add those properties to the mapping and the POJO class. However, I don't wish to add a useless and dangerous API (to directly set to my POJOs. It should be possible to use the many-to-one mapping by extracting the column name(s) from it, and adding them to the unique index, but I guess Hibernate does not support that at present. The documentation is not clear on whether it should support it or not. Perhaps it's implied that a properties > property.name should name an actual property and not a many-to-one association, but it appears sparsely documented to me, and this would definitely be a useful feature for us. I'm sorry for not (yet) providing a patch myself, but I'm pretty new to Hibernate and not familiar with the code, and I currently can't build it due to the number of dependencies (and not being good with Maven).

    Hibernate JIRA | 7 years ago | Chris Wilson
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length [...]
  3. 0

    [HHH-4543] Hibernate does not allow a many-to-one to property-ref a composite unique key (properties element) that is based on many-to-one associations - Hibernate JIRA

    atlassian.net | 1 year ago
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'project' used in key specification without a key length
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    [java] Exception in thread "main" java.lang.RuntimeException: db-hibernate for PortalDb failed [java] at org.jasig.portal.shell.PortalShellBuildHelperImpl.hibernateCreate(PortalShellBuildHelperImpl.java:137) [java] at org.jasig.portal.shell.PortalShellBuildHelper$hibernateCreate.call(Unknown Source) [java] at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:45) [java] at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:108) [java] at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:128) [java] at upshell_114218966.run(upshell_114218966.groovy:20) [java] at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:257) [java] at groovy.lang.GroovyShell.run(GroovyShell.java:220) [java] at org.jasig.portal.shell.PortalShell.main(PortalShell.java:86) [java] Caused by: java.lang.RuntimeException: Failed to execute: create table UP_JGROUPS_PING (MEMBER_ADDRESS_DATA longtext not null, MEMBER_ADDRESS_CLASS varchar(100) not null, CLUSTER_NAME varchar(100) not null, MEMBER_ADDRESS longtext, PHYSICAL_ADDRESS_CLASS varchar(100), PHYSICAL_ADDRESS_DATA longtext, PHYSICAL_ADDRESS longtext, primary key (MEMBER_ADDRESS_DATA, MEMBER_ADDRESS_CLASS, CLUSTER_NAME)) [java] at org.jasig.portal.tools.dbloader.DataSourceSchemaExport.perform(DataSourceSchemaExport.java:133) [java] at org.jasig.portal.tools.dbloader.DataSourceSchemaExport.create(DataSourceSchemaExport.java:102) [java] at org.jasig.portal.shell.PortalShellBuildHelperImpl.hibernateCreate(PortalShellBuildHelperImpl.java:132) [java] ... 8 more [java] Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table UP_JGROUPS_PING (MEMBER_ADDRESS_DATA longtext not null, MEMBER_ADDRESS_CLASS varchar(100) not null, CLUSTER_NAME varchar(100) not null, MEMBER_ADDRESS longtext, PHYSICAL_ADDRESS_CLASS varchar(100), PHYSICAL_ADDRESS_DATA longtext, PHYSICAL_ADDRESS longtext, primary key (MEMBER_ADDRESS_DATA, MEMBER_ADDRESS_CLASS, CLUSTER_NAME))]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'MEMBER_ADDRESS_DATA' used in key specification without a key length [java] at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:94) [java] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) [java] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) [java] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407) [java] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428) [java] at org.jasig.portal.tools.dbloader.DataSourceSchemaExport.perform(DataSourceSchemaExport.java:127) [java] ... 10 more [java] Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'MEMBER_ADDRESS_DATA' used in key specification without a key length [java] at sun.reflect.GeneratedConstructorAccessor143.newInstance(Unknown Source) [java] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [java] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) [java] at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) [java] at com.mysql.jdbc.Util.getInstance(Util.java:386) [java] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) [java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) [java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) [java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617) [java] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) [java] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819) [java] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768) [java] at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894) [java] at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732) [java] at sun.reflect.GeneratedMethodAccessor68.invoke(Unknown Source) [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [java] at java.lang.reflect.Method.invoke(Method.java:606) [java] at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:235) [java] at com.sun.proxy.$Proxy130.execute(Unknown Source) [java] at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421) [java] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) [java] ... 12 more

    Apereo Issues | 3 years ago | Drew Wills
    java.lang.RuntimeException: db-hibernate for PortalDb failed
  6. 0

    [groovy-user] GroovyShell and exiting on exceptions

    Google Groups | 5 years ago | Eric Dalquist
    org.hibernate.JDBCException: Error during DDL export

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

      BLOB/TEXT column 'project' used in key specification without a key length [...]

      at com.mysql.jdbc.StatementImpl.executeUpdate()
    2. MySQL jdbc
      StatementImpl.executeUpdate
      1. com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1566)
      1 frame
    3. Hibernate
      SchemaExport.execute
      1. org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:383)
      2. org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:341)
      3. org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:262)
      3 frames
    4. org.aptivate.hibernate
      ReferencePropertiesTest.testReferenceToProperties
      1. org.aptivate.hibernate.test.ReferencePropertiesTest.testReferenceToProperties(ReferencePropertiesTest.java:63)
      1 frame