[xwiki-users] ORA-00932 inconsistent datatypes expected got clob
Hello, I have problem with editing documents with the new WYSIWYG editor (page syntax – XWiki 2.0) - on attempt to create a link to existing documents I get a message “There was an error loading the data” – it looks like the select from the xwikidoc table doesn’t work properly. Log file: “Exception while searching documents with sql select distinct doc from XWikiDocument doc where 1=1 and doc.author='XWiki.Admin' order by doc.date desc” and Oracle error “ORA-00932 inconsistent datatypes”. The full log files attached. Even so, if I switch to tab “All pages”, I can see the list of all existing pages, but cannot select any of them. Links creation with XWiki 1.0 syntax works fine. Are there Oracle-specific workarounds or is it just a bug? Our environment: XE 2.1.1 + JBoss 5 ( jdk 1.6 ) + Oracle 10g. http://n2.nabble.com/file/n4556042/wysiwyg_editor.jpg wysiwyg_editor.jpg http://n2.nabble.com/file/n4556042/server.log server.log http://n2.nabble.com/file/n4556042/xwiki.log xwiki.log Thanks, Alla -- View this message in context: http://n2.nabble.com/ORA-00932-inconsistent-datatypes-expected-got-clob-tp45... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi Alla, adoro wrote:
Hello,
I have problem with editing documents with the new WYSIWYG editor (page syntax – XWiki 2.0) - on attempt to create a link to existing documents I get a message “There was an error loading the data” – it looks like the select from the xwikidoc table doesn’t work properly.
Log file: “Exception while searching documents with sql select distinct doc from XWikiDocument doc where 1=1 and doc.author='XWiki.Admin' order by doc.date desc” and Oracle error “ORA-00932 inconsistent datatypes”. The full log files attached.
Does your wiki work fine besides this? The sql that produces the exception is pretty basic and I suspect that other features don't work properly because of the same problem.
Even so, if I switch to tab “All pages”, I can see the list of all existing pages, but cannot select any of them.
Does the tree view/tab from Main.AllDocs work fine? Marius
Links creation with XWiki 1.0 syntax works fine.
Are there Oracle-specific workarounds or is it just a bug?
Our environment: XE 2.1.1 + JBoss 5 ( jdk 1.6 ) + Oracle 10g. http://n2.nabble.com/file/n4556042/wysiwyg_editor.jpg wysiwyg_editor.jpg http://n2.nabble.com/file/n4556042/server.log server.log http://n2.nabble.com/file/n4556042/xwiki.log xwiki.log
Thanks, Alla
Hi Marius, thanks for your reply.
Does your wiki work fine besides this? The sql that produces the exception is pretty basic and I suspect that other features don't work properly because of the same problem.
I haven't tested all features yet, but on the first view it looks pretty well - I can administrate Wiki, create new groups and users and edit dokuments. AllDocs works fine too. I'm not sure, but could it be due to the basic problem with our installalation, exactly speaking - with the filling tables while importing .xar archiv. We had some error messages in JBoss and xwiki log files - - Exception while reading document XWiki.XWikiPreferences Wrapped Exception: a different object with the same identifier value was already associated with the session: [com.xpn.xwiki.doc.XWikiDocument#104408758] and - ORA-01400: ORA-01400: cannot insert NULL into in ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID") I reproduced this today again (S. attached). Despite of these errors it looked like as all documents from xar archiv were imported and I could open and edit existing pages - except of creating links using new WYSIWYG editor. Best Regards, Alla http://n2.nabble.com/file/n4561415/xwiki.log xwiki.log http://n2.nabble.com/file/n4561415/server.log server.log -- View this message in context: http://n2.nabble.com/ORA-00932-inconsistent-datatypes-expected-got-clob-tp45... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi Alla, adoro wrote:
Hi Marius,
thanks for your reply.
Does your wiki work fine besides this? The sql that produces the exception is pretty basic and I suspect that other features don't work properly because of the same problem.
I haven't tested all features yet, but on the first view it looks pretty well - I can administrate Wiki, create new groups and users and edit dokuments. AllDocs works fine too.
Can you try this velocity code in a new page: {{velocity}} $xwiki.search("select doc from XWikiDocument doc where doc.author='XWiki.Admin'").size() {{/velocity}} and tell me if the same exception is thrown? And then try: {{velocity}} $xwiki.searchDocuments("where doc.author='XWiki.Admin'").size() {{/velocity}} Maybe it's a problem with the way Hibernate translates HQL to Oracle's SQL dialect. Hope this helps, Marius
I'm not sure, but could it be due to the basic problem with our installalation, exactly speaking - with the filling tables while importing .xar archiv. We had some error messages in JBoss and xwiki log files - - Exception while reading document XWiki.XWikiPreferences Wrapped Exception: a different object with the same identifier value was already associated with the session: [com.xpn.xwiki.doc.XWikiDocument#104408758] and - ORA-01400: ORA-01400: cannot insert NULL into in ("XWIKI"."XWIKILARGESTRINGS"."XWL_ID")
I reproduced this today again (S. attached).
Despite of these errors it looked like as all documents from xar archiv were imported and I could open and edit existing pages - except of creating links using new WYSIWYG editor.
Best Regards, Alla
http://n2.nabble.com/file/n4561415/xwiki.log xwiki.log http://n2.nabble.com/file/n4561415/server.log server.log
Hi Marius, I tried to reproduce this situation with the new installation of the last version of XE (2.2 stable) + Oracle 10g + Tomcat. Installation and .xar import were OK – no errors. Insertion a new link in a page – the same message –“There was an error loading the data”. As I understood this message concerns not AllDocs, but the tab “My Recent Changes”, that comes as first by default while inserting link. http://n2.nabble.com/file/n4587113/wysiwyg_insert_link_myrecentchanges.jpg xwiki.log: 2010-02-17 14:10:30,125 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] ERROR server.DefaultWikiService - Error number 3223 in 3: Exception while searching documents with sql select distinct doc from XWikiDocument doc where 1=1 and doc.author='XWiki.Admin' order by doc.date desc localhost.log: Exception while dispatching incoming RPC call com.google.gwt.user.server.rpc.UnexpectedException: Service method 'public abstract java.util.List com.xpn.xwiki.wysiwyg.client.WikiService.getRecentlyModifiedPages(int,int)' threw an unexpected exception: java.lang.RuntimeException: Failed to retrieve the lists of recently modified pages. --- However Main.RecentChanges and Panels.MyRecentModifications show the pages changed by Admin. --- Marius Dumitru Florea wrote:
Can you try this velocity code in a new page:
{{velocity}} $xwiki.search("select doc from XWikiDocument doc where doc.author='XWiki.Admin'").size() {{/velocity}}
and tell me if the same exception is thrown? And then try:
{{velocity}} $xwiki.searchDocuments("where doc.author='XWiki.Admin'").size() {{/velocity}}
Maybe it's a problem with the way Hibernate translates HQL to Oracle's SQL dialect.
The both queries work fine --- I think the problem is either in “order by” or in “distinct” (Or in the combination of both of them…) I executed the similar query like in xwiki.log (select distinct doc from XWikiDocument doc where 1=1 and doc.author='XWiki.Admin' order by doc.date desc) against the xwiki-Oracle db select distinct doc.XWD_FULLNAME from xwikidoc doc where 1=1 and doc.XWD_AUTHOR='XWiki.Admin' order by doc.XWD_DATE desc and got an error ORA-01791: not a SELECTed expression, which means that "order by"- column should be selected as well (?) At the same time the query with order by expression but without distinct works fine... I have only 4 Docs, created by Admin, they are all unique and can't see any difference in the output with and without distinct... The next query with subquery works too. - select distinct XWD_FULLNAME from (select doc.XWD_FULLNAME, doc.XWD_DATE from xwikidoc doc where 1=1 and doc.XWD_AUTHOR='XWiki.Admin' order by doc.XWD_DATE desc) --- I tried also {{velocity}} $xwiki.search("select doc from XWikiDocument doc where doc.author='XWiki.Admin' order by doc.date",3,0).size() {{/velocity}} - works OK too. $xwiki.search("select distinct doc from XWikiDocument doc where doc.author='XWiki.Admin' order by doc.date",2,0) produces error “Failed to execute macro: velocity”: org.xwiki.rendering.macro.MacroExecutionException: Failed to evaluate Velocity Macro for content [ $xwiki.search("select distinct doc from XWikiDocument doc where doc.author='XWiki.Admin'").size() ] --- --- Tab “All Pages” - works fine. --- Switch to tab “Search” – arrears the same message –“There was an error loading the data” with the ORA-00932 error in the xwiki.log: 2010-02-17 15:36:49,453 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] WARN util.JDBCExceptionReporter - SQL Error: 932, SQLState: 42000 2010-02-17 15:36:49,453 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] ERROR util.JDBCExceptionReporter - ORA-00932: Inkonsistente Datentypen: - erwartet, CLOB erhalten 2010-02-17 15:36:49,453 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] ERROR server.DefaultWikiService - Error number 3223 in 3: Exception while searching documents with sql select distinct doc from XWikiDocument as doc where (lower(doc.title) like '%%' or lower(doc.fullName) like '%%') Wrapped Exception: could not execute query I have no clue why it happens, because the query: select distinct XWD_FULLNAME from xwikidoc doc where (lower(doc.XWD_TITLE) like '%%' or lower(doc.XWD_FULLNAME) like '%%'); works in Oracle well. Best Regards, Alla http://n2.nabble.com/file/n4587113/localhost.2010-02-17.log localhost.2010-02-17.log http://n2.nabble.com/file/n4587113/xwiki.log xwiki.log -- View this message in context: http://n2.nabble.com/ORA-00932-inconsistent-datatypes-expected-got-clob-tp45... Sent from the XWiki- Users mailing list archive at Nabble.com.
Hi Alla, I found this http://opensource.atlassian.com/projects/hibernate/browse/HHH-3606 . The problem is indeed caused by the distinct restriction. The good news is that this restriction is not really needed so I opened http://jira.xwiki.org/jira/browse/XWIKI-4907 and removed it. The problem should be fixed now on both 2.2 and 2.3 branches. Would be great if you could test the latest 2.3 or 2.2 XE snapshot ( http://maven.xwiki.org/snapshots/com/xpn/xwiki/products/ ). Thanks! Marius adoro wrote:
Hi Marius,
I tried to reproduce this situation with the new installation of the last version of XE (2.2 stable) + Oracle 10g + Tomcat. Installation and .xar import were OK – no errors. Insertion a new link in a page – the same message –“There was an error loading the data”.
As I understood this message concerns not AllDocs, but the tab “My Recent Changes”, that comes as first by default while inserting link.
http://n2.nabble.com/file/n4587113/wysiwyg_insert_link_myrecentchanges.jpg xwiki.log:
2010-02-17 14:10:30,125 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] ERROR server.DefaultWikiService - Error number 3223 in 3: Exception while searching documents with sql select distinct doc from XWikiDocument doc where 1=1 and doc.author='XWiki.Admin' order by doc.date desc
localhost.log:
Exception while dispatching incoming RPC call com.google.gwt.user.server.rpc.UnexpectedException: Service method 'public abstract java.util.List com.xpn.xwiki.wysiwyg.client.WikiService.getRecentlyModifiedPages(int,int)' threw an unexpected exception: java.lang.RuntimeException: Failed to retrieve the lists of recently modified pages.
---
However Main.RecentChanges and Panels.MyRecentModifications show the pages changed by Admin.
---
Marius Dumitru Florea wrote:
Can you try this velocity code in a new page:
{{velocity}} $xwiki.search("select doc from XWikiDocument doc where doc.author='XWiki.Admin'").size() {{/velocity}}
and tell me if the same exception is thrown? And then try:
{{velocity}} $xwiki.searchDocuments("where doc.author='XWiki.Admin'").size() {{/velocity}}
Maybe it's a problem with the way Hibernate translates HQL to Oracle's SQL dialect.
The both queries work fine
--- I think the problem is either in “order by” or in “distinct” (Or in the combination of both of them…)
I executed the similar query like in xwiki.log (select distinct doc from XWikiDocument doc where 1=1 and doc.author='XWiki.Admin' order by doc.date desc) against the xwiki-Oracle db
select distinct doc.XWD_FULLNAME from xwikidoc doc where 1=1 and doc.XWD_AUTHOR='XWiki.Admin' order by doc.XWD_DATE desc
and got an error ORA-01791: not a SELECTed expression, which means that "order by"- column should be selected as well (?) At the same time the query with order by expression but without distinct works fine... I have only 4 Docs, created by Admin, they are all unique and can't see any difference in the output with and without distinct...
The next query with subquery works too. - select distinct XWD_FULLNAME from (select doc.XWD_FULLNAME, doc.XWD_DATE from xwikidoc doc where 1=1 and doc.XWD_AUTHOR='XWiki.Admin' order by doc.XWD_DATE desc)
---
I tried also
{{velocity}} $xwiki.search("select doc from XWikiDocument doc where doc.author='XWiki.Admin' order by doc.date",3,0).size() {{/velocity}}
- works OK too.
$xwiki.search("select distinct doc from XWikiDocument doc where doc.author='XWiki.Admin' order by doc.date",2,0)
produces error “Failed to execute macro: velocity”:
org.xwiki.rendering.macro.MacroExecutionException: Failed to evaluate Velocity Macro for content [ $xwiki.search("select distinct doc from XWikiDocument doc where doc.author='XWiki.Admin'").size() ]
--- --- Tab “All Pages” - works fine.
---
Switch to tab “Search” – arrears the same message –“There was an error loading the data” with the ORA-00932 error in the xwiki.log:
2010-02-17 15:36:49,453 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] WARN util.JDBCExceptionReporter - SQL Error: 932, SQLState: 42000 2010-02-17 15:36:49,453 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] ERROR util.JDBCExceptionReporter - ORA-00932: Inkonsistente Datentypen: - erwartet, CLOB erhalten
2010-02-17 15:36:49,453 [http://localhost:8080/xwiki/resources/js/xwiki/wysiwyg/xwe/WikiService.gwtrp...] ERROR server.DefaultWikiService - Error number 3223 in 3: Exception while searching documents with sql select distinct doc from XWikiDocument as doc where (lower(doc.title) like '%%' or lower(doc.fullName) like '%%') Wrapped Exception: could not execute query
I have no clue why it happens, because the query: select distinct XWD_FULLNAME from xwikidoc doc where (lower(doc.XWD_TITLE) like '%%' or lower(doc.XWD_FULLNAME) like '%%'); works in Oracle well.
Best Regards, Alla
http://n2.nabble.com/file/n4587113/localhost.2010-02-17.log localhost.2010-02-17.log http://n2.nabble.com/file/n4587113/xwiki.log xwiki.log
Hi Marius, I've just tested WYSIWYG on XE 2.2-SNAPSHOT.27143 and XE 2.3-SNAPSHOT.27143 Insert link in both tabs (My Recent Changes and Search) works perfect. Yippee! Thanks a lot, Marius! Alla -- View this message in context: http://n2.nabble.com/ORA-00932-inconsistent-datatypes-expected-got-clob-tp45... Sent from the XWiki- Users mailing list archive at Nabble.com.
participants (2)
-
adoro -
Marius Dumitru Florea