Bug #5738 Error During installation of OpenCMS 6.0 : Illegal mix of collations
Submitted: 24 Sep 2004 17:57 Modified: 8 Nov 2004 14:44
Reporter: Max JEAN-NOEL Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:Opencms 6.0 OS:Windows (Windows 2000 Professional)
Assigned to: Assigned Account CPU Architecture:Any

[24 Sep 2004 17:57] Max JEAN-NOEL
Description:
Hi,

I try to install OpenCMS 6.0 with MySQL 5.0.1 Alpha + Tomcat 5.0.28 + JDK 1.4 on Windows 2000 Profesionnal "French language".

There is a critical error when I was installing the workplace as you can see below :

149:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:153)
148:		at org.opencms.setup.CmsSetupWorkplaceImportThread.kill(CmsSetupWorkplaceImportThread.java:107)
147:		at org.opencms.main.CmsShell.exit(CmsShell.java:560)
146:	java.lang.NullPointerException
145:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:153)
144:		at org.opencms.setup.CmsSetupWorkplaceImportThread.kill(CmsSetupWorkplaceImportThread.java:107)
143:		at org.opencms.main.CmsShell.exit(CmsShell.java:554)
142:	java.lang.NullPointerException
141:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:148)
140:		at org.opencms.main.CmsShell.start(CmsShell.java:491)
139:		at org.opencms.main.CmsShell.executeCommands(CmsShell.java:666)
138:		at org.opencms.main.CmsShell.printPrompt(CmsShell.java:719)
137:	java.lang.NullPointerException
136:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
135:		at org.opencms.main.CmsShell.<init>(CmsShell.java:448)
134:	java.lang.NullPointerException
133:	
132:	>>>	at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
131:	>>>	at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
130:	>>>	at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
129:	>>>	at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
128:	>>>	at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:414)
127:	>>>	at org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4865)
126:	>>>	at org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:362)
125:	>>>	at org.opencms.db.generic.CmsUserDriver.createGroup(CmsUserDriver.java:140)
124:	>>>	at org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781)
123:	>>>	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
122:	>>>	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
121:	>>>	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
120:	>>>	at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
119:	>>>	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
118:	>>>	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
117:	>>>	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
116:	>>>java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
115:	>>>Root cause:
114:	>>>-----------
113:	>>	at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
112:	>>	at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
111:	>>	at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
110:	>>	at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
109:	>>	at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:414)
108:	>>	at org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4865)
107:	>>	at org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:362)
106:	>>	at org.opencms.db.generic.CmsUserDriver.createGroup(CmsUserDriver.java:140)
105:	>>	at org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:791)
104:	>>	at org.opencms.db.generic.CmsSqlManager.getCmsException(CmsSqlManager.java:381)
103:	>>Root cause was: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
102:	>>org.opencms.main.CmsException: [org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" [Code 4 - Sql exception]
101:	>>Root cause:
100:	>>-----------
99:	>	at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
98:	>	at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
97:	>	at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
96:	>	at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
95:	>	at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:424)
94:	>Root cause was: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
93:	>Root cause was: org.opencms.main.CmsException: [org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" [Code 4 - Sql exception]
92:	>org.opencms.main.CmsException: Critical error while loading driver manager [Code 33 - DriverManager init error]
91:	>Root cause:
90:	>-----------
89:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
88:		at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
87:		at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
86:		at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:949)
85:	Root cause was: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
84:	Root cause was: org.opencms.main.CmsException: [org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" [Code 4 - Sql exception]
83:	Root cause was: org.opencms.main.CmsException: Critical error while loading driver manager [Code 33 - DriverManager init error]
82:	org.opencms.main.CmsException: Database init failed [Code 33 - DriverManager init error]
81:	Critical error during OpenCms initialization
80:	
79:	>>	at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
78:	>>	at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
77:	>>	at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
76:	>>	at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
75:	>>	at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:414)
74:	>>	at org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4865)
73:	>>	at org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:362)
72:	>>	at org.opencms.db.generic.CmsUserDriver.createGroup(CmsUserDriver.java:140)
71:	>>	at org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781)
70:	>>	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
69:	>>	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
68:	>>	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
67:	>>	at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
66:	>>	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
65:	>>	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
64:	>>	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
63:	>>java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
62:	>>Root cause:
61:	>>-----------
60:	>	at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
59:	>	at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
58:	>	at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
57:	>	at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
56:	>	at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:414)
55:	>	at org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4865)
54:	>	at org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:362)
53:	>	at org.opencms.db.generic.CmsUserDriver.createGroup(CmsUserDriver.java:140)
52:	>	at org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:791)
51:	>	at org.opencms.db.generic.CmsSqlManager.getCmsException(CmsSqlManager.java:381)
50:	>Root cause was: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
49:	>org.opencms.main.CmsException: [org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" [Code 4 - Sql exception]
48:	>Root cause:
47:	>-----------
46:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
45:		at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
44:		at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
43:		at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
42:		at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:424)
41:	Root cause was: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
40:	Root cause was: org.opencms.main.CmsException: [org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" [Code 4 - Sql exception]
39:	org.opencms.main.CmsException: Critical error while loading driver manager [Code 33 - DriverManager init error]
38:	Critical init error/3
37:	
36:	>	at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
35:	>	at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
34:	>	at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
33:	>	at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
32:	>	at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:414)
31:	>	at org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4865)
30:	>	at org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:362)
29:	>	at org.opencms.db.generic.CmsUserDriver.createGroup(CmsUserDriver.java:140)
28:	>	at org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781)
27:	>	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
26:	>	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
25:	>	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
24:	>	at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
23:	>	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
22:	>	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
21:	>	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
20:	>java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
19:	>Root cause:
18:	>-----------
17:		at org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:142)
16:		at org.opencms.main.CmsShell.<init>(CmsShell.java:445)
15:		at org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1330)
14:		at org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:943)
13:		at org.opencms.db.CmsDriverManager.newInstance(CmsDriverManager.java:414)
12:		at org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4865)
11:		at org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:362)
10:		at org.opencms.db.generic.CmsUserDriver.createGroup(CmsUserDriver.java:140)
9:		at org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:791)
8:		at org.opencms.db.generic.CmsSqlManager.getCmsException(CmsSqlManager.java:381)
7:	Root cause was: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
6:	org.opencms.main.CmsException: [org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='" [Code 4 - Sql exception]
5:	Critical error while loading driver manager
4:	[org.opencms.db.mysql.CmsUserDriver] where: org.opencms.db.generic.CmsUserDriver.readGroup(CmsUserDriver.java:781), why: java.sql.SQLException: General error,  message from server: "Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='"
3:	
2:	OpenCms property file: C:\jakarta-tomcat-5\webapps\opencms\WEB-INF\config\opencms.properties
1:	OpenCms WEB-INF path:  C:\jakarta-tomcat-5\webapps\opencms\WEB-INF\

Is Someone can help me to solve this error. I tride to initilize all MySQL Variables to
"utf8" without success.

How to repeat:
Installation of OpenCMS 6.0 with MySQL 5.0.1 Alpha + Tomcat 5.0.28 + JDK 1.4
[8 Oct 2004 14:44] Hartmut Holzgraefe
can you please add the SQL statements causing the error messages
and the create statements for the tables used in the offending statements?
[16 Oct 2004 15:21] Mike Pagel
Hi,

I also got the same error message. Is there a way to log the exact commands that are sent to an SQL server?

As a first idea, these are the sql scritps that are used by OpenCMS 6 at this point (some contain still macros to be replaced during execution).

create_db.sql
=========

#
# replacer = "${database}"
############################

CREATE USER ${user} 
	IDENTIFIED BY ${password}
	DEFAULT TABLESPACE ${defaultTablespace}
	TEMPORARY TABLESPACE ${temporaryTablespace}
	QUOTA UNLIMITED ON ${defaultTablespace}
	QUOTA UNLIMITED ON ${temporaryTablespace};

GRANT CONNECT, RESOURCE TO ${user};

--------------------------------------------------------------------------------------------

create_tables.sql
===========

CREATE TABLE CMS_USERS (
	USER_ID VARCHAR2(36) NOT NULL,
	USER_NAME VARCHAR2(64) NOT NULL,
	USER_PASSWORD VARCHAR2(32) NOT NULL,
	USER_DESCRIPTION VARCHAR2(255) NOT NULL,
	USER_FIRSTNAME VARCHAR2(50) NOT NULL,
	USER_LASTNAME VARCHAR2(50) NOT NULL,
	USER_EMAIL VARCHAR2(100) NOT NULL,
	USER_LASTLOGIN DATE NOT NULL,
	USER_FLAGS INT NOT NULL,
	USER_INFO LONG RAW,
	USER_ADDRESS VARCHAR2(100) NOT NULL,
	USER_TYPE INT NOT NULL,
	PRIMARY KEY(USER_ID),
	UNIQUE(USER_NAME)
);

CREATE TABLE CMS_PROJECTS (
	PROJECT_ID INT NOT NULL,
	PROJECT_NAME VARCHAR2(64) NOT NULL,
	PROJECT_DESCRIPTION VARCHAR2(255) NOT NULL,
	PROJECT_FLAGS INT NOT NULL,
	PROJECT_TYPE INT NOT NULL,
	USER_ID VARCHAR2(36) NOT NULL,
	GROUP_ID VARCHAR2(36) NOT NULL,
	MANAGERGROUP_ID VARCHAR2(36) NOT NULL,
	TASK_ID INT NOT NULL,
	DATE_CREATED DATE NOT NULL,
	PRIMARY KEY (PROJECT_ID),
	UNIQUE(PROJECT_NAME,DATE_CREATED)
);

CREATE TABLE CMS_BACKUP_PROJECTS (
	PROJECT_ID INT NOT NULL,
	PROJECT_NAME VARCHAR2(64) NOT NULL,
	PROJECT_DESCRIPTION VARCHAR2(255) NOT NULL,
	PROJECT_TYPE INT NOT NULL,
	USER_ID VARCHAR2(36) NOT NULL,
	GROUP_ID VARCHAR2(36) NOT NULL,
	MANAGERGROUP_ID VARCHAR2(36) NOT NULL,
	TASK_ID INT NOT NULL,
	DATE_CREATED DATE NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	PROJECT_PUBLISHDATE DATE,
	PROJECT_PUBLISHED_BY VARCHAR2(36) NOT NULL,
	PROJECT_PUBLISHED_BY_NAME VARCHAR2(167),
	USER_NAME VARCHAR2(167),
	GROUP_NAME VARCHAR2(64),
	MANAGERGROUP_NAME VARCHAR2(64),
	PRIMARY KEY(VERSION_ID)
);

CREATE TABLE CMS_PROJECTRESOURCES (
	PROJECT_ID INT NOT NULL,
	RESOURCE_PATH VARCHAR2(1024) NOT NULL,
	PRIMARY KEY(PROJECT_ID, RESOURCE_PATH)
);

CREATE TABLE CMS_BACKUP_PROJECTRESOURCES (
	VERSION_ID INT NOT NULL,
	PROJECT_ID INT NOT NULL,
	RESOURCE_PATH VARCHAR2(1024) NOT NULL,
	PRIMARY KEY (VERSION_ID, PROJECT_ID, RESOURCE_PATH)
);

CREATE TABLE CMS_OFFLINE_PROPERTYDEF (
	PROPERTYDEF_ID VARCHAR2(36) NOT NULL,
	PROPERTYDEF_NAME VARCHAR2(64) NOT NULL,
	PROPERTYDEF_MAPPING_TYPE INT NOT NULL,
	PRIMARY KEY(PROPERTYDEF_ID),
	UNIQUE(PROPERTYDEF_NAME, PROPERTYDEF_MAPPING_TYPE)
);

CREATE TABLE CMS_ONLINE_PROPERTYDEF (
	PROPERTYDEF_ID VARCHAR2(36) NOT NULL,
	PROPERTYDEF_NAME VARCHAR2(64) NOT NULL,
	PROPERTYDEF_MAPPING_TYPE INT NOT NULL,
	PRIMARY KEY(PROPERTYDEF_ID),
	UNIQUE(PROPERTYDEF_NAME, PROPERTYDEF_MAPPING_TYPE)
);

CREATE TABLE CMS_BACKUP_PROPERTYDEF (
	PROPERTYDEF_ID VARCHAR2(36) NOT NULL,
	PROPERTYDEF_NAME VARCHAR2(64) NOT NULL,
	PROPERTYDEF_MAPPING_TYPE INT NOT NULL,
	PRIMARY KEY(PROPERTYDEF_ID),
	UNIQUE(PROPERTYDEF_NAME, PROPERTYDEF_MAPPING_TYPE)
);

CREATE TABLE CMS_OFFLINE_PROPERTIES (
	PROPERTY_ID VARCHAR2(36) NOT NULL,
	PROPERTYDEF_ID VARCHAR2(36) NOT NULL,
	PROPERTY_MAPPING_ID VARCHAR2(36) NOT NULL,
	PROPERTY_MAPPING_TYPE INT NOT NULL,	
	PROPERTY_VALUE VARCHAR2(255) NOT NULL,
	PRIMARY KEY(PROPERTY_ID)
);

CREATE TABLE CMS_ONLINE_PROPERTIES (
	PROPERTY_ID VARCHAR2(36) NOT NULL,
	PROPERTYDEF_ID VARCHAR2(36) NOT NULL,
	PROPERTY_MAPPING_ID VARCHAR2(36) NOT NULL,
	PROPERTY_MAPPING_TYPE INT NOT NULL,	
	PROPERTY_VALUE VARCHAR2(255) NOT NULL,
	PRIMARY KEY(PROPERTY_ID)
);

CREATE TABLE CMS_BACKUP_PROPERTIES (
	PROPERTY_ID VARCHAR2(36) NOT NULL,
	PROPERTYDEF_ID VARCHAR2(36) NOT NULL,
	PROPERTY_MAPPING_ID VARCHAR2(36) NOT NULL,
	PROPERTY_MAPPING_TYPE INT NOT NULL,		
	PROPERTY_VALUE VARCHAR2(255) NOT NULL,
	VERSION_ID INT,
	PRIMARY KEY(PROPERTY_ID)
);

CREATE TABLE CMS_OFFLINE_RESOURCES (
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_TYPE INT NOT NULL,
	RESOURCE_FLAGS INT NOT NULL,
	RESOURCE_STATE INT NOT NULL,
	RESOURCE_SIZE INT NOT NULL,
	SIBLING_COUNT INT NOT NULL,	
	DATE_CREATED DATE NOT NULL,
	DATE_LASTMODIFIED DATE NOT NULL,
	USER_CREATED VARCHAR2(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR2(36) NOT NULL,
	PROJECT_LASTMODIFIED INT NOT NULL,
	PRIMARY KEY(RESOURCE_ID)
);

CREATE TABLE CMS_ONLINE_RESOURCES (
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_TYPE INT NOT NULL,
	RESOURCE_FLAGS INT NOT NULL,
	RESOURCE_STATE INT NOT NULL,
	RESOURCE_SIZE INT NOT NULL,
	SIBLING_COUNT INT NOT NULL,	
	DATE_CREATED DATE NOT NULL,
	DATE_LASTMODIFIED DATE NOT NULL,
	USER_CREATED VARCHAR2(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR2(36) NOT NULL,
	PROJECT_LASTMODIFIED INT NOT NULL,
	PRIMARY KEY(RESOURCE_ID)
);

CREATE TABLE CMS_BACKUP_RESOURCES (
	BACKUP_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_TYPE INT NOT NULL,
	RESOURCE_FLAGS INT NOT NULL,
	RESOURCE_STATE INT NOT NULL,
	RESOURCE_SIZE INT NOT NULL,
	SIBLING_COUNT INT NOT NULL,	
	DATE_CREATED DATE NOT NULL,
	DATE_LASTMODIFIED DATE NOT NULL,
	USER_CREATED VARCHAR2(36) NOT NULL,
	USER_LASTMODIFIED VARCHAR2(36) NOT NULL,
	PROJECT_LASTMODIFIED INT NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	VERSION_ID INT NOT NULL,
	USER_CREATED_NAME VARCHAR2(64) NOT NULL,
	USER_LASTMODIFIED_NAME VARCHAR2(64) NOT NULL,
	PRIMARY KEY(BACKUP_ID),
	UNIQUE(VERSION_ID,RESOURCE_ID)
);

CREATE TABLE CMS_OFFLINE_CONTENTS (
	CONTENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	FILE_CONTENT LONG RAW NOT NULL,
	PRIMARY KEY (CONTENT_ID)
);

CREATE TABLE CMS_ONLINE_CONTENTS (
	CONTENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	FILE_CONTENT LONG RAW NOT NULL,
	PRIMARY KEY (CONTENT_ID)
);

CREATE TABLE CMS_BACKUP_CONTENTS (
	BACKUP_ID VARCHAR2(36) NOT NULL,
	CONTENT_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	FILE_CONTENT LONG RAW NOT NULL,
	VERSION_ID INT,
	PRIMARY KEY (BACKUP_ID)
);

CREATE TABLE CMS_GROUPS (
	GROUP_ID VARCHAR2(36) NOT NULL,
	PARENT_GROUP_ID VARCHAR2(36) NOT NULL,
	GROUP_NAME VARCHAR2(64) NOT NULL,
	GROUP_DESCRIPTION VARCHAR2(255) NOT NULL,
	GROUP_FLAGS INT NOT NULL,
	PRIMARY KEY(GROUP_ID),
	UNIQUE(GROUP_NAME)
);

CREATE TABLE CMS_SYSTEMID (
	TABLE_KEY VARCHAR2(255) NOT NULL,
	ID INT NOT NULL,
	PRIMARY KEY (TABLE_KEY)
);

CREATE TABLE CMS_GROUPUSERS (
	GROUP_ID VARCHAR2(36) NOT NULL,
	USER_ID VARCHAR2(36) NOT NULL,
	GROUPUSER_FLAGS INT NOT NULL
);

CREATE TABLE CMS_Task (
	AUTOFINISH INT,
	ENDTIME DATE,
	ESCALATIONTYPEREF INT,
	ID INT NOT NULL,
	INITIATORUSERREF VARCHAR2(36),
	MILESTONEREF INT,
	NAME VARCHAR(254),
	ORIGINALUSERREF VARCHAR2(36),
	AGENTUSERREF VARCHAR2(36),
	PARENT INT,
	PERCENTAGE VARCHAR(50),
	PERMISSION VARCHAR(50),
	PRIORITYREF INT DEFAULT '2',
	ROLEREF VARCHAR2(36),
	ROOT INT,
	STARTTIME DATE,
	STATE INT,
	TASKTYPE INT,
	TIMEOUT DATE,
	WAKEUPTIME DATE,
	HTMLLINK VARCHAR(254),
	ESTIMATETIME INT DEFAULT '86400',
	PRIMARY KEY(ID)
);

CREATE TABLE CMS_TaskType (
	AUTOFINISH INT,
	ESCALATIONTYPEREF INT,
	HTMLLINK VARCHAR(254),
	ID INT NOT NULL,
	NAME VARCHAR(50),
	PERMISSION VARCHAR(50),
	PRIORITYREF INT,
	ROLEREF VARCHAR2(36),
	PRIMARY KEY(ID)
);

CREATE TABLE CMS_TaskLog (
	COMENT LONG,
	EXTERNALUSERNAME VARCHAR(254),
	ID INT NOT NULL,
	STARTTIME DATE,
	TASKREF INT,
	USERREF INT,
	TYPE INT DEFAULT '0',
	PRIMARY KEY(ID)
);

CREATE TABLE CMS_TaskPar (
	ID INT NOT NULL,
	PARNAME VARCHAR(50),
	PARVALUE VARCHAR(50),
	REF INT,
	PRIMARY KEY(ID)
);

CREATE TABLE CMS_ONLINE_ACCESSCONTROL (
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	PRINCIPAL_ID VARCHAR2(36) NOT NULL,
	ACCESS_ALLOWED INT,
	ACCESS_DENIED INT,
	ACCESS_FLAGS INT,
	PRIMARY KEY(RESOURCE_ID, PRINCIPAL_ID)
);

CREATE TABLE CMS_OFFLINE_ACCESSCONTROL (
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	PRINCIPAL_ID VARCHAR2(36) NOT NULL,
	ACCESS_ALLOWED INT,
	ACCESS_DENIED INT,
	ACCESS_FLAGS INT,
	PRIMARY KEY(RESOURCE_ID, PRINCIPAL_ID)
);
   
CREATE TABLE CMS_OFFLINE_STRUCTURE (
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024) NOT NULL,
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED DATE NOT NULL,
	DATE_EXPIRED DATE NOT NULL,
	PRIMARY KEY(STRUCTURE_ID)
);

CREATE TABLE CMS_ONLINE_STRUCTURE (
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024) NOT NULL,
	STRUCTURE_STATE INT NOT NULL,	
	DATE_RELEASED DATE NOT NULL,
	DATE_EXPIRED DATE NOT NULL,
	PRIMARY KEY(STRUCTURE_ID)
);

CREATE TABLE CMS_BACKUP_STRUCTURE (
	BACKUP_ID VARCHAR2(36) NOT NULL,
	VERSION_ID INT NOT NULL,
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024) NOT NULL,
	STRUCTURE_STATE INT NOT NULL,
	DATE_RELEASED DATE NOT NULL,
	DATE_EXPIRED DATE NOT NULL,
	PRIMARY KEY(BACKUP_ID)
);   
 
