Comments - Locking SQL_MODE against client changes
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Sorry, but don't understand exactly what you need (or why). Can you provide an example of how this could work and why this would be needed?
Hello Monty, of course - let me be a little more descriptive.
As we know MySQL sports the sql_mode variable/setting allowing a DBA to better control data formats, value ranges, and server behavior towards invalid data that is thrown at the server. (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html)
There are many reasons to use sql_mode settings stronger than the default, for one when working in an environment with mixed RDBMS brands (MS SQL, PostgreSQL, Oracle). In such a case you would want MySQL to behave similarly to others, using the TRADITIONAL sql_mode setting for example. One of the reasons being app portability, or at least DBA's ease of life knowing that data will behave mostly the same on all platforms.
Personally I try to employ the TRADITIONAL option all the time as it allows to catch some unwanted developer behavior - zero dates, uncaught exceptions on maxing out auto-increment keys, NULL values in not-null columns, or unexpected values in ENUM columns. All this can be allievated with a proper sql_mode setting. And this is definitely something you want on a server used by tens of inexperienced developers for their apps. You don't want for their apps to keep functioning while generating warnings which you have to sift through to find those that really matter, you want their queries to fail so they go and fix their apps.
The problem is, and this is often pointed out to me when I try to push MySQL choice for a project, that while sql_mode can be set by the DBA on a server-wide basis, the Mysql manual says: "Any client can change its own session sql_mode value at any time."
For people coming from PostgreSQL or SQL Server this is simply unacceptable and, I have to agree with them here, makes the whole sql_mode idea pointless. If it's there to make data more predictable to the DBA, letting users change it at their whim simply destroys that purpose. Sure, I understand some apps may actually be using the functionalities not allowed under the TRADITIONAL mode (zero dates for example) but a DBA doesn't even have a choice on which server to allow or ban said functionalities. Users might just as well set their own session variables if they finds the server-wide setting too restrictive.
As I understand the official server version does not have any additional feature that would forbid clients changing the session-level SQL mode (like a "sql_mode_client_no_change" setting), nor have I heard anything of the sort for Percona Server. I was wondering if MariaDB had anything that would do the trick. For the time being I'm using MySQL proxy to just block any "set sql_mode" requests, but this solutions does not allow the developers to connect to the server (at least not easy enough) via SSL, which the Proxy does not support for the time being.