"; */ ?>

mysql

howto / mysql / rails / tutorials — 7 Comments
14
Sep 08

Configure Rails and MySQL to Support UTF-8

Rails on MySql

The fact that there are so many different countries, people and languages makes it very interesting to watch all them to use a single tool. Besides the different cultures of programming, there is a definite difference in languages that the tool needs to support in order to become widely used.

Luckily, if the tool is written to support UTF-8 encoding it is guaranteed to support all the modern spoken languages. Since UTF-8 is able to represent any character in the Unicode standard, yet the initial encoding of byte codes and character assignments for UTF-8 is backwards compatible with ASCII, and for these reasons, it is steadily becoming the preferred encoding for e-mail, web pages, and other places where characters are stored or streamed – in our case it is a mySql database.

When working with Rails on mySql, it is most of the time, a good practice to make sure the UTF-8 support is enabled, since even if there is no immediate need, in the future, clients of the Rails application could come from different points of Earth – due to the Earthy nature of the Internet.

Here are 3 simple steps on how to configure a Rails application and mySql database to support UTF-8 encoding:

Step 1. From the Rails side, due to the “convention over configuration” principle, there is only one thing to make sure of. Open the Rails database configuration file:

  vi config/database.yml

(here I used “vi” text editor, but any editor of choice can be used: notepad/textmate/emacs/aptana.. etc)

Notice the “encoding” option, and make sure it is set to “utf-8”:

  ...
        development:
        adapter: mysql
>>>  encoding: utf8
        database: my_international_db
        username: user
        password: password
        socket: /var/run/mysqld/mysqld.sock
  ...

That will conclude this step, since everything from Rails side is configured. Simple? Well, yes – Rails is well designed to keep it simple stupid.

Above is the sample for the Rails development environment, make sure that testing and production environments have the same configuration.

Step 2. Now it is time to configure MySql server. This will be done by editing “my.cnf” – mySQL configuration file:

  vi /etc/mysql/my.cnf

There are several sections in the file. Modify two of them – “client” and “mysqld” as shown below to configure mySql for UTF-8 support:

...
[client]
 
default-character-set=utf8
 
...
[mysqld]
 
default-character-set=utf8
character-set-server=utf8
default-collation=utf8_unicode_ci
...

Step 3. The very last action would be to restart MySql server. Here is an example on how to do it in Linux (Ubuntu):

  sudo /etc/init.d/mysql restart

NOTE: Only databases that are created after the above change will support UTF-8 encoding.

After these three steps Rails application and MySql server are configured, and ready to serve the whole planet!

howto / mysql / tutorials — 242 Comments
10
Apr 07

MySQL: Reset Lost Root Password

Here is a quick Step-by-Step “how to” which helps restoring MySQL root password that was lost/forgotten.

It happens to everybody, especially if several distributed (different) systems are maintained, where the password is not the same. Here is what needs to be done to restore it:

Step 1: Stop MySQL daemon if it is currently running

Depending on the operating system MySQL is installed on, the daemon can be checked/stopped differently. Here is an example on how to do it in Unix-like systems. 

[ NOTE ]: You might need to run it as a Unix System superuser (root) - depending on 
          how the system is configured, and what permissions your Unix account is granted)

Here is how to stop/kill the existing mysql daemon, in case it is running:

      ps -ef | grep mysql      - checks if mysql/mysqld is one of the running processes.
 
      pkill mysqld             - kills the daemon, if it is running.

Note: if pkill (’process kill’) is not on a particular Unix system, use kill -9 ‘pid’, where ‘pid’ corresponds to processes that were found with ps -ef | grep mysql

Step 2: Run MySQL safe daemon with skipping grant tables

      mysqld_safe --skip-grant-tables &

Step 3: Login to MySQL as root with no password

      mysql -u root mysql

Step 4: Run UPDATE query to reset the root password

In MySQL command line prompt issue the following two commands:

      UPDATE user SET password=PASSWORD("ualue=42") WHERE user="root";
      FLUSH PRIVILEGES;

“ualue=42” is a common password for “The Hitchhiker’s Guide to the Galaxy” people which reads “Ultimate Answer to Life, the Universe, and Everything=42“

Step 5: Stop MySQL safe daemon

Follow the first two steps, but this time kill (pkill) “mysqld_safe” instead of “mysqld”

Step 6: Start MySQL daemon

Depending on the operating system (Unix-like examples):

      /etc/rc.d/rc.mysql start

OR

      /etc/init.d/mysql start

OR

      /etc/rc.5/mysql start

etc.. check existing MySQL configuration

Step 7: Root password is reset and ready to use

Password is reset. Privileges are flushed. Start MySQL and login as root with the password set in step 4:

      mysql -u root -p mysql

Note: sometimes (most of the time) ‘root user’ privileges are required for the system (OS) in order to stop/start processes

what is next? Reset Lost Password in Sun Application Server…