Open Dental Forum

I am setting up a new database server virtual machine and wondering if MySQL 5.7 is supported? Ubuntu 16.04 LTS and all newer versions default with 5.7...I am currently still running 14.04 LTS with MySQL 5.5. When I ran a test I got this error and wondered if it is setting listed here as "sql_mode=only_full_group_by"or a problem with 5.7 before I go trying to fix it.

Unhandled exception from ProgramEntry: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'opendental.plannedappt.ItemOrder' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
MySql.Data.MySqlClient.MySqlException
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at OpenDentBusiness.DataConnection.<>c__DisplayClass80_0.<GetTable>b__1() in C:\SVN\beta\OpenDentBusiness\Db\DataConnection.cs:line 601
at OpenDentBusiness.DataConnection.RunDbAction(Action actionDb, DbConnection connection) in C:\SVN\beta\OpenDentBusiness\Db\DataConnection.cs:line 940
at OpenDentBusiness.DataConnection.GetTable(String command, Boolean hasConnLost) in C:\SVN\beta\OpenDentBusiness\Db\DataConnection.cs:line 601
at OpenDentBusiness.ChartModules.GetPlannedApt(Int64 patNum) in C:\SVN\beta\OpenDentBusiness\Db Multi Table\ChartModules.cs:line 1298
at OpenDentBusiness.ChartModules.GetAll(Int64 patNum, Boolean isAuditMode, ChartModuleComponentsToLoad componentsToLoad) in C:\SVN\beta\OpenDentBusiness\Db Multi Table\ChartModules.cs:line 26
at OpenDental.ContrChart.FillProgNotes(Boolean retainSelection) in C:\SVN\beta\OpenDental\Main Modules\ContrChart.cs:line 6319
at OpenDental.ContrChart.RefreshModuleScreen(Boolean isClinicRefresh) in C:\SVN\beta\OpenDental\Main Modules\ContrChart.cs:line 3880
at OpenDental.ContrChart.ModuleSelected(Int64 patNum, Boolean isFullRefresh, Boolean isClinicRefresh) in C:\SVN\beta\OpenDental\Main Modules\ContrChart.cs:line 3578
at OpenDental.ContrChart.ModuleSelectedNewCrop(Int64 patNum) in C:\SVN\beta\OpenDental\Main Modules\ContrChart.cs:line 3551
at OpenDental.FormOpenDental.SetModuleSelected(Boolean menuBarClicked) in C:\SVN\beta\OpenDental\Main Modules\FormOpenDental.cs:line 5786
at OpenDental.FormOpenDental.myOutlookBar_ButtonClicked(Object sender, ButtonClicked_EventArgs e) in C:\SVN\beta\OpenDental\Main Modules\FormOpenDental.cs:line 5718
at OpenDental.OutlookBar.OnButtonClicked(OutlookButton myButton, Boolean myCancel) in C:\SVN\beta\OpenDental\User Controls\OutlookBar.cs:line 321
at OpenDental.OutlookBar.OnMouseUp(MouseEventArgs e) in C:\SVN\beta\OpenDental\User Controls\OutlookBar.cs:line 313
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

ok, I see here:
http://www.opendental.com/manual/mysql.html
"If you would like to update to MySQL version 5.7 or Maria, you may do so, but we do not support it. We do have customers using them though."

so, it should work? I guess I will have to mess with the settings...

also, as I am looking into this again the InnoDB vs MyISAM debate sticks out at me: Do you guys have any feedback from people using InnoDB, good, bad, or ugly? I know you don't officially like InnoDB because it is harder to fix if a problem occurs, right? Is there any performance advantages to InnoDB for OD, or not so much? I know most comparison are probably web based applications, not local programs, so hard to know how that compares....

drtech wrote:I guess I will have to mess with the settings...

Right, the particular setting you care about for the error above is the "sql_mode". We haven't had time to give an official stance on what it should be changed to because we are still researching what all of these issues even mean. I don't know of any users actively using 5.7, only ones that want to use it. Regardless, we're looking into what it would take to fully support MySQL 5.7.

drtech wrote:also, as I am looking into this again the InnoDB vs MyISAM debate sticks out at me: Do you guys have any feedback from people using InnoDB, good, bad, or ugly? I know you don't officially like InnoDB because it is harder to fix if a problem occurs, right? Is there any performance advantages to InnoDB for OD, or not so much? I know most comparison are probably web based applications, not local programs, so hard to know how that compares....

