Webmaster Tasks

Import CSV Data File directly into MySQL Table

Recently while working on a travel portal, I needed to import huge csv data into MySQL database. Apart from the importing data into its tables, there was another problem. how to upload some 650Mb huge data file of sql dump to server for importing.

The trick is that download the csv directly on your server using the wget command via shell. Once it’s on the server, you can easily import the csv data file directly into MySQL tables.

Step 1: Putting the huge CSV file on Server

Login to shell and get to the project directory where you want to place the csv files. Type the following command to get the data from remote server.

root@server19424 [/home/user]# wget http://path/to/your/csv/file.csv

Step 2: Create destination table in the Database

This is straight forward task, you just need to create the table with matching fields for data import. Make sure you choose the correct field type to hold the data.

BTW, if you do not know how to create tables, you should not be reading this post!

Step 3: Import CSV data into MySQL Table

Now as a last step, login to Shell and get to the mysql prompt. First select your database to use and then type the following command:

mysql> load data local infile 'my_csv_data.csv' into table my_DB.my_csv_table fields terminated by ',' enclosed by '"'
 lines terminated by '\r\n' IGNORE 1 LINES (hotel_id, review_count, review_score);
Query OK, 364111 rows affected, 52774 warnings (0.85 sec)
Records: 364111  Deleted: 0  Skipped: 0  Warnings: 0

Now go and check your table and you will hopefully see your records there!

p.s: If your csv file does not first row as columns names, then remove the IGNORE 1 LINES from the above command.

Hope that helps!

How to Start & Stop Passenger in Production Environment for Rails Apps

You can tell passenger to start the application in production environment with following command on shell by going to the app folder first. I wanted to use Passenger for Redmine app.

Start Passenger

Log into shell and go to app directory and type the following command:

root@server [/home/redmine]# passenger start -a -p 3000 -d -e production

In the above command, 3000 is the port where server listens to the requests, and tells the server to listen to all IP addresses. If you remove it, you will only be able to access it from localhost.

Stop Passenger

Login to shell and type following command:

root@server [/home/redmine]# passenger stop -p 3000

Hope that helps.

Logging PHP $_POST, $_FILES data for Debugging


Debugging is part and parcel of development and especially now applications getting complex with the advent of mobile apps development, developers need to log Form submitted $_POST and $_FILES data. Here’s a useful snippet to log the posted data that you can place in your PHP script to help you identify the problematic part.

Jut create the /logs/ directory along side the file where you include the code:

if ( isset($_POST) && is_array($_POST) && count($_POST) > 0 ) {
  $log_dir = dirname( __FILE__ ) . '/logs/';
  $log_name = "posts-" . $_SERVER['REMOTE_ADDR'] . "-" . date("Y-m-d-H") . ".txt";
  $log_entry = gmdate('r') . "\t" . $_SERVER['REQUEST_URI'] . "\r\n" . serialize($_POST) . "\r\n\r\n";
  $fp=fopen( $log_dir . $log_name, 'a' );
  fputs($fp, $log_entry);
  fclose($fp); }
if ( isset($_FILES) && is_array($_FILES) && count($_FILES) > 0 ) {
  $log_dir = dirname( __FILE__ ) . '/logs/';
  $log_name = "posts-" . $_SERVER['REMOTE_ADDR'] . "-" . date("Y-m-d-H") . ".txt";
  $log_entry = gmdate('r') . "\t" . $_SERVER['REQUEST_URI'] . "\r\n" . serialize($_FILES) . "\r\n\r\n";
  $fp=fopen( $log_dir . $log_name, 'a' );
  fputs($fp, $log_entry);
  fclose($fp); }

Hope that helps.

Installing libphp2 for PHP over Joyent SmartMachine Server

On a Joyent server, I needed to install the libphp2 for PHP on SmartMachine so that WordPress can use SSH in your PHP code, but when I tried following command to install package, it gave followign error:

