Append \G at the end of a SELECT query .

select * from countries limit 5\G;

*************************** 1. row ***************************
id: 4
continent_id: 2
region_id: 14
permalink: afghanistan
name_en: Afghanistan
name_de: Afghanistan
lat: 33.9391
lng: 67.71
eu_member: 0
alpha2: NULL
alpha3: NULL
*************************** 2. row ***************************
id: 8
continent_id: 3
region_id: 19
permalink: albania
name_en: Albania
name_de: Albanien
lat: 41.1533
lng: 20.1683
eu_member: 0
alpha2: NULL
alpha3: NULL

mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.0.45-Debian_1ubuntu3.3 Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

use myapplicationl_development;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

SELECT * FROM mailing_jobs where status != 'done';

Empty set (0.00 sec)

mysql> show databases;

mysql> show tables;

# Use back quotes for tables whose names contain spaces (very bad idea if you ask me)
select count(*) from `gmail account`;

Here we go again!

Yesterday, I filled twice my linux partitions! The first time because I had mysql configure to use binlog and the second time because the Rails development log was filled with the details of the SQL query!!!

This time, the size of the data for the 50 000 emails filled the /var/lib/mysql/lecool_development/emails.MYD file which contains the data of the "emails" table.

I used the following command to find the big files:

sudo find / -xdev -size +104857600c

And it found:
-rw-rw---- 1 mysql mysql 3942782004 2008-06-10 19:58 emails.MYD

I deleted with a SQL "DELETE FROM emails" the data and that was it ...

Hum hum, time to upgrade to a new hard drive with bigger partitions and configure mysql to store its data into the home partition ... or buy a mac??? Let's see in 2009 ;-)

I was trying to optimize a script on Rails to send 50 000 emails (TODO Add link) and I came across a problem of space with my root partition:

The script crashed with an error message explaining that the hard drive was full.

Using commands like

du -s * | sort -nr | head

or the slower Ubuntu "Disk Usage Analyser", it turned out that the /var/log/mysq was full of huge logs!!!

/var/log/mysql$ ll
total 1925788
-rw-rw---- 1 mysql adm 98 2007-04-10 10:57 mysql-bin.000558
-rw-rw---- 1 mysql adm 117 2008-06-01 15:01 mysql-bin.001456
-rw-rw---- 1 mysql adm 117 2008-06-01 21:45 mysql-bin.001457
-rw-rw---- 1 mysql adm 141 2008-06-02 09:45 mysql-bin.001458
-rw-rw---- 1 mysql adm 104940360 2008-06-10 16:24 mysql-bin.001468
-rw-rw---- 1 mysql adm 104940040 2008-06-10 16:25 mysql-bin.001469
-rw-rw---- 1 mysql adm 104941440 2008-06-10 16:25 mysql-bin.001470
... etc

As a quick fix, I just deleted manually these files with
sudo rm mysql-bin.00148*

A more permanent solution, found on the web, was to edit:

sudo gedit /etc/mysql/my.cnf

Comment all references to log_bin:

# log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
# expire_logs_days = 10
# max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

sudo /etc/init.d/mysql restart

Obviously, I will have to refactor a bit the code of ar_mailer to take in account the fact that I am sending 50 000 identical newsletters to 50 000 people.
ar_mailer uses a "emails" table with a "mail" column which is the email content. In my case, the "mail" is duplicated 50000 times + it's a 340KB HTML email. No wonder it filed my sql logs!

Epilogue:
I just found out about Evan Weaver top-secret-tuned-mysql-configurations-for-rails post, I really loved the PS ;-)
# PS. Do not under any circumstances enable binlog

Next time, I have to configure mysql, I know what I'll use ...