sql_mode must be one of the most elusive issues in setting up a MySQL database. It is also responsible for difficulties in migration, upgrading and securing of databases.
MySQL usually has little issues with migrating database from one machine to another, one version to another, one OS to another, one architecture to another. If we’re not sure, we can always migrate using mysqldump’s logical dump, right?
Not entirely right. The elusive sql_mode (empty by default) may affect out backup+restore, may affect our application behavior, may affect our data integrity.
- Did we remember to set NO_AUTO_CREATE_USER? Oh dear, we have some users without passwords.
- Did we remember to set NO_AUTO_VALUE_ON_ZERO? Oh dear, we’ve dumped our database for backup, restored, but AUTO_INCREMENT values have changed!
- Did we set STRICT_ALL_TABLES? How do we know if the 255 value in our TINYINT really stands for 255 or if it was a truncated 299?
- Do we allow selecting non aggregated columns in GROUP BY? Did we set ONLY_FULL_GROUP_BY? Will our application crash now?
- Our old database has zero for empty date values (columns are NOT NULL). But what settings do we have for NO_ZERO_IN_DATE on our new installation? Will import fail?
- And how did the NULL get in? Was it because we divided by zero, and forgot to set ERROR_FOR_DIVISION_BY_ZERO? How can we tell?
The fact is: two mysql instances, same version, same OS and architecture, with different sql modes – can be incompatible!
As said, the sql_mode is empty by default. This is very non-strict. But more than that: it can be changed even while the database is running; even on a per connection basis.
Setting sql_mode should be one of the first things to do after installation. The usual manuals talk about setting the innodb_buffer_pool_size and the query_cache_size, when sql_mode will dictate the nature of your database and application on an entirely grander scale.
I think it would be best if MySQL chooses a desired set of sql modes (like ‘TRADITIONAL’), then make it the default. I further believe it would be best if MySQL would not allow changes to sql_mode. Not globally and certainly not per session. Choosing the stricter mode is better, I believe: errors such as overflow values should be reported to the application, not just swiped under the carpet.
Backward compatibility? Indeed a problem (inherent to the very existence of sql_mode). Perhaps allow one setting per installation. Perhaps just go for it.
sql_mode was one of the first things tossed in Drizzle. It was a horrible idea to begin with and it just kept making everything worse.
Well I think the SQL_MODE was mainly invented as a BC measure.
And yes being able to set it in the session is problematic. I filed a bug on this long ago. For example its possible to change the setting for invalid dates in a session. Ugh!
Lukas, Exactly so. I was just sitting down to write about exactly that: the fact that any user can use sql_mode and break data integrity (to be honest, sql_mode is only one of several such vulnerabilities). I’ll post sometime next week. I was wondering about the origins of sql_mode. Seemed to me more like a bait with which to lure DBAs from other RDBMS. I think this reasoning explains the ORACLE, DB2, POSTGRESQL sql_modes. Brian, good to know. I have a feeling that in retrospection the MySQL developers would not have invented it themselves. I guess this is just one… Read more »
I disagree – being able to override the server’s sql_mode at the session level is a good thing. This allows backward compatibility without holding back other users and schemas to benefit from a more strict mode.
The only bad thing is that setting the sql_mode on the session level is not privileged. It should have been tied to the privilege system so that it would be possible to disallow changing the mode for particular end users.
Hi Roland, “without holding back other users and schemas to benefit from a more strict mode” The other users cannot benefit from a strict mode if any user can choose to ignore the strict mode. If it were only allowed for a user to be more strict than the global sql_mode, it would be fine. I think the global sql_mode should be as strict as possible, so no need for that feature as well. Apart from some minor modes, such as “||” for string concatenation, I don’t see the benefit of allowing users to change settings. If some 3rd based… Read more »
[…] Noach asks, Do we need sql_mode?, beginning, “sql_mode must be one of the most [elusive] issues in setting up a MySQL […]
“The other users cannot benefit from a strict mode if any user can choose to ignore the strict mode.”
sure they can, provided they can’t acces each others schemas. I still don’t see any issue with mixing modes provided different schemas are used in isolation, which is effectively achieved by issuing proper object/schema privileges
Another point is that the sqlmode also contains a number of things that have nothing todo with “strictness”, such as the PIPES_AS_CONCAT you mentioned (other examples are ignore space, double quotes for identifiers and a bunch more).
regards,
Roland
Hi Roland,
I see your point. If I understand your intention, though, you’re referring to a completely new set of privileges, integrated into the privileges system.
That in itself is just fine. In my opinion, though, sql_mode must still not exist.
So for being able to set zero for dates you would
GRANT ZERO_ON_DATE ON world.* TO ‘someuser’@’somehost’
But that user should *not* be able to
SET sql_mode=’ZERO_ON_DATE’
How does that sound?
With your permission, I would like to quote you on a post I’m writing on the subject.
Regards,
Shlomi
Roland: if you need to support legacy apps, why not offer them a separate server instance? I think this is the cleaner approach.
Hi All! @Schlomi: sure, feel free to quote me. (I guess it would be best to include a link to this comment thread so ppl can verify real quick who said what, but this is just a suggestion) Actually, what I meant was container privileges like: GRANT SET [SESSION|GLOBAL] SQL_MODE TO user[@host] But your suggestion gave me the idea that it would in addition be nice to do: CREATE USER user[@host] IDENTIFIED BY ‘pasword’ DEFAULT SQL_MODE = ‘….’; (and this would assign the default sql_mode on connect) As for sql_mode not having to exist at all – well, that would… Read more »
[…] thought this deserves more than a comment on my previous post on the subject, in which I expressed the opinion that sql_mode is […]
Roland .. that is a very developer centric view (rather than DBA) .. however what happens when you have two apps messing with the same data? I think for the good of MySQL users, people should be pushed towards the strict sql compliance mode and only if they beg their DBA’s they can get an instance with the legacy BC crap.
Hi Lukas! I am not sure what you mean by a developer perspective. My point is that it depends on the application whether things like zero dates and zero in dates are permissable. I agree the case where multiple database users access the same tables is a problem. I am sure you are aware that for stored procedures, views and events the sql_mode that was in effect at DDL time is stored together with the definitions of these object. At runtime, that sql_mode is applied in order to allow the code to run predictably. Well, this always made me wonder….why… Read more »
Developer centric means you are looking after the worries of someone that writes the application. DBA centric is about who manages the data and who gets a call when the database goes up in smoke.
More discussion is available on Roland Bouman‘s post.
[…] my earlier posts here and here. Roland Bouman also offers suggestions for fixing this […]
[…] I’ve discussed this in length before. My opinion […]