MySQL version 5.7

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

MySQL version 5.7

Postby drtech » Thu Nov 02, 2017 11:48 am

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)
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com
User avatar
drtech
 
Posts: 1534
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO

Re: MySQL version 5.7

Postby drtech » Thu Nov 02, 2017 11:49 am

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...
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com
User avatar
drtech
 
Posts: 1534
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO

Re: MySQL version 5.7

Postby drtech » Thu Nov 02, 2017 12:05 pm

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....
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com
User avatar
drtech
 
Posts: 1534
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO

Re: MySQL version 5.7

Postby jsalmon » Thu Nov 02, 2017 12:50 pm

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.
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: 1290
Joined: Tue Nov 30, 2010 1:33 pm

Re: MySQL version 5.7

Postby jsalmon » Thu Nov 02, 2017 12:55 pm

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.
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: 1290
Joined: Tue Nov 30, 2010 1:33 pm

Re: MySQL version 5.7

Postby drtech » Thu Nov 02, 2017 2:29 pm

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
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com
User avatar
drtech
 
Posts: 1534
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO

Re: MySQL version 5.7

Postby jsalmon » Thu Nov 02, 2017 2:59 pm

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/sql-mode.html#sql-mode-combo
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: 1290
Joined: Tue Nov 30, 2010 1:33 pm

Re: MySQL version 5.7

Postby jsalmon » Mon Nov 06, 2017 10:45 am

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.
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: 1290
Joined: Tue Nov 30, 2010 1:33 pm

Re: MySQL version 5.7

Postby jsalmon » Mon Nov 06, 2017 11:02 am

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/doc.150/e12155/oracle_mysql_compared.htm#i1027526
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: 1290
Joined: Tue Nov 30, 2010 1:33 pm


Return to Advanced Topics

Who is online

Users browsing this forum: No registered users and 1 guest