Handling MySQL’s warnings in Go code
I was just bitten by failing to catch a MySQL warning. It’s the old familiar tune: I inserted 100 characters into a VARCHAR(50) and it didn’t throw an error*. Of course, then subsequent SELECT statements didn’t find the value I inserted.
What’s different this time is that I was using Go as the client. There is no single official MySQL driver for Go, although there are several good-quality community-maintained ones. I was using one of those through the official Go database interface, which is a simple and lightweight way to interact with relational databases. This interface will generate errors, but I didn’t think about warnings. This is funny, because usually I’m paranoid about capturing warnings from MySQL and treating them as errors.
After I discovered my mistake, I realized that Go’s database interface doesn’t provide a way to observe the warnings at all, because they are driver-specific. I suppose the underlying driver could promote warnings to errors, but that is probably not the right way to do things, just in terms of following the principle of least surprise. It would immediately break a lot of functioning applications. For new applications like the one I’m developing, it is arguably the right way to go, because I would have been a lot less surprised if I’d caught the error up front.
What are my options? I can modify the driver as just mentioned, or I can change SQL_MODE to be more strict. I think I’m going to do both, because I want the database not to lie to me about inserting my data, AND I know that’s, ahem, less than perfectly implemented. There are other cases where MySQL will proceed and “warn” the client application, and there’s no way to turn that into an error. I do wish there was a “all warnings are errors” setting in MySQL.
The root cause of this problem is me: I was developing the application on my laptop, and running MySQL with default settings because it’s “just a laptop.” This is how applications end up depending on stupid defaults. I recently revisited some code that I wrote for a company in 2006, trying to clean up a reliance on a buggy GROUP BY setting, and in 2012 the company still has the comment in the code: “TODO, change this setting in production and clean up all the SQL that relies on it.” It’ll never happen if it hasn’t happened in 6 years. You’d think I’d have learned not to start a new app’s development with stupid buggy settings, but you’d only be partially right!
* Yes, I know this is fixed in Drizzle, and PostgreSQL doesn’t allow it, and neither does SQL Server, etc etc.
*You might also know that this uses errors by default in MySQL 5.6, which defaults to sql_mode=NO_ENGINE_SUBSTITUTION_STRICT_TRANS_TABLES even on linux for new installations. It already did in 5.5 on Windows if the installer is used and for some of our connectors.
create table a (a varchar(4)) engine=innodb;
insert into a(a) values (“hello”);
ERROR 1406 (22001): Data too long for column ‘a’ at row 1
For backwards compatibility it’s set by the new default my.cnf file, as described at https://blogs.oracle.com/supportingmysql/ .
If you don’t have a backwards compatibility concern and aren’t using our default my.cnf file please set this yourself. While some applications do need warnings instead of errors, we’d prefer it if all possible installations were using sql_mode=NO_ENGINE_SUBSTITUTION_STRICT_TRANS_TABLES or more strict settings.
If the driver author is receptive to suggestions, please consider suggesting that they default to those settings in their connection options, so everyone gets them unless they specifically choose to override them. Most Go code will be new so there’s little reason for a driver to not set the more strict settings.
View are my own, for an official Oracle view please consult a PR person.
James Day, MySQL Senior Principal Support Engineer, Oracle.
James Day
23 Dec 12 at 4:10 pm
I should perhaps add a little: we’d really prefer it if just about every development platform on the planet defaulted to using STRICT_TRANS_TABLES or a more strict setting by default for new code.
We have to be backwards compatible but that doesn’t mean we want to see new code relying on the non-strict behaviour.
James
James Day
23 Dec 12 at 4:13 pm
James, when you do a major version upgrade backwards compatibility does not _have_ to be the default configuration though you probably would need to warn about this. I see 5.6 has a new timestamp option and on startup mysql warns about this but hope that in 5.7 perhaps it becomes the default.
Going back to the original issue posed by Xaprb once you have setup MySQL with the more lenient settings changing this to something stricter is pretty hard and can require a lot of work to see what might break and to fix it. Since “truncation of data” is probably rather infrequent it’s not perceived to happen much and so unless you really can push for this sort of change to happen the work may get delayed … indefinitely.
Certainly a “treat warnings as errors” setting would be good, but the problem is that ideally you’d like to be able to choose which warnings to apply this to as making the blanket change is too intrusive.
Simon J Mudd
24 Dec 12 at 8:06 am
Simon, I know. The problem with STRICT_TRANS_TABLES is that it will cause data loss for applications that need the old setting. You can expect it’ll be the server default in 5.7. I’ll probably file the request for that early in 2013, so it’ll be that way through most of the 5.7 alpha and beta cycle.
My guess is that the timestamp will also be default in 5.7. No direct knowledge of that though.
What we did for the default choices is consider the potential harm that might be done and where we thought they might bite people a bit too hard – say data loss – we went with a more cautious approach in general. Several of the settings ended up changing as a result of different people’s opinions and experiences.
Now would be a good time to think of things that you think should be changed in 5.7 for less backwards compatibility, then file a bug report at bugs.mysql.com with a please tell James about it note. Or you can use other channels, but I prefer bugs.mysql.com for this so others can more easily see the reason.
I agree with you about the desirability of control for individual errors. That’d be another good one for a bug with feature request.
Maybe you might like to file those then blog to invite others to comment on the requests and see what people come up with?
James
James Day
25 Dec 12 at 10:26 am