is SET GLOBAL sql_mode=' ' necessary or will SESSIONS work?

This forum is for programmers who have questions about the source code.
Post Reply
apollonia
Posts: 40
Joined: Sat Nov 08, 2008 7:17 pm
Location: Bakersfield, CA

is SET GLOBAL sql_mode=' ' necessary or will SESSIONS work?

Post by apollonia » Sun Mar 21, 2010 5:46 pm

in MiscData.SetSqlMode is it important to

Code: Select all

SET GLOBAL sql_mode=' ' 
??

would

Code: Select all

SET SESSION sql_mode=' ' 
accomplish the same thing?

i have a number of other applications running on the same MySQL server, and i haven't assigned SUPER privileges to the OD user account, so it throws a privileges error. but i really don't want to let OD stuff dead squirrels into my.cnf (linux).

i'm hoping this is just a random choice, and not crucial. otherwise i'm going to have to recompile every update version, or in the alternative set up a separate OD server.

tell me it ain't so? ;-)
Last edited by apollonia on Mon Mar 22, 2010 1:22 pm, edited 1 time in total.

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

Re: is SET GLOBAL sql_mode=' ' really necessary?

Post by jordansparks » Sun Mar 21, 2010 10:10 pm

Every query we send is with a new connection to the database. In other words, we close the connection after every query. Our mysql connector handles pooling so that connections typically don't close after we use them, but instead get reused. However, we have no control over which connections get reused and which don't. So, to me, this means that the concept of a session is meaningless. A session seems to last for somewhere between one and hundreds of queries. Maybe my understanding of a session is wrong, but global seemed like a much safer approach. Keep in mind that if the sql_mode is wrong because of your custom configuration, that OD will end up appearing buggy including failing during version updates. As an alternative, we could check the sql_mode and only set it if it's wrong. At least I think we can do that.
Jordan Sparks, DMD
http://www.opendental.com

apollonia
Posts: 40
Joined: Sat Nov 08, 2008 7:17 pm
Location: Bakersfield, CA

Re: is SET GLOBAL sql_mode=' ' really necessary?

Post by apollonia » Mon Mar 22, 2010 1:21 pm

that makes sense, jordan. i'm going to try to change the my.cnf to SET GLOBAL sql_mode=' ' and then see if anything breaks on my other applications (i'll do that on a development server first).

Then I'll reset the mode to it's current value, and try OD with a SET SESSIONS sql_mode=' ' and see if OD breaks (it doesn't seem to have any problem running with the current my.cnf settings. again on a dev serv.

if that seems to work OK, i will recompile with the command in MiscData.SetSqlMode set to SESSIONS, try to run an update and then report back to you here.

even if this proves out as a bad idea, at least it may help others follow the breadcrumbs.

i suppose there is a "only one one app on a single mysql server" argument, but that does seems too obvious to be my first choice.

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

Re: is SET GLOBAL sql_mode=' ' necessary or will SESSIONS work?

Post by jordansparks » Mon Mar 22, 2010 3:05 pm

Your sql mode might already be blank. Did you check that? You would have had to explicitly set that server variable to something different in your .cnf file, I think. As for testing, the bugs in OD would be very rare, very hard to catch in any kind of testing. Most of the time, OD would run just fine.
Jordan Sparks, DMD
http://www.opendental.com

Jess
Posts: 17
Joined: Fri Jul 10, 2015 3:54 pm

Re: is SET GLOBAL sql_mode=' ' necessary or will SESSIONS w

Post by Jess » Mon Aug 24, 2015 2:38 pm

OD will only ever use sessions that OD creates, right? Therefore, I don't see the problem with keeping this at session level. If this is the only reason we're granting on '*.*' instead of 'opendental.*', that seems ill-advised.

Post Reply