[root@server]# pecl channel-update pear.php.net
bash: pecl: command not found

This error can be resolved by doing following:

[user@server]# su -

Just make sure you do “su -”, so that a full environment gets initialized rather than just the . If you do just get a limited PATH if you do simple ‘su root’.

To install libphp2 for PHP on your joyent SmartMachine, do foloowing:

These commands should be run as root user:

[root@server]#pkgin in libssh2
[root@server]#pkgin in autoconf
[root@server]#pecl channel-update pear.php.net
[root@server]#pecl install -f ssh2

As a final step, you should add “extension=ssh2.so” to php.ini.

You can do that easily from your Webmin, just log in to Webmin and navigate to Webmin – Others – PHP Configuration – Edit Manually.

Hope that helps.


Copying MySQL Database From One Server To Another Remote MySQL DB Server

Copying MySQL databases from one server to other remote machine is task every webmaster has to do very often if not daily, and if your databases are huge, following command comes very handy to do this job without first downloading and then uploading the dump file.

Also, phpMyAdmin and other MySQL clients fail while importing big dump files due to timeout issues.

Following MySQL command basically pipes the data from mysqldump from one server directly into the other database instead of just dumping it into a file and then manually uploading it to the other server . ALso the uploading may stop if the file is too big.

The first host is FROM where you want to copy and destination is where you want to copy TO. Login to shell using SSH and run the following command by providing the DB name and DB user name and password of both servers.


You can also add number of other options in this above MySQL command from here.

You may get following error on running the command from shell.

ERROR 1130 (00000): Host 'x.x.x.x' is not allowed to connect to this MySQL server
mysqldump: Got errno 32 on write

If you get above error, you need to allow the dumping MySQL server IP in the destination server. In cPanel, you can easily do that from “Remote Database Access Hosts” manager. Just add the dumping Dsevrer Ip there and that will do the job.

Hope that helps.


Error Uncompressing tar.gz file tar: Archive contains obsolescent base-64 headers

For transferring one website from one server to another, I had to take the backup. usually this is not a big issue, but this time this machine was 64 bit Spark machine and the new server was a 32 bit GNU machine. While uncompressing, I got the following error:

root@server # tar -xvf file.tar.gz 
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Archive contains obsolescent base-64 headers
tar: Error exit delayed from previous errors

This error happens when you create the tar-ball on 64 bit Spark machine and then trying to untar on a 32 bit GNU machine, if this is the case then solution is following, you first need to first

gzip -d file.tar.gz
#and then do 
tar -xf file.tar

And it will extract without errors.

Hope that helps. Cheers!

Redirecting Sub-Directories and All URLs Under it to the Root or to other Directory via htaccess

There are times when you want to change your website directory structure for the latest directory structure on the request of your SEO team or for some other reason, and using 301 redirects in apache’s .htaccess, this can be done very quickly and efficiently.

Here are some scenarios that I have encountered and used .htaccess redirects to good effect to get the desired result.

Redirect Sub-Directory and All URLs under it to Home Page

Open your root .htaccess file and place the following lines. It will redirect all URL requests under “directory-name” directory to the home page of your domain. Make sure you replace with the actual name of your website.

RewriteEngine On 
RewriteRule ^directory-name/.*$ http://www.domain.com/ [R=301,L]

Redirect Sub-Directory and All URLs under it to Some Other Directory

Open your root .htaccess file and place the following lines. It will redirect all URL requests under “directory-name” directory to the “new-directory” on your website. Make sure you replace with the actual name of your website with the domain.

RewriteEngine On 
RewriteRule ^directory-name/(.*)$ http://www.domain.com/new-directory/$1 [R=301,L]

Hope that helps.


Installing ffmpeg PHP Extension on Joyent Smart Machine Server

For a Drupal based website to work properly, you need FFmpeg installed on server.

