PDA

View Full Version : DB Solution, hibernateManaged=true, create tables



dhartford
09-07-2007, 10:36 AM
The databases schemas are not fully functional for create/update.

After I create the individual tables, the schemas seem to work fine, but I still have to add PRO_FILES table, CONTENTITEM table, CONTENTLOCATION, etc to the database defined in the hibernateManaged datasource. After the tables are created, it updates the fields fine.

My hibernateManaged datasource is MySQL 4. I'm going step-by-step on each SQL error to try to get this up and running. Hopefully simply something I'm doing wrong. ;-)

I've only changed hibernateManaged=true in web.xml, configured the /system/hibernate/hibernate-jboss-managed.xml to be MySQL, and then setup the datasource on the Jboss side with an *-ds.xml file.

hibernate-jboss-managed.xml
================
<property name="connection.datasource">java:PentahoHibernateDS</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<!-- property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property -->
<property name="transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>
<property name="transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
<property name="show_sql">false</property>
<property name="session_factory_name">hibernate/HibernateFactory</property>

<!-- property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property -->
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.cache.use_query_cache">false</property>

<property name="hibernate.hbm2ddl.auto">update</property>

<property name="hibernate.autocommit">false</property>
================


pentahohibernate-ds.xml
============
<local-tx-datasource>
<jndi-name>PentahoHibernateDS</jndi-name>
<connection-url>jdbc:mysql://<server>/<table>?autoCommit=false</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>user</user-name>
<password>password</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
</local-tx-datasource>
==========


edit: jboss 4.2.1.GA, j2ee-deployments-RC2.820, jboss-ear-mysql-noportal.


Seperate - minor issue with fullPath field in PRO_FILES for mysql:
10:40:49,480 ERROR [SchemaUpdate] Unsuccessful: alter table PRO_FILES add column fullPath text unique
10:40:49,480 ERROR [SchemaUpdate] BLOB/TEXT column 'fullPath' used in key specification without a key length


-D

dhartford
09-27-2007, 10:38 AM
Build 838, problem still persists, Mysql 4 database.

edit: tested postgres 8.1, this db seems to be fine for auto-schema-creation (other than ' You cannot commit with autocommit set!' error again, which I have taken extreme measures to avoid)

error log
===========
10:34:07,540 INFO [DatabaseMetadata] table not found: CONTENTITEM
10:34:07,571 INFO [DatabaseMetadata] table not found: CONTENTLOCATION
10:34:07,571 INFO [DatabaseMetadata] table not found: PRO_FILES
10:34:07,571 INFO [DatabaseMetadata] table not found: PRO_SUBCONTENT
10:34:07,571 INFO [STDOUT] create table CONTENTITEM (CONTITEMID varchar(100) not null, REVISION integer not null, NAME
varchar(200) not null, parent_id varchar(100), PATH text not null, TITLE varchar(200) not null, MIMETYPE varchar(100) no
t null, URL varchar(254), LATESTVERNUM integer not null, EXTENSION varchar(10) not null, WRITEMODE integer not null, pri
mary key (CONTITEMID), unique (PATH))
10:34:07,571 ERROR [PentahoSchemaUpdate] Unsuccessful:create table CONTENTITEM (CONTITEMID varchar(100) not null, REVISI
ON integer not null, NAME varchar(200) not null, parent_id varchar(100), PATH text not null, TITLE varchar(200) not null
, MIMETYPE varchar(100) not null, URL varchar(254), LATESTVERNUM integer not null, EXTENSION varchar(10) not null, WRITE
MODE integer not null, primary key (CONTITEMID), unique (PATH))
10:34:07,587 ERROR [PentahoSchemaUpdate] BLOB/TEXT column 'PATH' used in key specification without a key length
10:34:07,587 INFO [STDOUT] create table CONTENTLOCATION (CONTENTID varchar(100) not null, REVISION integer not null, NA
ME varchar(200) not null, SOLNID varchar(100) not null, DESCRIPTION varchar(200) not null, DIRPATH text not null, primar
y key (CONTENTID), unique (DIRPATH))
10:34:07,587 ERROR [PentahoSchemaUpdate] Unsuccessful:create table CONTENTLOCATION (CONTENTID varchar(100) not null, REV
ISION integer not null, NAME varchar(200) not null, SOLNID varchar(100) not null, DESCRIPTION varchar(200) not null, DIR
PATH text not null, primary key (CONTENTID), unique (DIRPATH))
10:34:07,618 ERROR [PentahoSchemaUpdate] BLOB/TEXT column 'DIRPATH' used in key specification without a key length
10:34:07,618 INFO [STDOUT] create table PRO_FILES (FILE_ID varchar(100) not null, revision integer not null, parent var
char(100), fileName varchar(255) not null, fullPath text not null unique, data longblob, directory bit not null, lastMod
ified bigint not null, CHILD_ID varchar(100), primary key (FILE_ID))
10:34:07,618 ERROR [PentahoSchemaUpdate] Unsuccessful:create table PRO_FILES (FILE_ID varchar(100) not null, revision in
teger not null, parent varchar(100), fileName varchar(255) not null, fullPath text not null unique, data longblob, direc
tory bit not null, lastModified bigint not null, CHILD_ID varchar(100), primary key (FILE_ID))
10:34:07,618 ERROR [PentahoSchemaUpdate] BLOB/TEXT column 'fullPath' used in key specification without a key length
10:34:07,634 INFO [STDOUT] create table PRO_SUBCONTENT (SUBCONTID varchar(100) not null, REVISION integer not null, SUB
CONTTYPE varchar(255) not null, SUBCONTACTREF text not null, primary key (SUBCONTID), unique (SUBCONTACTREF))
10:34:07,634 ERROR [PentahoSchemaUpdate] Unsuccessful:create table PRO_SUBCONTENT (SUBCONTID varchar(100) not null, REVI
SION integer not null, SUBCONTTYPE varchar(255) not null, SUBCONTACTREF text not null, primary key (SUBCONTID), unique (
SUBCONTACTREF))
10:34:07,634 ERROR [PentahoSchemaUpdate] BLOB/TEXT column 'SUBCONTACTREF' used in key specification without a key length

