Open Dental Forum

Hello! Some notes in my process of upgrading to MySQL 8.0.13 with Open Dental:

I recently did a complete upgrade of the IT infrastructure in my office. In doing so, I didn't really think through the actual migration of the Open Dental database until much later in the process. I was focused on the hardware and supporting software, and not so much on the physical moving of the database to a new machine.

While doing this, I decided it was time to upgrade my mysql binary. As an aside, I didn't really pay attention to the fact that I had never updated the mysql package since my original installation. It's likely significant bugs were fixed since v5.5.14. The current general availability (GA) release for v5.5 is 5.5.62.

After going through much documentation, I realized that the upgrade path to get from 5.5 to 8.0 is pretty complicated and time consuming. Utilizing the mysql community installer makes the process easier. I had to upgrade from 5.5 to 5.6, and mysql_upgrade the databases. Then to 5.7, and then to 8.0. On the first leg of the upgrade chain, I realized that I had a ton of opendentalbackup* directories, which I subsequently deleted which cut down the upgrade time. At the end of the process, I started upgrading all of the databases to InnoDB, and I'm still waiting patiently for that to complete. My preliminary tests showed no issues with using Open Dental.

Outside of the immense difficulty in supporting multiple version of MySQL, any technical or development issues as to why this hasn't been explored yet? Anyone else testing this?

dqadri wrote:...to get from 5.5 to 8.0 is pretty complicated and time consuming. Utilizing the mysql community installer makes the process easier. I had to upgrade from 5.5 to 5.6, and mysql_upgrade the databases. Then to 5.7, and then to 8.0.

That's the main reason right there. We're still trying to figure out a smooth and automated way to make that transition with the "mysql_upgrade" in the middle. That upgrade affects all databases present (not only the Open Dental ones) so that is something else that we try to be conscientious about even though that isn't a terribly big concern. We always tell users to have a single instance of MySQL dedicated to Open Dental because of such changes.

You're quite lucky that we upgraded our MySQL connector recently otherwise you wouldn't have been able to even connect to a MySQL 8.0 service

jsalmon wrote:You're quite lucky that we upgraded our MySQL connector recently otherwise you wouldn't have been able to even connect to a MySQL 8.0 service

As with everything else in life, it's all about timing! My database connection has been slow this morning, but I'm seeing significant disk activity on the database machine. Changing a day on the schedule is taking a while and I see spikes almost to 100% for drive activity, I'll see how it looks after I move the database to a (much) faster machine over lunch.

There are several default settings that MySQL has chosen that slow the system down significantly.
e.g. setting eq_range_index_dive_limit to 0 would probably speed up your system.
https://dev.mysql.com/doc/refman/8.0/en ... dive_limit

Sorry for not posting earlier, I had to take a vacation:

Upgrading wasn't too bad, but I did hit one snafu that took me a while to iron out (during work hours, which wasn't fun).

In newer versions of MySQL, a connection using an RSA certificate is supported, but that functionality hasn't been fully implemented within OD and can give strange error messages. Also, the hashing function that MySQL uses internally has changed from 5.5. I had some issues with the migrated database and establishing a connection from OD even after I got the past the RSA error. After I removed the user and recreated it, and specified the older password hashing algorithm, and updated all of the permissions I was able to login without an issue. I was not entirely sure why the SUPERUSER permission is required though.

Also, my speed issues resolved on the newer hardware. I haven't really sat down to tune the database yet, it's a future project.