Re: [gatein-dev] GateIn on oracle DB

On Thu, Mar 11, 2010 at 1:35 PM, Michal Vančo <mvanco(a)redhat.com&gt; wrote: > Nicolas Filotto wrote: >> >> >> On Thu, Mar 11, 2010 at 9:57 AM, Michal Vančo <mvanco(a)redhat.com&gt; >> wrote: >> Hi, >> >> today I checked following situations with oracle10: >> - oracle11 driver for oracle10 db >> - create bundle with JCR 1.12.0-GA >> - delete gatein-sample* in server/default/deploy -> only >> change was >> that it failed with: Cannot create the portal container >> 'portal', before >> it was: Cannot create the portal container 'sample-portal' and >> Cannot >> create the portal container 'portal' >> - creating jcr tables manually with script: >> http://anonsvn.jboss.org/repos/exo-jcr/jcr/tags/1.12.0-GA/exo.jcr.compone... >> >> didn't help, but after adding script to create JCR_CONFIG >> table -> it >> started to work (detail of script in another mail on >> gatein-dev) >> >> So, looks like JCR don't want to initialize. Any suggestions >> why? >> So far the only thing that we know is that >> con.getMetaData().getTables(null, null, "JCR_CONFIG", null); returns a >> result, so the JCR assumes that the table exists and thus try to >> launch a query on it so it fails since actually the table doesn't >> exist. >> So far, we don't know why this method returns something on your side, >> can you add a breakpoint on the method >> org.exoplatform.services.jcr.impl.config.JDBCConfigurationPersister.isDbInitialized() of the project exo.jcr.component.core and tell us what you have in your resultset? > > > when calling > ResultSet trs = con.getMetaData().getTables(null, null, "JCR_CONFIG", null); > return trs.next(); > > it returns true and content of trs variable from debug is attached. > > Then it ends on line 237: ResultSet res = ps.executeQuery(); > > Is it enough? Or what more can I explore in debug? con.getMetaData().getTables(null, null, "JCR_CONFIG", null); is wrong on oracle at all, as the call returns all JCR_CONFIG tables in the Oracle instance (crazy for me) but here I have several databases with different tablespaces and I see all of them within user who should not see ones. Nicolas, it will be like if several databases contains JCR_CONFIG table: null EXOCLUSTER1 JCR_CONFIG null EXOCLUSTER2 JCR_CONFIG null JCRDB JCR_CONFIG .... FYI, I have applied your script and yes - it reproduces the issue but I still guess it's DBA side work to provide the rights to users from one schema. After I replaced con.getMetaData().getTables(null, null, "JCR_CONFIG", null); with try { ResultSet tprs = con.createStatement().executeQuery("SELECT COUNT(*) FROM " + tableName); return tprs.next(); catch (SQLException e) { return false; }