Before going any further, please note that FFmpeg and FFmpeg PHP extension are two different things (distinct software). While FFmpeg is available as a package on Joyent Smart Machine, however you’ll need to download the FFmpeg extension package from the http://ffmpeg-php.sourceforge.net/ and install it yourself.

Please also note that you do not need to install FFmpeg PHP Extension to make Drupal working for videos, FFmpeg command line will do the job for you. Check the Drupal documentation for configuring that.

You can easily check if the FFmpeg is installed or not on your Joyent Smart Machine by using:

[user@server ~]$ ffmpeg -version

If you see following (or similar) output, FFmpeg is installed on your Smart Machine server:

FFmpeg version SVN-r19146-snapshot, Copyright (c) 2000-2009 Fabrice Bellard, et al.
  configuration: --enable-avfilter --enable-avfilter-lavf --enable-postproc --enable-libvorbis --enable-libmp3lame --enable-libfaad --disable-libopencore-amrnb --disable-libopencore-amrwb --enable-libtheora --enable-libxvid --enable-libx264 --mandir=/opt/local/man --cc=gcc --prefix=/opt/local --disable-debug --disable-stripping --enable-shared --enable-pthreads --enable-gpl --disable-amd3dnow --disable-amd3dnowext --disable-mmx --disable-mmx2 --disable-sse --disable-ssse3
  libavutil     50. 3. 0 / 50. 3. 0
  libavcodec    52.30. 2 / 52.30. 2
  libavformat   52.34. 0 / 52.34. 0
  libavdevice   52. 2. 0 / 52. 2. 0
  libavfilter    0. 5. 0 /  0. 5. 0
  libswscale     0. 7. 1 /  0. 7. 1
  libpostproc   51. 2. 0 / 51. 2. 0
  built on Jul 13 2010 13:50:14, gcc: 4.4.2
FFmpeg SVN-r19146-snapshot
libavutil     50. 3. 0 / 50. 3. 0
libavcodec    52.30. 2 / 52.30. 2
libavformat   52.34. 0 / 52.34. 0
libavdevice   52. 2. 0 / 52. 2. 0
libavfilter    0. 5. 0 /  0. 5. 0
libswscale     0. 7. 1 /  0. 7. 1
libpostproc   51. 2. 0 / 51. 2. 0

For installing ffmpeg PHP Extension on Joyent Smart Machine, use the following steps and commands.

Login to your Joyent server using SSH and do following:

First of all, make sure things are up to date:

type the following commands:

[user@server ~]$ pkgin up

It will show message

database for http://pkgsrc.joyent.com/2010Q2/All is up-to-date

Make sure ffmpeg is installed by running:

[user@server ~]$ sudo pkgin in ffmpeg
Password: provide yuor account password

You should see following output:

calculating dependencies for ffmpeg...
nothing to upgrade.
8 packages to be installed: libogg-1.2.0 xvidcore-1.2.2 x264-devel-20090920 libvorbis-1.3.1 libtheora-1.1.1nb1 lame-3.98.3 faad2-2.7nb1 ffmpeg-20090611nb8 (7995K to download, 21M to install)

When prompted, type ‘y’:

proceed ? [y/N] y

That should start downloading the above mentioned packages and install them. You will see following messages:

downloading packages...
downloading libogg-1.2.0.tgz: 100%
downloading xvidcore-1.2.2.tgz: 100%
downloading x264-devel-20090920.tgz: 100%
downloading libvorbis-1.3.1.tgz: 100%
downloading libtheora-1.1.1nb1.tgz: 100%
downloading lame-3.98.3.tgz: 100%
downloading faad2-2.7nb1.tgz: 100%
downloading ffmpeg-20090611nb8.tgz: 100%
installing packages...
installing libogg-1.2.0...
installing xvidcore-1.2.2...
installing x264-devel-20090920...
installing libvorbis-1.3.1...
installing libtheora-1.1.1nb1...
installing lame-3.98.3...
installing faad2-2.7nb1...
installing ffmpeg-20090611nb8...
ffmpeg-20090611nb8: copying /opt/local/share/examples/ffmpeg/ffserver.conf to /opt/local/etc/ffserver.conf
processing local summary...
updating database: 100%
marking ffmpeg-20090611nb8 as non auto-removable

