Delete from same table as select

August 23, 2012 Leave a comment

Simple code to delete from same table as when selecting from it. MySQL doesn’t allow you to delete from same table as you select.

So this is not possible:

DELETE FROM chess inner join tennis on chess.some_id = tennis.some_id where chess.some_col = ‘great’;

You will get an error saying: You can’t specify target table ‘chess’ for update in FROM clause.

So you need to create a temp table and then delete.

DELETE FROM chess where some_id in (select some_id from
(SELECT chess.some_id FROM chess inner join tennis on tennis.some_id = chess.some_id where some_col = ‘great’
) as y);

Done, easy.

Categories: Coding, MySQL

Cleaning tables of duplicates

August 15, 2012 Leave a comment

Cleaning a MySQL database called presschoice that has combined duplicate values candidate_id and press_id columns. The table only has 3 columns, presschoice_id (primary key, auto_increment), candidate_id (int), presschoice_id (int).

# first checking how many duplicates you have

SELECT * FROM presschoice p;
select
candidate_id,
press_id,
count(*) as n

from presschoice
group by candidate_id,press_id
having n > 1

# creating a temp table to store the distinct values
create temporary table presschoice_clean2 as SELECT distinct candidate_id,press_id FROM presschoice p;

# deleting the orginal values from the table. Make sure you have a backup!
delete from presschoice;

# add the new “clean” values.
insert into presschoice(candidate_id, press_id) select candidate_id,press_id from presschoice_clean2;

# also deleting any rogue data
delete from presschoice where candidate_id=0;

Looking into creating a Trigger so the duplicate values don’t go in. That maybe in next post!

Categories: Coding, MySQL Tags:

Learning about Nginx and PHP FPM

July 25, 2012 Leave a comment

So I wanted to learn about Nginx and PHP FPM as Nginx seems to be taking over from Apache as the HTTP server of choice. The reason I choose PHP-FPM is because it is a FastCGI with additional features like  fastcgi_finish_request(). This is a special function to finish request & flush all data while continuing to do something time-consuming, like video converting, which is becoming a norm on most sites.

The main thing to learn from this how Nginx and PHP-fpm can work with each other. They can either talk to each other via IP or a socket, here we are going to talk about how you can connect via  a socket.  Nginx like Apache have config files for each site (sites-enabled), and similarly PHP-FPM has a directory where you specify PHP configuration for each site on your webserver (pool.d). Depending on the config files bascially a socket file is created on the server to allow Nginx and PHP-fpm to “talk” to each other.

As I am learning all this from the start I’m including instructions on how to create your VM Ubuntu server. If you don’t need to know this, you can skip down to the Nginx and PHP-fpm code.

Virtual Machine & Installing nginx and PHP-fpm

To start from scratch you need a virtual machine, I prefer VM Player for my VMs. To get an image for the VM player you can go to Thought Police. I choose the latest Ubuntu server release – 12.04 (Precise Pangolin). Download the VM and have your VM player open it.

Running your VM for the first time, log into your server (normal username and password for thoughtpolice VMS are: notroot/thoughtpolice).

Run updates for Ubuntu:

sudo apt-get -y update; sudo apt-get -y upgrade; sudo reboot;

Then install the nginx and php-fpm and standard packages you need for a web server:

apt-get -y install php5-fpm php-pear php5-suhosin php5-dev php5-mysql php5-gd php5-curl curl lynx htop libpcre3-dev nginx g++ libinnodb3 php5-xsl php5-curl php5-imagick php5-mysql  php5-sqlite make –force-$

You can also install additional packages like APC, Memcache , Libmemcached (caching), Libevent (notifications) if needed.

Check nginx and php5-fpm daemons are running on startup:

chkconfig nginx on
chkconfig php5-fpm on

Create your web files directory and give it the right permissions

mkdir /var/www/; mkdir /var/www/htdocs;

chown -R www-data.www-data /var/www/

Nginx & PHP-fpm

Nginx configs are almost like Apache, you will find the Nginx folder in /etc/

/etc/nginx/

In Nginx directory you have the default config file – nginx.conf. I am going to leave this file as it is, the changes I am going to make are in the config files of each site on the server. Just like Apache you have sites-available and sites-enabled folders.

Lets create a site on our server, demo-one. Create a directory in your htdocs folder, demo-one.

mkdir /var/www/htdocs/demo-one

touch /var/www/htdocs/demo-one/index.php

nano /var/www/htdocs/demo-one/index.php
<?php echo “This is demo one!” ?>

Now create files on your nginx sites-available folder.

touch /etc/nginx/sites-available/demo-one

Time to add some config settings

server {

server_name demo-one.localhost;
root /var/www/htdocs/demo-one; ## <– Your only path reference.

error_log /var/log/nginx/demo-one.error.log info;

listen 80;

index index.php index.html index.htm;

location @rewrite {
# Some modules enforce no slash (/) at the end of the URL
# Else this rewrite block wouldn’t be needed (GlobalRedirect)
rewrite (.*) /index.php;
}

location ~ \.php$ {
fastcgi_split_path_info ^(.+\.php)(/.+)$;
#NOTE: You should have “cgi.fix_pathinfo = 0;” in php.ini
include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_pass unix:/var/run/fpm_core_dev.sock;
}
}

The important ones are server_name, root (where you site documents are), listen (listening on which port), index (which files and order to look for first), and location. You can have many instances/array of location files. The location settings we are interested in most is:

location ~ \.php$ {
fastcgi_split_path_info ^(.+\.php)(/.+)$;
#NOTE: You should have “cgi.fix_pathinfo = 0;” in php.ini
#include fastcgi_params;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_pass unix:/var/run/fpm_demo_one.sock;
}
}

This location means how to deal with every PHP page request that you get . E.g. article.php PHP request should be sent to the /path/to/php/article.php. The most important this we are concerned about is “fastcgi_pass unix:/var/run/fpm_core_dev.sock;”. This is the socket we talked about earlier. This is the socket that nginx will use to communicate with the php-fpm.

You can even have multiple sockets, e.g.

PHP-fpm

Now from the PHP-fpm side you need to create the directory where you will store the PHP-fpm connection settings.

In there you need to create a connection setting for each site. There should be a http://www.conf file already in the folder. Copy that file to another file in the same directory and rename it to demo_one.conf

The things we need to change in that file are:

[www] change to [demo_one]
listen = /var/run/fpm_demo_one.sock

The most important thing to remember is that your are changing the name (from http://www.conf to demo_one.conf) and now you are listening on the socket rather than the IP.
The rest of the values can be left as default.

Load Configuration

After you have made these changes you need to restart both nginx and PHP-fpm

/etc/init.d/nginx restart
/etc/init.d/php-fpm restart

To check if nginx is listening on port 80 and the socket is up and running:

netstat -lanp

This will give you results such as

tcp 0 0 0.0.0.0:8 0 0.0.0.0:* LISTEN 9700/nginx.conf

unix 2 [ ACC ] STREAM LISTENING 48459 9875/php-fpm.conf) /var/run/fpm_demo_one.sock

This shows you nginx is listening on port 80, and the php-fpm is listening on the socket fpm_demo_one.

Hopefully you can see “This is demo one” when you go to the index.php page of your website!