this part works well but next thing you'll meet will be http://jira.exoplatform.org/browse/JCR-1333 so it's still DBA task IMO. > >> >> Michal >> >> >> Michal Vančo wrote: >> > Hi, >> > >> > Nicolas Filotto wrote: >> > > Could you please provide the list of tables that you can >> see with your >> > > credentials on oracle 10 G? >> > >> > As I wrote yesterday in my additional reply: >> > I'm using sqldeveloper tool to see the content of oracle DB, >> and at all >> > oracle10 cases, I can't see that anything is created >> (tables, sequence, >> > indexes...) in JCR schema. Only IDM schema initialization is >> done. >> > >> > > >> > > On Wed, Mar 10, 2010 at 8:41 PM, Michal Vančo >> <mvanco(a)redhat.com&gt; >> > > wrote: >> > > Hi, >> > > >> > > currently I have same results as before (table or >> view does >> > > not exist) >> > > on oracle10 in these conditions: >> > > - 2 datasources on one machine (differentiated by >> user names) >> > > - 1 datasource on a machine (JCR and IDM >> together) >> > > - 2 datasources on separate machines >> > >> > It was added for these cases ^. >> > >> > > >> > > To run GateIn trunk on Oracle10, I do following >> steps: >> > > - prepare GateIn trunk bundle >> > > - unzip bundle >> > > - change driver class, url, user and password in >> > > jcr-configuration.xml >> > > >> in ../server/default/deploy/gatein.ear/02portal.war/WEB-INF/conf/jcr/ >> > > - change driver class, url, user and password in >> > > idm-configuration.xml >> > > >> in ../server/default/deploy/gatein.ear/02portal.war/WEB-INF/conf/organization/ >> > > - add <property name="hibernate.hbm2ddl.auto" >> > > value="create-drop"/> in >> > > idm-configuration.xml >> > > - copy oracle10 jdbc driver >> into ../server/default/lib >> > > - start gatein by run.sh in bin folder >> > > >> > > (I can provide a script from hudson where you can >> add your own >> > > paths and >> > > credentials...) >> > > >> > > For oracle11: >> > > - when run with same config as on oracle10, I get >> error: >> > > ORA-00972: identifier is too long (when >> initializing >> > > >> org.exoplatform.services.jcr.impl.core.lock.jbosscache.CacheableLockManagerImpl) >> > > >> > > - oracle11_jcr.jpg - screen of what has been >> created in DB >> > > >> > > - if I edit table names >> > > >> in ../server/default/deploy/gatein.ear/02portal.war/WEB-INF/conf/jcr/repository-configuration.xml, gatein started correctly >> > > - oracle11_jcr_shorten_names.jpg - screen of what >> has been >> > > created in DB >> > > >> > > Let me know if you need more details to reproduce >> it on your >> > > side. >> > > >> > > Best regards, >> > > >> > > Michal >> > > >> > > >> > > Nicolas Filotto píše v St 10. 03. 2010 v 17:39 >> +0100: >> > > >> > > > >> > > > >> > > > On Wed, Mar 10, 2010 at 5:19 PM, Prabhat Jha >> > > <pjha(a)redhat.com&gt; wrote: >> > > > I have created >> > > https://jira.jboss.org/jira/browse/EXOJCR-582 >> > > > for oracle11 issue. Please check that I >> have right >> > > affects and >> > > > fix versions. >> > > > >> > > > thx, FYI I've just set the right settings >> > > > >> > > > The version of GateIn and JCR is the one >> that's in >> > > GateIn >> > > > trunk currently. The way our environment >> is setup is >> > > that we >> > > > have one Oracle10 DB servers and >> different usernames >> > > which I >> > > > believe means different schemas in >> Oracle for >> > > different tests >> > > > (gatein-ui, gatein-jcr etc) configured >> on that >> > > server. I would >> > > > have to wait for Michal to show up for >> info. He >> > > needed to >> > > > attend to some personal stuff : hospital >> to check on >> > > his mom. >> > > > >> > > > But in your environment, you have GateIn >> starting >> > > fine on >> > > > Oracle 10, right? >> > > > >> > > > no so far, we have only tested with pure JCR but >> in the same >> > > > conditions as gatein, now as we know the version >> of gatein >> > > we will try >> > > > with the same version (i.e the trunk) >> > > > >> > > > thanks, >> > > > prabhat >> > > > >> > > > >> > > > >> > > > >> > > > ----- "Nicolas Filotto" >> <nicolas.filotto(a)gmail.com&gt; >> > > wrote: >> > > > > Prabhat and/or Michal, could you >> please provide >> > > the answers >> > > > of the questions asked by Peter and me. >> We cannot >> > > reproduce so >> > > > far, so we need your help. Please note >> that we >> > > blocked the >> > > > release of GA because of this last >> issue, so we need >> > > your help >> > > > to fix it asap. >> > > > > >> > > > > >> > > > Thank you in advance, >> > > > BR, >> > > > Nicolas >> > > > > >> > > > > On Wed, Mar 10, 2010 at 3:02 PM, >> Nicolas Filotto >> > > > <nicolas.filotto(a)gmail.com&gt; wrote: >> > > > > >> > > > Hi Prabhat, >> > > > > >> > > > > >> > > > my comments inline >> > > > > >> > > > > >> > > > > On Wed, Mar 10, 2010 at 2:01 >> PM, Prabhat >> > > Jha >> > > > <pjha(a)redhat.com&gt; wrote: >> > > > > >> > > > > >> > > > > Hi Nicolas, >> > > > > >> > > > > Yes, a jira is on the >> way but way >> > > to >> > > > reproduce this problem >> is quite >> > > simple. You >> > > > start with a clean >> Oracle 10 >> > > database instance >> > > > and see the problem >> during startup. >> > > It would >> > > > make sense to have this >> issue solved >> > > or have a >> > > > work around before >> GateIn GA. >> > > > > >> > > > It is not enough, we need to >> know, which >> > > exact version >> > > > of eXo JCR and GateIn, you are >> using and the >> > > content >> > > > of the files that you modified. >> > > > could you please open a >> connexion with the >> > > same >> > > > account and give us the list of >> tables that >> > > you can >> > > > see >> > > > > >> > > > > >> > > > > Quick questions: >> > > > > As per oracle11 >> finding, is JCR >> > > config >> > > > (table names) going to >> be updated >> > > across the >> > > > board? >> > > > > >> > > > You mean to change the sql >> scripts for >> > > oracle 11G? If >> > > > so please create a JIRA, it will >> be done in >> > > the next >> > > > maintenance version of eXo JCR >> which will be >> > > 1.12.1 GA >> > > > planned for the end of March >> > > > > >> > > > > Also, I am wondiering >> given that >> > > Oracle 10 >> > > > has been around for a >> while, I am >> > > wondering >> > > > what has changed in the >> latest JCR >> > > that it >> > > > does not work here. Did >> the last GA >> > > version >> > > > work? >> > > > > >> > > > Hard to say before reproducing >> your bug >> > > > >> > > > > >> > > > > >> > > > > Thanks, >> > > > > Prabhat >> > > > >> > > > >> > > > > >> > > > > >> > > > > >> > > > > ----- "Nicolas >> Filotto" >> > > > >> <nicolas.filotto(a)gmail.com&gt; wrote: >> > > > > > Hi Michal, could you >> please >> > > create JIRA >> > > > issues in the eXo JCR >> project >> > > > >> > > (https://jira.jboss.org/jira/browse/EXOJCR) >> > > > with all the information >> needed to >> > > allow us to >> > > > reproduce? >> > > > > > >> > > > > > >> > > > > > On Wed, Mar 10, 2010 >> at 1:46 PM, >> > > Nicolas >> > > > Filotto >> <nicolas.filotto(a)gmail.com&gt; >> > > wrote: >> > > > > > >> > > > >> > > > > > >> > > > > > >> > > > > > >> > > > > On Wed, Mar >> 10, 2010 at >> > > 1:29 PM, >> > > > Michal Vančo >> > > <mvanco(a)redhat.com&gt; >> > > > wrote: >> > > > > > >> > > > Hi, >> > > > > > >> > > > > > >> oracle11 now >> > > works, >> > > > oracle10 >> not. See >> > > below... >> > > > > > >> > > > > > >> Peter Nedonosko >> > > wrote >> > > > > > >> > > > > Hi, >> > > > > > > >> > > > > > > >> about Oracle >> > > 10 and ' >> > > > table or >> view does >> > > not exist': >> > > > > > > >> the problem is >> > > possible >> > > > if >> addConfig used in >> > > > > > > >> > > > >> > > >> org.exoplatform.services.jcr.config.RepositoryServiceConfiguration >> > > > > > > (I >> found it in >> > > portal >> > > > \trunk >> \examples >> > > \portal\war\src >> > > > \main >> \webapp\WEB-INF >> > > \conf >> > > > >> \sample-portal\jcr >> > > > >> > > \jcr-configuration.xml) >> > > > > > >> > > > > > >> > > > What you >> meant with >> > > addConfig >> > > > plugin? >> > > > > > >> Should I add >> > > > >> > > <set-method>addConfig</set-method> in >> > > > > > >> > > jcr-configuration.xml? >> > > > > > Or >> how can I >> > > solve 'table >> > > > or view >> does not >> > > exist' on >> > > > >> oracle10? >> > > > > > >> > > > >> > > > > > >> > > > I guess it is >> not related >> > > since it >> > > > works with other >> databases >> > > > >> > > > > >> > > > >> > > > > > > >> > > > > > > if >> the >> > > database is >> > > > creating >> from the >> > > scratch and >> > > > if the >> configuration >> > > > > > > >> persister got >> > > an answer >> > > > from the >> Oracle - >> > > your table >> > > > exists >> in >> > > > > > > >> > > isDbInitialized(): >> > > > > > > >> ResultSet trs >> > > = >> > > > >> > > con.getMetaData().getTables(null, null, >> configTableName, >> > > null); >> > > > > > > >> return >> > > trs.next(); // >> > > > true = >> yes >> > > > > > > >> > > > > > > >> but then >> > > Oracle fails >> > > > with >> > > > > > > >> > > PreparedStatement ps = >> > > > >> > > con.prepareStatement("SELECT >> > > > COUNT(*) >> FROM " + >> > > > > > > >> > > configTableName + " >> > > > WHERE >> NAME=?"); >> > > > > > > >> > > ps.executeQuery(); // >> > > > here >> > > > > > > >> > > > > > > >> but I have to >> > > reproduce >> > > > it >> first... >> > > > > > > >> does Oracle 10 >> > > and 11 >> > > > runs on >> clean >> > > database from >> > > > the >> start? >> > > > > > > or >> just >> > > restarted on >> > > > existing >> db for >> > > Oracle 11? >> > > > > > >> > > > > > >> > > > Always >> on clean DBs. >> > > > > > >> > > > >> > > > > > > >> > > > > > > >> about >> > > Oracle11: >> > > > > > > >> seems it's >> > > related to >> > > > >> JBossCache loader, >> > > it creates >> > > > a table >> longer of >> > > > > > > 30 >> symbols >> > > (ORA-00972: >> > > > >> identifier is too >> > > long). >> > > > > > > >> Why it's not a >> > > problem >> > > > for >> Oracle10 or why >> > > JBossCache >> > > > acts in >> > > > > > > >> different way >> > > on >> > > > Oracle10 >> or etc... >> > > > > > > >> try to fix the >> > > problem >> > > > with >> shorten table >> > > name from >> > > > >> > > repository-configuration.xml >> > > > > > > >> e.g. >> > > > > > > >> <property >> > > > >> > > name="jbosscache-cl-cache.jdbc.table.name" >> > > value="lock_portalsys" /> >> > > > > > > >> instead of >> > > > > > > >> <property >> > > > >> > > name="jbosscache-cl-cache.jdbc.table.name" >> > > > > > > >> > > > >> > > value="jcrlock_portal_system" /> >> > > > > > > it >> should >> > > helps. >> > > > > > >> > > > > > >> > > > I >> shortened all >> > > table names in >> > > > >> > > repository-configuration.xml >> > > > and >> GateIn >> > > > > > on >> Oracle11 >> > > started >> > > > without >> "identifier >> > > is too >> > > > long" >> error. >> > > > > > >> > > > > > >> There is job >> > > when it >> > > > starts >> for the first >> > > time: >> > > > > > >> > > > >> > > >> http://hudson.jboss.org/hudson/job/gatein_portal_ui_selenium_tests_jboss5... >> > > > > > >> > > > > > I >> think that it >> > > would be >> > > > better >> to edit >> > > configuration, >> > > > because >> editing >> > > > > > >> table names >> > > before running >> > > > gatein >> isn't a good >> > > way. >> > > > > > >> > > > >> > > > >> > > > > > > >> > > > > > > >> Regards, >> > > > > > > >> Peter >> > > > > > > >> > > > > > > >> > > > > > > On >> Tue, Mar 9, >> > > 2010 at >> > > > 11:24 >> AM, Michal >> > > Vančo >> > > > >> <mvanco(a)redhat.com&gt; >> > > wrote: >> > > > > > > > >> Yes Bolek, >> > > that's >> > > > true. >> > > > > > > > >> I can't >> > > explain myself >> > > > the job >> in hudson on >> > > EPP5 >> > > > >> > > (epp_ui_selenium_tests_oracle10): >> > > > > > > > >> Because >> > > there was same >> > > > problem >> - table or >> > > view does >> > > > not >> exist (build >> > > > > > > > >> #3), builds >> > > #5 and #7 >> > > > were OK >> (GateIn >> > > started with >> > > > no >> errors) and >> > > > > > > > >> following >> > > builds again >> > > > failed >> due the same >> > > problem as >> > > > before. >> > > > > > > > >> > > > > > > > >> Regards, >> > > > > > > > >> Michal >> > > > > > > > >> > > > > > > > >> Boleslaw >> > > Dawidowicz >> > > > píše v >> Út 09. 03. >> > > 2010 v 09:59 >> > > > +0100: >> > > > > > > >> >> From >> > > mentioned logs >> > > > it looks >> like those >> > > errors >> > > > don't >> come from IDM. >> > > I >> > > > > > > >> >> think the >> > > stack trace >> > > > in all >> cases goes >> > > via >> > > > > > > >> >> > > > >> > > "org.exoplatform.services.jcr.impl.*" >> > > > > > > >> >> > > > > > > >> >> Bolek >> > > > > > > >> >> > > > > > > >> >> On >> > > 03/09/2010 09:15 >> > > > AM, >> Michal Vančo >> > > wrote: >> > > > > > > >> >> > Hi, >> > > > > > > >> >> > >> > > > > > > >> >> > I'm >> > > dealing with >> > > > problem >> to start >> > > GateIn with >> > > > Oracle >> DB. >> > > > > > > >> >> > I always >> > > start with >> > > > clean DB >> and use 2 >> > > datasource >> > > > >> instances (JCR, >> > > IDM). >> > > > > > > >> >> > >> > > > > > > >> >> > There are >> > > 2 >> > > > >> different issues: >> > > > > > > >> >> > Oracle10: >> > > > > > > >> >> > - >> > > ORA-00942: table >> > > > or view >> does not >> > > exist (start >> > > > on >> > > > > > > >> >> > >> > > > >> > > >> org.exoplatform.services.jcr.impl.config.RepositoryServiceConfigurationImpl@b2ecc0) >> > > > > > > >> >> > - only >> > > IDM schema >> > > > is >> initialized, JCR >> > > not >> > > > > > > >> >> > >> > > > > > > >> >> > Oracle11: >> > > > > > > >> >> > - >> > > ORA-00972: >> > > > >> identifier is too >> > > long (Cannot >> > > > >> instantiate >> > > component class >> > > > > > > >> >> > >> > > > >> > > >> org.exoplatform.services.jcr.impl.core.lock.jbosscache.CacheableLockManagerImpl) >> > > > > > > >> >> > - both >> > > schemas are >> > > > created >> (IDM + JCR) >> > > > > > > >> >> > >> > > > > > > >> >> > To run on >> > > oracle, I >> > > > change >> configuration >> > > in >> > > > >> > > idm-configuration.xml and >> > > > > > > >> >> > >> > > > >> > > jcr-configuration.xml, then >> > > > copy >> jdbc driver and >> > > start. >> > > > > > > >> >> > I also >> > > add<property >> > > > >> > > name="hibernate.hbm2ddl.auto" >> > > > >> > > value="create-drop"/> >> > > > > > > >> >> > to >> > > > >> > > idm-configuration.xml >> > > > >> (recommended by >> > > Boleslaw >> > > > >> Dawidowicz - it >> > > worked >> > > > > > > >> >> > on EPP, >> > > but not on >> > > > GateIn >> trunk). >> > > > > > > >> >> > >> > > > > > > >> >> > There are >> > > links to >> > > > log: >> > > > > > > >> >> > >> > > > >> > > >> http://hudson.jboss.org/hudson/job/gatein_portal_ui_selenium_tests_jboss5... >> > > > > > > >> >> > >> > > > > > > >> >> > >> > > > >> > > >> http://hudson.jboss.org/hudson/job/gatein_portal_ui_selenium_tests_jboss5... >> > > > > > > >> >> > >> > > > > > > >> >> > >> > > > > > > >> >> > I would >> > > appreciate >> > > > any >> help. >> > > > > > > >> >> > >> > > > > > > >> >> > Thanks in >> > > advance >> > > > > > > >> >> > Michal >> > > Vanco >> > > > > > > >> >> > >> > > > > > > >> >> > >> > > > >> > > _______________________________________________ >> > > > > > > >> >> > >> > > gatein-dev mailing >> > > > list >> > > > > > > >> >> > >> > > > >> > > gatein-dev(a)lists.jboss.org >> > > > > > > >> >> > >> > > > >> > > >> https://lists.jboss.org/mailman/listinfo/gatein-dev >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > >> > > _______________________________________________ >> > > > > > > > >> gatein-dev >> > > mailing >> > > > list >> > > > > > > > >> > > > >> > > gatein-dev(a)lists.jboss.org >> > > > > > > > >> > > > >> > > >> https://lists.jboss.org/mailman/listinfo/gatein-dev >> > > > > > > > >> > > > > > >> > > > > > >> > > > > > >> > > > Thanks, >> > > > > > >> Michal >> > > > > > >> > > > >> > > > >> > > > > > >> > > > >> > > _______________________________________________ >> > > > > > >> gatein-dev >> > > mailing list >> > > > > > >> > > gatein-dev(a)lists.jboss.org >> > > > > > >> > > > >> > > >> https://lists.jboss.org/mailman/listinfo/gatein-dev >> > > > > > >> > > > >> > > > > > >> > > > >> > > > > > -- >> > > > > > >> > > > >> > > > > Nicolas >> Filotto >> > > > > > JCR Product >> Manager >> > > > > > Project >> Manager >> > > > > > eXo Platform >> SAS >> > > > > > >> > > nicolas.filotto(a)exoplatform.com >> > > > > > +33 (0)6 31 >> 32 92 19 >> > > > > > >> > > > >> > > > > > >> > > > >> > > > > > -- >> > > > > > Nicolas Filotto >> > > > > > JCR Product Manager >> > > > > > Project Manager >> > > > > > eXo Platform SAS >> > > > > > >> nicolas.filotto(a)exoplatform.com >> > > > > > +33 (0)6 31 32 92 19 >> > > > > > >> > > > > >> > > > > >> > > > >> > > _______________________________________________ >> gatein-dev >> > > mailing list gatein-dev(a)lists.jboss.org >> > > >> https://lists.jboss.org/mailman/listinfo/gatein-dev >> > > > >> > > > > >> > > > > >> > > > >> > > > > -- >> > > > > Nicolas Filotto >> > > > > JCR Product Manager >> > > > > Project Manager >> > > > > eXo Platform SAS >> > > > > >> nicolas.filotto(a)exoplatform.com >> > > > > +33 (0)6 31 32 92 19 >> > > > > >> > > > >> > > > > >> > > > >> > > > > -- >> > > > > Nicolas Filotto >> > > > > JCR Product Manager >> > > > > Project Manager >> > > > > eXo Platform SAS >> > > > > nicolas.filotto(a)exoplatform.com >> > > > > +33 (0)6 31 32 92 19 >> > > > > >> > > > >> > > > >> > > > >> > > > -- >> > > > Nicolas Filotto >> > > > JCR Product Manager >> > > > Project Manager >> > > > eXo Platform SAS >> > > > nicolas.filotto(a)exoplatform.com >> > > > +33 (0)6 31 32 92 19 >> > > >> > > >> > > >> > > >> > > >> > > -- >> > > Nicolas Filotto >> > > JCR Product Manager >> > > Project Manager >> > > eXo Platform SAS >> > > nicolas.filotto(a)exoplatform.com >> > > +33 (0)6 31 32 92 19 >> > >> > Michal >> > >> > _______________________________________________ >> > gatein-dev mailing list >> > gatein-dev(a)lists.jboss.org >> > https://lists.jboss.org/mailman/listinfo/gatein-dev >> >> >> >> >> >> >> -- >> Nicolas Filotto >> JCR Product Manager >> Project Manager >> eXo Platform SAS >> nicolas.filotto(a)exoplatform.com >> +33 (0)6 31 32 92 19 >> >