TLS versions and DB engines

For complex topics that regular users would not be interested in. For power users and database administrators.

TLS versions and DB engines

Postby Jess » Tue Mar 27, 2018 8:23 pm

I'll preface this by saying that I know tech support don't support Linux. Also I know that there is probably some mailing list we could have monitored to realize we'd need to update from v15 on an emergency basis. (Anyone who could point out that mailing list would be appreciated!) I'll also note that TLS 1.3 was approved this week... what version of OpenDental will support that? Based on 1.2 being approved in 2008 but still not in OD in 2015, I'm thinking we might get it in... 2024? 2025? Yikes.

As you will have gathered, there was an emergency last week at one of the practices I assist. I wasn't in the office when the doctor called in to get help with the OD update, but I was listening on the phone. After navigating past our overzealous firewall, we were told that our Linux setup was all wrong, since it used MyISAM for the DB engine. It was a total mystery to me why the engine would have been set to old-n-busted MyISAM. I specifically remembered that I had edited the configuration file exactly as specified, three years ago when I installed OD in the first place. I theorized that perhaps apt had written over my config file, which it isn't supposed to do, but which I certainly have heard of it doing. "Yeah that must have been it!"

Over the weekend the firewall was fixed. This evening was able to get over to the practice to try again. Wow, the config file does have MyISAM! Confusion! Then I visited the OD Linux page and saw that yes I had done as I was told; that page actually recommends MyISAM. So, at some point between v15 and v17, OD decided to use the modern database engine that mysql has used for years. That's great! Still, this page should be updated. Also, in order to support people who only did as they were told, would it be so hard to drop something like the following at the beginning of the database update scripts?

Code: Select all
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM information_schema.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'opendental';

Failing that, just have the support staff tell people, "that thing you did before because we told you to: undo it!"

Cheers to the support people who stayed late tonight!
Jess
 
Posts: 16
Joined: Fri Jul 10, 2015 3:54 pm

Re: TLS versions and DB engines

Postby cmcgehee » Wed Mar 28, 2018 8:27 am

In regards to TLS, Open Dental will support TLS 1.3 once it becomes widely adopted. We will have to wait at least until Microsoft includes TLS 1.3 in the .NET framework. To give a little bit of explanation on why we did not support TLS 1.2 sooner, doing so required that we upgrade to .NET 4.5. This version of the framework is not available on Windows XP, so any office of ours that had computers on Windows XP would no longer be able to update Open Dental. At the time, we had a surprisingly high number of offices with XP workstations, so we took very seriously imposing the cost of updating their computers. Eventually we reached the point where TLS 1.2 was becoming crucial enough to warrant the loss of the ability to support Windows XP.
Chris McGehee
Open Dental Software
http://www.opendental.com
User avatar
cmcgehee
 
Posts: 465
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: TLS versions and DB engines

Postby jsalmon » Wed Mar 28, 2018 9:18 am

Jess wrote:...we were told that our Linux setup was all wrong, since it used MyISAM for the DB engine. It was a total mystery to me why the engine would have been set to old-n-busted MyISAM.

We recommend users use MyISAM and will continue to do so because it is significantly easier for us to support on a grand scale. We probably won't suggest another storage engine until we've written a complicated tool that can make transportable copies of entire databases (which is just copy and paste files when using MyISAM).
Jess wrote:So, at some point between v15 and v17, OD decided to use the modern database engine that mysql has used for years. That's great!

That's just false and I'm not sure where you got this impression. We have not decided to use the "modern database engine" yet, we still suggest the trusty "old-n-busted" engine.
Jess wrote:Also, in order to support people who only did as they were told, would it be so hard to drop something like the following at the beginning of the database update scripts?

Code: Select all
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM information_schema.TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'opendental';

Failing that, just have the support staff tell people, "that thing you did before because we told you to: undo it!"

We will never add such code to our convert script in order to force such infrastructure change onto our users. That should always be a decision that is made by the company themselves (your entire rant proves that this should always be a decision left up to the users / IT). We do however provide a tool that users can optionally run if they've done their due diligence to help them switch between one storage type and the other.
The InnoDb tool within Database Maintenance:
http://www.opendental.com/manual/databasemaintenance.html
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com
User avatar
jsalmon
 
Posts: 1420
Joined: Tue Nov 30, 2010 1:33 pm

Re: TLS versions and DB engines

Postby Jess » Fri Mar 30, 2018 1:58 pm

jsalmon wrote:
Jess wrote:...we were told that our Linux setup was all wrong, since it used MyISAM for the DB engine. It was a total mystery to me why the engine would have been set to old-n-busted MyISAM.

We recommend users use MyISAM and will continue to do so because it is significantly easier for us to support on a grand scale. We probably won't suggest another storage engine until we've written a complicated tool that can make transportable copies of entire databases (which is just copy and paste files when using MyISAM).
Jess wrote:So, at some point between v15 and v17, OD decided to use the modern database engine that mysql has used for years. That's great!

