upgrade from 17.2.15 to 18.0.0

[Piotr Sz]

Hello,

one of openolats i'm upgrading from version 17.0.2 has problem with upgrading to version 18.0 0

webapp compiles and run, everything seems ok but when entering some of the tests i got error I351-J

"

Last business path:  http://myip:8088/url/RepositoryEntry/19038309/CourseNode/100182100276189
Business path:  http://myip:8088/url/RepositoryEntry/19038309/CourseNode/101017423724936/Reminders/0 

"

in olat.log there is an error with round function in select query 

[http-nio-8088-exec-9] ERROR  OLATRuntimeException ^%^ I351-J391 ^%^ org.olat.core.logging.OLATRuntimeException ^%^ 31621325 ^%^ IP ^%^ http://MYIP:8088/auth/RepositoryEntry/19038309/CourseNode/100182100276189 ^%^ Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36 ^%^ **RedScreen** JDBC exception executing SQL [select round(a1_0.a_score, 0),count(a1_0.id) from o_as_entry a1_0 where a1_0.a_score is not null and a1_0.fk_entry=? and a1_0.fk_reference_entry=? and a1_0.a_subident=? and (a1_0.a_obligation is null or a1_0.a_obligation in (?,?,?,?)) and (a1_0.fk_identity in(select distinct g1_0.fk_identity_id from o_re_to_group r3_0,o_bs_group_member g1_0,o_bs_group_member g2_0 where r3_0.fk_entry_id=? and r3_0.fk_group_id=g2_0.fk_group_id and g2_0.g_role='coach' and g2_0.fk_identity_id=? and r3_0.fk_group_id=g1_0.fk_group_id and g1_0.g_role='participant') or a1_0.fk_identity in(select distinct g6_0.fk_identity_id from o_re_to_group r4_0,o_bs_group_member g6_0 where r4_0.fk_entry_id=? and r4_0.fk_group_id=g6_0.fk_group_id and g6_0.g_role='participant')) group by round(a1_0.a_score, 0)] [ERROR: function round(double precision, integer) does not exist
  Wskazowka: No function matches the given name and argument types. You might need to add explicit type casts.

from what i have read, this is no a existing function problem, but a parameter it gets (number is to big)

what can i do in this situation ? 

i could change that select query if i'd know where it is 

Please, OpenOLAT Community, anyone has any idea how to fix it ? 

cheers 

[Urs Hensler]

Hi Piotr

What kind of database are you using? Oracle?

Kind regards,

Urs

--
--
Sie erhalten diese Nachricht, weil Sie Mitglied sind von Google
Groups-Gruppe "OpenOlat".
Für das Erstellen von Beiträgen in dieser Gruppe senden Sie eine E-Mail
an open...@googlegroups.com
Um sich von dieser Gruppe abzumelden, senden Sie eine E-Mail an
openolat+u...@googlegroups.com
Weitere Optionen finden Sie in dieser Gruppe unter
http://groups.google.com/group/openolat?hl=de
-------------------------------------------------------------------------------------------------------------------
OpenOlat - infinite learning - http://www.openolat.org
---
You received this message because you are subscribed to the Google Groups "OpenOlat" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openolat+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openolat/c1d9afd0-5144-4ebb-a378-7d3fc2a9e435n%40googlegroups.com.

[Piotr Sz]

Hi Urs, 

its postgres 15 

[Urs Hensler]

Hi Piotr

We do not have tested OpenOlat with Postgres 15 but only with Postgres 14. 🤔 However, we suspect that one instance runs successfully with Postgres 15.

Did you only upgrade OpenOlat or did you change the database version as well?

I do not think the number is too big. The number has the same value as before the upgrade, doesn't it? But you can check the highest rounded number.

select max(a_score) from o_as_entry;

Can you check which round methods are present in your postgres?

\df round

The select query is generated by this code:

Kind regards,

Urs

To view this discussion on the web visit https://groups.google.com/d/msgid/openolat/1cbfa036-5a89-4b37-9669-148022ac0037n%40googlegroups.com.

[Piotr Sz]

oh,i though its necessary to upgrade Postgres as well as other deamons 

my original system is openolat 17.0.2 on tomcat 9, java 11 and postgres 14 

im upgrading OpenOLATs till version 17.1.8, then i switch to tomcat10,java17 and postgres 15 on OO version 17.2.0 till a newest one 

thanks for a tip, im gonna trying upgrading olat's leaving postgres on 14 

