MySQL - Convert MySQL date, datetime, timestamp to unix time and Vice versa

&& To convert Unix time to regular MySQL DATETIME
select from_unixtime('1213907115');
It prints '2008-06-19 16:25:15'


&& To Convert DATETIME to unixtime
select unix_timestamp('2008-06-19 16:25:15');
It prints 1213907115


&& To Convert current date & time to unix time.
select unix_timestamp();


&& To Convert a column of DATETIME type to unixtime.
select unix_timestamp(c8013_date_created) from RealTimeStatus.T8013_PROGRAM;

PERL - Hash, Hash reference and file handler passing to a subroutines

Fews days ago - One of my friends asked me about:
1. hashes in perl
2. hash references and how to use them
3. file handler passing to a subroutines....

Here is an example.....


#!/opt/local/bin/perl

use warnings;
use strict;

my $n_ref;
my $p_ref;


open(NAME, "./tns_names") or die " Failed to open tns_names file : $! \n ";
open(PASS, "./tns_pass") or die " Failed to open tns_pass file : $! \n ";

$n_ref = &process_names(*NAME);
$p_ref = &process_pass(*PASS);

&do_final($n_ref, $p_ref);

close NAME;
close PASS;


sub process_names($)
{
local *h = shift;

my %n;
my $nkey;
my $nval;

while()
{
chomp $_;
($nkey, $nval) = split(/,/, $_);
$n{$nkey} = $nval;
}
return \%n;
}


sub process_pass($)
{
local *h = shift;

my %p;
my $pkey;
my $pval;

while()
{
chomp $_;
($pkey, $pval) = split(/,/, $_);
$p{$pkey} = $pval;
}
return \%p;
}


sub do_final(@)
{
my ($nam, $pas) = @_;
my $akey;

foreach $akey (keys %$nam)
{
print 'Key :' . $akey . ' and Value: ' . $nam->{$akey} . "\n";
}

foreach $akey (keys %$pas)
{
print 'Key :' . $akey . ' and Value: ' . $pas->{$akey} . "\n";
}
}


1;

PERL - I/O Redirection Example

The following example shows how I/O can be simply redirected:

open(F, '>/tmp/x') || die;
*STDOUT = *F;
print "hello world\n";

The print function thinks it is sending the output to STDOUT but ends up sending it to the open file instead, because the typeglob associated with STDOUT has been aliased to F. If you want this redirection to be temporary, you can localize *STDOUT.

PERL - Passing Filehandles to Subroutines

#!/usr/bin/perl
use warnings;
use strict;

open(NAME, "./names") or die " Failed to open tns_names file : $! \n ";
open(PASS, "./pass") or die " Failed to open tns_pass file : $! \n ";

&process_names(*NAME);
&process_pass(*PASS);

close NAME;
close PASS;


sub process_names($)
{
local (*h) = shift;
while()
{
chomp $_;
print $_ . "\n";
}

}


sub process_pass($)
{
local (*h) = shift;
while()
{
chomp $_;
print $_ . "\n";
}
}

How to install ‘mysql-server’ community 5.1 and client on RHEL 5.0

How to install ‘mysql-server’ community 5.1 and client on RHEL 5.0

Step 1:

First step is to check if there are any version of mysql already installed. This is due to the fact that RHEL has some default packages available already. Do this.

rpm –qa |grep mysql
mysql-connector-odbc-3.51.12-2.2
MySQL-python-1.2.1-1
mysql-5.0.45-7.el5
perl-DBD-MySQL-3.0007-1.fc6
libdbi-dbd-mysql-0.8.1a-1.2.2
mysql-server-5.0.45-7.el5

Printed are a list of mysql server installed, clients and drivers like perl DBD driver, python driver and other stuff. Best idea is to get rid of all these dependencies and build it again. That can be done by

rpm –e < mysql-server-5.0.45-7.el5>
rpm –e < libdbi-dbd-mysql-0.8.1a-1.2.2
…..and so on.


