Oracle Repository, SYS tables and Import solution problems

Classic Servoy
1

Hi,

After finally moving to 5.2, switching to a very secure server here at the Medical School, I’m now trying to import my main solution. I had to use Oracle as the repository as their security setup didn’t allow Postgres the correct permissions (writing to localhost I believe was the issue). I had imported a very simple, one table and one form solution on Monday without problems. However now when I am trying to import my main solution it chokes. I’m wondering if there is a problem in how the repository is set up on Oracle and the permissions that I have there. When the repository is first created this message comes through in the log:

2011-05-04 18:22 pool-1-thread-1 WARN com.servoy.j2db.server.Zc.Zo Column ‘ADTOWNER’ from table ‘EXU8COLU’ is char(0) – this is not supported when creating tables - it will be altered when exporting/importing solutions
2011-05-04 18:22 pool-1-thread-1 WARN com.servoy.j2db.server.Zc.Zo Column ‘ADTNAME’ from table ‘EXU8COLU’ is char(0) – this is not supported when creating tables - it will be altered when exporting/importing solutions

I imagine those are columns in the SYS table EXU8COLU and reading that message and then getting the import problem below I wonder if they are related to my ‘solution import’ problem and, if so, what I should do about it?

Basically when I try and import my solution I get a ton of error messages where it says that the length of the a (VARCHAR2) column in the import is smaller than the import version. I get this message regardless of whether or not I check the box “Allow data model (database) changes”. These messages all look like this and then the import chokes:

[warning] The server version of the column ‘prevrx’ of table ‘course’ in server ‘kidney’ has length 20 while in the import version the column has length 80.
[warning] The server version of the column ‘crs_comment’ of table ‘course’ in server ‘kidney’ has length 250 while in the import version the column has length 1000.
[warning] The server version of the column ‘protocol’ of table ‘course’ in server ‘kidney’ has length 10 while in the import version the column has length 40.
[warning] The server version of the column ‘regimens’ of table ‘course’ in server ‘kidney’ has length 50 while in the import version the column has length 200.

The import version lengths are ALL wrong and no schema that I have used has though lengths for those columns so no idea where they are coming from. After the failure of the import, checking the server log in the admin pages I have:

2011-05-04 18:26 	http-7000-1 	ERROR 	com.servoy.j2db.util.Debug 	Error executing sql: insert into servoy_users (user_id,user_uid,user_name,user_password) values (?,?,?,?) with params: [3 ,type: java.lang.Long, '1' ,type: java.lang.String, 'john' ,type: java.lang.String, 'ZjX1Zk7Stk24zLiRZf1XjA==' ,type: java.lang.String]
java.sql.SQLException: ORA-00001: unique constraint (SERVOY5_DEV.U_UI) violated
     at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
     at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
     at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
     at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
     at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1062)
     at sun.reflect.GeneratedMethodAccessor33.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at com.servoy.j2db.Za.Za.Zf.invoke(Zf.java:5)
     at $Proxy0.executeUpdate(Unknown Source)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at com.servoy.j2db.dataprocessing.Zu.performUpdates(Zu.java:717)
     at com.servoy.j2db.server.Za.Zc.Za(Zc.java:516)
     at com.servoy.j2db.server.Za.Zc.Za(Zc.java:141)
     at com.servoy.j2db.server.Ze.Zl.importUserInfo(Zl.java:318)
     at com.servoy.j2db.server.Ze.Zeb.Za(Zeb.java:209)
     at com.servoy.j2db.server.Ze.Zeb.importFromJarFile(Zeb.java:322)
     at com.servoy.j2db.server.servlets.ConfigServlet.Zj(ConfigServlet.java:532)
     at com.servoy.j2db.server.servlets.ConfigServlet.service(ConfigServlet.java:3717)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Thread.java:619)
     

//This ERROR below was repeated multiple times...
2011-05-04 18:26 	http-7000-1 	ERROR 	com.servoy.j2db.util.Debug 	Error executing sql: null with params: <null>
java.sql.SQLException: must rollback
     at com.servoy.j2db.Za.Za.Zn.startTransaction(Zn.java:58)
     at com.servoy.j2db.dataprocessing.Zu.performUpdates(Zu.java:359)
     at com.servoy.j2db.server.Za.Zc.Za(Zc.java:516)
     at com.servoy.j2db.server.Za.Zc.addUserToGroup(Zc.java:3)
     at com.servoy.j2db.server.Ze.Zl.importUserInfo(Zl.java:3)
     at com.servoy.j2db.server.Ze.Zeb.Za(Zeb.java:209)
     at com.servoy.j2db.server.Ze.Zeb.importFromJarFile(Zeb.java:322)
     at com.servoy.j2db.server.servlets.ConfigServlet.Zj(ConfigServlet.java:532)
     at com.servoy.j2db.server.servlets.ConfigServlet.service(ConfigServlet.java:3717)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Thread.java:619)

