Heroku pg:psql to CSV export (locally)

Heroku has a nice UI for that called Dataclips but IMHO command line still beats it. I needed to run an SQL query and export the results as CSV on a local machine and here is a quick little snippet to do just that:


heroku pg:psql -c "\COPY (SELECT id, name, email FROM users WHERE created_at BETWEEN '2016-01-01' AND '2016-12-31' ORDER BY created_at ASC) TO STDOUT CSV DELIMITER ',' HEADER" > users-2016.csv

WordPress MU Upgrade and Permalinks 404 Erros

WordPress MU upgrade(from 2.x to 3.2.1) was a rather simple process surprisingly! Having completed it in a matter of couple hours for a fairly large blogging network, I was a happy camper up till the moment when permalinks started giving 404s.

What followed is painstaking process where I verified everysingle aspect of configuration from Apache’s mod_rewrite setup to htaccess rules to WordPress’s network site configs. Everything looked correctly.

Googling around for a good hour I came across this site, which pointed to the incompatibility of some plugins with WP3. In my case problem lied in a plugin called Top Level Categories, which I had to disable to get the permalinks working.

Gnome Do, Docky, upgrade to Ubuntu 10.10 and an odd case of /var/lib/dpkg/tmp.ci/md5sums

So I finally upgraded to 10.10, a bit behind the curve but whatever. For the most part it went surprisingly swimmingly. I only had 2 issues :)

First one – gnome-do docky theme was gone. And apprently it’s gone for good and now it is a separate project. Fix is as simple as:


sudo apt-get install docky

Fixed that right away.

Second issue was a bit more annoying. Problem was with updating linux-firmware package. For some reason dpkg was throwing and error saying that ‘/var/lib/dpkg/tmp.ci/md5sums: Is a directory‘. After messing around fix for that was basically this:

sudo bash
cd /var/lib/dpkg/info
rm -rf linux-firwmare*
apt-get upgrade

What happen is /var/lib/dpkg/info/linux-firmware.md5sums got corrupted and instead of being a file it became a directory point to postfix’s folder(in my case). I had some hard drive issues few weeks ago, so probably fsck did this… oh well, can’t blame it :)

How to move MySQL storage to RamFS or TmpFS partition

Whether moving all MySQL storage to a tmpfs helps with speeding it up or not is questionable but I needed to do for some testing purposes, so this is a short overview of how I did that hopefully will be useful:

First mount tmpfs to a folder:

sudo mkdir /var/ramfs
sudo mount -t ramfs -o size=1G ramfs /var/ramfs/

Here I mounted ramfs to /var/ramfs. I am using ramfs in oppose to tmpfs mainly because:

  • ramfs grows dynamically(tmpfs doens’t)
  • ramfs doesn’t use swap(while tmpfs does)

RAM-backed file system is mounted, so now I need to populate it with MySQL files for processing.
To do that I will need to stop mysql, copy it’s database files over to ramfs, adjust AppArmor and MySQL settings and start mysql server again. Here is the chain of commands to do that:

Copying files:

sudo /etc/init.d/mysql stop
sudo cp -R /var/lib/mysql /var/ramfs/
sudo chown -R mysql:mysql /var/ramfs/mysql

Tweaking MySQL config:

sudo cp /etc/mysql/my.cnf /etc/mysql/original-my.cnf
sudo vim /etc/mysql/my.cnf

Find line with ‘datadir‘ definition(it will look something like datadir = /var/lib/mysql) and change it to

datadir = /var/ramfs/mysql

Next step is to tune apparmor settings:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the following few lines just before the closing curly braces:


/var/ramfs/mysql/ r,
/var/ramfs/mysql/*.pid rw,
/var/ramfs/mysql/** rwk,

Looks like we’re done with settings, let’s see if it will work:


sudo /etc/init.d/apparmor restart
sudo /etc/init.d/mysql start

If mysql daemon starts(double check /var/log/mysql.err for any errors) and you can connect to it, mostlikely now we’re running fully off of a RAM device. To double check it, run this from mysql client:

mysql> show variables where Variable_name = 'datadir' \G
*************************** 1. row ***************************
Variable_name: datadir
Value: /var/ramfs/mysql/
1 row in set (0.00 sec)

That’s pretty much it :)

TypePad to WordPress MU: Moving content with Technorati links, images, video and all that good stuff.

Getting content out of TypePad ain’t fun at fall. Getting content with images, technorati tags, videos is even more painful.

With default TypePad ‘export’ feature you’ll just get partial content(some limited number of posts AFAIR), no technorati tags, no images. Youtube videos are going to be there since that stuff is just an html that embed remote flash video player. So to make my life a bit easier I came up with few simple tricks/scripts of how to get everything out of TypePad and process it to import into WordPress with no pain.

Here is how it goes:

Continue reading

Moving TypePad blogs to WordPress MU: TypeLists

TypePad suck, no two ways about it. Why, in your right mind, would you want to use something so limited in functionality on the platform that you have no control over, when there are tons of open source solutions that are way better…

Strangely enough, TypePad being the way it is, made a lot of people wanting to switch to things like WordPress(well at least from what I hear :) . Moving a blog onto WordPress with 0-broken links, no missing images, while keeping all embedded things, such as YouTube videos and Technorati Tags fully functional with 0-downtime ain’t a simple task. Small blogs may not really care about everything I’ve mentioned, but some serious blogs out there however need all that done, done fast and done right the first time.

Continue reading

Dump MySQL table or SELECT resultset into CSV or TSV file

Full table select:


SELECT * INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `table`;

Variations(for instance select from derived table):

SELECT tbl.* INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM (
SELECT t1.category_name, t2.full_name
FROM table1 t1
LEFT JOIN table2 t2 ON t1.category_id = t2.category_id
) AS tbl;

To add a heading row into the exported by MySQL CSV file you can use UNION statement:

SELECT 'Column Name 1', 'Column Name 2'
UNION
(
SELECT tbl.column1, tbl.column2 INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table1 tbl
);

Note: /tmp/file.csv must writable by mysql process