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 http://pastie.caboo.se/42574. All credit to court3nay.

task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{backup_dir}/#{application}.dump.#{Time.now.to_f}.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:/
  end
end

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

task :before_deploy do
    backup
  end

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 http://sourceforge.net/projects/mysql-python.

Extract it and run:

python setup.py build
sudo python setup.py 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 setup.py build
python setup.py 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:

DELIMITER |
DROP PROCEDURE IF EXISTS category_for |
CREATE PROCEDURE category_for (product_id int, OUT r_category_id INT)
 BEGIN
  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 |
DELIMITER ;

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

Problem

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:

201
202
203
204
205
206
207
208

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}")
        end

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);
SHOW ENGINE INNODB STATUS;