Question about Sakai, Hibernate and MySQL 5.7

[Charles Severance]

Hi all,

I have a small Sakai production instance that I am using to teach using SakaiPlus at U Michigan. When classes started in January, I started getting weird tracebacks from Hibernate with causes like this:

o.h.e.j.s.SqlExceptionHelper.logExceptions (conn=12470) Incorrect arguments to mysqld_stmt_execute

exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet

o.h.e.j.s.SqlExceptionHelper.logExceptions No such column: 'ID1_110_'. 'id1_110_' must be in [count(in_collection), , .count(in_collection), .]

o.h.e.j.s.SqlExceptionHelper.logExceptions No such column: 'ID2_56_'. 'id2_56_' must be in [xml, sakai_preferences.xml]

I talked to Matt and Earle - Matt thought it might be the "table case" issue and Earl recommended that I change:

hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

To

hibernate.dialect=org.hibernate.dialect.MySQL57InnodbDialect

I made Earle’s suggested change and the errors went away.

So I am happy - but still would like to hear from others if it is important to be running the 5.7 dialects when using 5.7. Or if folks have seen these kinds of errors - what they turned out to be and how you fixed them.

Thanks in advance.

/Chuck

[Sam Ottenhoff]

What table is this? Do you have the full stack pointing to a specific line? I haven’t run into any MySQL 5.7 specific issues. 

--
You received this message because you are subscribed to the Google Groups "Sakai Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-dev/59AD4B1B-1D25-4B08-92E6-998FC774D22D%40umich.edu.

[Sanghyun Jeon]

Dear All,

 

I am seeking clarification regarding the appropriate Hiberate Dialect version to use with Sakai version 23, which requires MySQL 8. According to the Sakai 23 Database Support documentation,

The recommended dialect is “hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect”.

However, I have encountered some confusion as I have also heard that “MySQL8Dialect” is intended for MySQL 8.

On the other hand, the Hibernate JavaDoc indicates that “MySQL8Dialect” is deprecated. Additionally, I came across you’re the below  email thread which has further compounded my confusion.

 

Given these conflicting pieces of information, I would greatly appreciate your guidance on the correct Hibernate dialect to use. Accurate information on this matter would be extremely helpful for implementors working with Sakai 23 and MySQL8.

For context, I am currently using Mysql 8.0.39, mysql-connector-j-8.2.0.jar with MySQL5InnoDBDialect (sakai.properties - hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect), Sakai 22.x (not Sakai 23 yet) but I am uncertain if this is the optimal configuration.

 

Thank you for your assistance.

 

Best,

 

Sanghyun

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-dev/CAJEDiB7jkB9bWpS7x50pDwmVZgA_CTsxQ7R_9MEg8fer%3DGVfvQ%40mail.gmail.com.

---

[EXTERNAL EMAIL] Exercise caution before clicking on links or opening attachments.

[Austin]

We've been using MySQL8Dialect since January 2024

Sakai 22.4
MySQL 8
mysql-connector-j-8.2.0.jar
org.hibernate.dialect.MySQL8Dialect

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-dev/SJ0PR03MB7052233B004C38180F5C619D83B02%40SJ0PR03MB7052.namprd03.prod.outlook.com.

[Sanghyun Jeon]

Thank you for your reply, Austin,

 

Could you confirm if you used “MySQL5InnoDBDialect” for Sakai 21 with MySQL 5? I’m curious if you’ve transitioned from “MySQL8InnoDBDialect” to MySQL8InnoDBDialect” for MySQL 8? Additionally, is there a specific reason for not using “MySQL8InnoDBDialect”?

 

Best,

 

Sanghyun

[Sanghyun Jeon]

Sorry, typos.

 

I meant “I’m curious if you’ve transitioned from “MySQL5InnoDBDialect” to MySQL8Dialect” for MySQL 8?”

 

Sanghyun

[Austin]

for Sakai 21.5 we used:

MySQL 5.7

org.hibernate.dialect.MySQL57InnoDBDialect

mysql-connector-java-5.1.46-bin.jar

I don't think "MySQL8InnoDBDialect" exists?

[Sanghyun Jeon]

Thank you for your confirmation.

 

We used MySQL 5.7, mysql-connector-java-5.1.43.jar, org.hibernate.dialect.MySQL5InnoDBDialect

 

For MySQL8InnoDBDialect, I have some doubts as well, but I came across an example while Googling. It suggests “Run tests on MySQL 8 with MySQL8InnoDBDialect” in this Github

I haven’t seen any references to “MySQL8InnoDBDialect” elsewhere either.

 

Additionally, I’m curious about the dialect used fo the Sakai community QA server. Could someone please check the Hibernate version used on the Sakai QA server (version 23.x with MySQL8)? It seems that the Sakai.properties file available at the provided URL does not include this information.

[Sanghyun Jeon]

Austin,

 

Have you updated any settings besides “org.hibernate.dialect.MySQL8Dialect” after I upgrade MySQL 57 to MySQL 8? For instance, I recall needing to adjust the ‘sql_mode’ settings in my “my.cnf” file. If you’ve come across any other necessary changes, please let me know.

 

Best,

 

Sanghyun

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-dev/SJ0PR03MB705222D2644034A33811D90783B02%40SJ0PR03MB7052.namprd03.prod.outlook.com.

[Austin]

in our MySQL 8 database we're using:

mysql> show variables like "sql_mode";
+---------------+----------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                              |
+---------------+----------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------+

however, during the upgrade, I believe we had to temp. remove one or all of "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE" then add them back in after the migration was complete.  This was to get around some legacy data we had in some tables.

We also had to modify some of the conversion .sql files to accommodate existing data as well.  But this could also depend on whether or not you have auto.ddl = true or false.  We have ours set to false.

Another thing we had to do was start up the app once (the separate server in our cluster that handles SiteStats) and check the logs for the error, "Job Scheduler could not start after delay" and if the error had something to do with the "SiteStats Event Aggregator Trigger", we had to:

mysql> delete from qrtz_cron_triggers where trigger_name = 'SiteStats Event Aggregator Trigger';
mysql> delete from qrtz_triggers where trigger_name = 'SiteStats Event Aggregator Trigger';
mysql> delete from qrtz_job_details where job_name = 'SiteStats Event Aggregator';

then re-create the site stats aggregator job and trigger in the Admin UI

I would highly recommend testing your migration procedure on a test server and test database before you run it on your production systems.

Hope this helps,

Austin

[Earle Nietzel]

I've update the docs 

nightly mysql8 instances use:

hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

-earle

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-dev/CAKL84%3Dm3z4A4ZVff6KgCiCHUYYZcUap1gR8uS1x01PumJTkXOA%40mail.gmail.com.

[Earle Nietzel]

Nightly instances don't set any sql_mode flags

and only have NO_ENGINE_SUBSTITUTION

But as Austin pointed out they don't deal with legacy data either.

-earle

[Sanghyun Jeon]

Thank you, Austin!

[Sanghyun Jeon]

Thank you, Earle!

 

Sanghyun