2011-05-04 18:27 	http-7000-1 	ERROR 	com.servoy.j2db.util.Debug 	Throwable
java.sql.SQLException: connection already closed or transaction in progress
     at com.servoy.j2db.Za.Za.Zn.close(Zn.java:67)
     at com.servoy.j2db.util.Utils.closeConnection(Utils.java:1468)
     at com.servoy.j2db.server.Ze.Zl.importingDone(Zl.java:147)
     at com.servoy.j2db.server.Ze.Zeb.Za(Zeb.java:295)
     at com.servoy.j2db.server.Ze.Zeb.importFromJarFile(Zeb.java:322)
     at com.servoy.j2db.server.servlets.ConfigServlet.Zj(ConfigServlet.java:532)
     at com.servoy.j2db.server.servlets.ConfigServlet.service(ConfigServlet.java:3717)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Thread.java:619)

2011-05-04 18:27 	http-7000-1 	ERROR 	com.servoy.j2db.util.Debug 	Throwable
com.servoy.j2db.persistence.RepositoryException: Error in transaction
     at com.servoy.j2db.server.Zc.Za.Za(Za.java:607)
     at com.servoy.j2db.server.Zc.Za.Za(Za.java:46)
     at com.servoy.j2db.server.Ze.Zl.Za(Zl.java:49)
     at com.servoy.j2db.server.Ze.Zl.importingDone(Zl.java:54)
     at com.servoy.j2db.server.Ze.Zeb.Za(Zeb.java:295)
     at com.servoy.j2db.server.Ze.Zeb.importFromJarFile(Zeb.java:322)
     at com.servoy.j2db.server.servlets.ConfigServlet.Zj(ConfigServlet.java:532)
     at com.servoy.j2db.server.servlets.ConfigServlet.service(ConfigServlet.java:3717)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:567)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
     at java.lang.Thread.run(Thread.java:619)
    Caused by: java.sql.SQLException: must rollback
     at com.servoy.j2db.Za.Za.Zn.startTransaction(Zn.java:58)
     at com.servoy.j2db.server.Zc.Za.Za(Za.java:1113)
     ... 21 more

After this the ‘solution’ actually shows up in the Admin pages as though it was fine but fails to launch of course as client.

So my first question is:

Are these issues related to that SYS table message I get when setting up servoy_repository on Oracle and if so what should I do about it?

Secondly if there is a fix there how should I completely and cleanly remove all traces of the messed up import?

Any help greatly appreciated. It has been a real slog getting this going on this server having to work with multiple levels of IT security and now that I think I’m there…

John

2

the first message doesn’t really have anything to do with the exceptions you get,
You somehow import users that has already id’s that already exist.

What happens if you import the solution but don’t import the users?

3

As far as the column length issues is concerned I can say that I know this behavior from wrong Oracle driver versions. If you do not use the exact (!) Oracle driver version, you can end up with a column being reported as 200 while it is actually only 50 in length. So if your import version is reported as 200 and you say the column is 50, it seems that the database info in the import file has been created / updated with a wrong driver version.

4

Thanks Johan and Patrick. I think the reason for the import actually choking had something to do with the user ids as Johan suggests. When I don’t import the users at all then the solution can indeed be imported. I’d really like to import the users however if possible. Using the servoy-admin pages you can create users but you can’t create the groups. I’d like all that permission/security stuff associated with each group to come over as well.

I also do still get the error messages about the column length so thanks Patrick for that tip. I am moving the solution from an Oracle 10 database (data tables not repository) to an Oracle 11 database. So do you think I should use two different drivers, one for exporting from 10 and another for importing into 11? Currently I’ve just been using the one driver for everything which until now (mainly querying both 10 and 11) has seemed to work OK.

Finally what do you guys think about that very first message I get about the SYS table EXU8COLU and two columns being char(0) and not being supported when creating tables. Is that anything to worry about?

John

5

groups will be created, thats is not depending on the users.
So groups should just show up.

Do you already have users in the repository when you import?
Or else you solution file somehow has some bad user information. If we could look into the solution file we could see why that is.

6

john.allen:
I am moving the solution from an Oracle 10 database (data tables not repository) to an Oracle 11 database. So do you think I should use two different drivers, one for exporting from 10 and another for importing into 11? Currently I’ve just been using the one driver for everything which until now (mainly querying both 10 and 11) has seemed to work OK.

Absolutely! Google for “oracle jdbc download” and get the exact driver for your DB. We have had this issue even with differences between 10.2.0.3 and 10.2.04 databases… This is also a problem if your users enter data. Imagine Servoy thinks the column is 40 while it is only 10. This throws tons of errors in the client.

7

Again thanks to you both for your advice. I never really knew that importing a solution to a new server would actually create the groups without importing the users. Of course I never really had the situation before as I haven’t had a completely ‘new’ server in years! So for that, once I have a clean export following Patrick’s advice with 100% accurate drivers for 10 and 11, I guess I’ll drop all the Servoy Repository tables in my Oracle schema and have Servoy recreate them to have a fresh start and a fresh repository.