You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@roller.apache.org by Boris Milikič <bo...@mf-rs.si> on 2008/02/15 13:08:58 UTC
Upgrade to Roller 4.1 on 10g
I upgraded Roller 3.1 to Roller 4.1 (oracle 10g, Tomcat 5.25, Windoes xp)
roller-custom.properties:
installation.type=manual
database.configurationType=jdbc
database.jdbc.driverClass=oracle.jdbc.driver.OracleDriver
database.jdbc.connectionURL=jdbc:oracle:thin:@irisint.com:1521:ROMA
database.jdbc.username=bmil
database.jdbc.password=bmil
mail.configurationType=properties
mail.hostName=smtp.mf
1) There is a lot of bugs in oracle/dbscripts.sql
SQL> insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser as u, website as w, roller_user_permissions as p
4 where p.user_id = u.id and p.website_id = w.id and permission_mask = 1;
from rolleruser as u, website as w, roller_user_permissions as p
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser u, website w, roller_user_permissions p
4* where p.user_id = u.id and p.website_id = w.id and permission_mask = 1
SQL> /
0 rows created.
2) Roller didn't find any user in DB, that exists in 3.1
ID
----------------------------------
USERNAME
----------------------------------
PASSPHRASE
----------------------------------
8a82f0e61763560d01176399cb1a0001
admin
----
3) I can't insert new user
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot insert NULL into ("BMIL"."USERROLE"."USERID")
{prepstmnt 14838503 INSERT INTO userrole (id, rolename, username) VALUES (?, ?, ?) [params=(String) 47cc40d6-aeb7-4a5b-8d8a-f808c3806b01, (String) editor, (String) bmil]} [code=1400, state=23000]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:95)
... 189 more
RE: Upgrade to Roller 4.1 on 10g
Posted by Boris Milikič <bo...@mf-rs.si>.
My previous mail was mistake. Follwing alter table script :
alter table userrole alter column userid null;
should be in oracle
alter table userrole modify (userid null);
-----Original Message-----
From: Boris Milikič [mailto:boris.milikic@mf-rs.si]
Sent: Wednesday, February 20, 2008 2:27 PM
To: dev@roller.apache.org
Subject: RE: Upgrade to Roller 4.1 on 10g
There is no "approved", "pending" cols in "roller_comment" table in 3.1, 4.0 or 4.1.
Boris
-- update existing data to use new status column
SQL> update roller_comment set status = 'APPROVED', posttime=posttime
SQL> where approved=1;
update roller_comment set status = 'APPROVED', posttime=posttime where approved=1
* ERROR at line 1:
ORA-00904: "APPROVED": invalid identifier
SQL> update roller_comment set status = 'PENDING', posttime=posttime
SQL> where pending=1;
update roller_comment set status = 'PENDING', posttime=posttime where pending=1
* ERROR at line 1:
ORA-00904: "PENDING": invalid identifier
SQL> update roller_comment set status = 'SPAM', posttime=posttime where
SQL> spam=1;
update roller_comment set status = 'SPAM', posttime=posttime where spam=1
* ERROR at line 1:
ORA-00904: "SPAM": invalid identifier
SQL> desc roller_comment
Name Null? Type
----------------------------------------- -------- -----------------------
ID NOT NULL VARCHAR2(48)
ENTRYID NOT NULL VARCHAR2(48)
NAME VARCHAR2(255)
EMAIL VARCHAR2(255)
URL VARCHAR2(255)
CONTENT CLOB
POSTTIME NOT NULL TIMESTAMP(6)
NOTIFY NOT NULL NUMBER(1)
REMOTEHOST VARCHAR2(128)
REFERRER VARCHAR2(255)
USERAGENT VARCHAR2(255)
PLUGINS VARCHAR2(255)
CONTENTTYPE NOT NULL VARCHAR2(128)
STATUS NOT NULL VARCHAR2(20)
-----Original Message-----
From: Boris Milikič [mailto:boris.milikic@mf-rs.si]
Sent: Friday, February 15, 2008 4:33 PM
To: dev@roller.apache.org
Subject: RE: Upgrade to Roller 4.1 on 10g
Correction:
1) There is a lot of bugs in oracle/310-to-400-migration.sql
SQL> drop table if exists rollerconfig;
drop table if exists rollerconfig
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
I can't find "if exists" in Oracle DDL.
Boris
-----Original Message-----
From: Boris Milikič [mailto:boris.milikic@mf-rs.si]
Sent: Friday, February 15, 2008 1:09 PM
To: dev@roller.apache.org
Subject: Upgrade to Roller 4.1 on 10g
I upgraded Roller 3.1 to Roller 4.1 (oracle 10g, Tomcat 5.25, Windoes xp)
roller-custom.properties:
installation.type=manual
database.configurationType=jdbc
database.jdbc.driverClass=oracle.jdbc.driver.OracleDriver
database.jdbc.connectionURL=jdbc:oracle:thin:@irisint.com:1521:ROMA
database.jdbc.username=bmil
database.jdbc.password=bmil
mail.configurationType=properties
mail.hostName=smtp.mf
1) There is a lot of bugs in oracle/dbscripts.sql
SQL> insert into roller_permission
SQL> (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser as u, website as w, roller_user_permissions as p
4 where p.user_id = u.id and p.website_id = w.id and permission_mask = 1;
from rolleruser as u, website as w, roller_user_permissions as p
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser u, website w, roller_user_permissions p
4* where p.user_id = u.id and p.website_id = w.id and permission_mask = 1
SQL> /
0 rows created.
2) Roller didn't find any user in DB, that exists in 3.1 ID
----------------------------------
USERNAME
----------------------------------
PASSPHRASE
----------------------------------
8a82f0e61763560d01176399cb1a0001
admin
----
3) I can't insert new user
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot insert NULL into ("BMIL"."USERROLE"."USERID") {prepstmnt 14838503 INSERT INTO userrole (id, rolename, username) VALUES (?, ?, ?) [params=(String) 47cc40d6-aeb7-4a5b-8d8a-f808c3806b01, (String) editor, (String) bmil]} [code=1400, state=23000]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:95)
... 189 more
RE: Upgrade to Roller 4.1 on 10g
Posted by Boris Milikič <bo...@mf-rs.si>.
There is no "approved", "pending" cols in "roller_comment" table in 3.1, 4.0 or 4.1.
Boris
-- update existing data to use new status column
SQL> update roller_comment set status = 'APPROVED', posttime=posttime where approved=1;
update roller_comment set status = 'APPROVED', posttime=posttime where approved=1
*
ERROR at line 1:
ORA-00904: "APPROVED": invalid identifier
SQL> update roller_comment set status = 'PENDING', posttime=posttime where pending=1;
update roller_comment set status = 'PENDING', posttime=posttime where pending=1
*
ERROR at line 1:
ORA-00904: "PENDING": invalid identifier
SQL> update roller_comment set status = 'SPAM', posttime=posttime where spam=1;
update roller_comment set status = 'SPAM', posttime=posttime where spam=1
*
ERROR at line 1:
ORA-00904: "SPAM": invalid identifier
SQL> desc roller_comment
Name Null? Type
----------------------------------------- -------- -----------------------
ID NOT NULL VARCHAR2(48)
ENTRYID NOT NULL VARCHAR2(48)
NAME VARCHAR2(255)
EMAIL VARCHAR2(255)
URL VARCHAR2(255)
CONTENT CLOB
POSTTIME NOT NULL TIMESTAMP(6)
NOTIFY NOT NULL NUMBER(1)
REMOTEHOST VARCHAR2(128)
REFERRER VARCHAR2(255)
USERAGENT VARCHAR2(255)
PLUGINS VARCHAR2(255)
CONTENTTYPE NOT NULL VARCHAR2(128)
STATUS NOT NULL VARCHAR2(20)
-----Original Message-----
From: Boris Milikič [mailto:boris.milikic@mf-rs.si]
Sent: Friday, February 15, 2008 4:33 PM
To: dev@roller.apache.org
Subject: RE: Upgrade to Roller 4.1 on 10g
Correction:
1) There is a lot of bugs in oracle/310-to-400-migration.sql
SQL> drop table if exists rollerconfig;
drop table if exists rollerconfig
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
I can't find "if exists" in Oracle DDL.
Boris
-----Original Message-----
From: Boris Milikič [mailto:boris.milikic@mf-rs.si]
Sent: Friday, February 15, 2008 1:09 PM
To: dev@roller.apache.org
Subject: Upgrade to Roller 4.1 on 10g
I upgraded Roller 3.1 to Roller 4.1 (oracle 10g, Tomcat 5.25, Windoes xp)
roller-custom.properties:
installation.type=manual
database.configurationType=jdbc
database.jdbc.driverClass=oracle.jdbc.driver.OracleDriver
database.jdbc.connectionURL=jdbc:oracle:thin:@irisint.com:1521:ROMA
database.jdbc.username=bmil
database.jdbc.password=bmil
mail.configurationType=properties
mail.hostName=smtp.mf
1) There is a lot of bugs in oracle/dbscripts.sql
SQL> insert into roller_permission
SQL> (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser as u, website as w, roller_user_permissions as p
4 where p.user_id = u.id and p.website_id = w.id and permission_mask = 1;
from rolleruser as u, website as w, roller_user_permissions as p
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser u, website w, roller_user_permissions p
4* where p.user_id = u.id and p.website_id = w.id and permission_mask = 1
SQL> /
0 rows created.
2) Roller didn't find any user in DB, that exists in 3.1 ID
----------------------------------
USERNAME
----------------------------------
PASSPHRASE
----------------------------------
8a82f0e61763560d01176399cb1a0001
admin
----
3) I can't insert new user
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot insert NULL into ("BMIL"."USERROLE"."USERID") {prepstmnt 14838503 INSERT INTO userrole (id, rolename, username) VALUES (?, ?, ?) [params=(String) 47cc40d6-aeb7-4a5b-8d8a-f808c3806b01, (String) editor, (String) bmil]} [code=1400, state=23000]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:95)
... 189 more
RE: Upgrade to Roller 4.1 on 10g
Posted by Boris Milikič <bo...@mf-rs.si>.
Correction:
1) There is a lot of bugs in oracle/310-to-400-migration.sql
SQL> drop table if exists rollerconfig;
drop table if exists rollerconfig
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
I can't find "if exists" in Oracle DDL.
Boris
-----Original Message-----
From: Boris Milikič [mailto:boris.milikic@mf-rs.si]
Sent: Friday, February 15, 2008 1:09 PM
To: dev@roller.apache.org
Subject: Upgrade to Roller 4.1 on 10g
I upgraded Roller 3.1 to Roller 4.1 (oracle 10g, Tomcat 5.25, Windoes xp)
roller-custom.properties:
installation.type=manual
database.configurationType=jdbc
database.jdbc.driverClass=oracle.jdbc.driver.OracleDriver
database.jdbc.connectionURL=jdbc:oracle:thin:@irisint.com:1521:ROMA
database.jdbc.username=bmil
database.jdbc.password=bmil
mail.configurationType=properties
mail.hostName=smtp.mf
1) There is a lot of bugs in oracle/dbscripts.sql
SQL> insert into roller_permission
SQL> (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser as u, website as w, roller_user_permissions as p
4 where p.user_id = u.id and p.website_id = w.id and permission_mask = 1;
from rolleruser as u, website as w, roller_user_permissions as p
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1 insert into roller_permission (id,username,actions,objectid,objecttype,pending,datecreated)
2 select w.id||u.username, u.username, 'edit_draft', w.handle, 'Weblog', 0, current_timestamp
3 from rolleruser u, website w, roller_user_permissions p
4* where p.user_id = u.id and p.website_id = w.id and permission_mask = 1
SQL> /
0 rows created.
2) Roller didn't find any user in DB, that exists in 3.1 ID
----------------------------------
USERNAME
----------------------------------
PASSPHRASE
----------------------------------
8a82f0e61763560d01176399cb1a0001
admin
----
3) I can't insert new user
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ORA-01400: cannot insert NULL into ("BMIL"."USERROLE"."USERID") {prepstmnt 14838503 INSERT INTO userrole (id, rolename, username) VALUES (?, ?, ?) [params=(String) 47cc40d6-aeb7-4a5b-8d8a-f808c3806b01, (String) editor, (String) bmil]} [code=1400, state=23000]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$800(LoggingConnectionDecorator.java:56)
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:857)
at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:269)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1363)
at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:95)
... 189 more