Posts Tagged ‘mysql’

I was getting the following error in Airbrake for my Rails app which uses MySQL:

Mysql2::Error: Incorrect string value: '\xCE\x94\xCE\xB1\xCE\xBD...' for column 'comment'

This was caused by the fact that many of my older tables still used legacy encoding.

To convert tables to UTF8 issue the following command at the MySQL prompt:

ALTER TABLE tweets CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

To find tables that have legacy encoding use:

SHOW TABLE STATUS;

Spent some time tuning my MySQL database for a small website (~2K users per day). MySQL Tuner was recommending that we increase the size of the query cache above 16M but we were dubious. The relevant metrics according to this article are:

• Hit rate     = Qcache_hits / (Qcache_hits + Com_select)
• Insert rate = Qcache_inserts / (Qcache_hits + Com_select)
• Prune rate = Qcache_lowmem_prunes / Qcache_inserts

In our case we had gathered the following stats over a 48 hour period:

| Com_select                        | 1163740   |
| Qcache_hits                       |   531650   |
| Qcache_inserts                   | 1021165   |
| Qcache_lowmem_prunes     |    82507   |

| Qcache_not_cached             | 142575    |
| Qcache_queries_in_cache    | 2145        |
| Qcache_total_blocks           | 5643        |
| Qcache_free_blocks            | 1175        |
| Qcache_free_memory         | 11042672  |

So for our database:

• Hit rate     = 24%
• Insert rate = 60%
• Prune rate =   8%

We’re not too sure what to make of this. A hit rate of 24% doesn’t seem to bad but our insert rate is also quite high. For now, we’re leaving the query cache as is. Especially since the comments in the post mentioned above suggest that making it larger than 20M is futile.

When you’re debugging/analyzing MySQL queries in the Rails console, it helps to turn on ActiveRecord logging:

#Enable ActiveRecord logging
def loud_logger(enable = true)
  logger = (enable == true ? Logger.new(STDOUT) : nil)
  ActiveRecord::Base.logger = logger
  ActiveRecord::Base.clear_active_connections!
end

If you’re trying to run the rake task required for installing Cucumber in Rails and keep getting the following error message:

rake aborted!
undefined method `select’ for class `ActiveRecord::ConnectionAdapters::MysqlAdapter’

Check to see whether you’re using the query_analyzer plugin. I had to uninstall it to get the rake task to complete using:

script/plugin remove query_analyzer

That solved the problem

Assuming you want to make sure that no two models have the same COMBINATION of values for a and b:

so having two models with:

a = 1, b = 2
a = 1, b = 3

would not be a conflict.

If that’s the case then the standard validation

class Widget < ActiveRecord::Base
validates_uniqueness_of :a, :b
end

wouldn’t work since it tries to prevent saving two models with the same value of a, OR with the same value of b.

And even if that’s not what you’re trying to do, and you’re ok with the example being a conflict, validates_uniqueness_of doesn’t guarantee uniqueness if two users try to save conflicting records simultaneously.  The validation works by first trying to find a record with the value, and if it doesn’t find it inserting the ‘new’ record, and this can fail due to a concurrency hole.

To fill this hole requires leaning on the database server, and the way to do that in SQL is by having a unique index on the table which covers the column or columns you want to be unique. This assume you are using a database which supports it, e.g. MySql.

To create an index you can create a migration which includes a statement like

add_index  :widgets, [:a, :b], :unique => true)

Assuming that the table name for the model is ‘widgets’

Now if you do this, you also need to be aware that if you try to save a record with a uniqueness conflict the save will raise an ActiveRecord::StatementInvalid exception, which you’ll need to rescue and do something like telling the user of the conflict so that he can rectify it.

Rails Guides

Keeping Ruby on Rails up to date:

Installing gems on Dreamhost

Rails > 2.1 now with gem dependencies and here

ruby -v — check which version of ruby you have installed

rails -v — check which version of rails you have installed

gem list — check versions of all installed gems

sudo gem update — bulk update of all installed gems (some say this is a bad idea)

sudo gem update –system (updates rubygems, note use of double dash)

sudo gem uninstall <gem_name> — uninstall a specific gem

sudo gem install <gem_name> — install a specific gem

sudo gem cleanup — remove old versions of gems

sudo gem install -v=2.0.2 rails — install a specific version of Rails (to stay in synch with Dreamhost)

Rmagick

Installing Rmagick

1. sudo apt-get update
2. sudo apt-get install imagemagick
3. Then, install the imagemagick9 dev library:
   sudo apt-get install libmagick9-dev
4. Last, install the RMagick gem:
sudo gem install rmagick

Rails Migrations

Useful cheat sheet

Use rake db:schema:load if having trouble with Rails migrations and you have a working schema.

Use rake db:migrate VERSION=3 to roll back to version 3

Use rake db:migrate:reset — drop db, recreate it, and then run all migrations

rake db:rollback — go back one migration

rake db:migrate:redo — undo last migration and then redo it

rake db:sessions:clear — purge sessions from database

MySQL

To create a new MySQL DB on Dreamhost it is best to use the Dreamhost panel.

To create a database locally: mysqladmin -u root -p create <dbname>_development

mysql -u yourdblogin -p -hyourdbdomain.yourdomain.com yourdb

To load a table into a database:

mysql -u [username] -p[password] -hmysql.[domainname].com [database_name] < iso_country_list.sql

drop table sessions; — delete the sessions table

show tables; — show all tables for database

describe sessions; — show the sessions table

check table sessions; — check the sessions table for corruption and/or nonexistence

Gem Sources

Make sure to add GitHub: gem sources -a http://gems.github.com

If you’re getting this error:

Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

When you try to execute a migration, it might be worth trying:

rake db:migrate RAILS_ENV=production

I’m sure this could be solved in a configuration file – database.yml (?) – but that worked for me.

Also, on Dreamhost, make sure to add the following line

‘host: mysql.<dbhostname>.com’ to your database.yml file (under the production environment)