Oracle 11

[andreas...@ebcont.com]

Hi,

I am trying to run OpenOLAT with Oracle 11.2.0.3 (this is what we have). 

I've found incompatibilities in setupDatabases.sql because Oracle 11 does not support "generated as identity" columns. I've fixed this using Oracle sequences. I've attached the modified file, in case somebody is interested (setupDatabases_autoincrement-sequences.sql).

Now I can start OpenOLAT and access the login screen. But logging in gives an error: "Passwort oder Benutzername sind nicht richtig" (password or username are wrong).

In the OpenOLAT log file I see an exception:

Job threw an unhandled exception
Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL-Befehl wurde nicht korrekt beendet

A database trace shows that a "PARSE ERROR" happens due to a query ending with "fetch first :1 rows only". I've attached the full query.

My conclusion is that I have to change the Hibernate dialect from "org.hibernate.dialect.Oracle12cDialect" to "org.hibernate.dialect.Oracle10gDialect" (there is not Oracle 11 dialect). 

Does anybody know where I can configure that without changing databaseCorecontext.xml and recompiling OpenOLAT? Maybe somehow in ROOT.xml (as defined on https://www.openolat.com/fileadmin/adminwiki/_START_.html) ?

Best regards,

Andreas

[Florian Gnaegi]

Hi Andreas

I can not help you with this as I have no Oracle and don’t know enough about the details there, I am sure others can tell you where you have to modify this. 

SHORT: 

But I can tell you that you should consider using MySQL or Postgres, one of the „official“ supported DBMS. For both databases we run all unit tests several times a day while there is zero testing (not automated not manual) for any version of Oracle. Today, it is a bit a risk using OpenOLAT together with Oracle as no one in the community is actually doing this. 

LONG:

In the past several instances used Oracle, however they moved to Postgres several years ago. As we already had everything running on Oracle at this time we decided to keep the Oracle dialect in case someone want to use this in the future as you do. We kept adding the database creation stuff but did not actually test them. 

We do not recommend using Oracle unless you are either a) building up an automated testing infrastructure yourself and you are doing all the testing and maintenance for all the code changes in the future or b) you pay us for integrating such an testing environment in our continuous integration and testing infrastructure to „officially“ support Oracle. We would love to fully support Oracle, however at this point is is just not economically feasibly for us to build and maintain such a testing infrastructure and building all the testing pipelines. Using Oracle is definitely not a way to go if you want to use the unmodified OpenOLAT code. 

Don’t get me wrong: it is great if you are testing and providing fixes for Oracle, I absolutely applaud and welcome this contribution! But I think it is important for you to be aware of the fact that those initial problems will only be the beginning and there will probably be more if it once the system is up and running. You will need to perform high-load tests and optimize some queries as we did for Postgres and MySQL as the DBMS behave differently in some scenarios. 

For production of large installations we recommend Postgres. All large installations we are aware of are running with Postgres and our entire cloud with many installations all run on Postgres. 

Best Regards

Florian

--
--
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.
For more options, visit https://groups.google.com/d/optout.
<setupDatabases_autoincrement-sequences.sql><query-with-parse-error.sql>

--------------------------------------------------------------------

professional services for the e-learning system OpenOLAT

hosting - operating - support - development - mobile - consulting

--------------------------------------------------------------------

www.frentix.com | www.openolat.com 

frentix  GmbH

Florian Gnägi, Geschäftsführer

Hardturmstrasse 76

CH-8005 Zürich, Switzerland

tel://+41-43-544-9000

dir://+41-43-544-9001

fax://+41-43-544-9009

Find me on wire skype twitter xing linkedin 

--------------------------------------------------------------------

[Stéphane Rossé]

Hi Andreas

We need Oracle 12. It's not a problem of setupDatabase.sql and dialect. We need Oracle 12c because use auto_increment (there are some big advantages with this in specific setup of our customers) and we defined this in the Hibernate mappings ( @GeneratedValue(strategy = GenerationType.IDENTITY)), and because of the pageing feature added in oracle 12c which is more inline with MySQL and PostreSQL. I cannot simply replace the auto_increment with sequences, this breaks the Hibernate Mapping. It's a compromise to support MySQL, PostgreSQL and Oracle at the same time.

Best regards

Stéphane

[Stéphane Rossé]

Hi

We have fix the possible issues with Oracle 12c and unit tests run with this database. These fixes are available with OpenOLAT 13.1.1. Bringing support for Oracle 11 will be more work, if you are interested to finance this development, you can contact frentix.

Best regards

Stéphane Rossé

---------------------------------------------------------------
professional services for the open source eLearning system OLAT
hosting - operating - support - java development - consulting
---------------------------------------------------------------

www.frentix.com | www.openolat.org

frentix GmbH
Stéphane Rossé

Hardturmstrasse 76
CH-8005 Zürich, Switzerland

tel://+41-43-544-9000

dir://+41-43-544-9004

fax://+41-43-544-9009

Find me on linkedin
---------------------------------------------------------------

On Thursday, 17 January 2019 13:40:34 UTC+1, Andreas Hubmer wrote:

[Andreas Hubmer]

Hi,

Thanks for this update.

I guess/hope that we will follow your advice and switch to PostgreSQL.

The Administrator's Wiki (https://www.openolat.com/fileadmin/adminwiki/_START_.html) mentions PostgreSQL 9.4 and MySQL 5.6.

Is OpenOLAT compatible/tested with the latest versions, PostgreSQL 11 and MySQL 8?

Best regards,

Andreas

[Stéphane Rossé]

Hi

I quicly tested with PostgreSQL 11 without seeing any issues. PostgreSQL is for us very stable for years. Personally I work with a PostgreSQL 9.6 for the moment.

MySQL 8 works too. But if you use the new default utf8mb4 encoding, there are a few index which are too long. Replacing the 8 - 10 fields of o_user from varchar(255) to text(255) will do it. And last thing, you need the version 8.0 of the JDBC Driver.

Best regards

Stéphane Rossé