10:34:07,634 INFO [STDOUT] alter table CONTENTITEM add index FK692B5EEC875C1BF0 (parent_id), add constraint FK692B5EEC8
75C1BF0 foreign key (parent_id) references CONTENTLOCATION (CONTENTID)
10:34:07,649 ERROR [PentahoSchemaUpdate] Unsuccessful:alter table CONTENTITEM add index FK692B5EEC875C1BF0 (parent_id),
add constraint FK692B5EEC875C1BF0 foreign key (parent_id) references CONTENTLOCATION (CONTENTID)
10:34:07,649 ERROR [PentahoSchemaUpdate] Table 'PENTAHO16_TEST.CONTENTITEM' doesn't exist
10:34:07,665 INFO [STDOUT] alter table PRO_FILES add index FK94A87E2559B105EA (CHILD_ID), add constraint FK94A87E2559B1
05EA foreign key (CHILD_ID) references PRO_FILES (FILE_ID)
10:34:07,665 ERROR [PentahoSchemaUpdate] Unsuccessful:alter table PRO_FILES add index FK94A87E2559B105EA (CHILD_ID), add
constraint FK94A87E2559B105EA foreign key (CHILD_ID) references PRO_FILES (FILE_ID)
10:34:07,665 ERROR [PentahoSchemaUpdate] Table 'PENTAHO16_TEST.PRO_FILES' doesn't exist
10:34:07,665 INFO [STDOUT] alter table PRO_FILES add index FK94A87E25BB71F776 (parent), add constraint FK94A87E25BB71F7
76 foreign key (parent) references PRO_FILES (FILE_ID)
10:34:07,680 ERROR [PentahoSchemaUpdate] Unsuccessful:alter table PRO_FILES add index FK94A87E25BB71F776 (parent), add c
onstraint FK94A87E25BB71F776 foreign key (parent) references PRO_FILES (FILE_ID)
10:34:07,680 ERROR [PentahoSchemaUpdate] Table 'PENTAHO16_TEST.PRO_FILES' doesn't exist
10:34:07,680 INFO [PentahoSchemaUpdate] schema update complete
10:34:07,680 ERROR [PentahoSchemaUpdate] PentahoSchemaUpdate.ERROR_0003 - Error closing connection
java.sql.SQLException: You cannot commit with autocommit set!
======

dhartford
09-27-2007, 10:59 AM
jira: http://jira.pentaho.org/browse/BISERVER-373

Edit:
Closed, Won't Fix, good explanation.

begunrom
10-08-2007, 03:33 AM
Hi,

There is a similiar problem with Mysql5 if your default char set = UTF8

Creation of the CONTENTITEM table fails because of the length of the index


2007-10-07 21:38:41,109 INFO [STDOUT] create table CONTENTITEM (CONTITEMID varchar(100) not null, REVISION integer not null, NAME varchar(200) not null, parent_id varchar(100), PATH varchar(767) not null, TITLE varchar(200) not null, MIMETYPE varchar(100) not null, URL varchar(254), LATESTVERNUM integer not null, EXTENSION varchar(10) not null, WRITEMODE integer not null, primary key (CONTITEMID), unique (PATH)) ENGINE=InnoDB
2007-10-07 21:38:41,109 ERROR [org.pentaho.repository.PentahoSchemaUpdate] Unsuccessful:create table CONTENTITEM (CONTITEMID varchar(100) not null, REVISION integer not null, NAME varchar(200) not null, parent_id varchar(100), PATH varchar(767) not null, TITLE varchar(200) not null, MIMETYPE varchar(100) not null, URL varchar(254), LATESTVERNUM integer not null, EXTENSION varchar(10) not null, WRITEMODE integer not null, primary key (CONTITEMID), unique (PATH)) ENGINE=InnoDB
2007-10-07 21:38:41,109 ERROR [org.pentaho.repository.PentahoSchemaUpdate] Specified key was too long; max key length is 765 bytes