[opencms-dev] OpenCms 7 RC 1 available
Marco
mailings at weeswel.com
Thu May 10 23:20:44 CEST 2007
I got a bit further by creating my own pg.sql (postgresql create_tables)
file.
All files including my pg.sql file can be found on
http://www.weeswel.com/logs/
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
Read on for the gory details.
P.S. Can the devs shed some light on this and create a patch or is this
a self-service scenario.
P.P.S. Can I trust this code to work properly on anything else then Mysql.
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.
In Pseudo language my steps to get something working, since the original
postgresql database template does not work
1)Download the script mysql2pgsql.perl
2)cp %opencms%/setup/database/mysql_41/create_tables.sql
/tmp/Mysql_create_tables.sql
3)vi /tmp/Mysql_create_tables.sql
Strip all indexes from this file
Strip all Characterset utf 8 from this file
Strip all TEXT BINARY from this file
Strip all (255) from the PRIMARY KEY entries
3)./mysql2pgsql.perl --nodrop /tmp/Mysql_create_tables pg.sql
4)Deploy your opencms.war(7.0RC1) file wait until it is extracted
5)cp pg.sql .%OPENCMS%/setup/database/postgresql/create_tables.sql
6) Open the http://yoursite/opencms/setup and follow the setup routine
7) Choose to install all documentation to exercise the database table a bit
This import failed, I found the following problem in the
%opencms%/WEB-INF/logs/setup.log file
Important part follows
/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)
VALUES
('04a8d8f6-ff37-11db-964e-2df1a5244f64','04a8d8f7-ff37-11db-964e-2df1a5244f64','/',0,0,9223372036854775807,'00000000-0000-0000-0000-000000000000').
23: Caused by: org.postgresql.util.PSQLException: Parameter index
out of range.
/
/
/So we try to run this query from the pgsql commandline/
opencms150=# \d CMS_ONLINE_STRUCTURE;
Table "public.cms_online_structure"
Column | Type | Modifiers
-------------------+----------+-----------
structure_id | bytea | not null
resource_id | bytea | not null
parent_id | bytea | not null
resource_path | text | not null
structure_state | smallint | not null
date_released | bigint | not null
date_expired | bigint | not null
structure_version | integer | not null
Indexes:
"cms_online_structure_pkey" PRIMARY KEY, btree (structure_id)
Check constraints:
"cms_online_structure_structure_state_check" CHECK (structure_state
>= 0)
opencms150=# INSERT INTO CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID)
opencms150-# VALUES
('04a8d8f6-ff37-11db-964e-2df1a5244f64','04a8d8f7-ff37-11db-964e-2df1a5244f64','/',0,0,9223372036854775807,'00000000-0000-0000-0000-000000000000')
opencms150-# ;/
/ERROR: null value in column "structure_version" violates not-null
constraint
/
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.
Just to pain myself I installed the product pretending to be a database
server My-SQL.
/mysql> describe CMS_ONLINE_STRUCTURE;
+-------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+----------------------+------+-----+---------+-------+
| STRUCTURE_ID | varchar(36) | NO | PRI | | |
| RESOURCE_ID | varchar(36) | NO | MUL | | |
| PARENT_ID | varchar(36) | NO | MUL | | |
| RESOURCE_PATH | text | NO | MUL | | |
| STRUCTURE_STATE | smallint(5) unsigned | NO | MUL | | |
| DATE_RELEASED | bigint(20) | NO | | | |
| DATE_EXPIRED | bigint(20) | NO | | | |
| STRUCTURE_VERSION | int(11) | NO | | | |
+-------------------+----------------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
/
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.
Lets try the query in Mysql
/mysql> INSERT INTO
-> CMS_ONLINE_STRUCTURE
(STRUCTURE_ID,RESOURCE_ID,RESOURCE_PATH,STRUCTURE_STATE,DATE_RELEASED,DATE_EXPIRED,PARENT_ID
) VALUES (
->
'95c23c20-ff2c-11db-9bd2-b3972de27f02','95c23c21-ff2c-11db-9bd2-b3972de27f02','/',0,0,9223372036854775807,'000000
00-0000-00
'> 00-0000-000000000000');
Query OK, 1 row affected, 2 warnings (0.05 sec)
/
/mysql> select * from CMS_ONLINE_STRUCTURE;
+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+
| STRUCTURE_ID |
RESOURCE_ID |
PARENT_ID | RESOURCE_PATH | STRUCTURE_STATE |
DATE_RELEASED | DATE_EXPIRED | STRUCTURE_VERSION |
+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+
| 95c23c20-ff2c-11db-9bd2-b3972de27f02 |
95c23c21-ff2c-11db-9bd2-b3972de27f02 | 00000000-0000-00
00-0000-00000000000 | / | 0 | 0 |
9223372036854775807 | 0 |
+--------------------------------------+--------------------------------------+--------------------------------------+---------------+-----------------+---------------+---------------------+-------------------+
1 row in set (0.00 sec)
/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.
/
/
Marco schreef:
> It seems like it,
> Caused by: java.sql.SQLException: Column not found message from
> server: "Unknown column 'CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION' in
> 'field list'"
>
> 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.
>
>
>
> Stéphane Verger schreef:
>> Hi !
>>
>> I had an unexpected error trying to install OpenCms 7 RC1 with the
>> java 1.4 package.
>> It seems to have a problem with the sql schemas, is it the same
>> problem as yours, marco ?
>>
>> Has anybody already had the same probleme, and got a solution ?
>>
>> Ty,
>>
>> R.
>>
>>
>>
>> 47: Shutdown completed, total uptime was 00:00:29.
>> 46: Shutting down OpenCms, version 7 RC 1 (6.9.1) in web
>> application "opencms7beta"
>> 45: 44: 43: at
>> org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:209)
>>
>> 42: at
>> org.opencms.setup.CmsSetupWorkplaceImportThread.kill(CmsSetupWorkplaceImportThread.java:118)
>>
>> 41: at org.opencms.main.CmsShell.exit(CmsShell.java:557)
>> 40: java.lang.NullPointerException
>> 39: at
>> org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:194)
>>
>> 38: at org.opencms.main.CmsShell.start(CmsShell.java:646)
>> 37: at
>> org.opencms.main.CmsShell.executeCommands(CmsShell.java:818)
>> 36: at
>> org.opencms.main.CmsShell.executeCommand(CmsShell.java:743)
>> 35: ${user}@${project}>java.lang.NullPointerException
>> 34: ${user}@${project}># Turn echo on
>> 33: ... 9 more
>> 32: at
>> org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.java:1653)
>> 31: at
>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
>>
>> 30: at
>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
>>
>> 29: at
>> com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1634)
>>
>> 28: at com.mysql.jdbc.Connection.execSQL(Connection.java:2281)
>> 27: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1279)
>> 26: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1168)
>> 25: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2001)
>> 24: Caused by: java.sql.SQLException: Column not found message
>> from server: "Unknown column 'CMS_ONLINE_STRUCTURE.STRUCTURE_VERSION'
>> in 'field list'"
>> 23: ... 5 more
>> 22: at
>> org.opencms.db.CmsSecurityManager.init(CmsSecurityManager.java:2579)
>> 21: at
>> org.opencms.db.CmsDriverManager.init(CmsDriverManager.java:4034)
>> 20: at
>> org.opencms.db.generic.CmsProjectDriver.fillDefaults(CmsProjectDriver.java:577)
>>
>> 19: at
>> org.opencms.db.generic.CmsVfsDriver.createResource(CmsVfsDriver.java:471)
>>
>> 18: at
>> org.opencms.db.generic.CmsVfsDriver.readResource(CmsVfsDriver.java:1666)
>> 17: Caused by: org.opencms.db.CmsDbSqlException: An SQL error
>> occurred when executing the following query:
>> com.mysql.jdbc.PreparedStatement at 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.
>> 16: at
>> org.opencms.setup.CmsSetupWorkplaceImportThread.run(CmsSetupWorkplaceImportThread.java:155)
>>
>> 15: at org.opencms.main.CmsShell.<init>(CmsShell.java:450)
>> 14: at
>> org.opencms.main.OpenCmsCore.upgradeRunlevel(OpenCmsCore.java:1684)
>> 13: at
>> org.opencms.main.OpenCmsCore.initConfiguration(OpenCmsCore.java:1111)
>> 12: at
>> org.opencms.db.CmsSecurityManager.newInstance(CmsSecurityManager.java:168)
>>
>> 11: at
>> org.opencms.db.CmsSecurityManager.init(CmsSecurityManager.java:2588)
>> 10: org.opencms.main.CmsInitException: Critical error while
>> loading driver manager.
>> 9: --------------------
>> 8: Giving up, unable to start OpenCms.
>> 7: Critical error while loading driver manager.
>> 6: The following critical error occured:
>> 5: --------------------
>> 4: 3: 2: OpenCms property file:
>> "C:\dev\java\jakarta-tomcat-5.0.30\webapps\opencms7beta\WEB-INF\config\opencms.properties".
>>
>> 1: OpenCms WEB-INF path:
>> "C:\dev\java\jakarta-tomcat-5.0.30\webapps\opencms7beta\WEB-INF\".
>>
>> Marco a écrit :
>>> I just tried to install this release candidat but couldn't get it to
>>> work.
>>> 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.
>>>
>>> This field could not be found on the database server and in the
>>> setup/database/postgresql/create_tables file.
>>> When comparing the oracle, mysql en Postgresql dump I suspect that
>>> the Postgresql dump is horribly out of date.
>>>
>>> Hope this helps the RC phase a bit further.
>>>
>>> Cheers,
>>>
>>>
>>> _______________________________________________
>>> This mail is sent to you from the opencms-dev mailing list
>>> To change your list options, or to unsubscribe from the list, please
>>> visit
>>> http://lists.opencms.org/mailman/listinfo/opencms-dev
>>>
>>>
>>
>>
>> _______________________________________________
>> This mail is sent to you from the opencms-dev mailing list
>> To change your list options, or to unsubscribe from the list, please
>> visit
>> http://lists.opencms.org/mailman/listinfo/opencms-dev
>
>
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please
> visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20070510/5ffc8d67/attachment.htm>
More information about the opencms-dev
mailing list