Topics: Persistence

See which MySQL database is currently in use

When you work in the MySQL console and you want to see which database is used, type:

SELECT database();

The result you see is the database you would activate with

USE database_name;

Reset mysql root password

Stop the MySQL Server.

sudo /etc/init.d/mysql stop

Start the mysqld configuration.

sudo mysqld --skip-grant-tables &

Login to MySQL as root.

mysql -u root mysql

Replace YOURNEWPASSWORD with your new password!

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;

MySQL: Can I speed up LIKE queries by adding an index? (Spoiler: Yes)

For string columns, MySQL indexes the left side of a string. That means an index can speed a like query that has a wildcard on the right side:

SELECT * FROM foo WHERE field LIKE "bar%" # will be faster with an index

It can not speed up a query that has a variable left side:

SELECT * FROM foo WHERE field LIKE "%bar%" # will not be faster with an index

That also means if you use the ancestry gem you should index your ancestry column if you use scopes like descendants or `su…

Check if an object is an ActiveRecord scope

Don't say is_a?(ActiveRecord::NamedScope::Scope) because that is no longer true in Rails 3 and also doesn't match unscoped ActiveRecord classes themselves (which we consider scopes for all practical purposes).

A good way is to say this instead:

object.respond_to?(:scoped)

When connecting to a second database, take care not to overwrite existing connections

Sometimes, you may want to open up a second database connection, to a read slave or another database. When doing that, you must make sure you don't overwrite an existing connection.

The problem

While this may look good, it will actually cause all kinds of trouble:

def with_other_database
  ActiveRecord::Base.establish_connection(slave_settings)
  yield
ensure
  ActiveRecord::Base.establish_connection(master_settings)
end

Putting aside that you are setting the general connection here (not generally a …

Rails 3.1 gives you free down migrations

In Rails 3.1+, instead of defining a separate up and down method you can define a single method change:

class AddComparisonFieldsToReport < ActiveRecord::Migration
  def change
    add_column :reports, :compare, :boolean
    update "UPDATE reports SET compare = #{quoted_false}"
    add_column :reports, :compare_start_date, :date
    add_column :reports, :compare_end_date, :date
  end
end

Migrating up works as expected:

``` b rake db:migrate == AddComparisonFieldsToReport: migrating ==================================== – ad…

Paperclip: Move attachements from local storage to AWS S3

We frequently use the handy Paperclip Gem to manage file attachments.

If you need to move the files from local storage (i.e., your servers' harddisk) to Amazon S3, you can simply change settings for Paperclip to use the S3 storage adapter and use this script to migrate to S3. Put the snippet into a chore if you don't want to run that in the console. YOUR_LOCAL_STORAGE_MODEL_DIRECTORY should be something like 'storage/your_model'.

Dir.glob(YOUR_LOCAL_STORAGE_MODEL_DIRECTORY**/*).each do |path|...
External content

ActiveRecord::SpawnMethods

Methods to remove e.g. order or conditions from an existing scope chain.

Why stubbing on associated records does not always work as expected

Be careful when stubbing out attributes on records that are defined by associations. Nothing is as it seems to be.

The associated record has its own universe of things; when delegating calls to it, you ca not stub methods on the associated record and expect them to be around. That is a general issue with this pattern/approach.

What's happening?

Consider these classes:

class Post < ActiveRecord::Base
  belongs_to :thread
  
  def thread_title
    thread.title
  end
end

class Thread < Acti...

Use the "paper_trail" gem to track versions of records

paper_trail is an excellent gem to track record versions and changes.

You almost never want to reimplement something like it yourself. If you need to log some extra information, you can add them on top.

It comes with a really good README file that holds lots of examples. I'll show you only some of its features here:

  • Setting up a model to track changes
    Just add has_paper_trail to it:
    class User < ActiveRecord::Base
      has_paper_trail
    end
    
  • Accessing a previous version
    Saying user.previous_version gi…

MySQL operator precedence

Take care in queries where multiple AND or OR operators are used. In doubt, always use braces to enforce precedence.

Boolean fields in migrations

If you want to update some records with boolean fields in a migration, always remember to set your values with field=#{quoted_true} and field=#{quoted_false}. The Rails methods quoted_false and quoted_true return the correct boolean representations for your database.

External content

Rails, callbacks, workers, and the race you never expected to lose « Logical Friday

How delayed jobs can make your after_save callbacks execute before the record is saved.

External content

MySQL 5.6 will expose tables as key/value stores through memcached, might be awesome

The next version of MySQL will include a built-in memcached daemon. This daemon can quickly get and set key/value pairs from and to InnoDB tables while completely bypassing the parsing and planning overhead of SQL.

This could potentially be very awesome because we would then have a distributed key/value store that stores data in a way it can also be queried with complex SQL queries.

Maximum size of a MySQL query

Unless you changed the default, this will be 16 MB:

mysql> SHOW VARIABLES WHERE Variable_name="max_allowed_packet";
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+

How to enable MySQL query logging

This will make MySQL log all received queries so you can see for yourself what happens on the database level.

Don't switch this on for production machines!

  1. Edit your my.cnf:
    sudo vim /etc/mysql/my.cnf 
    
  2. In the [mysqld] section, add:
    log=/var/log/mysql.log 
    
  3. Restart your MySQL daemon. On Ubuntu:
    sudo service mysql restart
    

Note that your MySQL performance will suffer. But when you need to enable query logging for a debug fest, you probably don't care about that.

CSS3 Media Queries have reached recommendation status

Media Queries have reached W3C Recommendation on June 19th 2012.

If you were interested in other (future) web standards you can have a look at recent W3C publications.

255 cards