mysql snippets

Capistrano 2 task for backing up your MySQL production database before each deployment

Tagged ruby, rails, mysql, backup, capistrano  Languages ruby

This Capistrano task connects to your production database and dumps the contents to a file. The file is compressed and put in a directory specified with set :backup_dir, "#{deploy_to}/backups". This is a slight modification of All credit to court3nay.

task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{backup_dir}/#{application}.dump.#{}.sql.bz2"
  text = capture "cat #{deploy_to}/current/config/database.yml"
  yaml = YAML::load(text)

  on_rollback { run "rm #{filename}" }
  run "mysqldump -u #{yaml['production']['username']} -p #{yaml['production']['database']} | bzip2 -c > #{filename}" do |ch, stream, out|
    ch.send_data "#{yaml['production']['password']}\n" if out =~ /^Enter password:/

To automatically backup your data before you deploy a new version add this to config/deploy.rb:

task :before_deploy do

To restore the backup run the following command:

mysql database_name -uroot < filename.sql

How to reset the MySQL root password

Tagged mysql, password, reset  Languages bash

I just happened to lock myself out of MySQL, but luckily I have root access to the server so I can reset it easily by first shutting down MySQL:

/etc/init.d/mysql stop

And then creating a MySQL init file with the desired password:

$ echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234');" >> /tmp/mysql_init.txt

Starting MySQL with the --init-file parameter like this resets the password:

$ mysqld_safe --init-file=/tmp/mysql_init.txt

Remember to delete the file:

rm /tmp/mysql_init.txt

MySQL rownum equivalent

Tagged mysql, rownum, user, variable  Languages sql

MySQL doesn't have rownum, but user variables can be used as a replacement:

select @rownum:=@rownum+1 'rank', p.* from products p, (SELECT @rownum:=0) r order by created_at desc limit 10;

Read more: MySQL 5.0 Reference Manual

How to install and use the mysql-python library

Tagged python, mysql, mysql-python, install  Languages python

First download mysql-python from

Extract it and run:

python build
sudo python install

If you get this error you need to install python-dev package:

In file included from _mysql.c:29:
pymemcompat.h:10:20: error: Python.h: No such file or directory
_mysql.c:30:26: error: structmember.h: No such file or directory
In file included from /usr/include/mysql/mysql.h:44,
                 from _mysql.c:40:
_mysql.c:2808: warning: return type defaults to 'int'
_mysql.c: In function 'DL_EXPORT':
_mysql.c:2808: error: expected declaration specifiers before 'init_mysql'
_mysql.c:2886: error: expected '{' at end of input
error: command 'gcc' failed with exit status 1

Installing the python-dev package on Debian is done with apt-get or synaptic:

apt-get install python-dev

Installing the library should now work:

python build
python install

Next test the library in the python console:

import MySQLdb

# Note that this example uses UTF-8 encoding
conn = MySQLdb.connect(host='localhost', user='...', passwd='...', db='...', charset = "utf8", use_unicode = True)
cursor = conn.cursor()

cursor.execute ("SELECT * FROM cities")
rows = cursor.fetchall ()

for row in rows:
  print "%s, %s" % (row[0], row[1].encode('utf-8'))

print "Number of rows returned: %d" % cursor.rowcount

Don't forget to close the cursor and connection, and if you're inserting data commit before closing, because autocommit is disabled by default:

cursor.close ()
conn.commit ()
conn.close ()

For more information about MySQLdb see this article.

How to create and use MySQL stored procedures

Tagged mysql, stored, procedure, cursor  Languages sql

This is a simple example of a MySQL stored procedure that has both an in and out parameter:

CREATE PROCEDURE category_for (product_id int, OUT r_category_id INT)
  DECLARE category_id INT;
  DECLARE products_cursor CURSOR FOR SELECT category_id FROM products where id = product_id;
  OPEN products_cursor;
   FETCH products_cursor INTO category_id;
  CLOSE products_cursor;
  SET r_category_id     = category_id;
 END |

Note that we use ; inside the procedure so we have to set the delimiter to | temporarily.

To call the stored procedure use the following SQL commands:

CALL category_for(202, @category_id);

The @category_id session variable will now contain the procedure's output:

select @category_id;
| @category_id |
| 3     | 

How to select adjacent rows (next and previous rows) with MySQL

Tagged sql, mysql, next, previous, has_adjacent_finders  Languages ruby

I've now packaged this into a Rails plugin called has_adjacent_finders


Finding the next and previous product is a common task on, for example, e-commerce sites.

Let's say we have a table containing data having the following IDs:


How do we get the rows adjacent to row 205? We can rely on MySQL sorting—the primary key in this case— so these two queries will do the job for us:

# Find previous row
select id from products where id < 205 order by id desc limit 1

# Find next row
select id from products where id > 205 order by id asc limit 1

The two queries will return 204 and 206 respectively. You can also use other columns, not just ID...

How to fix "Mysql::Error: Duplicate entry '2147483647' for key 3: INSERT INTO xxx"

Tagged mysql, numeric, error, gotcha, migration  Languages ruby

2147483647 is the maximum for an integer column in MySQL, so this error probably means you've exceeded this limit somewhere in your code.

Rails automatically detects the best type for your columns, so be sure to specify the correct limit when creating the column with migrations:

# from activerecord-2.1.1/lib/active_record/connection_adapters/mysql_adapter.rb
        case limit
        when 1; 'tinyint'
        when 2; 'smallint'
        when 3; 'mediumint'
        when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
        when 5..8; 'bigint'
        else raise(ActiveRecordError, "No integer type has byte size #{limit}")

This Rails migration code would create a big integer column:

t.integer :product_id, :null => false, :limit => 8

See the section on Numeric Types in the MySQL documentation for more information.

Fix for "mysql error 1005 (errno: 150) "

Tagged mysql, sql, fix, problem, 150  Languages sql

There are at least three reasons, that I know of, to why you could be seeing mysql error 1005 (errno: 150)

  • Adding a foreign key constraint when column types don't match
  • Not enough privileges to execute the script
  • Trying to delete an index that is needed by some other index or constraint ("error on rename of")

Use "SHOW ENGINE INNODB STATUS;" to view the error:

ALTER TABLE videos ADD constraint fk_videos_channels_id FOREIGN KEY (channel_id) REFERENCES channels (id);