CREATE TABLE CMS_PUBLISH_HISTORY (
	HISTORY_ID VARCHAR(36) NOT NULL,
	PUBLISH_TAG INT NOT NULL,
	STRUCTURE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_ID VARCHAR2(36) NOT NULL,
	RESOURCE_PATH VARCHAR2(1024) NOT NULL,
	RESOURCE_STATE INT NOT NULL,
	RESOURCE_TYPE INT NOT NULL,
	SIBLING_COUNT INT NOT NULL,
	CONSTRAINT PK_PUBLISH_HISTORY PRIMARY KEY(HISTORY_ID,PUBLISH_TAG,STRUCTURE_ID,RESOURCE_ID) USING INDEX TABLESPACE ${indexTablespace}
);

CREATE TABLE CMS_STATICEXPORT_LINKS (
	LINK_ID VARCHAR(36) NOT NULL,
	LINK_RFS_PATH VARCHAR(1024) NOT NULL,
	LINK_TYPE INT NOT NULL,
	LINK_PARAMETER TEXT,
	LINK_TIMESTAMP BIGINT,
	PRIMARY KEY(LINK_ID)	
);

CREATE INDEX IDX_PUBLISH_HISTORY_01 
	ON CMS_PUBLISH_HISTORY(HISTORY_ID);