That's just false and I'm not sure where you got this impression. We have not decided to use the "modern database engine" yet, we still suggest the trusty "old-n-busted" engine.

I suggest you try to run an update against an ISAM database. You might see the same error we (me, and your support techs with whom I was sharing the client computer) saw: this DB is MyISAM; fix it to InnoDB if you want this update to run. After I updated it to InnoDB using the code above, the update went through fine. Seriously, just ask your techs about the goofball running Linux who called in last week. Like me, they trusted the message they got from the update program. After I followed the advice it gave me, it worked. Sorry I didn't screenshot it.

I'll admit that I'm confused on this, but I ain't the only one. b^)

jsalmon wrote:We will never add such code to our convert script in order to force such infrastructure change onto our users.

I'm telling you the update we tried to run last week and actually did run on Tuesday did force us to do just that, in that it refused to perform the upgrade until we had converted to InnoDB. Seriously just try it yourself, or ask your techs. I guess this is the point of confusion, in that you didn't intend for the update process to enforce this requirement. Sorry I thought this was intentional.
Last edited by Jess on Fri Mar 30, 2018 2:17 pm, edited 1 time in total.
Jess
 
Posts: 16
Joined: Fri Jul 10, 2015 3:54 pm

Re: TLS versions and DB engines

Postby Jess » Fri Mar 30, 2018 2:10 pm

cmcgehee wrote:In regards to TLS, Open Dental will support TLS 1.3 once it becomes widely adopted. We will have to wait at least until Microsoft includes TLS 1.3 in the .NET framework. To give a little bit of explanation on why we did not support TLS 1.2 sooner, doing so required that we upgrade to .NET 4.5. This version of the framework is not available on Windows XP, so any office of ours that had computers on Windows XP would no longer be able to update Open Dental.

That's reasonable. This practice had to buy a new desktop this week because one of the treatment rooms, to our shame, still had an XP box. Every morning someone started that computer and saw the not-supported-by-M$ warning. I've personally probably seen it on that computer a dozen times. Keeping such a computer in an office with HIPAA responsibilities is probably a violation of some sort. I hope I notice when OD adds 1.3 so I can be more proactive in future.
Jess
 
Posts: 16
Joined: Fri Jul 10, 2015 3:54 pm

Re: TLS versions and DB engines

Postby bpcomp » Fri Mar 30, 2018 2:39 pm

Using a computer with XP on it for patient data is a HIPAA violation. A vulnerability could be discovered and XP does not receive updates to address new vulnerabilities so it puts you in violation territory. This is also going to be true for Windows 7 and Server 2008 as of January 14, 2020 when they reach their end of life and stop getting updates.
bpcomp
 
Posts: 304
Joined: Mon Feb 27, 2012 8:30 am
Location: Tucson, AZ

Re: TLS versions and DB engines

Postby cmcgehee » Sat Mar 31, 2018 11:16 am

Jess wrote:I suggest you try to run an update against an ISAM database. You might see the same error we (me, and your support techs with whom I was sharing the client computer) saw: this DB is MyISAM; fix it to InnoDB if you want this update to run. After I updated it to InnoDB using the code above, the update went through fine.


It sounds to me like you got this error: "The database tables are in MyISAM format, but the default database engine format is InnoDB. You must change the default storage engine within the my.ini (or my.cnf) file on the database server and restart MySQL in order to fix this problem. Exiting."

We present this error because we do not want a mixture of InnoDB and MyISAM tables to arise in the database. If your default storage engine was InnoDB, then any new tables would become InnoDB. In this situation, the easiest and recommended action is to add this line to the server's my.cnf file:
default-storage-engine=MyISAM
Chris McGehee
Open Dental Software
http://www.opendental.com
User avatar
cmcgehee
 
Posts: 465
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: TLS versions and DB engines

Postby Jess » Wed Apr 04, 2018 1:29 pm

cmcgehee wrote:We present this error because we do not want a mixture of InnoDB and MyISAM tables to arise in the database. If your default storage engine was InnoDB, then any new tables would become InnoDB. In this situation, the easiest and recommended action is to add this line to the server's my.cnf file:
default-storage-engine=MyISAM


I didn't screenshot it, so I can neither confirm nor deny that it was this error or some other similarly-worded one. I'm quite certain that it complained about the default being MyISAM.

I had that line in that file, and didn't remove it until after I had seen the error several times. Your techs also confirmed that we were set to MyISAM, and they advised me to fix that. I have never intentionally added any non-OD tables to this database. (Maybe e.g. SQL Workbench could have done something like that, and maybe it could have forced such tables to be Inno?)

It's fine, since we're probably in the best possible situation now: all InnoDB, everywhere.
Jess
 
Posts: 16
Joined: Fri Jul 10, 2015 3:54 pm


Return to Advanced Topics

Who is online

Users browsing this forum: Google [Bot] and 4 guests