InnoDB has better performance especially when there are lots of connections. There are many other things that it does better (hence it is the default when installing MySQL standalone) but MyISAM is extremely easy to make backups of and for us to support like you mentioned. You are more than welcome to use InnoDB and we even have a tool to switch between the two. Our only requirement is that you don't have a mixture of the two table types. Make sure to change the default in your my.ini or my.cnf if you decide to switch over. And if we ever need a copy of your db for debugging you'll have to provide us with a dump or something similar until we can make a tool for such a thing.

OD is markedly slower since I upgraded to 17.3. Hence my search for upgrading the DB engine, a new VM setup, etc. I am finding a lot of tempdash"X" tables now, what are those? Any thoughts on why OD is slower? If I am testing, there is a long pause now from the choose database window to the login screen, like > 10 seconds. This is "new" since a month or so ago when I upgraded to 17.3

Found this little gem when reading about sql modes:

SQL Mode Changes in MySQL 5.7

In MySQL 5.7.5, the ONLY_FULL_GROUP_BY SQL mode is enabled by default because GROUP BY processing has become more sophisticated to include detection of functional dependencies. However, if you find that having ONLY_FULL_GROUP_BY enabled causes queries for existing applications to be rejected, either of these actions should restore operation:

If it is possible to modify an offending query, do so, either so that nonaggregated columns are functionally dependent on GROUP BY columns, or by referring to nonaggregated columns using ANY_VALUE().

If it is not possible to modify an offending query (for example, if it is generated by a third-party application), set the sql_mode system variable at server startup to not enable ONLY_FULL_GROUP_BY.

https://dev.mysql.com/doc/refman/5.7/en ... mode-combo

We have code built into Open Dental to automatically clear out the sql_mode which probably fixed the aforementioned error you had after you restarted Open Dental (if you tried). We deemed it safe to do that in MySQL 5.5 and I'm not positive it is safe to do that to MySQL 5.7. Having the sql_mode set to empty string seems to have the desired results that it did back in 5.5 so here is to hoping it is safe to do.

I stumbled across a very good description of MyISAM and InnoDB:

MyISAM: The default non-transactional storage engine that provides full-text indexing and is highly portable
InnoDB: A transactional-safe storage engine designed for maximum performance when processing large volume of data and that provides row-level locking

https://docs.oracle.com/cd/E12151_01/do ... m#i1027526

drtech wrote:also, as I am looking into this again the InnoDB vs MyISAM debate sticks out at me: Do you guys have any feedback from people using InnoDB, good, bad, or ugly? I know you don't officially like InnoDB because it is harder to fix if a problem occurs, right? Is there any performance advantages to InnoDB for OD, or not so much? I know most comparison are probably web based applications, not local programs, so hard to know how that compares....

Have you tried 5.5 with windows, the default setup? Curious if that makes any difference. I put some offices on 17.3, nobody said anything about a slow down.... knock on wood.

drtech wrote:OD is markedly slower since I upgraded to 17.3. Hence my search for upgrading the DB engine, a new VM setup, etc. I am finding a lot of tempdash"X" tables now, what are those? Any thoughts on why OD is slower? If I am testing, there is a long pause now from the choose database window to the login screen, like > 10 seconds. This is "new" since a month or so ago when I upgraded to 17.3

I setup a virtual machine today with a trial database, Ubuntu 17 with MySQL 5.7 and samba... so far so good.. no long pause at the login screen... Bridged Networking with the guest machine. 17.4.. compiled from source.

When I first moved the database over.. Open Dental wanted to optimize the tables and if I said yes it complained about commlog date and time or something.. so I said no to optimizing... and it didn't ask again. I ran the database fix tool for fun, and it griped the first time... looks like a crash in mysql client library.. second time I ran it, no errors.. Odd.


MySQL configuration file: https://pastebin.com/F8WW305U
Samba's config file: https://pastebin.com/Cax93MrD

I wonder if the switch to SharpDX in 17.3 did anything.. Try setting the simple tooth chart??

I am messing with the wine emulator in linux and am having an issue... seeing if I can get past it.. just curious if that helps you. Maybe make sure you have installed the Windows 7 SP1 Platform Update if you have Windows 7 clients??
https://www.microsoft.com/en-us/downloa ... x?id=36805