Simple Settings for a Saner MySQL – Part I

One day, after taking a job using MySQL, I was writing a query quietly at my desk. It was around quittin’ time, and I was whipping up a new report on the monthly sales figures that the CEO needed for his board meeting in the morning. I, in my ignorance, wrote a nice query with a simple little sum() function. What I did not realize is this caused all my rows to sum up to one row when it really should’ve show several rows. This made my report completely inaccurate. Blindly trusting me as I had blindly trusted MySQL, the CEO presented my report to the board who decided that the company was grossly unprofitable and voted to shut it down. Only when I was waiting in the bread line at my local workhouse did I realize my mistake.

Bread line
That’s me in the bowler.

Okay, so that’s totally made up. Mostly. I caught the error before I handed the report off, but it did take me a while to figure out what was going on. When you use the sum() function or any aggregate function for one column but not all of them, MySQL helpfully and implicitly selects a random value for those columns not in an aggregate function. It does the same thing when you put a column in your select clause that’s not in your group by clause:

Select
	 FirstName
	,LastName # This will just get a random value
	,Sum(OrderTotal) As OrderTotal
From Orders O
Join Customers C On C.CustomerID = O.CustomerID
Group By
	FirstName

This means that all the people named Robert will be grouped together, and Robert will have a random last name. Not helpful, MySQL: we need you to be like a good spouse: quick to point out our failures.

MySQL does have a solution to this:

Set sql_mode = 'Only_Full_Group_By';

This makes MySQL throw an error when you don’t write your aggregate queries correctly. Neat! Using the Set command above changes how MySQL works at the session level, meaning it’s temporary and only works on your connection. To set it at the server level, add this line to your my.cnf file and restart the server:

sql_mode = 'Only_Full_Group_By'

Make sure to check there’s not already a sql_mode in the file. If there is, you can specify two modes by separating them with a comma.

You need to test before you whip this on in production. When I changed this for my company’s app, I immediately found out this was not a behind the scenes fix. Tests failed since some queries threw an error, even if the query was correct. Any change to sql_mode changes the basic behavior of your database. Be kind, and tell the other people using or developing on the system about what you’ve done.

This is just one simple setting for a saner MySQL. The next topic we’ll cover is character sets in MySQL, or how to not lose your mind while dealing with character sets in MySQL.

Want to hear more about what I’ve learned about MySQL? Well, I’m writing a book about it! You can subscribe below and I’ll send you updates. I’ll also send out stuff on MySQL like tips and tutorials.



 No spam

Tagged with:
Posted in Databases, IT

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>