08 Apr

MySQL Load Data Infile Syntax Generator Tool Download

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string.

I have already written the basic how to follow that considering additional cases.

A lot of questions I have seen for loading separated data to MySQL, so here I’ve created a very beginner level excel tool that will allow you to generate the LOAD DATA syntax as per your choices.
Presently sample table name and field/line separators are specified, which you may alter as per your own file.

Load-data-syntax-v1.0

Load-data-syntax-v1.0

Download the sheet here: Load Data

[ad#ad-2-300x250]

Load Data Syntax:

Options column specifies options that are required for syntaxes generation.
Under Selection column you will choose your option while under Description column, for each Option description is provided.

Using basic knowledge of load data you can quite easily generate the syntax by specifying option, choosing file to be loaded and finally clicking the Syntax button.

Please refer the syntax on MySQL documentation as well.

This is a “early release”, please check for the updates.
If you have any suggestions or bug report please comment / mail.

Thanks.

05 Apr

In the world of NoSQL-Hands on Mongodb-1

I’m hearing a lot of “NoSQL” these days. To really understand how (and) does it works, I decided to give a try on MongoDB.

MongoDB (hu*mongo*us) is an open source, scalable, high-performance, schema-free, document-oriented database written in the C++ programming language.

MongoDB is not a Relational Database Management System. The database manages collections of JSON (JavaScript Object Notation)-like documents which are stored in a binary format referred to as BSON (Binary JSON).

This post will surely get you started with the MongoDB-NoSQL.

Installing MongoDB:

  • Download Mongodb from www.mongodb.org
  • Extract binaries
  • Create data directory – default for windows C:\data\db
  • Install service: Full\path\to\mongod.exe –install

If you want to change default data directory location:
Full\path\to\mongod.exe –dbpath=”\”full\path\to\”” –install

Troubleshooting Mongodb Installtion Error:
I faced while installing, if you get error like:
dbexit:
shutdown: going to close listening sockets.
shutdown: going to flush oplog…
shutdown: going to close sockets…
shutdown: waiting for fs preallocator…
shutdown: closing all files…
closeAllFiles() finished
dbexit: really exiting now

Go to: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/
Check for MongoDB
Verify the value of ImagePath is proper

This seems to be caused by not giving full-path while installing.

Add Environment variable Path, add path/upto/mongo-bin-dir;

Okay, now we’re all set to checkout the MongoDB-NoSQL world.

MongoDB GUI Administration Tool:

There is a GUI Admin tool available phpmoadmin to simplify the MongoDB Operations.
Signle php script: phpmoadmin
Download PHP extension from php.net/manual/en/mongo.installation.php
Extract in ext directory of WAMP & Restart.

In case of error you may install Microsoft Visual C++ 2008 SP1 Redistributable Package (x86) [www.microsoft.com/downloads/details.aspx?familyid=A5C84275-3B97-4AB7-A40D-3802B2AF5FC2&displaylang=en]

MySQL to MongoDB Database Migration:

Create CSV from MySQL:
mysql> select * into outfile “c:/TABLENAME.csv” FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ from TABLENAME;
Query OK, 293911 rows affected (1 min 8.17 sec)


Import CSV to mongodb using mongoimport:
C:\>mongoimport -d DATABASENAME -c TABLENAME –file “c:\TABLENAME.csv” –type csv -f FIELD1,FIELD2…

Using MongoDB on Windows:

On Command Prompt:
C:\MongoDB\mongodb-win32-i386-1.4.0\bin>mongo.exe
MongoDB shell version: 1.4.0
url: test
connecting to: test
type “exit” to exit
type “help” for help

By default it’s connecting to test database.
Default mongodb port is 9999.

Basic Commands

  • show dbs : show database names
  • show collections : show collections in current database
  • show users : show users in current database
  • show profile : show most recent system.profile entries with time >= 1ms
  • use : set curent database to
  • db.help() : help on DB methods
  • db.foo.help() : help on collection methods
  • db.foo.find() : list objects in collection foo
  • db.foo.find( { a : 1 } ) : list objects in foo where a == 1 it result of the last line evaluated; use to further iterate

Simple Queries with MongoDB:

Set Current Database:
>use DATABASENAME


See Present Database:
> db
DATABASENAME


View collections (tables) under present db:
> db.getCollectionNames()
[ "TABLENAME", "system.indexes" ]


SQL>Select count(*) from TABLENAME:
NoSQL> db.TABLENAME.count()
238735


SQL>select * from TABLENAME where field1 regexp ‘enrique’:
NoSQL>db.TABLENAME.find( { field1 : /enriqu/i} );


For simple find:
NoSQL>db.TABLENAME.find( { field1 : “value” } );


SQL>Select field1 from table where field2=”%search%”:
NoSQL>db.TABLENAME.find( { field2 : /search/i} , {field1 : 1 } );


SQL>Select All but one (field1) column:
NoSQL>db.TABLENAME.find( { field2 : /search/i} , {field1 : 0 } );


SQL>Select with sort order ascending and descending (1,-1):
NoSQL>db.TABLENAME.find( { ar_name : /enrique/i }, {field1 : 1 } ).sort({ field1 : -1})

MongoDB now seems easy to follow, but I’m still having hard time in taking on the Map/Reduce.
More in the next post; ofcourse documentation home is a good place, else you may export the pdf / html / xml documentation here.

03 Apr

Place to visit Mumbai: Alibaug 1-2 day tour weekend picnic

Think about one day tour and a lot of fun in Mumbai? Alibaug is a good choice.

It a normal budget, full day, full fun and great place.

Reach Gateway Of India. [20 Mins walk / 5 Mins (Rs. 15) Taxi from Chatrapati Shivaji Terminus / Victoria Terminus]

Gateway of India

Gateway of India

Book a PNP / Ajanta Jetty from booking stalls opposite to Gateway of India. [AC - 120, non-AC - 65]. [First ferry @ 8.30 AM – depends on tide conditions too]

Non-AC ferry, top seat is best way to travel as you can feel the breezing wind and cool waves. The birds following in the mid-sea to the ship is a different fun to capture them.

Sea Birds following Jetty

Sea Birds following Jetty

[ad#ad-2-300x250]

Next you will land at Mandwa port, Quick walk to the bus services provided by PNP / Ajanta to Alibaug. [It takes 1 Hr from Gateway of India to Mandwa and ~30 Mins from Mandwa to Alibaug.]

If you’re over cautious, book tickets for return journey and confirm day’s last jetty. PNP / Ajanta Jetty service offices are near by only.

The closest beach is Alibaug beach, walk-able distance from Alibaug bus stand.

At Alibaug beach you may have water bike / wave ride, or normal sea-routines.

If you’re a fort’oholic, you may choose to visit the Colaba Fort @ 100 Rs / person through a 10 minute boat ride. 1 hr. is sufficient to capture all major corners of the fort.

Colaba Fort, Alibaug

Colaba Fort, Alibaug

Kolaba Fort, Alibaug

Kolaba Fort, Alibaug

I don’t much recommend Alibaug to waste your precious time on.

Akshi and Negaon beaches are on the same route. Auto will charge 120 Rs for Negaon beach ~30 mins from Alibaug Station / beach. [Take mobile number from auto driver in case of no return transport availability.]

Negaon beach

and is really a recommended one. It has nice long beach with all those coconut trees greeting the waves to the shore.

If you’re a bunch of crazy guys looking for a place to freak-out, Negaon beach is for you. Football, cricket, Hourse riding or any beaching activity is absolute fun here. I don’t like lazy bums in the gang but they can rest under the shadows on misty sand. Gliding and water rides are of-course available.

Negaon Beach, Alibaug

Negaon Beach, Alibaug

Negaon Beach, Alibaug

Negaon Beach, Alibaug

Hotels / rooms are cheap; food is okay; though these shouldn’t impact as these are not major fun part.

You cannot cover everything in one day; nor have I visited all of the attractions there.

For 1 day tour, pack a bag and directly reach to Negaon beach.

Approx expenditure: <500 Rs / person.
Large

Other Routes:

By Road: One can reach Alibag via Pen (30 km away), which is on the Mumbai (78 km away) – Goa road.

From Mumbai, one can reach Alibag by traveling on the Mumbai-Goa highway (NH-17) till Wadkhal (Vadkhal) and taking the right fork from Wadkal – the left fork will lead you to Goa. The distance is approximately 108 km from Mumbai.

By Railways: The nearest rail railway station are Pen, India. Through Pen, India, it is connected to Khopoli.

Other beaches at Alibaug:

Varsoli Beach:Located about a mile from the main beach, home to a very large naval base.

Kihim-Navgaon Beach: Kihim is a secluded place at a distance of 12 km from Alibag. The Kihim beach is famous for dense cover of Coconut trees. It is also famous for woods which are home to rare butterflies, birds and flowers.

Awas: It is situated about 16 km away from Alibag.

Saswane: It is situated about 18 km away from Alibag.

Rewas: It is situated about 24 km away from Alibag.

Chaul Revdanda: It is situated about 17 km away from Alibag.

kaneshwar Mandir: It is situated about 17 km away from Alibag to karlekhind-chondi road.

Mandawa: It is situated about 20 km north of Alibag. The catamaran/ferry services are available from Mumbai to the Mandawa jetty. Many Bollywood celebrities own bungalows here.

Kashid Beach: It is 36 km away from Alibag, on the Alibag-Murud highway, also this beach is possibly one of the cleanest and most beautiful beaches in the region with almost ‘white’ sand.

29 Mar

MySQL related file types and basic information

This post covers the basic information of files that MySQL Server uses / creates for various tasks.

my.cnf :

It is the main configuration file for MySQL. You may find it under base directory in windows or under /etc/.

.sock :

mysqld creates a socket for programs to connect to and notes in this file. It is named as mysql.sock.

Pid file :

MySQL server write its process ID in this file. You may specify it by –pid-file=file_name otherwise it defaults to hostname of the server.

.frm :

MySQL represents each table by an .frm table format file, stores table definition in the database directory. It is stored as
.frm under data directory.

.MYD :

This is the extension of the Data files for MyISAM tables. It is stored as
.MYD under data directory.

.MYI :

This is the extension of the Index files for MyISAM tables. It is stored as
.MYI under data directory.

[ad#ad-2-300x250]

.ibd :

If you specify innodb_file_per_table option to my.cnf, InnoDB stores each table in its own .ibd file in the same MySQL database directory where the .frm file is created. For InnoDB tables, the data and the indexes are stored together in the .ibd file.

ibdata1 :

Shared tablespace / data file for InnoDB tables when innodb_file_per_table option is not specified in my.cnf, all tables are created in this shared tablespace.

ib_logfile0 & ib_logfile1 :

InnoDB log files. The information is logged in circular fashion. InnoDB applies changes recorded in logfiles to tablespace. This is also important for performance and recovery.

.db :

This is data file for BDB (BerkeleyDB) storage engine which stores table data and indexes.

db.opt :

In CREATE DATABASE command, create_specification options specify database characteristics. Database characteristics are stored in the db.opt file in the database directory.

error log :

This log contains information about mysqld start and stop events, any critical errors that occur while the server is running etc. In my.cnf you can specify log-error[=file_name] option and error log will be created under data directory file_name.err. If no name is specified MySQL will create hostname.err file.

slow query log :

This log contains queries that runs slower than specified long_query_time variable. You can specify log-slow-queries[=file_name] option. If no file name specified, MySQL will log slow queries in host_name-slow.log file under data directory.

general query log :

This file logs all the SQL sent from client about MySQL server and mysqld start up/down details. You may enable it by log[=file_name]. If file_name is not specified MySQL will create hostname.log under data directory.

#sql-55c_6.MYD / #sql-55c_6.frm / #sql-55c_6.MYI :

Temporary files created when you normally issue ALTER TABLE, MySQL creates a copy of table, apply changes, delete original table and then rename temporary table to original. The names are like #sql-…/MYD/MYI/frm.

binary log files :

These files contains events that describe database changes such as table creation operations or changes to table data. You may specify their name by –log-bin[=base_name] option. Default name is value of pid-file option (defaults to hostname of server).

relay log files :

These files contains events read from the binary log of the master and written by the slave I/O thread. Events in the relay log are executed on the slave as part of the SQL thread.

master.info :

This file contains the status and current configuration information for the slave’s connectivity to the master.

relay-log.info :

This file holds the status information about the execution point within the slave’s relay log.

.index :

To keep track of which binary log files have been used, mysqld creates a binary log index file. It contains the names of all used binary log files. You may change it by –log-bin-index[=file_name] option otherwise basename will be same as that of binary log file with default extension “.index”.
In case of replication, relay logs uses .index files for same purpose. The default relay log index file name is host_name-relay-bin.index in the data directory. You may change the name by –relay-log-index option.

.TMD:

This is an intermediate data file for a table that needs to recreate its data file. You can find this files normally during REPAIR TABLE operations.

TRG & TRN Files:

Trigger definitions are stored in plain text files in the directory that contains the schema objects.
The file tablename.TRN is the TRIGGERNAME file stored under database directory.
The file tablename.TRG is the TRIGGERS file. It represents all the table triggers attached to a given table, so this file can contain triggers for multiple events (BEFORE/AFTER, INSERT/UPDATE/DELETE).

.ARZ, .ARM & .ARN files:

These are files related to Archieve Storage Engine.

.ARZ is data files holds data for table, .ARM holds metadata while during table optimization process a .ARN file may appear.

27 Mar

Earth Hour March 27 2010-India-Switch off lights for future

Earth Hour is a global event organized by WWF (World Wide Fund for Nature). Earth Hour held on the last Saturday of March annually, asking households and businesses to turn off their non-essential lights and other electrical appliances for one hour to raise awareness towards the need to take action on climate change.

In 2007, 2.2 million residents of Sydney participated by turning off all non-essential lights and in 2008 many other cities around the world adopted the event in 2008.

In India during Earth Hour 2009, almost five million Indians across 56 cities switched off their lights for an hour to contribute towards the action for climate change.
Earth hour saved almost 1000 MW of power! Celebrities and cricketers including Aamir Khan, Sachin Tendulkar and Anil Kumble have supported the cause with Abhishek Bachchan pledging his support as the brand ambassador.

Earth Hour 2010 will take place on March 27, 2010 from 8:30 p.m. to 9:30 p.m., local time.

Earth Hour 2010 is reportedly on track to become the largest Earth Hour yet, aiming to garner more than the one billion participant goal of 2009′s Earth Hour.

121 countries have signed up for Earth Hour 2010.

Show your support:

New economic modelling indicates the world has only 5 years to initiate a low carbon industrial revolution before runaway climate change becomes almost inevitable. But it can be done, and the long term benefits will be enormous.

So now’s the time to take a stand and give world leaders the mandate they need to make the right climate deal.

[ad#ad-2-300x250]

This year, India – IHG Hotels Group Support the Earth Hour Cause by encouraginh in-house guests to switch off lights in their rooms and join the “guestogether” parties being hosted by them between 8:30 and 9:30pm.

Lets start making difference-

Switch off all unnecessary lights @ 8.30 on 27th March

and make the Earth Hour a success towards our a better future.

-- Kedar Vaijanapurkar --