Opendental with mysql 8.x -- 1st try

This forum is for programmers who have questions about the source code.
Post Reply
jfd2
Posts: 43
Joined: Sat Feb 16, 2008 11:14 pm

Opendental with mysql 8.x -- 1st try

Post by jfd2 » Sat Nov 21, 2020 6:50 pm

Hello all,

I have an initial try today with the opendental 20.3.44 to connect with the mysql8.0 (ubuntu 20lts default installation). here are the findings:

1) when using the native code without any changes, there is an error message "Retrieval of the RSA public key is not enabled for insecure connections" when initiate the db connection.
This is due to the MySQL Server 5.7 added the sha256_password authentication plugin. MySQL Server 8.0 adds the caching_sha2_password authentication plugin and makes it the default. These plugins use RSA public key encryption to protect the user’s password in transit.

2) with a small change in the DataConnection.cs to change the ConnectionString:
+ ";SslMode=none" => + ";SslMode=Required"
and rebuild the solution

3) then I tried the newly build and played for 10 minutes, so far no surprises. Great job for the opendental team!

Hope this helps.

Thanks
best regards,

Jinfan

jfd2
Posts: 43
Joined: Sat Feb 16, 2008 11:14 pm

Re: Opendental with mysql 8.x -- 1st try

Post by jfd2 » Sun Nov 22, 2020 6:36 pm

I checked the table type, they are still myisam types. long time ago I converted all tables to inno and opendental converted back automatically. Should I keep the myisam type or innoengine is supported?

Thanks.
best regards,

Jinfan

User avatar
jordansparks
Site Admin
Posts: 5382
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Opendental with mysql 8.x -- 1st try

Post by jordansparks » Mon Nov 23, 2020 1:13 pm

innodb is supported, but ALL tables should be the same, and it must also be the default engine. The downside is that you can no longer make backups by copying files. That's a deal breaker as far as I'm concerned. Making backups is hugely important, and you should not be doing a single thing that would make backups more complex.
Jordan Sparks, DMD
http://www.opendental.com

jfd2
Posts: 43
Joined: Sat Feb 16, 2008 11:14 pm

Re: Opendental with mysql 8.x -- 1st try

Post by jfd2 » Tue Nov 24, 2020 7:13 pm

Thanks for the reply. I noticed that while I was playing with it with inno engine. But if backup is the concern here, do we think the copy of db is the best way? I would think that with lots of unused databases must have some performance hits, in addition to the diskspace and fragmentation.

In my case, I have half day backups ( I did not use raid due to the small office use and disk damage creates lot of hassel to find another one etc) using sqldump. In app upgrade, I immediately delete the od db copy as soon as it finishes.

Another way to implementing backup is to use the OD file system to backup the mysqldump files with daily backup and weekly pruning. Currently I assume there is no implementation of this kind except during app upgrade. So the backup between OD upgrades could be none unless the clinic implemented the own, I guess.

I am also curious what percentage of users using linux as the db and file server and how many use msft servers.

Thanks.
best regards,

Jinfan

User avatar
jordansparks
Site Admin
Posts: 5382
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Opendental with mysql 8.x -- 1st try

Post by jordansparks » Tue Nov 24, 2020 9:02 pm

When we do a db backup during update, you might notice that we create all myisam files. Backups are pointless otherwise.
Jordan Sparks, DMD
http://www.opendental.com

jfd2
Posts: 43
Joined: Sat Feb 16, 2008 11:14 pm

Re: Opendental with mysql 8.x -- 1st try

Post by jfd2 » Wed Jan 13, 2021 5:50 pm

Hello Everybody,

After running 2 months of the OD 20.3.x with mysql 8.0.x, there are something to be noted in case anyone else going to try the new version of mysql:

1) We use lighthouse before for appointment reminders etc. It was working smoothly. Due to the upgrade of the mysql, the lighthouse was not working anymore and they decided not to upgrade their java code for our office alone ( my brief looking at their config files indicting that at least need to upgrade the mysql connector lib and ssl enhancement in the connection). After some investigation and exploration, I ended up coding something myself to replace it and it works beautifully right now to send reminders with the definition of rules by the clinic.
2) Previously, we use ssh tunnel for offsite access of the database to avoid security breaches. Right now after the ssl in the db connection, it is way too slow to access the db from offsite. Something I will start to investigate and might need to recode the the connection string. Anybody has done that?

Thanks a lot.
best regards,

Jinfan

Post Reply