Formatting Drupal's UNIX timestamp dates

Drupal stores date/time value as int columns in MySQL. Its value is UNIX timestamp based. You will not be able to determine the actual date/time by selecting from the table.

Here's a convenient way to convert the date/time columns directly from SQL:

SELECT cid, data, FROM_UNIXTIME(created) FROM main_cache

You can also use this in the WHERE clause like below:

SELECT COUNT( * ) 
FROM  main_commerce_product 
WHERE FROM_UNIXTIME( created ) 
BETWEEN  '2013-07-17 00:00:00'
AND  '2013-07-17 23:59:59'

Here's the result:

Using MySQL General Query Log

MySQL comes with the feature to log all SQL queries which is sent to the server.  This feature can be enabled dynamically without having to restart the server.  However, only MySQL 5.1 and above supports this.

To enable this feature, login to MySQL as root.  At the MySQL prompt, type this:

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

Double check if it's really been turned on:

mysql> select @@global.general_log;

+----------------------+

| @@global.general_log |

+----------------------+

|                    1 |

+----------------------+

1 row in set (0.00 sec)

The value should read '1'.  You can now see all SQL queries logged in the general_log table in the mysql database.  

To turn off the logging, set the general_log to OFF using the same syntax as above.

Using mysqldump to export CSV file

By default, mysqldump outputs SQL table dumps.  If you ever need to export a table (or even a database) in CSV format using only mysqldump, here's the quick and easy way without using any additional clients:

mysqldump -u root -p --fields-terminated-by="," --fields-enclosed-by="" --fields-escaped-by="" --no-create-db --no-create-info --tab="." information_schema CHARACTER_SETS

mysqldump will generate 2 files (generated file name is based on the table name):

• CHARACTER_SETS.txt
• CHARACTER_SETS.sql

The actual output is in the .txt file.  Output of the command below (output trimmed for brevity):

big5,big5_chinese_ci,Big5 Traditional Chinese,2dec8,dec8_swedish_ci,DEC West European,1cp850,cp850_general_ci,DOS West European,1hp8,hp8_english_ci,HP West European,1koi8r,koi8r_general_ci,KOI8-R Relcom Russian,1latin1,latin1_swedish_ci,cp1252 West European,1latin2,latin2_general_ci,ISO 8859-2 Central European,1[..]

If you get the following error when running the command, specify a location where the "mysql" user (or the owner of the MySQL process) is running can write to (e.g. /tmp).

mysqldump: Got error: 1: Can't create/write to file '/home/mike/CHARACTER_SETS.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

Now for some description on the options:

• --fields-terminated-by: String to use to terminate fields/columns.
• --fields-enclosed-by: String to use to enclose the field values.  Single quote by default.  I set to nothing as it suits my needs.
• --fields-escape-by: Set of string used to escape special characters e.g. tabs, nulls and backspace.  Look here for more info on escape sequences.
• --no-create-db: Do not print DB creation SQL.
• --no-create-info: Do not print table creation SQL.

For more comprehensive info on the mysqldump command, visit the reference manual page here.

Connecting to remote MySQL via SSH in Ubuntu

I had to access my MySQL server via SSH tunnel on my Ubuntu desktop machine.  First up, setup ssh to tunnel the server's MySQL port (default 3306) to my Ubuntu's desktop machine port 13306:

ssh mylogin@myserver.com -p 4265 -L 13306:127.0.0.1:3306

However, when I tried accessing port 13306 on my Ubuntu desktop, it failed:

$ mysql -u root -p -P 13306
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

It seems the default localhost server used by mysql client does a socket connection instead of TCP/IP.  In order to overcome this, I had to use the --host option:

$ mysql -u root -p -P 13306 --host 127.0.0.1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 115946
Server version: 5.1.49-3 (Debian)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

That took me a while to figure out :)

MySQL: "Cannot insert new word" error

The internal forum (powered by phpBB) I was using was suddenly giving me the error "cannot insert new word". Tailing /var/log/messages and MySQL error logs came up empty. After Googling a bit, it seems that the MySQL tables are corrupt. Running the SQL command below fixes the problem beautifully:

repair table [tablename];