A useful overview of options, syntax and tools that have been deprecated or removed for the upcoming MySQL 5.7 release.
This is a little quiz (could be a discussion). I know what we tend to prefer (and why), but we’re interested in hearing additional and other opinions!
Given the way MySQL/MariaDB is architected, what would you prefer to see in a new server, more cores or higher clock speed? (presuming other factors such as CPU caches and memory access speed are identical).
For example, you might have a choice between
- 2x 2.4GHz 6 core, or
- 2x 3.0GHz 4 core
which option would you pick for a (dedicated) MySQL/MariaDB server, and why?
And, do you regard the “total speed” (N cores * GHz) as relevant in the decision process? If so, when and to what degree?
Open Query is supporting the mysql-cli Kickstarter project (for MySQL and MariaDB) by Amjith Ramanujam who already successfully completed a similar tool for PostgreSQL.
It is a new MySQL client with Auto-Completion and Syntax Highlighting. From the info provided, it’s Python based, thus portable, and can be installed without root access. Could be a very useful tool. The good old mysql command line client does lack some things, yet a relatively low-level command line client is often useful for remote tasks (as opposed to graphical tools) so we reckon it’s good that this realm gets a bit of attention!
Maish Saidel-Keesing writes:
VMware announced last week the launch of VIO and there are a number of things that I think people are missing and should be pointed out.
Open Query Engineer Daniel Black and Engineer/Trainer Peter Lock will be presenting sessions at the upcoming Open Source Developers’ Conference which is hosted at Griffith University Gold Coast Campus, 4-7 November 2014.
- Coding for Scale – When One Database Doesn’t Cut it (Daniel)
- Bigger, Faster, Safer, Easier databases with MariaDB 10 (Peter)
- MySQL Scale Wars – A New Hope & Replication Strikes Back (Peter)
I also spotted
which should be very interesting as well. There many be more still, there are lots of sessions!
Full conference tickets cost less than $300 and include the lunches as well as the conference dinner, and all the tutorials/workshops in the main conference. Speaking from experience, OSDC is always great with good talks and excellent people to chat with.
With the conference over, the session videos are now online!
Friend and former colleague Roland Bouwman has written an excellent update on the GROUP BY implementation in MySQL.
MySQL’s implementation of GROUP BY has historically been quirky. Sometimes that quirkiness has been useful, but often it causes grief as SQL authors can make mistakes that are executed but don’t produce the results they want (or expect).
SELECT cat, COUNT(val) as cnt, othercol FROM tbl GROUP BY cat
The ‘cat‘ column is in the GROUP BY clause, the COUNT(val) is an aggregate, but the ‘othercol‘ column is … well… neither. What used to effectively happen is that the server would pick one othercol value from within each group. As I noted before, sometimes useful but often a pest as the server wouldn’t know if you just made a mistake or whether you actually intended to use this ‘feature’.
The long existent sql_mode option ONLY_FULL_GROUP_BY mitigated some of that (but not fully in all cases – see Roland’s explanation).
With the sql_mode option enabled, the server chucks an error for faulty constructs, the most common one being:
Error: 1055 SQLSTATE: 42000 (ER_WRONG_FIELD_WITH_GROUP)
Message: ‘col’ isn’t in GROUP BY
MySQL 5.7.5 and above have ONLY_FULL_GROUP_BY enabled by default, with an updated implementation, so GROUP BY will now generally behave properly.
We know that many existing applications often rely on the previously “standard” but technically quirky/faulty behaviour. So when upgrading to this version or beyond, it’s important to
- use and test in development and staging environment first. This also means upgrading your dev environment first.
- scan the code for GROUP BY use, and ensure that the queries are correct.
- have proper error handling and reporting in your application.
- in some cases disable the sql_mode ONLY_FULL_GROUP_BY option, but that’s really a last resort. It’s best to fix the application.
Daniel was tracking down what appeared to be a networking problem….
- server reported 113 (No route to host)
- However, an strace did not reveal the networking stack ever returning that.
- On the other side, IP packets were actually received.
- When confronted with mysteries like this, I get suspicious – mainly of (fellow) programmers.
- I suggested a grep through the source code, which revealed return -EHOSTUNREACH;
- Mystery solved, which allowed us to find what was actually going on.
- Don’t just believe or presume the supposed origin of an error.
- Programmers often take shortcuts that cause grief later. I fully appreciate how the above code came about, but I still think it was wrong. Mapping a “similar” situation onto an existing error code is convenient. But when an error occurs, the most important thing is for people to be able to track down what the root cause is. Reporting this error outside of its original context (error code reported by network stack) is clearly unhelpful, it actually misdirects and requires people to essentially waste time to track it down (as above).
- Horay once again for Open Source, which makes it so much easier to figure these things out. While possibly briefly embarrassing for the programmer, more eyes allows code to improve better and faster – and, perhaps, also entices towards better coding practices from the outset (I can hope!).
What do you think?