OpenKM-5.1.7 and Oracle 10g

[jlargent]

I am trying to set up OpenKM using Oracle 10g as the backend.
When I start openkm and it creites to create the tables in oracle I'm getting the following error:

Code: Select all

2011-10-04 10:25:16,325 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table JBPM_DELEGATION (ID
_ number(19,0) not null, CLASSNAME_ long, CONFIGURATION_ long, CONFIGTYPE_ varchar2(255 char), PROCESSDEFINITION_ numb
er(19,0), primary key (ID_))
2011-10-04 10:25:16,325 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] ORA-01754: a table may contain only one column
 of type LONG

This is just the first one, I must be missing a setting somewhere in the configs that hibernate needs to create the correct schema for oracle.
I've taken a look at the wiki oracle page, I was better off before. That page is a waste of time and resources.

[jllort]

I'll not response about the "That page is a waste of time and resources." but understand is not good presentation for you, you could suggest some more positive comment like "in documentation I think is missing etc..." we considering all user inquires and obviously we're not perfect but we recognise our errors and try to solve it.

Try with our latest build at integration.openkm.com ( simply replace the war file ) that's solved some problems with 5.1.7, it'll be the newer 5.1.8 ( but still not yet released ). It's stable version don't worry for it ( only we've solved some bugs we've found in 5.1.7 )

We'll let's take a fast checklist:
1- Put your OpenKM.cfg
2- Which jar driver are you using
3- Are these tables created on database ( might be 8 tables )

Code: Select all

TABLE DEFAULT_BUNDLE
TABLE DEFAULT_REFS
TABLE DEFAULT_BINVAL
TABLE DEFAULT_NAMES
TABLE VERSION_BUNDLE
TABLE VERSION_REFS
TABLE VERSION_BINVAL
TABLE VERSION_NAMES

Provide me a list os created tables.

First I would like to indentifying the problem, if not I'll send to you the sql creation script.

[jlargent]

I uninstalled oracle XE and reinstalled just to make sure I had a clean db.
Used the OpenKM.war from 5.1.8, this is the list of tables created on startup.

Code: Select all

SQL> select table_name from all_tables where owner = 'OPENKM' order by table_name;

TABLE_NAME
------------------------------
DEFAULT_BINVAL
DEFAULT_BUNDLE
DEFAULT_NAMES
DEFAULT_REFS
JBPM_ACTION
JBPM_BYTEARRAY
JBPM_BYTEBLOCK
JBPM_COMMENT
JBPM_DECISIONCONDITIONS
JBPM_EVENT
JBPM_EXCEPTIONHANDLER

TABLE_NAME
------------------------------
JBPM_JOB
JBPM_MODULEDEFINITION
JBPM_MODULEINSTANCE
JBPM_NODE
JBPM_POOLEDACTOR
JBPM_PROCESSDEFINITION
JBPM_PROCESSINSTANCE
JBPM_RUNTIMEACTION
JBPM_SWIMLANE
JBPM_SWIMLANEINSTANCE
JBPM_TASK

TABLE_NAME
------------------------------
JBPM_TASKACTORPOOL
JBPM_TASKCONTROLLER
JBPM_TASKINSTANCE
JBPM_TOKEN
JBPM_TOKENVARIABLEMAP
JBPM_TRANSITION
JBPM_VARIABLEACCESS
JBPM_VARIABLEINSTANCE
OKM_ACTIVITY
OKM_BOOKMARK
OKM_CONFIG

TABLE_NAME
------------------------------
OKM_CONTACT
OKM_CRON_TAB
OKM_DASHBOARD
OKM_DB_METADATA_SEQUENCE
OKM_DB_METADATA_TYPE
OKM_DB_METADATA_VALUE
OKM_EXTENSION
OKM_FORUM
OKM_FORUM_POST
OKM_FORUM_TOPIC
OKM_LANGUAGE

TABLE_NAME
------------------------------
OKM_LOCK_TOKEN
OKM_MAIL_ACCOUNT
OKM_MAIL_FILTER
OKM_MAIL_FILTER_RULE
OKM_MESSAGE_RECEIVED
OKM_MESSAGE_SENT
OKM_MIME_TYPE
OKM_MIME_TYPE_EXTENSION
OKM_NODE_CONTACT
OKM_PROFILE
OKM_PROFILE_MSC_EXTENSION

TABLE_NAME
------------------------------
OKM_PROFILE_MSC_REPORT
OKM_PROFILE_WZRD_PROP_GRP
OKM_PROFILE_WZRD_WORKFLOW
OKM_PROP_QUERY_RECEIVED
OKM_PROP_QUERY_SENT
OKM_PROP_SUB_RECEIVED
OKM_PROP_SUB_SENT
OKM_QUERY_PARAMS
OKM_QUERY_PARAMS_CATEGORY
OKM_QUERY_PARAMS_KEYWORD
OKM_QUERY_PARAMS_PROPERTY

TABLE_NAME
------------------------------
OKM_QUERY_PARAMS_SHARED
OKM_REPORT
OKM_ROLE
OKM_STAMP_IMAGE
OKM_STAMP_IMAGE_USER
OKM_STAMP_TEXT
OKM_STAMP_TEXT_USER
OKM_STAPLE
OKM_STAPLE_GROUP
OKM_TRANSLATION
OKM_TWITTER_ACCOUNT

TABLE_NAME
------------------------------
OKM_USER
OKM_USER_CONFIG
OKM_USER_DOCUMENT
OKM_USER_DOCUMENT_KEYWORD
OKM_USER_ITEMS
OKM_USER_ROLE
VERSION_BINVAL
VERSION_BUNDLE
VERSION_NAMES
VERSION_REFS

