The latest from the MySQL community

First, I have to tell you, that the “fragmentation” is not the best word what I should use, but that was the closest to that what I wanted to say. The base of the fragmentation checker script is Peter Zaitchev’s 2008 article about Information_schema, I used that query to get that results what I needed.

Some background: a few weeks ago we got some free space related problems (well, they weren’t a real problem, but they could lead onto one, and we had to act fast.) The innodb is working on a bit strange way, it is not like to free up space what is deleted before, and because of this, the datafiles will grow to the end of the world. The first thing what we could do to reclaim free space is to use

innodb_file_per_table

configuration option, what will split up the database to a lot of …

Don't you hate disclaimers? I do, but before I do anything else, I must ask that you don't use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL's SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner join …

Don’t you hate disclaimers? I do, but before I do anything else, I must ask that you don’t use the techniques below unless you are emailing responsibly.

Today I needed to pull email addresses for people who had signed up to a thing out of MySQL and into MailChimp so that I could actually email them about the thing. MySQL actually has a very cute feature for exporting the results of an SQL query as a CSV file, which I had to look up to remember how to do it. It goes something like this:

My original query*

select u.username, u.email, a.account_name
from users u
inner join accounts a on a.user_id = u.id
where a.status_id = 2;

To write this to a file, we use MySQL’s SELECT ... INTO OUTFILE feature, so my query suddenly looks like this:

select u.username, u.email, a.account_name
into outfile '/tmp/users-extract.csv'
from users u
inner …

There has been some interesting discussion on-line recently about how to handle database (meaning MySQL, but really it applies to other systems too) failover. The discussion that I’ve followed so far, in order, is:

• GitHub’s report on their automated failover and downtime issues
• Baron’s follow-up to that, Is automated failover the root of all evil? which generated a bit of discussion in the comments
• Peter’s follow-up to both, The Math of Automated Failover which heads in the direction I was going when I realized I might want to toss my 2 cents into the mix
…

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn't matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you've seen it, it's really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I'd capture it here in case it's helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here's the staff table from the database (today's imaginary data isn't particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | Harry      | …

Getting two sets of information from one table in a select statement often leads people to write subselects, but it really doesn’t matter that this is the same table twice, we can just give it a new alias and treat it as if it were a different table. This is one of those techniques where, once you’ve seen it, it’s really obvious, but until that point it can be very confusing. I explained this to someone else recently, so I thought I’d capture it here in case it’s helpful to anyone else.

Consider that tried-and-tested example: employees and managers. Here’s the staff table from the database (today’s imaginary data isn’t particularly imaginative, sorry):

mysql> select * from staff;
+----+------------+-----------+------------+
| id | first_name | last_name | manager_id |
+----+------------+-----------+------------+
|  1 | Hattie     | Hopkins   |          4 |
|  2 | Henry      | Hopkins   |          4 |
|  3 | …

The Oracle Linux and Virtualization Documentation Team is seeking an experienced Technical Writer
with a focus on writing documentation for the Oracle Linux product. (The MySQL Documentation Team is part of that group as well.)

Applicants should be located in either Ireland, the UK, Sweden, Norway, …

The Oracle Linux and Virtualization Documentation Team is seeking an experienced Technical Writer
with a focus on writing documentation for the Oracle Linux product. (The MySQL Documentation Team is part of that group as well.)

Applicants should be located in either Ireland, the UK, Sweden, Norway, …

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 |
|       2 | cat     | Fluffy |         2 |
|       3 | cat     | Smudge |         3 |
|       4 | cat     | Toffee |         3 |
|       5 | dog     | Pig    |         3 |
|       6 | hamster | Henry  |         1 |
|       7 | dog     | Honey  |         1 | …

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 |
|       2 | cat     | Fluffy |         2 |
|       3 | cat     | Smudge |         3 |
|       4 | cat     | Toffee |         3 |
|       5 | dog     | Pig    |         3 |
|       6 | hamster | Henry  |         1 |
|       7 | dog     | Honey  |         1 | …