Geeks-Plug-ins

How to change the engine type of a table : >alter table reportsTransfer engine= innodb ; How to add foreign key to a table : >ALTER TABLE tbl_name ADD [CONSTRAINT symbol ] FOREIGN KEY [ index_name ] ( index_col_name , ...) REFERENCES tbl_name ( index_col_name , ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] e.g >alter table reportsTransfer add foreign key reportsId (reportsInsertedId ) references reportsInserted (id) Note > Before adding a foreign key to table make sure both the tables ( Reference table and table having foreign key ) engine type = InnoDb . >InnoDB does not check foreign key constraints on those foreign key or referenced key values that contain a NULL column. >Currently, triggers are not activated by cascaded foreign key actions. Explain doesn't works with Delete and Update statement.

> show create table tablename ;

1. root user can grant privileges to other user so you need to login as mysql -u , before granting privileges ... GRANT ALL PRIVILEGES ON database.* to cruncher@localhost identified by "password" 2. create database IF NOT EXISTS indiagATpincampaignsDOTcom ; ERROR 1044 (42000): Access denied for user 'cruncher'@'localhost' to database 'indiagDOTcom' Reason : I did ... " grant all privileges on *.* to cruncher identified by "password" ; " instead of grant all privileges on *.* to cruncher@localhost identified by "password" ;

Problem: A uncompression module ( IO::Uncompress::AnyUncompress ) started giving problem in my new machine. which was working fine on old server. Solution: Installed IO::Uncompress::Unzip qw(unzip $UnzipError) from cpan and used, use IO::Uncompress::Unzip qw(unzip $UnzipError) ; unzip $infileName => $outfileName or die "unzip failed: $UnzipError\n"; which worked fine. Original machine (Server ): Linux dev 2.6.20-16-server #2 SMP Tue Feb 12 05:48:21 UTC 2008 i686 GNU/Linux ( uname -a) New machine (Server): Linux datacruncher 2.6.24-16-server #1 SMP Thu Apr 10 13:58:00 UTC 2008 i686 GNU/Linux

Step 1. Export SQL result in to an csv file Step 2. Report Validation ( Through Excel ) ------------- The lifeline for any business intelligence solution is the process called extract, transform, and load (ETL). According to a recent survey by The Data Warehousing Institute (TDWI), ETL design and development work consumes 60 to 80 percent of the resources of an entire BI project. Selecting an ETL solution that fits your needs is an essential first step in building a successful data warehouse.

CSV File : sudo echo " select * from accountMaster" | mysql -u username -p database > accounts.txt; TEXT FILE: sudo echo " select * from accountMaster" | mysql -u username -p database > accounts.txt; MAIL : sudo echo "select * from campaignMaster" | mysql -u username -p database > "campaigns.txt" | mail xyz@pintoodomain.com :-)

Error: DBI connect('database :','username',...) failed: Access denied for user 'username'@'localhost' (using password: YES) at COMPLETE_PATH_PROGRAM.pm line 41 couldn't connect to database: Access denied for user 'username'@'localhost' (using password: YES) at COMPLETE_PATH_PROGRAM.pm line 41.. Explanation: This error occurred because password provided to MySql by perl was containing special characters ..like '@' with in double quotes (which looked like password ="@ll3" ) any special char mentioned inside in double quotes is converted to its original. so I changed it to password ='@ll3' to resolve the issue. any string inside " " looks for phrase match. any string inside ' ' looks for exact match.