In text:

1. Download “mysql-connector-net-6.2.2-src.zip”  from http://dev.mysql.com/downloads/connector/net/ and extract the zip file.  Note that we need ‘src’ version of library if we want to debug in visual studio.

2. Open your Visual Studio Solution and add  “Mysql.data.csproj”  located at  “MySql.Data\Provider\” inside the extracted archive.

3. Add this project’s reference to your project (mysql.data).

4. Now you can debug into mysql.data methods.

Advertisements

Once in a while we been in situations where you need to quickly understand/analyze a database given to you by others.  We can go through each table and try to understand but it is usually lot simpler if you can visualize all the tables involved and relationships between those.  Enter SchemaSpy: a free tool to do that.    For sake of specifics, say the database given is MySQL and your pc is running some version of Windows.

A. Download and Install Pre-requsites (if you dont have already)

B. Download latest SchemaSpy Jar File  from http://sourceforge.net/projects/schemaspy/files/   (In my case it is schemaSpy_4.1.1.jar)  to a known location (using same C:\mydblayout)

Now run the schemaspy to generate report (html format) for database “mytestdb”  which is sitting at a local mySQL server.

C:\mydblayout>java -jar schemaSpy_4.1.1.jar -t mysql
 -dp mysql-connector-java-5.1.10\mysql-connector-java-5.1.10\mysql-connector-java-5.1.10-bin.jar
-db mysql -host localhost -u root -p mysecretpass -o mytestdb_report

See how -dp flag is used to point to jdbc driver.  The above command creates a folder called “mytestdb_report”  with all the nice html files.  Just open the “index.html” file.

For example output see http://schemaspy.sourceforge.net/unifieddb/characters/index.html

For more details, look at web page of  this wonderful SchemaSpy (http://schemaspy.sourceforge.net/) written by John Currier.

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….

MySql vs Postgres

June 19, 2006

Wait… Its not one of many arguments over which one is better.  Both are excellent open source database systems  with rich set of features competing commmercial databases. (it look like MySQL has more marketing power than postgres though….) . The point I want to make is how both are different.  I been using Postgresql from almost 5 years.  When we tried to pick between MySQL and Postgres back then, I picked Postgresql as it has support for Foriegn Keys, triggers and stored procedures. So in the last five or so years when people asked me why Postgres over MySQL, I could proudly point to those features MySQL lacked. But NOT anymore!  Looks like MySQL 5 has all these covered. Now it has support for Views, Stored Procedures and Triggers.  ( See http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html)  More over they added more features from MaxDB (SA- DB from MySQL AB) like Event Scheduler in 5.1 version.  So now its purely personal preference of which one to pick.  

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