N Rao Lakkakula’s Blog

I knew this before but couldnt recall it when I needed it…. so  I am posting it here……(credit goes to vhikida@inreach.com )

Problem : 

TABLE_A has columns x and y. You want to select duplicate rows from that table

Solution :

 select * from TABLE_A where (x,y) IN (SELECT x, y FROM TABLE_A m2 GROUP BY x,y HAVING COUNT(*) > 1)

A) Installing Ruby

$ yum install ruby ruby-libs ruby-mode ruby-rdoc ruby-irb ruby-ri ruby-docs

B) Install Ruby Gems

Download rubygems-0.9.4.tgz from http://rubygems.org/ to /tmp folder

$ tar -xvfz /tmp/rubygems-0.9.4.tgz

$cd rubygems-0.9.4

$ ruby setup.rb

C) Install Rails

$ gem install rails

D) Install MySQL

Download From mysql-community server from http://dev.mysql.com/downloads/mysql/5.0.html

I downloaded mysql-5.0.41-linux-i686-icc-glibc23.tar.gz to /tmp folder

$ groupadd mysql

$ useradd -g mysql mysql

$ cd /usr/local

$ gunzip < /tmp/mysql-5.0.41-linux-i686-icc-glibc23.tar.gz | tar xvf -

$ ln -s mysql-5.0.41-linux-i686-icc-glibc23 mysql

$ cd mysql

$ chown -R mysql .

$ chgrp -R mysql .

$ scripts/mysql_install_db –user=mysql

$ chown -R root .

$ chown -R mysql data

(See http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html instructions if you need detailed instructions)

Now to Automatically start server at boot

$ cp mysql.server /etc/init.d/mysql

$ chmod +x /etc/init.d/mysql

$ chkconfig –add mysql

(see http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html for detailed instructions )

Finally remove anonymous user and set password for root user

$ mysql -u root

mysql> DELETE FROM mysql.user WHERE User = ”;

mysql> FLUSH PRIVILEGES;

mysql> UPDATE mysql.user SET Password = PASSWORD(’yournewpwd’) WHERE User = ‘root’;

mysql> FLUSH PRIVILEGES;

E) Installing mysql Gem

$ gem install mysql — –with-mysql-include=/usr/local/mysql/include –with-mysql-lib=/usr/local/mysql/lib

F)  Create Rails Project and Makesure it works

$ rails testproject

Now add mysql password to config/database.yml file.  And  create respecive databases in mysql.  I am assuming you, you didnt change database names in database.yml file so database names are testproject_development, testproject_test, testproject_production

$ mysql -u root -p

Enter your password

mysql > CREATE DATABASE testproject_development;

mysql > CREATE DATABASE testproject_test;

mysql > CREATE DATABASE testproject_production;

Now run cd to your project and see if this thing works

$ cd testproject

$ rake db:migrate

If you dont see errors, mean rails can see your databases.

Go ahead and develop Rails applications….

To start mysql Server 

#mysqld_safe  &

or

#mysqld &

(To start server with logging option 

#mysqld –l  &

log files are written in data directory as hostname.err  and hostname.log)

To Change root user password 

#mysqladmin -u root password ‘newpass’

#mysqladmin –u root –h “hostname” password ‘newpass’

 ( usually mysql has 2 entries : one for localhost and one for the hostname and 2 anonymous users for those both initially )  

To connect to mysql server 

#mysql –u root –p

Show All database in System

mysql> SHOW DATABASES;

Use particular database

Mysql> use  testdb

Or you can run quries with prefix  :  like   SELECT * FROM testdb.mytable;

DELETE INITIAL ACCOUNTS & SET ROOT PASSWORD 

Mysql> DELETE FROM mysql.user WHERE User = ‘’;

Mysql> FLUSH PRIVILEGES;

Use mysqladmin  to set password as explained above

Create NEW Database 

Mysql> CREATE DATABASE   mydb;

Create New USERS 

Mysql> GRANTE ALL ON mydb.*  TO ‘testuser1’@’localhost’ IDENTIFIED BY ‘testpass1’;

Mysql>FLUSH PRIVELEGES;

 

Shutdown Server 

#mysqladmin shutdown