CREATE INDEX IDX_GROUPS_01 
	ON CMS_GROUPS(PARENT_GROUP_ID);

CREATE INDEX IDX_GROUPUSERS_01 
	ON CMS_GROUPUSERS(GROUP_ID);
	
CREATE INDEX IDX_GROUPUSERS_02 
	ON CMS_GROUPUSERS(USER_ID);

CREATE INDEX IDX_PROJECTS_01 
	ON CMS_PROJECTS(GROUP_ID);
	
CREATE INDEX IDX_PROJECTS_02 
	ON CMS_PROJECTS(MANAGERGROUP_ID);
	
CREATE INDEX IDX_PROJECTS_03 
	ON CMS_PROJECTS(USER_ID);
	
CREATE INDEX IDX_PROJECTS_04 
	ON CMS_PROJECTS(PROJECT_NAME);
	
CREATE INDEX IDX_PROJECTS_05 
	ON CMS_PROJECTS(TASK_ID);
	
CREATE INDEX IDX_PROJECTS_06 
	ON CMS_PROJECTS (PROJECT_FLAGS);
	
CREATE INDEX IDX_OFFLINE_RESOURCES_01 
	ON CMS_OFFLINE_RESOURCES (RESOURCE_TYPE);
  
CREATE INDEX IDX_ONLINE_RESOURCES_01 
	ON CMS_ONLINE_RESOURCES (RESOURCE_TYPE);

