Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 352882

Summary:	[Hibernate] resource.hbm.xml: the "blob" column name for the "cdo_lob" table causes MySQL error
Product:	[Modeling] EMF	Reporter:	Slawomir Duszynski <Slawomir.Duszynski>
Component:	cdo.core	Assignee:	Martin Taal <mtaal>
Status:	CLOSED FIXED	QA Contact:	Eike Stepper <stepper>
Severity:	normal
Priority:	P3	CC:	ales_d, mtaal
Version:	4.1
Target Milestone:	---
Hardware:	PC
OS:	Windows XP
Whiteboard:

Description Slawomir Duszynski

2011-07-22 11:18:22 EDT

Build Identifier: 20110615-0604

(created after Eclipse forum reply from Martin Taal, see http://www.eclipse.org/forums/index.php/t/222593/)

Hi,
I am using Eclipse 3.7, CDO 4.0 together with Hibernate, Teneo and MySQL 5.5.14 to persist some EMF objects to a MySQL database. When starting the CDO Server on an empty database, the default CDO tables and columns (as specified by org.eclipse.emf.cdo.server.hibernate in resource.hbm.xml) are created by Hibernate. In that process, I get the following error:

1860 [app thread - org.eclipse.emf.cdo.server.app.0] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - Unsuccessful: create table cdo_lob (id varchar(255) not null, clob longtext, blob longblob, size integer, primary key (id)) ENGINE=InnoDB
1860 [app thread - org.eclipse.emf.cdo.server.app.0] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob longblob,
size integer,
primary key (id)
) ENGINE=InnoD' at line 4

"blob" is a reserved keyword in MySQL and therefore it is not possible to create a DB column with that name. The table column name is specified by org.eclipse.emf.cdo.server.hibernate in resource.hbm.xml:


<class name="org.eclipse.emf.cdo.server.internal.hibernate.HibernateStoreLob" table="cdo_lob" lazy="false">
<id name="id"/>
<property name="clob"/>
<property name="blob"/>
<property name="size"/>
</class>

It seems that adding <column name="something"/> to the "blob" property would solve the problem for MySQL. Thanks!

Reproducible: Always

Steps to Reproduce:
1.create an empty MySQL 5.5 database
2.configure CDO 4.0 server and Hibernate to use that database
3.start the server

Comment 1 Martin Taal

2011-07-22 11:33:22 EDT

Fix committed to svn, but need to check how to backport to 4.1.

Comment 2 Ales Dolecek

2012-01-23 16:22:35 EST

Hello,

  run into same problem with Oracle. I got:

INFO  o.h.tool.hbm2ddl.DatabaseMetadata - table not found: cdo_lob
DEBUG o.h.i.f.DefaultIdentifierGeneratorFactory - Setting dialect [org.hibernate.dialect.Oracle10gDialect]
DEBUG o.h.i.f.DefaultIdentifierGeneratorFactory - Setting dialect [org.hibernate.dialect.Oracle10gDialect]
DEBUG o.h.i.f.DefaultIdentifierGeneratorFactory - Setting dialect [org.hibernate.dialect.Oracle10gDialect]
DEBUG o.h.tool.hbm2ddl.SchemaUpdate - create table "ANNOTATION_CONTENTS" ("ANNOTATION_E_ID" number(19,0) not null, "CONTENTS_TYPE" varchar2(255 char), "CONTENTS_ID" number(19,0), "ANNOTATION_CONTENTS_IDX" number(10,0) not null, primary key ("ANNOTATION_E_ID", "ANNOTATION_CONTENTS_IDX"))
DEBUG o.h.tool.hbm2ddl.SchemaUpdate - create table "ANNOTATION_REFERENCES" ("ANNOTATION_E_ID" number(19,0) not null, "REFERENCES_TYPE" varchar2(255 char), "REFERENCES_ID" number(19,0), "ANNOTATION_REFERENCES_IDX" number(10,0) not null, primary key ("ANNOTATION_E_ID", "ANNOTATION_REFERENCES_IDX"))
DEBUG o.h.tool.hbm2ddl.SchemaUpdate - create table "MODELELEMENT_details" ("ANNOTATION_DETAILS_E_ID" number(19,0), elt varchar2(255 char), "ANNOTATION_DETAILS_IDX" number(10,0) not null, primary key ("ANNOTATION_DETAILS_E_ID", "ANNOTATION_DETAILS_IDX"))
DEBUG o.h.tool.hbm2ddl.SchemaUpdate - create table "MODELELEMENT" (idcol number(19,0) not null, DTYPE varchar2(255 char) not null, resource_id varchar2(255 char), container_id varchar2(255 char), version number(10,0), "SOURCE" varchar2(255 char), "ANNOTATION_MODELELEMENT_E_ID" number(19,0), "MODELELEMENT_ANNOTATIONS_IDX" number(10,0), primary key (idcol))
DEBUG o.h.tool.hbm2ddl.SchemaUpdate - create table cdo_lob (id varchar2(255 char) not null, clob clob, blob blob, size number(10,0), primary key (id))
ERROR o.h.tool.hbm2ddl.SchemaUpdate - Unsuccessful: create table cdo_lob (id varchar2(255 char) not null, clob clob, blob blob, size number(10,0), primary key (id))
ERROR o.h.tool.hbm2ddl.SchemaUpdate - ORA-00904: : invalid identifier

Not sure if your fix solve the problem in generic way or only MySQL.

Ales

Comment 3 Martin Taal

2012-01-26 14:35:48 EST

Pushed a solution to the git repository, will be in the next build.

Comment 4 Ales Dolecek

2012-01-30 06:35:26 EST

Hello Martin,

  just FYI: in Oracle the problem is caused by the "size" column, not the "blob" column...

Ales

Comment 5 Martin Taal

2012-01-30 09:07:13 EST

Thanks for letting me know, I have changed the name of the size column, this has been pushed to the git repository for the 4.1 build.

Comment 6 Eike Stepper

2012-09-21 07:16:54 EDT

Closing.

Comment 7 Eike Stepper

2013-05-20 03:17:16 EDT

Mass update. Don't worry if this bug is already closed!