MySql query log to table or file

For debugging it’s often useful to see all queries and since you usually don’t want to save all this data, it’s nice to be able to turn it on and off efficiently.

You can log to file but also to a table in the mysql database.

While you can turn this on and off by updating the my.cnf file, you can also do it via mysql commands:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

One nice thing about logging to a table is that you can search for specific things, see examples below.

USE mysql;
SELECT * FROM general_log;
SELECT * FROM general_log WHERE argument LIKE "DELETE%";

You probably want to clear the log periodically and can do so with:

TRUNCATE general_log;

Then simply turn OFF when finished.

SET GLOBAL general_log = 'OFF';

If you would rather log to file (the default), use:

SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Swap column values in MySql (via temp variables)

If you want to swap values in one query in MySql without a temporary table, you can use variables to hold values that otherwise would be changed before you can use them.

E.g., swap start_date and end_date via:

UPDATE mydates SET start_date=@tmp:=start_date, start_date=end_date, end_date=@tmp WHERE start_date > end_date

Install MariaDB via Yum, Apt, etc.

To get the latest MariaDB for your distribution (Yum, Apt, etc.), use:

https://downloads.mariadb.org/mariadb/repositories/

It will generate a config entry like:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

which you can install by saving to /etc/yum.repos.d/mariadb.repo and run: yum install MariaDB-server MariaDB-client

Rackspace API: Trove install to enable root user for cloud DB

Rackspace Trove set-up:

Mac OSX:

sudo easy_install pip
sudo pip install python-troveclient

# trove:
# http://docs.rackspace.com/cdb/api/v1.0/cdb-getting-started/content/Install_Trove_Client.html

trove list
trove root-enable

Set sort order on group by in MySql

Problem: When using GROUP BY in MySql you don’t really have control over which item will be selected in the result set (and using ORDER BY in your main query won’t help due to the processing order). You also are limited to a single value.

Answer: Use MIN or MAX in your select statement. For example in a case where you had a bunch of days grouped by some attribute and you wanted to find both the first and last date, you can use something like:

SELECT...
MIN(my_days.date) AS start_date, 
MAX(my_days.date) AS end_date,
...
GROUP BY...

« Previous Entries