Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: DB Solution, hibernateManaged=true, create tables

  1. #1

    Default DB Solution, hibernateManaged=true, create tables

    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
    Last edited by dhartford; 09-07-2007 at 10:42 AM.

  2. #2

    Default

    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!
    ======
    Last edited by dhartford; 09-27-2007 at 10:58 AM.

  3. #3

    Default

    jira: http://jira.pentaho.org/browse/BISERVER-373

    Edit:
    Closed, Won't Fix, good explanation.
    Last edited by dhartford; 09-27-2007 at 02:54 PM.

  4. #4
    Join Date
    Jan 2006
    Posts
    313

    Default similiar problem with mysql5 & UTF8

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.