<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I got a bit further by creating my own pg.sql (postgresql
create_tables) file.<br>
All files including my pg.sql file can be found on
<a class="moz-txt-link-freetext" href="http://www.weeswel.com/logs/">http://www.weeswel.com/logs/</a><br>
<br>
At the moment I am stuck on the fact that the code depends on Mysql
fucking up an schema constraint by making something up for an not null
field<br>
Read on for the gory details.<br>
<br>
P.S. Can the devs shed some light on this and create a patch or is this
a self-service scenario.<br>
P.P.S. Can I trust this code to work properly on anything else then
Mysql. <br>
The fact that you can't even do a basic install of this 7.0RC1 on an
postgresql doesn't inspire confidence.This is next to the fact that the
code is dependant on Mysql quirks.<br>
<br>
<br>
<br>
In Pseudo language my steps to get something working, since the
original postgresql database template does not work<br>
<br>
1)Download the script mysql2pgsql.perl<br>
2)cp %opencms%/setup/database/mysql_41/create_tables.sql
/tmp/Mysql_create_tables.sql<br>
3)vi /tmp/Mysql_create_tables.sql <br>
Strip all indexes from this file<br>
Strip all Characterset utf 8 from this file<br>
Strip all TEXT BINARY from this file<br>
Strip all (255) from the PRIMARY KEY entries<br>
<br>
3)./mysql2pgsql.perl --nodrop /tmp/Mysql_create_tables pg.sql<br>
4)Deploy your opencms.war(7.0RC1) file wait until it is extracted<br>
5)cp pg.sql .%OPENCMS%/setup/database/postgresql/create_tables.sql<br>
<br>
6) Open the <a class="moz-txt-link-freetext" href="http://yoursite/opencms/setup">http://yoursite/opencms/setup</a> and follow the setup routine<br>
7) Choose to install all documentation to exercise the database table a
bit<br>
<br>
This import failed, I found the following problem in the
%opencms%/WEB-INF/logs/setup.log file<br>
Important part follows<br>
<i>17: Caused by: org.opencms.db.CmsDbSqlException: An SQL error
occurred when executing the following query: INSERT INTO
CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID)<br>
VALUES
('04a8d8f6-ff37-11db-964e-2df1a5244f64','04a8d8f7-ff37-11db-964e-2df1a5244f64','/',0,0,9223372036854775807,'00000000-0000-0000-0000-000000000000').<br>
<br>
23: Caused by: org.postgresql.util.PSQLException: Parameter index
out of range.<br>
</i><br>
<i><br>
</i>So we try to run this query from the pgsql commandline<i><br>
opencms150=# \d CMS_ONLINE_STRUCTURE;<br>
Table "public.cms_online_structure"<br>
Column | Type | Modifiers<br>
-------------------+----------+-----------<br>
structure_id | bytea | not null<br>
resource_id | bytea | not null<br>
parent_id | bytea | not null<br>
resource_path | text | not null<br>
structure_state | smallint | not null<br>
date_released | bigint | not null<br>
date_expired | bigint | not null<br>
structure_version | integer | not null<br>
Indexes:<br>
"cms_online_structure_pkey" PRIMARY KEY, btree (structure_id)<br>
Check constraints:<br>
"cms_online_structure_structure_state_check" CHECK (structure_state
>= 0)<br>
<br>
opencms150=# INSERT INTO CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID)<br>
opencms150-# VALUES
('04a8d8f6-ff37-11db-964e-2df1a5244f64','04a8d8f7-ff37-11db-964e-2df1a5244f64','/',0,0,9223372036854775807,'00000000-0000-0000-0000-000000000000')<br>
opencms150-# ;</i><br>
<i>ERROR: null value in column "structure_version" violates not-null
constraint<br>
</i><br>
So what happens is a violation of the database schema, column
structure_version is not filled in the statement so Postgresql properly
refuses to honor the request.<br>
<br>
<br>
Just to pain myself I installed the product pretending to be a database
server My-SQL.<br>
<i>mysql> describe CMS_ONLINE_STRUCTURE;<br>
+-------------------+----------------------+------+-----+---------+-------+<br>
| Field | Type | Null | Key | Default |
Extra |<br>
+-------------------+----------------------+------+-----+---------+-------+<br>
| STRUCTURE_ID | varchar(36) | NO | PRI |
| |<br>
| RESOURCE_ID | varchar(36) | NO | MUL |
| |<br>
| PARENT_ID | varchar(36) | NO | MUL |
| |<br>
| RESOURCE_PATH | text | NO | MUL |
| |<br>
| STRUCTURE_STATE | smallint(5) unsigned | NO | MUL |
| |<br>
| DATE_RELEASED | bigint(20) | NO | |
| |<br>
| DATE_EXPIRED | bigint(20) | NO | |
| |<br>
| STRUCTURE_VERSION | int(11) | NO | |
| |<br>
+-------------------+----------------------+------+-----+---------+-------+<br>
8 rows in set (0.01 sec)<br>
</i><br>
So the NOT NULL constraint is still with us, thats great that means it
is not a problem caused by my conversion, but what goes wrong.<br>
Lets try the query in Mysql<br>
<br>
<i>mysql> INSERT INTO<br>
-> CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID
) VALUES (<br>
->
'95c23c20-ff2c-11db-9bd2-b3972de27f02','95c23c21-ff2c-11db-9bd2-b3972de27f02','/',0,0,9223372036854775807,'000000
00-0000-00<br>
'> 00-0000-000000000000');<br>
Query OK, 1 row affected, 2 warnings (0.05 sec)<br>
<br>
</i><br>
<i>mysql> select * from CMS_ONLINE_STRUCTURE;<br>
+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+<br>
| STRUCTURE_ID |
RESOURCE_ID |
PARENT_ID | RESOURCE_PATH | STRUCTURE_STATE
| DATE_RELEASED | DATE_EXPIRED | STRUCTURE_VERSION |<br>
+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+<br>
| 95c23c20-ff2c-11db-9bd2-b3972de27f02 |
95c23c21-ff2c-11db-9bd2-b3972de27f02 | 00000000-0000-00<br>
00-0000-00000000000 | / | 0 | 0 |
9223372036854775807 | 0 |<br>
+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+<br>
1 row in set (0.00 sec)<br>
<br>
<br>
</i>I didn't mention any versions since My-Sql has a long reputation of
mangling data and Postgresl has a long reputation of honoring these
kinds of constraints.<br>
<i><br>
</i><br>
<br>
<br>
<br>
<br>
<br>
Marco schreef:
<blockquote cite="mid:46433B56.2050502@weeswel.com" type="cite">It
seems like it,
<br>
Caused by: java.sql.SQLException: Column not found message from server:
"Unknown column 'CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION' in 'field
list'"
<br>
<br>
Same as with me, the Postgresl dump doesn't contain the
STRUCTURE_VERSION field list for the CMS_ONLINE_STRUCTURE table. And
atleast one of the many Mysql dumps does contain a dump which
presumably is correct.
<br>
<br>
<br>
<br>
Stéphane Verger schreef:
<br>
<blockquote type="cite">Hi !
<br>
<br>
I had an unexpected error trying to install OpenCms 7 RC1 with the java
1.4 package.
<br>
It seems to have a problem with the sql schemas, is it the same problem
as yours, marco ?
<br>
<br>
Has anybody already had the same probleme, and got a solution ?
<br>
<br>
Ty,
<br>
<br>
R.
<br>
<br>
<br>
<br>
47: Shutdown completed, total uptime was 00:00:29.
<br>
46: Shutting down OpenCms, version 7 RC 1 (6.9.1) in web application
"opencms7beta"
<br>
45: 44: 43: at
org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:209)
<br>
42: at
org.opencms.setup.CmsSetupWorkplaceImportThread.kill(CmsSetupWorkplaceImportThread.java:118)
<br>
41: at org.opencms.main.CmsShell.exit(CmsShell.java:557)
<br>
40: java.lang.NullPointerException
<br>
39: at
org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:194)
<br>
38: at org.opencms.main.CmsShell.start(CmsShell.java:646)
<br>
37: at
org.opencms.main.CmsShell.executeCommands(CmsShell.java:818)
<br>
36: at
org.opencms.main.CmsShell.executeCommand(CmsShell.java:743)
<br>
35: ${user}@${project}>java.lang.NullPointerException
<br>
34: ${user}@${project}># Turn echo on
<br>
33: ... 9 more
<br>
32: at
org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.java:1653)
<br>
31: at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
<br>
30: at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
<br>
29: at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1634)
<br>
28: at com.mysql.jdbc.Connection.execSQL(Connection.java:2281)
<br>
27: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1279)
<br>
26: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1168)
<br>
25: at
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2001)
<br>
24: Caused by: java.sql.SQLException: Column not found message from
server: "Unknown column 'CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION' in
'field list'"
<br>
23: ... 5 more
<br>
22: at
org.opencms.db.CmsSecurityManager.init(CmsSecurityManager.java:2579)
<br>
21: at
org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4034)
<br>
20: at
org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:577)
<br>
19: at
org.opencms.db.generic.CmsVfsDriver.createResource(CmsVfsDriver.java:471)
<br>
18: at
org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.java:1666)
<br>
17: Caused by: org.opencms.db.CmsDbSqlException: An SQL error
occurred when executing the following query:
com.mysql.jdbc.PreparedStatement@12ce574: SELECT
CMS_ONLINE_STRUCTURE.STRUCTURE_ID,CMS_ONLINE_STRUCTURE.RESOURCE_ID,CMS_ONLINE_STRUCTURE.RESOURCE_PATH,CMS_ONLINE_STRUCTURE.STRUCTURE_STATE,CMS_ONLINE_STRUCTURE.DATE_RELEASED,CMS_ONLINE_STRUCTURE.DATE_EXPIRED,CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION,CMS_ONLINE_RESOURCES.RESOURCE_ID,CMS_ONLINE_RESOURCES.RESOURCE_TYPE,CMS_ONLINE_RESOURCES.RESOURCE_FLAGS,CMS_ONLINE_RESOURCES.RESOURCE_STATE,CMS_ONLINE_RESOURCES.DATE_CREATED,CMS_ONLINE_RESOURCES.DATE_LASTMODIFIED,CMS_ONLINE_RESOURCES.USER_CREATED,CMS_ONLINE_RESOURCES.USER_LASTMODIFIED,CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED
LOCKED_IN_PROJECT,CMS_ONLINE_RESOURCES.RESOURCE_SIZE,CMS_ONLINE_RESOURCES.DATE_CONTENT,CMS_ONLINE_RESOURCES.SIBLING_COUNT,CMS_ONLINE_RESOURCES.RESOURCE_VERSION,CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED
FROM CMS_ONLINE_STRUCTURE,CMS_ONLINE_RESOURCES WHERE
CMS_ONLINE_STRUCTURE.RESOURCE_PATH='/' AND
CMS_ONLINE_STRUCTURE.RESOURCE_ID=CMS_ONLINE_RESOURCES.RESOURCE_ID ORDER
BY CMS_ONLINE_STRUCTURE.STRUCTURE_STATE ASC.
<br>
16: at
org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:155)
<br>
15: at org.opencms.main.CmsShell.<init>(CmsShell.java:450)
<br>
14: at
org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1684)
<br>
13: at
org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:1111)
<br>
12: at
org.opencms.db.CmsSecurityManager.newInstance(CmsSecurityManager.java:168)
<br>
11: at
org.opencms.db.CmsSecurityManager.init(CmsSecurityManager.java:2588)
<br>
10: org.opencms.main.CmsInitException: Critical error while loading
driver manager.
<br>
9: --------------------
<br>
8: Giving up, unable to start OpenCms.
<br>
7: Critical error while loading driver manager.
<br>
6: The following critical error occured:
<br>
5: --------------------
<br>
4: 3: 2: OpenCms property file:
"C:\dev\java\jakarta-tomcat-5.0.30\webapps\opencms7beta\WEB-INF\config\opencms.properties".
<br>
1: OpenCms WEB-INF path:
"C:\dev\java\jakarta-tomcat-5.0.30\webapps\opencms7beta\WEB-INF\".
<br>
<br>
Marco a écrit :
<br>
<blockquote type="cite">I just tried to install this release
candidat but couldn't get it to work.
<br>
The OpenCms logging mentioned problems with a query, this had to do
with the absence of the group_ou column in the cms_groups tables.
<br>
<br>
This field could not be found on the database server and in the
setup/database/postgresql/create_tables file.
<br>
When comparing the oracle, mysql en Postgresql dump I suspect that the
Postgresql dump is horribly out of date.
<br>
<br>
Hope this helps the RC phase a bit further.
<br>
<br>
Cheers,
<br>
<br>
<br>
_______________________________________________
<br>
This mail is sent to you from the opencms-dev mailing list
<br>
To change your list options, or to unsubscribe from the list, please
visit
<br>
<a class="moz-txt-link-freetext" href="http://lists.opencms.org/mailman/listinfo/opencms-dev">http://lists.opencms.org/mailman/listinfo/opencms-dev</a>
<br>
<br>
<br>
</blockquote>
<br>
<br>
_______________________________________________
<br>
This mail is sent to you from the opencms-dev mailing list
<br>
To change your list options, or to unsubscribe from the list, please
visit
<br>
<a class="moz-txt-link-freetext" href="http://lists.opencms.org/mailman/listinfo/opencms-dev">http://lists.opencms.org/mailman/listinfo/opencms-dev</a>
<br>
</blockquote>
<br>
<br>
_______________________________________________
<br>
This mail is sent to you from the opencms-dev mailing list
<br>
To change your list options, or to unsubscribe from the list, please
visit
<br>
<a class="moz-txt-link-freetext" href="http://lists.opencms.org/mailman/listinfo/opencms-dev">http://lists.opencms.org/mailman/listinfo/opencms-dev</a>
<br>
</blockquote>
<br>
</body>
</html>