with big i meant its bigint data type in database, and i guess it cannot be rounded , like in my error - SELECT ROUND(SOMETHING, 0)

here's a screen 

i had 3 other OpenOLATs upgraded (one very huge) and they are working great as 18.0.5, with  tomcat 10 and postgres 15, but that one stubborn

thanks for trying to help me 

[Piotr Sz]

unfortunately, leaving postgres 14 didnt fix it. 

Everything is ok, until switching from version 17.2.15 to 18.0.0, some tests are throwing errors as below. 

[http-nio-8088-exec-3] WARN   SqlExceptionHelper ^%^ I727-J484 ^%^ org.hibernate.engine.jdbc.spi.SqlExceptionHelper ^%^ 31621325 ^%^ IP ^%^ http://IP:8088/auth/RepositoryEntry/31391771/CourseNode/107448309768575 ^%^ Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/117.0 ^%^ SQL Error: 0, SQLState: 42883
[http-nio-8088-exec-3] ERROR  SqlExceptionHelper ^%^ I727-J485 ^%^ org.hibernate.engine.jdbc.spi.SqlExceptionHelper ^%^ 31621325 ^%^ IP ^%^ http://IP:8088/auth/RepositoryEntry/31391771/CourseNode/107448309768575 ^%^ Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/117.0 ^%^ ERROR: function round(double precision, integer) does not exist

  Wskazowka: No function matches the given name and argument types. You might need to add explicit type casts.

  Pozycja: 8
2023-09-29 01:16:42.349 [http-nio-8088-exec-3] WARN   ExceptionWindowController ^%^ I727-J486 ^%^ org.olat.core.gui.exception.ExceptionWindowController ^%^ 31621325 ^%^ IP ^%^ http://IP:8088/auth/RepositoryEntry/31391771/CourseNode/107448309768575 ^%^ Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:109.0) Gecko/20100101 Firefox/117.0 ^%^ ExceptionWindowController<init>: Throwable occurred, logging the full stacktrace: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select round(a1_0.a_score, 0),count(a1_0.id) from o_as_entry a1_0 where a1_0.a_score is not null and a1_0.fk_entry=? and a1_0.fk_reference_entry=? and a1_0.a_subident=? and (a1_0.a_obligation is null or a1_0.a_obligation in (?,?,?,?)) and (a1_0.fk_identity in(select distinct g1_0.fk_identity_id from o_re_to_group r3_0,o_bs_group_member g1_0,o_bs_group_member g2_0 where r3_0.fk_entry_id=? and r3_0.fk_group_id=g2_0.fk_group_id and g2_0.g_role='coach' and g2_0.fk_identity_id=? and r3_0.fk_group_id=g1_0.fk_group_id and g1_0.g_role='participant') or a1_0.fk_identity in(select distinct g6_0.fk_identity_id from o_re_to_group r4_0,o_bs_group_member g6_0 where r4_0.fk_entry_id=? and r4_0.fk_group_id=g6_0.fk_group_id and g6_0.g_role='participant')) group by round(a1_0.a_score, 0)] [ERROR: function round(double precision, integer) does not exist   Wskazowka: No function matches the given name and argument types. You might need to add explicit type casts

what can i do to fix it ? is my only solution to compile OO again with modified select query, im sure it will create many other problems.

Meybe some database modifications ? i can see that a_score in o_as_entry table has double precision data type, makes me confusing 

im lost of ideas 

[Urs Hensler]

Hoi Piotr

If your other OpenOlat are running fine with 18.0.x and Postgres 15, the problem can't be the Postgres version. I’m a bit late, you have also found that as well...

Maybe it is actually because the database field has the wrong datatype. Usually a_score in o_as_entry has datatype numeric not double precision. What’s the datatype in the database of your other OpenOlats?

Kind regards

Urs

To view this discussion on the web visit https://groups.google.com/d/msgid/openolat/c0ba337f-0656-40f0-a90c-f3a633e314adn%40googlegroups.com.

[Piotr Sz]

Urs, that was it! 

datatype in a_score on crashing OO was double precision , on other Olats numeric 

i changed it to numeric ale it started working on the spot , Yeah! 

<champagne opening > 

Many thanks Urs for directing me to problem, i owe you one! :) 

[Urs Hensler]

You are welcome.

Have a nice weekend,

Urs

To view this discussion on the web visit https://groups.google.com/d/msgid/openolat/eb608839-b7dd-4f76-9b97-1f53750a3635n%40googlegroups.com.