The first error in the output is still the the ORA-01754 error

Code: Select all

2011-10-05 09:57:30,271 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table JBPM_DELEGATION (ID
_ number(19,0) not null, CLASSNAME_ long, CONFIGURATION_ long, CONFIGTYPE_ varchar2(255 char), PROCESSDEFINITION_ numb
er(19,0), primary key (ID_))
2011-10-05 09:57:30,271 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] ORA-01754: a table may contain only one column of type LONG

[jlargent]

I think I found my error in the config.
OpenKM.cfg I still had
hibernate.hbm2ddl=create

changed to
hibernate.hbm2ddl=none

And OpenKM started.

[jllort]

If all goes fine don't make anything else, otherside I suggest to be sure that was the problem, drop all database and restarting with database creation from empty DBMS environment ( In order starting from empty environement must delete repository folder and then, start jboss with parameter set to create and secondly and the others with parameter to none. )

Hibernate makes some kind of magic ( creating and updating, tables definitions etc... ), but not all. And sometimes how much powerfull is the software solution ( Oracle database ) more dificult is making automatic operations ( hand made usually not fails ).

We'll be waiting your feedback if it was the origin of your problem.

[jlargent]

It looks like that was not the problem.
We started poking around in the oracle DB and couldn't find item we expected to be there so after looking around it looks like I had a bastardized oracle/hsqldb
running. Some things were being put into the oracle db some in the hsqldb.
So I copied my configs, deleted the entire jboss tree, restored from original zip added the new version of the war file, and copied my configs back into place.
Deleted the Oracle openkm schema then tried to restart with hibernate.hbm2ddl=create and ran into the same issues as before.

Code: Select all

2011-10-13 14:05:32,030 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table JBPM_DELEGATION (
ID_ number(19,0) not null, CLASSNAME_ long, CONFIGURATION_ long, CONFIGTYPE_ varchar2(255 char), PROCESSDEFINITION_ 
number(19,0), primary key (ID_))
2011-10-13 14:05:32,030 ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] ORA-01754: a table may contain only one column of type LONG

This is just first instance of this error, it is repeated multiply times as it tries to create other tables.

list of tables that are created.

Code: Select all

DEFAULT_BINVAL
DEFAULT_BUNDLE
DEFAULT_NAMES
DEFAULT_REFS
JBPM_ACTION
JBPM_BYTEARRAY
JBPM_BYTEBLOCK
JBPM_COMMENT
JBPM_DECISIONCONDITIONS
JBPM_EVENT
JBPM_EXCEPTIONHANDLER
JBPM_JOB
JBPM_MODULEDEFINITION
JBPM_MODULEINSTANCE
JBPM_NODE
JBPM_POOLEDACTOR
JBPM_PROCESSDEFINITION
JBPM_PROCESSINSTANCE
JBPM_RUNTIMEACTION
JBPM_SWIMLANE
JBPM_SWIMLANEINSTANCE
JBPM_TASK
JBPM_TASKACTORPOOL
JBPM_TASKCONTROLLER
JBPM_TASKINSTANCE
JBPM_TOKEN
JBPM_TOKENVARIABLEMAP
JBPM_TRANSITION
JBPM_VARIABLEACCESS
JBPM_VARIABLEINSTANCE
OKM_ACTIVITY
OKM_BOOKMARK
OKM_CONFIG
OKM_CONTACT
OKM_CRON_TAB
OKM_DASHBOARD
OKM_DB_METADATA_SEQUENCE
OKM_DB_METADATA_TYPE
OKM_DB_METADATA_VALUE
OKM_EXTENSION
OKM_FORUM
OKM_FORUM_POST
OKM_FORUM_TOPIC
OKM_LANGUAGE
OKM_LOCK_TOKEN
OKM_MAIL_ACCOUNT
OKM_MAIL_FILTER
OKM_MAIL_FILTER_RULE
OKM_MESSAGE_RECEIVED
OKM_MESSAGE_SENT
OKM_MIME_TYPE
OKM_MIME_TYPE_EXTENSION
OKM_NODE_CONTACT
OKM_PROFILE
OKM_PROFILE_MSC_EXTENSION
OKM_PROFILE_MSC_REPORT
OKM_PROFILE_WZRD_PROP_GRP
OKM_PROFILE_WZRD_WORKFLOW
OKM_PROP_QUERY_RECEIVED
OKM_PROP_QUERY_SENT
OKM_PROP_SUB_RECEIVED
OKM_PROP_SUB_SENT
OKM_QUERY_PARAMS
OKM_QUERY_PARAMS_CATEGORY
OKM_QUERY_PARAMS_KEYWORD
OKM_QUERY_PARAMS_PROPERTY
OKM_QUERY_PARAMS_SHARED
OKM_REPORT
OKM_ROLE
OKM_STAMP_IMAGE
OKM_STAMP_IMAGE_USER
OKM_STAMP_TEXT
OKM_STAMP_TEXT_USER
OKM_STAPLE
OKM_STAPLE_GROUP
OKM_TRANSLATION
OKM_TWITTER_ACCOUNT
OKM_USER
OKM_USER_CONFIG
OKM_USER_DOCUMENT
OKM_USER_DOCUMENT_KEYWORD
OKM_USER_ITEMS
OKM_USER_ROLE
VERSION_BINVAL
VERSION_BUNDLE
VERSION_NAMES
VERSION_REFS

[jllort]

I've forwarded to you the database schema and data for oracle. Tell me if it solves the problem.