Now, install autoconf using:

sudo pkgin in autoconf

Now, download the latest version of ffmpeg-php to your SmartMachine using:

curl -L -O http://sourceforge.net/projects/ffmpeg-php/files/ffmpeg-php/0.6.0/ffmpeg-php-0.6.0.tbz2

Now Uncompress it using:

gtar -xjf ffmpeg-php-0.6.0.tbz2

Now build and install it using:

cd ffmpeg-php-0.6.0
./configure && make
sudo make install

Now you need enable the extension by adding it to /opt/local/etc/php.ini. Open the file and after the list of extensions (extention=*) add following:

extension=ffmpeg.so to /opt/local/etc/php.ini

and save the file. Now restart Apache to enable it using:

svcadm restart apache

Hope that helps.


Setting up osTicket Email Piping on Joyent Smart Machine Server

Setting up osTicket on cPanel is easy and somewhat in Plesk as well, especially the email piping part as they have in depth articles on their wiki. But as soon as you try to set up email piping other than cPanel hosting and Plesk hosting environments, you are in the uncharted waters. Recently, I had to set up osTicket support system on Joyent Smart Machine server and this is how I got it done.

Inside Joyent Smart Machine hosting Admin Panel, you should be able to create mail aliases using Postfix Mail Server that will direct the e-mails to a /home/user-account/web/public/osTicket/api/pipe.php script.

Go to Virtualmin > Your Server > Postfix Mail Server > Edit Mail Aliases > Add an alias
and provide information for these following fields:

Description, Address(email), Enabled? Yes/No and Alias to select “Feed to program ..” from the drop-down, and in the space provided, provide full path to the osTicket mail piping script /home/user-account/web/public/osTicket-dir/api/pipe.php

Make sure that you set the executable permissions (chmod 755) on pipe.php otherwise it might not run.

Also, If you were to make the directories leading up to your script executable, you could also rule out permission issue by something like:

chmod +x /home/user-account/web/public/osTicket-dir/api/

Hope that helps.


[MySQL] Enabling MySQL Query Cache to Speed Up Query Performance

If you ever have managed some website/portal that has any meaningful traffic, you already know what can it do to your MySQL server performance if you don’t optimise your queries and enable php server side caching. One of the things that you can do at DB level is to enable mysql_query_cache to speed up DB performance.

A Good or not so good thing about mysql_query_cache is that the cache expires automatically once the table is modified (inserts, updates, delete, etc). If there are these operations are veing performed in every table in your website, it might not help much but it can’t harm your website either so go ahead and just enable the cache and see what difference it makes.

How to Enable mysql_query_cache

To enable mysql_query_cache, you need to edit your my.cnf and set query_cache_type to 1, and set the query_cache_size to some value. This is set it to 64Mb in the following example. Default MySQL location for this file is /etc/my.cnf on your Linux server.

How to Check if mysql_query_cache is Enabled Already

mysql> show variables like 'query%';

You will see something like this:

| Variable_name                | Value   |
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 67108864|
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |

Please give special attention to query_cache_type variable which should be set to ON after making my.cnf changes, the query_cache_size variable should not be set to zero. Zero value means your query cache is practically disabled since it has no memory at its disposal to use.

When Enabling mysql_query_cache Can Help Most

Following are conditions which can best exploit the benefits of the MySQL query cache, although the query cache can be effective in most situations, but it will help greatly in following cases:

  1. Identical queries are issued by the same or multiple clients on a repetitive basis.
  2. The underlying data being accessed is static or semi-static in nature.
  3. Queries have the potential to be resource-intensive and/or build brief, but complexly computed result sets.

Hope that helps.


Page 1 of 3123