CREATE INDEX IDX_OFFLINE_STRUCTURE_01 
	ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID,RESOURCE_PATH);
	
CREATE INDEX IDX_OFFLINE_STRUCTURE_03 
	ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID,RESOURCE_ID);
	
CREATE INDEX IDX_OFFLINE_STRUCTURE_04 
	ON CMS_OFFLINE_STRUCTURE (STRUCTURE_STATE);
	
CREATE INDEX IDX_OFFLINE_STRUCTURE_05 
	ON CMS_OFFLINE_STRUCTURE (RESOURCE_ID);

CREATE INDEX IDX_ONLINE_STRUCTURE_01 
	ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID,RESOURCE_PATH);
	
CREATE INDEX IDX_ONLINE_STRUCTURE_03 
	ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID,RESOURCE_ID);
	
CREATE INDEX IDX_ONLINE_STRUCTURE_04 
	ON CMS_ONLINE_STRUCTURE (STRUCTURE_STATE);
	
CREATE INDEX IDX_ONLINE_STRUCTURE_05 
	ON CMS_ONLINE_STRUCTURE (RESOURCE_ID);  
	
CREATE INDEX IDX_OFFLINE_PROPERTIES_01 
	ON CMS_OFFLINE_PROPERTIES (PROPERTYDEF_ID,PROPERTY_MAPPING_ID,PROPERTY_MAPPING_TYPE);
	