Step 2:

Try installing MySQL server 5.1 from MySQL mirror website itself. It would automatically start the server once successfully installed. ‘root’ is the default user id with a blank password. Root’s password can be changed once client package is installed which includs mysql and mysqladmin and other administrative tools.

rpm –i http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-community-5.1.24-0.rhel5.i386.rpm/from/http://mirror.trouble-free.net/mysql_mirror/


Step 3:

Try installing MySQL clients packages which include administrative tools like mysql, mysqladmin, mysqldump and others…..

rpm –i http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-community-5.1.24-0.rhel5.i386.rpm/from/http://mirror.trouble-free.net/mysql_mirror/

Step 4:

Change root user password or simply execute /usr/bin/mysql_secure_installation. Answer all the questions appropriately. That’s it.

Step 5:

Check if mysql is running by doing: ps –eaf|grep mysqld

Also try connecting to it by doing.
mysql –uroot –p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.1.24-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysqlDB |
| test |
+--------------------+
4 rows in set (0.00 sec)

====== Now, you are all set =====================

DO NOT FORGET TO RE-INSTALL DRIVERS FOR CLIENTS LIKE PERL, PYTHON, PHP.

How to install 'MySQL' driver on Solaris platform

How to install 'MySQL' driver on Solaris platform:
-------------------------------------------------

STEP 1:

Download DBD-mysql-4.007.tar.gz http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm
by using wget tool from unix10/unix22 boxes because ftp/telnet/scp/sftp is prohibited.

STEP 2:

Copy this file into /tmp/mysql ( Create a temporary directory mysql under /tmp )

STEP 3:

gzip -d DBD-mysql-4.007.tar.gz

STEP 4:

tar xvf DBD-mysql-4.007.tar

STEP 5:

cd DBD-mysql-4.007


STEP 6:

perl Makefile.PL --libs="-L/usr/local/mysql/lib/mysql -lmysqlclient -lz" \
--cflags=-I/usr/local/mysql/include/mysql

STEP 7:

make
make install


STEP 8: mysql driver has been installed if 'make install' does not produce any errors.


STEP 9: How to verify if - mysql driver is installed.

Used this perl script to verify:

#!/opt/local/bin/perl -w

use strict;
use DBI;

$|=1;
my @x_drivers = DBI->available_drivers();
foreach (@x_drivers){ print $_ . "\n";}

__END__

How to install mtop for MySQL on RHEL 5.0 machine

How to install mtop for MySQL on RHEL 5.0 machine:

Step 1: Download
wget http://downloads.sourceforge.net/mtop/mtop0.6.6.tar.gz?modtime=1094947200&big_mirror=0

Step 2: unzip it.
gzip -d mtop-0.6.6.tar.gz

Step 3: Extract the files
tar xvf mtop-0.6.6.tar

NOTE: It will create a directory named ‘mtop-0.6.6’

Step 4: Install it.
mtop-0.6.6
perl Makefile.PL
sudo make
sudo make install


mtop is installed. If ‘which mtop’ is done, it will print
/usr/local/bin/mtop

Where to download mtop from - if unix/linux boxes can not connect to your windows

Folks,

For most of us, Unix/Linux (Dev/QA) environment is totally separate from our work machine which should be most likely windows boxes and basically users use putty, or something like that. What if network does not allow you to copy or ftp/sftp
anything from your windows box to Unix/Linux boxes.

Simply use this on your unix machine:
wget http://search.cpan.org/CPAN/authors/id/M/MP/MPREWITT/mtop/mtop-0.6.4.tar.gz

Convert character sets from UTF8 to Latin1 and Base64

Folks,
I came across this learning during some work where I have to convert some utf8 based characters from database to latin1 and some cases vice versa.....

Perl site has good demo of this. Check this out...

http://perldoc.perl.org/Encode/Encoder.html.

This can also convert Latin1 to base64 and vice versa....

Pretty cool stuff.