#1. Install Perl DBI::DBD module
(You may need to remove any installed older versions of perl-DBI-mysql package)
Several ways to install Perl modules.
1. Download source and compile;
2. Use perl -MCPAN -e 'install DBD::mysql';
3. Use 'cpan';
For more details about installing Perl modules, please visit here.
I pick 3, use cpan.
cpan DBI
cpan DBD::mysql
(sample run of cpan File::HomeDir)
[root@ipc4 ~]# cpan File::HomeDir
CPAN: Storable loaded ok
Going to read /home/shan/perl/.cpan/Metadata
Database was generated on Tue, 15 Dec 2009 22:06:58 GMT
Running install for module File::HomeDir
Running make for A/AD/ADAMK/File-HomeDir-0.88.tar.gz
CPAN: LWP::UserAgent loaded ok
Fetching with LWP:
ftp://cpan-du.viaverio.com/pub/CPAN/authors/id/A/AD/ADAMK/File-HomeDir-0.88.tar.gz
CPAN: Digest::MD5 loaded ok
Fetching with LWP:
ftp://cpan-du.viaverio.com/pub/CPAN/authors/id/A/AD/ADAMK/CHECKSUMS
Checksum for /home/shan/perl/.cpan/sources/authors/id/A/AD/ADAMK/File-HomeDir-0.88.tar.gz ok
Scanning cache /home/shan/perl/.cpan/build for sizes
File-HomeDir-0.88/
...
CPAN.pm: Going to build A/AD/ADAMK/File-HomeDir-0.88.tar.gz
Checking if your kit is complete...
Looks good
...
Appending installation info to /usr/lib/perl5/5.8.5/i386-linux-thread-multi/perllocal.pod
/usr/bin/make install -- OK
#2. Use instmodsh utility to manage installed modules
(if instmodsh doesn't show all installed modules, use perldoc perllocal or check perllocal file directly to see the installed modules)
root@drbl-01 DBD-mysql-4.010]# instmodsh
Available commands are:
l – List all installed modules
m – Select a module
q – Quit the program
cmd? l
Installed modules are:
CGI
Cwd
DBD::mysql
DBI
ExtUtils::MakeMaker
FCGI
File::HomeDir
HTML::Parser
IO
Perl
Test::Simple
mod_perl2
cmd? m DBI
Available commands are:
f [all|prog|doc] - List installed files of a given type
d [all|prog|doc] - List the directories used by a module
v - Validate the .packlist - check for missing files
t
q - Quit the module
DBI cmd?
Available commands are:
f [all|prog|doc] - List installed files of a given type
d [all|prog|doc] - List the directories used by a module
v - Validate the .packlist - check for missing files
t
q - Quit the module
DBI cmd? d
all directories in DBI are:
/usr/bin
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/Bundle
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Gofer/Policy
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/Gofer/Transport
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/Const
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/Const/GetInfo
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/DBD
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/Gofer
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/Gofer/Serializer
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/Gofer/Transport
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/ProfileDumper
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/SQL
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBI/Util
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/Win32
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBI
/usr/share/man/man1
/usr/share/man/man3
You can us 't' command to create a tarball to install modules on those same arch servers that don't have access to the Internet directly (e.g. behind firewall).
#3. Grant user privilege to access database
[root@ipc4 perl]# mysql -u root -p
mysql > grant all privileges on database-name.* to 'user'@'localhost' identified by 'password';
#4. Create a testing database with two tables
[shan@ipc4 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 94
Server version: 5.1.39-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database bestbuy;
Query OK, 1 row affected (0.00 sec)
mysql> use bestbuy;
Database changed
mysql> create table location(loc_id integer,
-> name varchar(50),
-> address varchar(60),
-> city varchar(20),
-> state char(2),
-> zipcode char(5));
Query OK, 0 rows affected (0.07 sec)
mysql> create table purchase
-> (item_id integer not null,
-> item_name varchar(30) not null,
-> purchase_date datetime,
-> loc_id integer,
-> unit_price decimal(8,2),
-> quantity decimal(6,2),
-> amount decimal(8,2),
-> comment varchar(100));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into location(loc_id,name,city,state) values(1,'BestBuy','Pasadena','CA');
Query OK, 1 row affected (0.01 sec)
mysql> insert into location(loc_id,name,city,state) values(1,'BestBuy','Duarte','CA');
Query OK, 1 row affected (0.00 sec)
mysql> insert into location(loc_id,name,city,state) values(1,'BestBuy','Sierra Madre','CA');
Query OK, 1 row affected (0.00 sec)
mysql> describe purchase;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| item_id | int(11) | NO | | NULL | |
| item_name | varchar(30) | NO | | NULL | |
| purchase_date | datetime | YES | | NULL | |
| loc_id | int(11) | YES | | NULL | |
| unit_price | decimal(8,2) | YES | | NULL | |
| quantity | decimal(6,2) | YES | | NULL | |
| amount | decimal(8,2) | YES | | NULL | |
| comment | varchar(100) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> insert into purchase(item_id,item_name,purchase_date,loc_id,unit_price,quantity,amount,comment)
-> values(1,'Nitendo DJHero','2009-12-15 19:15:00',1,100,1,108,'Prepare for a DJ job at a LA club.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into purchase(item_id,item_name,purchase_date,loc_id,unit_price,quantity,amount,comment) values(1,'Nitendo Wii','2009-12-15 19:15:00',1,199,1,218,'Prepare for a DJ job at a LA club.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into purchase(item_id,item_name,purchase_date,loc_id,unit_price,quantity,amount,comment) values(1,'MacBookPro','2009-12-15 19:15:00',1,1699,1,1810,'Prepare for a DJ job at a LA club.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into purchase(item_id,item_name,purchase_date,loc_id,unit_price,quantity,amount,comment) values(2,'Samsung LN55B650','2009-12-15 19:15:00',1,199,1,2150,'Prepare for a DJ job at a LA club.');
Query OK, 1 row affected (0.00 sec)
mysql> insert into purchase(item_id,item_name,purchase_date,loc_id,unit_price,quantity,amount,comment) values(3,'Samsung LN55iB8000','2009-12-15 19:15:00',1,1999,1,2150,'Prepare for a DJ job at a LA club.');
Query OK, 1 row affected (0.00 sec)
mysql> select * from location;
+--------+---------+---------+--------------+-------+---------+
| loc_id | name | address | city | state | zipcode |
+--------+---------+---------+--------------+-------+---------+
| 1 | BestBuy | NULL | Pasadena | CA | NULL |
| 1 | BestBuy | NULL | Duarte | CA | NULL |
| 1 | BestBuy | NULL | Sierra Madre | CA | NULL |
+--------+---------+---------+--------------+-------+---------+
3 rows in set (0.00 sec)
mysql> select * from purchase;
+---------+--------------------+---------------------+--------+------------+----------+---------+------------------------------------+
| item_id | item_name | purchase_date | loc_id | unit_price | quantity | amount | comment |
+---------+--------------------+---------------------+--------+------------+----------+---------+------------------------------------+
| 1 | Nitendo DJHero | 2009-12-15 19:15:00 | 1 | 100.00 | 1.00 | 108.00 | Prepare for a DJ job at a LA club. |
| 1 | Nitendo Wii | 2009-12-15 19:15:00 | 1 | 199.00 | 1.00 | 218.00 | Prepare for a DJ job at a LA club. |
| 1 | MacBookPro | 2009-12-15 19:15:00 | 1 | 1699.00 | 1.00 | 1810.00 | Prepare for a DJ job at a LA club. |
| 2 | Samsung LN55B650 | 2009-12-15 19:15:00 | 1 | 199.00 | 1.00 | 2150.00 | Prepare for a DJ job at a LA club. |
| 3 | Samsung LN55iB8000 | 2009-12-15 19:15:00 | 1 | 1999.00 | 1.00 | 2150.00 | Prepare for a DJ job at a LA club. |
+---------+--------------------+---------------------+--------+------------+----------+---------+------------------------------------+
5 rows in set (0.00 sec)
mysql> select item_id,item_name,purchase_date,loc_id,amount from purchase;
+---------+--------------------+---------------------+--------+---------+
| item_id | item_name | purchase_date | loc_id | amount |
+---------+--------------------+---------------------+--------+---------+
| 1 | Nitendo DJHero | 2009-12-15 19:15:00 | 1 | 108.00 |
| 1 | Nitendo Wii | 2009-12-15 19:15:00 | 1 | 218.00 |
| 1 | MacBookPro | 2009-12-15 19:15:00 | 1 | 1810.00 |
| 2 | Samsung LN55B650 | 2009-12-15 19:15:00 | 1 | 2150.00 |
| 3 | Samsung LN55iB8000 | 2009-12-15 19:15:00 | 1 | 2150.00 |
+---------+--------------------+---------------------+--------+---------+
5 rows in set (0.00 sec)
1 #!/usr/bin/perl -w
2
3 use DBI;
4
5 ## mysql user database name
6 $db ="bestbuy";
7
8 ## mysql database user name
9 $user = "shan";
10
11 ## mysql database password
12 $pass = "newyork1";
13
14 ## user hostname : This should be "localhost" but it can be diffrent too
15 $host="localhost";
16
17 # Get database handle
18 $dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass, {RaiseError=>1});
19
20 # 1. Use $dbh->do method to perform sql modification type operations
21
22 $results = $dbh->do(q{insert into purchase(item_id,item_name,purchase_date,loc_id,unit_price,quantity,amount,comment)
23 values(1,'SONY Laptop','2009-11-27 5:30:00',1,359,1,375,'Black Friday Bloody Reward')});
24 die "Unable to perform insert:$DBI::errstr\n" unless (defined $results);
25
26
27
28 # 2. Use $dbh->prepare/$dhb->execute methods to perform query type operations
29
30 $sth = $dbh->prepare(q{select * from purchase})
31
32 or die "Unable to prep our query:".$dbh->errstr."\n";
33
34 # sth is a statement handle retured by method prepare , DBI::st=HASH(0x8f7fbc4)->fetchrow_array()
35 my $rv = $sth->execute
36 or die "Unable to execute our query:".$dbh-errstr."\n";
37
38
39 # Output 2
40 print "The output of prepare-execute method sQL operation:\n";
41
42 while ( @row2 = $sth->fetchrow_array () ) {
43 my $temp2 = join "\t", @row2;
44 print " $temp2 \n";
45 }
Execution :
The above script insert record "SONY Laptop" and shows all records in purchase database.
The output of prepare-execute method sQL operation:
1 Nitendo DJHero 2009-12-15 19:15:00 1 100.00 1.00 108.00 Prepare for a DJ job at a LA club.
1 Nitendo Wii 2009-12-15 19:15:00 1 199.00 1.00 218.00 Prepare for a DJ job at a LA club.
1 MacBookPro 2009-12-15 19:15:00 1 1699.00 1.00 1810.00 Prepare for a DJ job at a LA club.
2 Samsung LN55B650 2009-12-15 19:15:00 1 199.00 1.00 2150.00 Prepare for a DJ job at a LA club.
3 Samsung LN55iB8000 2009-12-15 19:15:00 1 1999.00 1.00 2150.00 Prepare for a DJ job at a LA club.
1 SONY Laptop 2009-11-27 05:30:00 1 359.00 1.00 375.00 Black Friday Bloody Reward
Useful Hint for DBAs:
Tired of re-typing a long SQL command after making a typo?
Add the following line in your home directory's .inputrc file, you will be able to use vi style editing mode inside mysql shell.
e.g. ESC k to recall the last command you typed, or ESC j to travel down the command line history .. cheers!
set editing-mode vi
#6.Troubleshooting:
#a. My initial (failed) installation of DBD::mysql
#cpan DBD::mysql
Cannot find the file 'mysql_config'! Your execution PATH doesn't seem
not contain the path to mysql_config. Resorting to guessed values!
Can't exec "mysql_config": No such file or directory at Makefile.PL line 464.
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
Can't exec "mysql_config": No such file or directory at Makefile.PL line 464.
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
Can't exec "mysql_config": No such file or directory at Makefile.PL line 464.
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
#b. The root cause is it needs a file mysql_config which comes with MySQL-devel-community-5.x.x-0.rhel4.
Download it,
[shan@ipc4 rpm]$wget http://opensource.become.com/mysql/Downloads/MySQL-5.4/MySQL-devel-community-5.4.3-0.rhel4.i386.rpm
Or use curl,
Install it:
[root@ipc4 rpm]# rpm -ivh MySQL-devel-community-5.4.3-0.rhel4.i386.rpm
Note, make sure you download the correct dev package to match your MySQL server.
#c. Reinstall DBD::mysql
[root@ipc4 perl]# cpan DBD::mysql
CPAN: Storable loaded ok
Going to read /home/shan/perl/.cpan/Metadata
Database was generated on Tue, 15 Dec 2009 22:06:58 GMT
Running install for module DBD::mysql
Running make for C/CA/CAPTTOFU/DBD-mysql-4.013.tar.gz
CPAN: Digest::MD5 loaded ok
Checksum for /home/shan/perl/.cpan/sources/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.013.tar.gz ok
Scanning cache /home/shan/perl/.cpan/build for sizes
DBD-mysql-4.013/
cflags (mysql_config) = -I/usr/include/mysql -g -pipe -m32 -DUNIV_LINUX
embedded (mysql_config) =
libs (mysql_config) = -rdynamic -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lmygcc
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (guessed ) = 0
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testsocket (default ) =
testuser (guessed ) = root
….
You may get errors like below, that’s non-critical, it merely complains the DBD::mysql test can’t access the ‘test’ database with root account and password “NO”. You can always manually force the installation by entering the working directory ~.CPAN/build/module_name/ and type “make install”.
nt.t line 15
t/80procs...................skipped
all skipped: ERROR: Access denied for user 'root'@'localhost' (using password: NO). Can't continue test
t/85init_command............skipped
all skipped: ERROR: Access denied for user 'root'@'localhost' (using password: NO). Can't continue test
Failed Test Stat Wstat Total Fail Failed List of Failed
-------------------------------------------------------------------------------
t/00base.t 2 512 6 12 200.00% 1-6
35 tests skipped.
Failed 1/36 test scripts, 97.22% okay. 6/6 subtests failed, 0.00% okay.
make: *** [test_dynamic] Error 255
/usr/bin/make test -- NOT OK
Running make install
make test had returned bad status, won't install without force
Manually install:
[root@ipc4 DBD-mysql-4.013]# make install
Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mysql.so
Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mysql.bs
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql.pm
Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql/GetInfo.pm
Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql/INSTALL.pod
Installing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/Bundle/DBD/mysql.pm
Installing /usr/share/man/man3/DBD::mysql::INSTALL.3pm
Installing /usr/share/man/man3/Bundle::DBD::mysql.3pm
Installing /usr/share/man/man3/DBD::mysql.3pm
Writing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/.packlist
Appending installation info to /usr/lib/perl5/5.8.5/i386-linux-thread-multi/perllocal.pod
#d. Verify the installation:
[root@ipc4 ~]# instmodsh
Available commands are:
l - List all installed modules
m
q - Quit the program
cmd? l
Installed modules are:
DBD::mysql
DBI
Perl
mod_perl2
cmd? m DBD::mysql
Available commands are:
f [all|prog|doc] - List installed files of a given type
d [all|prog|doc] - List the directories used by a module
v - Validate the .packlist - check for missing files
t
q - Quit the module
DBD::mysql cmd? d
all directories in DBD::mysql are:
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/Bundle/DBD
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql
/usr/share/man/man3
DBD::mysql cmd? v
DBD::mysql has no missing files
DBD::mysql cmd? f
all files in DBD::mysql are:
/usr/share/man/man3/DBD::mysql::INSTALL.3pm
/usr/share/man/man3/DBD::mysql.3pm
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql/INSTALL.pod
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql.pm
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mysql.bs
/usr/share/man/man3/Bundle::DBD::mysql.3pm
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/DBD/mysql/GetInfo.pm
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mysql.so
/usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/Bundle/DBD/mysql.pm
DBD::mysql cmd? q
cmd? q
[root@ipc4 ~]#
#e. Clean up:
If your server is installed RHEL 4.0 and it already has MySQL, Perl installed, you may need to do some cleaning work to make sure you are using the correct versions of the package for each components. The sooner you do the cleaning job, the better. Once you installed/configured Perl, MySQL, Python, PHP, etc, etc, you find out you need to upgrade a package that’s required by 100 packages, you will feel the pain. That’s why platform engineering is very important for open source technology.
On my system, I have RHEL 4.0, which installed very early version of mysqlclient and perl-DBD-mysql. In fact, the old version perl-DBD-mysql-2.9xx is not compatible with MySQL server 5.x. I need to remove all those obsolete packages without damaging the system.
remove mysqlclient10-3.23 and its dependencies:
[root@ipc4 rpm]# rpm -qa | grep mysql
mysqlclient10-3.23.58-4.RHEL4.1
[root@ipc4 rpm]# rpm -ql $(rpm -qa | grep mysql)
/etc/ld.so.conf.d/mysqlclient10-i386.conf
/usr/lib/mysql/libmysqlclient.so.10
/usr/lib/mysql/libmysqlclient.so.10.0.0
/usr/lib/mysql/libmysqlclient_r.so.10
/usr/lib/mysql/libmysqlclient_r.so.10.0.0
[root@ipc4 rpm]# rpm -e $(rpm -qa | grep mysql)
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.9004-3.1.i386
libmysqlclient.so.10 is needed by (installed) MySQL-python-1.0.0-1.RHEL4.1.i386
libmysqlclient.so.10 is needed by (installed) MyODBC-2.50.39-21.RHEL4.1.i386
mysqlclient10 is needed by (installed) MySQL-python-1.0.0-1.RHEL4.1.i386
[root@ipc4 rpm]# rpm -e MySQL-python-1.0.0-1.RHEL4.1.i386
[root@ipc4 rpm]# rpm -e MyODBC-2.50.39-21.RHEL4.1.i386
[root@ipc4 rpm]# rpm -e MySQL-python-1.0.0-1.RHEL4.1.i386
also, remove the old perl-DBD-MySQL
[root@ipc4 rpm]# rpm -e perl-DBD-MySQL-2.9004-3.1.i386