CREATE INDEX IDX_OFFLINE_PROPERTIES_02 
	ON CMS_OFFLINE_PROPERTIES (PROPERTYDEF_ID);

CREATE INDEX IDX_ONLINE_PROPERTIES_01 
	ON CMS_ONLINE_PROPERTIES (PROPERTYDEF_ID,PROPERTY_MAPPING_ID,PROPERTY_MAPPING_TYPE);
	
CREATE INDEX IDX_ONLINE_PROPERTIES_02 
	ON CMS_ONLINE_PROPERTIES (PROPERTYDEF_ID);

CREATE INDEX IDX_OFFLINE_PROPERTYDEF_01 
	ON CMS_OFFLINE_PROPERTYDEF (PROPERTYDEF_MAPPING_TYPE);
	
CREATE INDEX IDX_ONLINE_PROPERTYDEF_01 
	ON CMS_ONLINE_PROPERTYDEF (PROPERTYDEF_MAPPING_TYPE);

CREATE INDEX IDX_SYSTEMID_01 
	ON CMS_SYSTEMID(TABLE_KEY,ID);

CREATE INDEX IDX_TASK_01 
	ON CMS_TASK(PARENT);
	
CREATE INDEX IDX_TASK_02 
	ON CMS_TASK(TASKTYPEREF);

CREATE INDEX IDX_TASKLOG_01
	ON CMS_TASKLOG(TASKREF);
	
CREATE INDEX IDX_TASKLOG_02 
	ON CMS_TASKLOG(USERREF);

CREATE INDEX IDX_TASKPAR_01 
	ON CMS_TASKPAR(REF);
	
CREATE INDEX IDX_PROJECTRESOURCES_01 
	ON CMS_PROJECTRESOURCES(RESOURCE_PATH); 

---------------------------------------------------------------------------------------------

Please let me know if there is a more direct way to get the command log other than reverse engineering OpenCMS...

Thanks,
Mike
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".