Archive for the ‘mysql’ Category

A Step by Step Guide to Setup Rails application on Ec2 instance (Ubuntu Server)

Sometimes the Bitnami or other Rails AMIs doesn’t fit your needs directly and you will feel the need of building the Server yourself.Here I go step by step in building such a stack on top of Amaon EC2 Ubuntu Server.
  • Rails applications are a little bit different to install on servers but the process is very easy.Rails application needs a web server and an application server to run with. For development, it comes with default Webrick server that serve as application server on local machine. For setting it up on production server, we have the following choices on Web and application servers :-

    • Web servers

      1. Apache

      2. Nginx

    • Application Servers

      1. Passenger

      2. Thin

      3. Puma

      4. Unicorn

  • The simplest and best combination consists of Nginx + Passenger. It allows greater flexibility for configuration and also allows good speed over other combinations. So we are going to setup an Rails application using Nginx + passenger configuration on a bare Ubuntu server. Here are the steps :-

  1. Launch an Ec2 instance with ubuntu AMI. Make sure you have HTTP and SSH access to the server.

  2. SSH into the server by using private key (.pem) used while launching the instance and install the available updates by running :-

    sudo apt-get install updates
  3. Now you need to setup ruby on your server, so install the single user rvm ruby by following this blog.
  4. Load the rvm and make the installed ruby as default by running the following commands :-

    source ~/.rvm/scripts/rvm
    rvm use 2.1.0 –default
  5. Install the version control to clone your rails application to server. We generally use Git with rails application which can be installed by running the following command :-

    sudo apt-get install git
  6. Now clone your application on the server :-

    git clone yourepo.git

    Note:- In case of private git repository, you need to add public key of server to deploy keys of your repository, otherwise you will be promped with an permission denied error.


    Deploy using application to this server using Capistrano script. Please read this blog for more details on deploying your application using Capistrano.

  7. Now go to your application and install the gems by running bundle install command. If you want to setup your database on the same server, you can do the same by using the following commands 😐

    • In case of MYSQL

      sudo apt-get install mysql-server mysql-client
      sudo apt-get install libmysql++-dev
    • In case of POSTGRESQL, follow this blog for installation and then install the development headers

      sudo apt-get install libpq-dev

      After setting this up, migrate your databases in whichever environment you want to launch the server.

  8. Now install the Passenger gem by running :-

    gem install passenger

  9. Next step is to install the Nginx server, but we have some pre-requisits for this.

      1. It needs curl development headers which can be installed by :-

        sudo apt-get install libcurl4-openssl-dev

      2. It will be installed under /opt directory and your user should have permissions to that folder, so make your user as user owner for /opt directory by :-

        sudo chown -R ubuntu /opt

  10. Now install the Nginx server with passenger extension by running the following command :-


  11. Set your Nginx server as service in init script by using the following commands :-

    wget -O
    sudo mv /etc/init.d/nginx
    sudo chmod  +x /etc/init.d/nginx
    sudo /usr/sbin/update-rc.d -f nginx defaults
  12. Setup your application path in the nginx configuration file i.e. /opt/nginx/conf/nginx.conf.

    server {

    listen 80;

    server_name localhost;

    root /home/ubuntu/my_application/public #<-- be sure to point to 'public'

    passenger_enabled on;

    rails_env production;


  13. Lastly start your server by running the following command :-

    sudo service nginx start


Migrating from MySQL to PostgreSQL In RoR Application

Since the day Heroku has made Postgres as the default database, the popularity of Postgres has gone through the roof. A lot of developers however feel that its quite a task to move from mysql to Postgres, so we thought of putting up a small writeup on how one can go about migrating from mysql from Postgres. PostgreSQL
  • Often simply Postgres, is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X.
  • It implements the majority of the SQL:2008 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, and indexes, and has a large number of extensions written by third parties
Migrating from MySQL to PostgreSQL – Why ?
  • One of the reasons is definitely for engineers developing on Heroku platform since postgres is now the default database on Heroku.
  • MySQL is faster and easier to use than PostgreSQL but PostgreSQL is perceived as more powerful, more focused on data integrity, and stricter at complying with SQL specifications.
Installing Postgres: For the purpose of blog, we will assume that the operating system is ubuntu, it should be equally easier to install it on other operating systems. Open the terminal and type following command to install postgresql :-
sudo apt-get install postgresql postgresql-client /*For Ubuntu Operating System*/
Now your postgresql server and client is installed. You can verify the installation by logging into the postgresql console by using this command:-
sudo -u postgres psql
NOTE:- use ‘\q’ to exit from the postgresql console. Creating the user This step is something different from mysql and developers migrating from mysql found this to be quite difficult to understand. We will try to decipher it here. Postgresql creates default superuser called “postgres” but we can’t use it in our application because of postgres security reasons . So, we need to create a new user that our application can use to login to postgres. To do so, go to terminal and type the following command to create a new user :-
sudo -u postgres createuser
Pressing ENTER after writing this command will ask you some questions like name of user and basic privileges to be given. Simply type the name of the user and answer of other questions in y/n like:- Enter name of role to add: user_name /*name of the user to be created*/ Shall the new role be a superuser? (y/n) /*Type y to make the user Superuser, otherwise type n*/ You are done creating the user, we will now create the database and assign the privileges for this database to the given user. This is almost similar like we do in mysql. Read More

mysql COLUMN_UPDATED() and bitwise operations

Sometimes to achieve a certain functionality, you need to determine the columns that were updated after the mysql update query. I will try to explain how to achieve this in this particular article. The article contains the reference to cakephp constructs because i happened to use this in a cakephp application. Handling Bits in General: Let me discuss about the basics of how we can do bitwise operations in php(The code contains references to cakephp constructs but the logic remains the same).
  • Depending on the number of bits that need to be used declare an attribute of type INT.
  • Tiny – 8bits, Small- 16 bits, Med – 24  bits or Int – 32 bits
  • smallint $status = 0;
  • Declare each flag that needs to be used as following in model in powers of 2.
<code><em>class</em><em> Node </em><em>extends</em><em> AppModel {</em></code>

<em>const</em><em> flag1 =1;//00000001 </em>

<em>const</em><em> flag2 =2;//00000010</em>

<em>const</em><em> flag3 =4;//00000100</em>

<em>const</em><em> flag4 =8;//00001000</em>

  • Check if a particular flag is set:  if($status & Node::flag2) { echo “flag 2 is set”;}
  • Set a flag: $status |= Node::flag3;
  • Unset a flag: $status = $status &~ Node::flag3;
  • Check how many flags are set:
<em>function </em><em>get_count($status){</em>

<em> $i=0;</em>

<em> while ($status){ $i++ ; $status &= ($status - 1) ; }</em>

<em> return $i; </em>

<em> }
MySql – COLUMNS_UPDATED() Mysql columns_updated works on the similar logic as explained above. COLUMNS_UPDATED() returns a varbinary bit pattern that indicates the columns in a table or view that were inserted or updated. COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions. COLUMNS_UPDATED returns one or more bytes that are ordered from left to right, with the least significant bit in each byte being the rightmost. The rightmost bit of the leftmost byte represents the first column in the table; the next bit to the left represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost. COLUMNS_UPDATED returns TRUE for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted. To test for updates or inserts to specific columns, follow the syntax with a bitwise operator and an integer bitmask of the columns being tested. For example, table t1 contains columns C1, C2, C3, C4, and C5. To verify that columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), follow the syntax with & 14(bitwise and operation). To test whether only column C2 is updated, specify & 2. Hope this helps someone !!