Skip to main content

Debugging with Xdebug and Eclipse on Mac

In my previous posts I explained how to install MacPorts and setup typical Apache/PHP setup with it. Now we’re going to start with something more serious, something that sooner or later every PHP developer will have to deal with. Debugging! And yes when I say debugging I didn’t mean echoing or using var_dump 😉

First off some background info. As far as I know, the very first PHP IDE which actually had debugger built in was Zend Studio. I’ve been using ZDE 5.2.x for very long time, but since Zend stopped supporting it, it was very difficult to continue using it. On Snow Leopard it required some nasty Java hacks to make it working, and any Java update would break it. It was obvious that time of ZDE 5.x was over so I had to pick something new. My choice was Eclipse (I didn’t see any point in paying Zend extra fee for IDE based on Eclipse) so I downloaded Eclipse Hellios release from Zend PDT pages. Unfortunately it was really slow and buggy so after a while I’ve decided to download “classic” Eclipse and get PHP Dev SDK installed manually. Somehow it worked much better for me and since there is new Eclipse Indigo 3.7.1 release right now, I’ve decided to wrote detailed explanation how to install it. In case you already have Eclipse installed and you’re happy with it, just skim to Xdebug setup part.

Setting up Eclipse
————————-
So, let’s download Eclipse 3.7.1 classic and unpack it to some dir. I prefer to use my home dir (which is /Users/dinke/eclipse) but you can pick any, just copy files there and run Eclipse application from that folder. On first run it will ask you about workspace (which is place where Eclipse keeps some internal files), I used /Users/dinke/workspace but you can use any location that suit you. Eclipse will start with Welcome window that we are going to close for now (you can always get it reopened by choosing Help->Welcome)

Eclipse Startup

Now it’s time to download and install PHP Development Tools SDK. So open Help -> Install New Software. Pick Indigo from “work with” drop-down and when list bellow gets updated, go to Programming Languages and check box next to PHP Development Tools SDK as in image bellow.

PDT Startup

Now click next, accept terms and Finish, and PHP tools will be installed. After installation is done (it may take some time), you’ll be offered to restart Eclipse, so do it immediately. After Eclipse has been restarted we are going to switch to PHP perspective. So click to “Open Perspective” icon in upper right corner of Eclipse and pick “Other”. There you’ll be offered options similar as in image bellow:

PHP Perspective

Pick PHP click OK and we’re done with Eclipse setup. Congratulations! Now you can tweak Eclipse to your own preferences (Eclipse->Preferences) or import/start new project and start playing with code.

Xdebug
————
Now we’re going to deal with Xdebug part. First we have to install it, and we’re going to use MacPorts. If you don’t have MacPorts installed just look at my previous post about it.

So, start terminal and type this:

sudo port install php5-xdebug

Restart Apache (sudo /opt/local/apache2/bin/apachectl restart) and make sure that you have proper Xdebug section on phpinfo page (see bellow):

phpinfo

Now we need to add some configuration options to php.ini file, so let’s add this to the bottom of php.ini file (in my case located at /opt/local/etc/php5/php.ini):

[xdebug]
zend_extension="/opt/local/lib/php/extensions/no-debug-non-zts-20090626/xdebug.so"
xdebug.profiler_output_dir = "/tmp/xdebug/"
xdebug.profiler_enable = On
xdebug.remote_enable=On
xdebug.remote_host="localhost"
xdebug.remote_port=9000
xdebug.remote_handler="dbgp"
xdebug.idekey=ECLIPSE_DBGP 

and restart Apache again.

Now let’s configure Eclipse debugging options. Open Eclipse Preferences and then PHP->Debug and instead of Zend pick XDebug from PHP Debugger drop down.

eclipse_debug

Click Configure link next to dropdown, select Xdebug and click configure button again and make sure that in Accept remote session (JIT) is set to any (see image bellow).

eclipse_xdebug_settings

And this is it! Hooray!

Now let’s see how it’s actually working, we’re going to use so called “remote” debugging which allow us to actually debug directly from browser (there’s even firefox ext for that), however I prefer to insert my URL’s directly to configuration options. So select Run->Debug As from the menu and pick PHP Web Page. You will be asked to insert URL and then Debugging perspective will be opened along with editor. There you have ability to step into code, setup break points and watch variables on the right.

debugger

Happy Debugging 🙂

Useful Links:
Eclipse/Xdebug Remote debugging on Windows
Xdebug

Installing Apache and PHP with MacPorts

As you probably already know, Mac OS X comes with Apache and PHP preinstalled, all you have to do in order to turn it on is to turn Web Sharing On in System Preferences and do some changes in httpd.conf/php.ini files.

web sharing

You can probably find tons of tutorials on that topic (try this one for example) so not gonna waste my time on explaining how to do it.

Problem with bundled PHP/Apache setup on Mac is lack of flexibility. You already have most of extensions you’d ever need enabled, but there are still some missing, and if you want something not already there, you’re out of luck. For example recently I’ve started working on new project which requires memcache and xdebug support and there isn’t any way to have them enabled in bundled Apache/PHP setup.

Mac Ports to the Rescue
——————————————
In my previous post I’ve explained how to setup MacPorts on Mac OS X. Now we’re going to use it in order to setup fully working and flexible “LAMP” environment. Speaking of LAMP, I assume that you already have MySQL installed which is really easy to do with pkg installer available at http://dev.mysql.com/downloads/mysql/ (just download DMG Archive, unpack and run installer).

We will start our port setup by installing Apache 2. So open terminal and type this:

sudo port install apache2

After Apache is successfully installed we will deal with PHP 5 part. There are many extensions that we may get installed, but great thing with MacPorts is that you don’t have to install all of them at once, it’s quite okay to install them later. So we’re going to start with really basic PHP 5 setup by running this cmd in terminal:

sudo port install php5 +apache2 +mysql5

and you’ll get really long output which tale looks like this:

...
Warning: php5 installs files outside the common directory structure.
--->  Installing php5 @5.3.8_0+apache2
--->  Activating php5 @5.3.8_0+apache2
To customize php, copy
/opt/local/etc/php5/php.ini-development (if this is a development server) or
/opt/local/etc/php5/php.ini-production (if this is a production server) to
/opt/local/etc/php5/php.ini and then make changes.

If this is your first install, you need to activate PHP in your web server.

To enable PHP in Apache, run
  cd /opt/local/apache2/modules
  /opt/local/apache2/bin/apxs -a -e -n "php5" libphp5.so
--->  Cleaning php5
Dragan-Dinics-MacBook-Pro:~ dinke$

As you can see at the end of output, we also need to run some cmd’s in order to enable PHP in Apache, so execute these in terminal:

cd /opt/local/apache2/modules
sudo /opt/local/apache2/bin/apxs -a -e -n “php5” libphp5.so

and you’ll get output as bellow:

Dragan-Dinics-MacBook-Pro:~ dinke$ cd /opt/local/apache2/modules
Dragan-Dinics-MacBook-Pro:modules dinke$ sudo /opt/local/apache2/bin/apxs -a -e -n "php5" libphp5.so
[activating module `php5' in /opt/local/apache2/conf/httpd.conf]
Dragan-Dinics-MacBook-Pro:~ dinke$

Now we need to edit /opt/local/apache2/conf/httpd.conf and tweaks it to our needs. You will probably want to change default DocumentRoot, for example my Doc root is under /Users/dinke/htdocs so on my Mac it’s set like this:

DocumentRoot "/Users/dinke/htdocs"

Note: If you change DocumentRoot path don’t forget to change path in Directory directive as well (should be the same as path in DocumentRoot).

Make sure that that in LoadModule section you have proper PHP modul:

LoadModule php5_module modules/libphp5.so

and last but not least, add proper entries in block to tell Apache to use PHP module for processing php files.

AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

That should be all regarding Apache conf.

Now copy PHP.ini file to proper location:

sudo cp /opt/local/etc/php.ini-dist /opt/local/etc/php.ini

and edit it if you want now or leave it for later, up to you.

Time to start Apache now! Let’s run this cmd:

sudo /opt/local/apache2/bin/apachectl start

and if you don’t get any error message, that means that Apache is up and running. If you do, check again your config and make sure that you don’t have any errors.

Now create usual phpinfo() page and save it in your docroot (/Users/dinke/htdocs/test.php in my example), and try to see if it works:

http://localhost/test.php

phpinfo page

If you get usual PHPinfo page simmilar as above, your installation was successful. Well done!

Now what is great with MacPorts setup is that you can now add more extensions easily. For example if you search for php5 extensions you will get 111 ports!

sudo port search php5

php5 @5.3.8 (lang, php, www)
PHP: Hypertext Preprocessor

php5-amf @0.9.2 (php, devel)
ActionScript Message Format extension

php5-apc @3.1.9 (php, devel)
Alternative PHP Cache

php5-bbcode @1.0.2 (php, devel)
BBCode parsing Extension
...
Found 111 ports.

So for example if you want mcrypt module installed, all you have to do is to run this:

sudo port install php5-mcrypt

and you’ll get output like this:

Dragan-Dinics-MacBook-Pro:~ dinke$ sudo port install php5-mcrypt
--->  Computing dependencies for php5-mcrypt
--->  Dependencies to be installed: libmcrypt
--->  Fetching archive for libmcrypt
--->  Attempting to fetch libmcrypt-2.5.8_1.darwin_10.x86_64.tbz2 from http://packages.macports.org/libmcrypt
--->  Attempting to fetch libmcrypt-2.5.8_1.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/libmcrypt
--->  Installing libmcrypt @2.5.8_1
--->  Activating libmcrypt @2.5.8_1
--->  Cleaning libmcrypt
--->  Fetching archive for php5-mcrypt
--->  Attempting to fetch php5-mcrypt-5.3.8_0.darwin_10.x86_64.tbz2 from http://packages.macports.org/php5-mcrypt
--->  Fetching php5-mcrypt
--->  Verifying checksum(s) for php5-mcrypt
--->  Extracting php5-mcrypt
--->  Configuring php5-mcrypt
--->  Building php5-mcrypt
--->  Staging php5-mcrypt into destroot
--->  Installing php5-mcrypt @5.3.8_0
--->  Activating php5-mcrypt @5.3.8_0
--->  Cleaning php5-mcrypt

Now restart Apache, and you should be able to see mcrypt module activated on phpinfo page. Enjoy!

mcrypt

Installing MacPorts

The MacPorts Project is an open-source community initiative to design an easy-to-use system for compiling, installing, and upgrading open source software on Mac OS X. As you’ll be able to see later, it’s easy to use but still very powerful.

Unless you already have it installed, the very first step in installing MacPorts actually starts with installing Xcode Developer Tools which is not installed by default. It comes with Mac OS X Applications DVD but it’s probably the best idea to download the latest version from Apple site. There are currently two branches of Xcode, 3.2.x and 4.1.x. If you are on Snow Leopard get Xcode 3, if you’re on Lion get Xcode 4.1, both are available on Apple Developers site, and unless you already have an account, you will need to register first.

After you’re logged in on Apple Dev site, you can download and install Xcode 3.2 from this link and then run Mac software update to update it to latest version. If you’re on Lion you can get Xcode 4.1 from this link or get it from App Store.

Now let’s deal with MacPorts installation. The easiest way to install it is with DMG package installer. Again if you’re on Snow Leopard get it from here, Lion version can be downloaded from here. Run installer which will setup everything automatically (copy files and modify your .bash_profile file by adding proper PATH variable for MacPorts tree which is something like export PATH=/opt/local/bin:/opt/local/sbin:$PATH).

Now open terminal and run this cmd:

sudo port -v selfupdate

This is how output looked on my system:

Dragan-Dinics-MacBook-Pro:~ dinke$ sudo port -v selfupdate
Password:
--->  Updating MacPorts base sources using rsync
receiving file list ... done
base.tar

sent 10864 bytes  received 111 bytes  4390.00 bytes/sec
total size is 3226624  speedup is 294.00
receiving file list ... done
base.tar.rmd160

sent 64 bytes  received 116 bytes  72.00 bytes/sec
total size is 512  speedup is 2.84
MacPorts base version 2.0.3 installed,
MacPorts base version 2.0.3 downloaded.
--->  Updating the ports tree
Synchronizing local ports tree from rsync://rsync.macports.org/release/tarballs/ports.tar
receiving file list ... done
ports.tar

sent 47525 bytes  received 5715 bytes  9680.00 bytes/sec
total size is 45947904  speedup is 863.03
receiving file list ... done
ports.tar.rmd160

sent 64 bytes  received 636 bytes  280.00 bytes/sec
total size is 512  speedup is 0.73
Creating port index in /opt/local/var/macports/sources/rsync.macports.org/release/tarballs/ports

Total number of ports parsed:   0 
Ports successfully parsed:      0 
Ports failed:                   0 
Up-to-date ports skipped:       12335

--->  MacPorts base is already the latest version

The ports tree has been updated. To upgrade your installed ports, you should run
  port upgrade outdated
Dragan-Dinics-MacBook-Pro:~ dinke$

If you didn’t get any error message, that’s it you’re done. Congratulations!

Now let’s deal with the installation of some apps, and we will start with wget which is very nice cli tool for downloading stuff and somehow it is not available on Mac by default. So let’s start by issuing port search cmd:

Dragan-Dinics-MacBook-Pro:/ dinke$ port search wget
gwget @1.0.4 (gnome, net)
    Gwget is a Download Manager for Gnome 2. It uses wget as a backend.

wget @1.13.4 (net, www)
    internet file retriever

wgetpro @0.1.3 (net, www)
    advanced internet file retriever

wput @0.6.2 (net)
    wput is like wget but is for uploading files to ftp-servers

Found 4 ports.
Dragan-Dinics-MacBook-Pro:/ dinke$

So it’s there in ports (internet file retriever), we’re going to install it by issuing port install cmd. Since this will require root permissions you will have to do it with sudo:

Dragan-Dinics-MacBook-Pro:/ dinke$ sudo port install wget
Password:
--->  Computing dependencies for wget
--->  Dependencies to be installed: gnutls libgcrypt libgpg-error libtasn1 lzo2
--->  Fetching archive for libgpg-error
--->  Attempting to fetch libgpg-error-1.10_0.darwin_10.x86_64.tbz2 from http://packages.macports.org/libgpg-error
--->  Attempting to fetch libgpg-error-1.10_0.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/libgpg-error
--->  Installing libgpg-error @1.10_0
--->  Activating libgpg-error @1.10_0
--->  Cleaning libgpg-error
--->  Fetching archive for libgcrypt
--->  Attempting to fetch libgcrypt-1.5.0_0.darwin_10.x86_64.tbz2 from http://packages.macports.org/libgcrypt
--->  Attempting to fetch libgcrypt-1.5.0_0.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/libgcrypt
--->  Installing libgcrypt @1.5.0_0
--->  Activating libgcrypt @1.5.0_0
--->  Cleaning libgcrypt
--->  Fetching archive for libtasn1
--->  Attempting to fetch libtasn1-2.9_0.darwin_10.x86_64.tbz2 from http://packages.macports.org/libtasn1
--->  Attempting to fetch libtasn1-2.9_0.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/libtasn1
--->  Installing libtasn1 @2.9_0
--->  Activating libtasn1 @2.9_0
--->  Cleaning libtasn1
--->  Fetching archive for lzo2
--->  Attempting to fetch lzo2-2.05_1.darwin_10.x86_64.tbz2 from http://packages.macports.org/lzo2
--->  Attempting to fetch lzo2-2.05_1.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/lzo2
--->  Installing lzo2 @2.05_1
--->  Activating lzo2 @2.05_1
--->  Cleaning lzo2
--->  Fetching archive for gnutls
--->  Attempting to fetch gnutls-2.8.6_1.darwin_10.x86_64.tbz2 from http://packages.macports.org/gnutls
--->  Attempting to fetch gnutls-2.8.6_1.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/gnutls
--->  Installing gnutls @2.8.6_1
--->  Activating gnutls @2.8.6_1
--->  Cleaning gnutls
--->  Fetching archive for wget
--->  Attempting to fetch wget-1.13.4_0+ssl.darwin_10.x86_64.tbz2 from http://packages.macports.org/wget
--->  Attempting to fetch wget-1.13.4_0+ssl.darwin_10.x86_64.tbz2.rmd160 from http://packages.macports.org/wget
--->  Installing wget @1.13.4_0+ssl
--->  Activating wget @1.13.4_0+ssl
To customize wget, you can copy wgetrc.sample to wgetrc
in /opt/local/etc and then make changes.
--->  Cleaning wget
Dragan-Dinics-MacBook-Pro:/ dinke$ 

Now issue wget cmd and you’ll get output like this:

Dragan-Dinics-MacBook-Pro:/ dinke$ wget
wget: missing URL
Usage: wget [OPTION]... [URL]...

Try `wget --help' for more options.
Dragan-Dinics-MacBook-Pro:/ dinke$

which means that wget tool is successfully installed and ready to be used for downloading stuff from the Internet. Enjoy!

Useful links:
http://www.macports.org/install.php
http://guide.macports.org

HTC Hero USB Tether on Mac OS X

Recently I got brand new HTC Hero – great android based phone. Since my previous phone (Nokia N73) has been working with Mac without any problem I was very disappointed to figure out that I have to deal with Windows for syncing my phone with computer and firmware upgrades.
After I upgraded it to latest official 2.73.405.4 ROM, I used Google Contacts/Calendar apps to sync my previous Nokia 73 stuff with Google and Hero, so having laptop<-->mobile connection to Internet (tethering) really sounded like something possible to achieve. A friend of mine with HTC Magic told me about way to connect to Internet with Wifi Tether application, which works on root-ed devices only. So after I passed trough complete rooting procedure explained here and here, I gained root access but still wasn’t able to make Wifi Tether work with Mac. Hero was visible as another AP, and even thought Mac was actually able to connect to it, I never got “outside” access to the Internet and eventually gave up. I found some threads that modaco custom ROM might be solution but figured that for newbie in android world like me, chances to brick brand new device while doing unsupported ROM upgrade are not so low so eventually I gave up.

Unlike for wifi tethering, USB tether for Hero and Windows comes “Out of the Box” (all you have to do is to turn on mobile sharing) but unfortunately it doesn’t work with a Mac. When you connect Hero to Mac, instead of new network device Mac see it as modem, but if you try to dial (like with *99#) you obviously get an error.
After I spent several hours reading endless discussions on various forums, today I finally managed to connect my MacBook Pro on the Internet with my HTC Hero mobile Internet (EDGE/GPRS/3G/HSDPA) connection.

Before I delve into instructions, please bear in mind that although this tutorial is based on HTC Hero and Mac running on latest (10.6.1) Snow Leopard, as far as I can tell it should be working with any other Android device or older version of Mac. I assume that you have MacPorts installed and that you are able to connect to Internet with your Mobile Phone.

Prepare your phone
————————–
You are going to install azilink on your phone. Since all tether applications are removed from Android Market, you have to download it directly from site. But before you do that, you have to make sure that installing applications from unknown sources is allowed. So Press Home, then Menu->Settings->Applications and make sure that “Unknown Sources” is checked. Also in Development menu make sure that “USB Debugging” is checked.

Now on your phone open browser and go to http://azilink.googlecode.com/files/azilink-2.0.2.apk , download file and follow install instructions. Once azilink application is installed start it and make sure that “Service Active” option is checked.

Prepare your Mac
————————–
Step1: TuneTap App
Download and install tuntap for Mac OS X (http://tuntaposx.sourceforge.net/) on the Mac and restart your mac.

Step2: Android SDK
Download Android SDK (http://developer.android.com/sdk/index.html) for Mac and unpack it somewhere on your machine. I used my $HOME dir (/Users/dinke) so I unpacked whole folder and named it android-sdk so full path is /Users/dinke/android-sdk. Since you are going to call tools from that folder, we have to put it into our $PATH variable by editing $HOME/.bash_profile file. I assume that you don’t have UNIX experience so this is step by step guide :

1) Open Terminal Applications
2) Type:

cd

and hit enter
3) You are now in your home dir (ie. /Users/dinke). Now you are going to edit .bash_profile file with pico editor :
Type:

pico .bash_profile

4) Add this line to last line of file:
export PATH=/Users/dinke/android-sdk/tools:$PATH
(replace /Users/dinke/android-sdk/tools with actuall path on your system)
5) Hit ctrl-x then answer Y and hit enter

Now you should be ready to call android tools (like we are going to do later) without specifying full system path.

Step3: OpenVPN2 App

Download and install openVPN2 . We will use MacPort’s port command for doing that. So again open terminal application and type:

sudo port install openvpn2

Now application will be automatically downloaded and compiled. This will take a while, and if this end without any error message you are good to go further 🙂

Step 4: azilink.ovpn file

1) Download http://azilink.googlecode.com/files/azilink.ovpn file
2) Create folder openvpn in $HOME/library folder (so you have new folder in for example /Users/dinke/Library/openvpn
3) Edit file azilink.ovpn and comment out line 8 with TCP_NODELAY (so it may looks like this):

dev tun

remote 127.0.0.1 41927 tcp-client
proto tcp-client
ifconfig 192.168.56.2 192.168.56.1
route 0.0.0.0 128.0.0.0
route 128.0.0.0 128.0.0.0
#socket-flags TCP_NODELAY
#keepalive 10 30
ping 10
dhcp-option DNS 192.168.56.1

4) Save file in new created in new created openvpn folder

Step 5: Modem script

1) Download script from http://pastie.org/405289 (there is download link on top right) and save it somewhere on your system. I assume you saved it under our home dir as modem.sh (/Users/dinke/modem.sh)
2) Give that script executable privileges. So open terminal application and type this:
chmod 777 /Users/dinke/modem.sh
(change path and script name according to your system)

Now we are almost done and we are ready to test actual connection. So connect your phone with Mac with USB cable, open terminal app and type this:

adb devices

You should get something like this:

dragan-dinics-macbook-pro:tools dinke$ adb devices
* daemon not running. starting it now *
* daemon started successfully *
List of devices attached
HT9FSL901734    device
dragan-dinics-macbook-pro:tools dinke$ 

If you get error (like command not found) check path setting for android sdk tools. If your device is not listed check that it is connected properly. If everything looks ok (you get device id listed) then we can connect our phone to Internet and run actual connection script. Make sure that your phone is connected to Mobile Internet (go to Menu->Settings->Wireless Controll and check Mobile Network checkbox), and then assuming that you’ve saved modem script under /Users/dinke/modem.sh you can run it by typing absolute path in terminal:

/Users/dinke/modem.sh

and you should get output like this:

dragan-dinics-macbook-pro:~ dinke$ /Users/dinke/modem.sh
Tue Nov 10 03:33:53 2009 OpenVPN 2.0.9 i686-apple-darwin10.0.0 [SSL] [LZO] built on Nov  9 2009
Tue Nov 10 03:33:53 2009 IMPORTANT: OpenVPN's default port number is now 1194, based on an official port number assignment by IANA.  OpenVPN 2.0-beta16 and earlier used 5000 as the default port.
Tue Nov 10 03:33:53 2009 ******* WARNING *******: all encryption and authentication features disabled -- all data will be tunnelled as cleartext
Tue Nov 10 03:33:53 2009 gw 0.0.0.0
Tue Nov 10 03:33:53 2009 TUN/TAP device /dev/tun0 opened
Tue Nov 10 03:33:53 2009 /sbin/ifconfig tun0 delete
ifconfig: ioctl (SIOCDIFADDR): Can't assign requested address
Tue Nov 10 03:33:53 2009 NOTE: Tried to delete pre-existing tun/tap instance -- No Problem if failure
Tue Nov 10 03:33:53 2009 /sbin/ifconfig tun0 192.168.56.2 192.168.56.1 mtu 1500 netmask 255.255.255.255 up
Tue Nov 10 03:33:53 2009 ./modem.sh up tun0 1500 1502 192.168.56.2 192.168.56.1 init
add net 0.0.0.0: gateway 192.168.56.1
add net 128.0.0.0: gateway 192.168.56.1
Tue Nov 10 03:33:53 2009 Attempting to establish TCP connection with 127.0.0.1:41927
Tue Nov 10 03:33:53 2009 TCP connection established with 127.0.0.1:41927
Tue Nov 10 03:33:53 2009 TCPv4_CLIENT link local: [undef]
Tue Nov 10 03:33:53 2009 TCPv4_CLIENT link remote: 127.0.0.1:41927
Tue Nov 10 03:34:03 2009 Peer Connection Initiated with 127.0.0.1:41927
Tue Nov 10 03:34:03 2009 Initialization Sequence Completed

If you get some errors make sure that you’ve passed correctly trough all needed steps. If no errors found, it is good time to test your new internet connection. You can do so by trying to ping some site or simple opening browser and visiting some Web Site. It should be working just fine!

My HSDPA connection with USB Tethering
Speedtest of my HSDPA connection with Telenor Serbia

Usefull Links:

http://androidsmartphone.de/apps/g1-als-usb-modem-ohne-root-auf-dem-mac/

http://thinkden.com/index.php/general/admin/75

Introduction to GeoIP

In case you’ve ever used Google Analytics or any simmilar tool where you can see exact location from where visitors of your site came from, you’ve probably wondered how they were able to dig that info. Is it magic or what? Of course, it’s not kind of magic, exact location of visitor is defined by visitor’s IP address, and technology used to locate user by his IP is well known as GeoIP.

Today we are going to look how to locate visitor of your site with PHP and Max Mind’s GeoIP database. In examples bellow we used free(lite) versions of GeoIP databases, because fully supported GeoIP databases are not free(you’d have to pay $50USD setup + $12USD update for GeoIP Country and $370USD + $90USD for GeoIP City base). Drawback of lite version is that it is not as accurate as fully supported GeoIP databases, but it is still very usefull and probably good enough for great majority of live projects.

MaxMind offer API for dozen of programming languages (full list is available here), details about PHP API are available here. This tutorial deal with so called “Pure PHP API”, there are also PECL extensions and apache mod_geoip modul available. Apache modul provide better perfomance, but Pure PHP API is easier to set up.

Just for a start let’s download all PHP API files from http://www.maxmind.com/download/geoip/api/php/, and save them somewhere inside of your Web tree(let say /htdocs/geoip). To use GeoIP Country you need to download lite database from here, and for GeoLiteCity download database from here. Just for the sake of simplicity, we are going to unpack both bases to the same dir where we saved our PHP API’s files (/htods/geoip in our example).

GeoIP Country
——————————–
Now, let’s see how country detection works:

<?php
/**
 * GeoIP Country Database Example
 *
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoip.inc");

$gi = geoip_open("GeoIP.dat", GEOIP_STANDARD);

$ip = $_SERVER['REMOTE_ADDR'];
//if you test on localhost use IP bellow for test
//since $_SERVER['REMOTE_ADDR'] would be 127.0.0.1
//$ip = "89.216.226.174";

$country_name = geoip_country_name_by_addr($gi, $ip);
$country_code = geoip_country_code_by_addr($gi, $ip);
if($country_name)
{
	echo "Your country is: $country_name <br />";
	echo "Country Code is: $country_code <br />";
}
else
{
	echo "Sorry, we weren't able to locate you.";
}

geoip_close($gi);
?>

So, at the beggining we’ve included geoip.inc which contains all functions needed to use GeoIP country database, then we’ve created new instance of GeoIP class with geoip_open function, and at the end we called proper functions(geoip_country_name_by_addr and geoip_country_code_by_addr) to get country name/code in which detected IP address reside. Again, in case you test localy, don’t use $_SERVER[‘REMOTE_ADDR’].

When you run script above you should get something like this as output:

Your country is: Serbia and Montenegro
Country Code is: CS

GeoIP City
—————————-
Now, let’s extend visitor’s country data with exact location(like city, postal code etc.)

<?php
/**
 * GeoIP City Database Example
 *
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoipcity.inc");

$gi = geoip_open("GeoLiteCity.dat", GEOIP_STANDARD);

$ip = $_SERVER['REMOTE_ADDR'];
//if you test on localhost use IP bellow for test
//since $_SERVER['REMOTE_ADDR'] would be 127.0.0.1
//$ip = "89.216.226.174";

$record = geoip_record_by_addr($gi, $ip);

if(!$record)
{
	echo "Sorry, we weren't able to locate you.";
}
else
{
	echo "Country: " .$record->country_name . "<br />";
	echo "Country Code: " . $record->country_code . "<br />";
	echo "Country Code 2: " . $record->country_code3 . "<br />";
	echo "Region: " .$record->region . "<br />";
	echo "City: " .$record->city . "<br />";
	echo "Postal Code: " .$record->postal_code . "<br />";
	echo "Latitude: " .$record->latitude . "<br />";
	echo "Longitude: " .$record->longitude . "<br />";
	echo "DMA Code: " .$record->dma_code . "<br />";
	echo "Area Code: " .$record->area_code . "<br />";
}

geoip_close($gi);
?>

As you see, PHP code is simmilar as in our country detection example, with exception that we used geoipcity.inc and GeoLiteCity.dat database. Function geoip_record_by_addr($gi, $ip) return instance of ‘geoiprecord’ class which contains in it’s properties location’s data we used in our example. After you run script you should get output like this one:

Country: Serbia and Montenegro
Country Code: CS
Country Code 2: SCG
Region: 02
City: Beograd
Postal Code: 11000
Latitude: 44.8186
Longitude: 20.4681
DMA Code:
Area Code:

CaseStudy – Redirection depending of Country
————————————————————–
At the end, we are going to see some real GeoIP usage. Our goal is to redirect users on multy language site(blog) to proper language section on the site depending of their location. Here is how code looks like on my own blog:

<?php
/**
 * Case Study - GeoIP Redirection
 *
 * @version $Id$
 * @package geoip
 * @copyright © 2006 Lampix.net
 * @author Dragan Dinic <dinke@lampix.net>
 */

require_once("geoip/geoip.inc");

$gi = geoip_open("geoip/GeoIP.dat",GEOIP_STANDARD);

$country_code = geoip_country_code_by_addr($gi, $_SERVER['REMOTE_ADDR']);

geoip_close($gi);

if($country_code == 'CS')
{
        header("HTTP/1.1 301 Moved Permanently");
        header('Location: http://www.dinke.net/blog/sr/');
}
else
{
        header("HTTP/1.1 301 Moved Permanently");
        header('Location: https://www.dinke.net/blog/en/');
}
?>

Above example is used on this blog in order to redirect all users located out of Serbia to english version of the blog. Sending custom 301 redirection headers is important so bots(like google etc. google) are able to index blog pages without problems.

MySQL Full-Text Searches

Today I am presenting you with a third and the last in tutorial series about “rarely used MySQL features”.

In first and second tutorials we spoke about transactions and referential integrity – features that are available in InnoDB and BDB tables only. Today we are going to deal with “Full-Text Search”, which is unlike features above available in MyISAM tables only (which is MySQL default engine).

So, let say you’ve just created one database driven site (forum, blog, cms, whatever) and you need to make it searchable. That shouldn’t be too complicated, so withot thinking much, you would probably come to easy solution like:

select * from foo_table where foo_text_field like '%search_text%'

Maybe include more fields in search, let say some dynamically generated from php form, and that should be it, right ? Hmmm not exactly.

Method we used above have one huge disadvantage – it is not able to use indexes! Because the fact that search pattern starts with joker ‘%’, query can’t use indexes even though you’ve defined them in mysql table fields used in search. No indexes means slow lookup (becasue search query have to pass trough all records in your tables). Fortunately, there is solution to this problem and it’s name is Full-Text Search.

Full-Text Search allow you efficient search trough text fields (CHAR, VARCHAR, TEXT), by using so called Full-Text Indexes.

There are three types of full-text searches:
Natural language search
Search pattern is splitted into words, so as a result you got only those records containings those words.

Bolean mode seach
Search pattern is splitted into words, but for each word you can add logic operator which allow you to dig only those records which contain or not contain some word.

Expansion Search
This is search in two phases. First one is identical to natural language search. In second phase words from the most relevant rows returned by the search are added to the search string and the search is done again.

During search, those words that are present in more than 50% of records are ignored. Also ignored are those so called “stopwords” (like “the”, “and”, etc.), as well as words shorter than 4 chars. You can find a list with all stopwords in corresponding section of MySQL Manual, and bear in mind that you can change those by changing path to file with stopwords. You can find a way how to do that here.

Now, lets see few examples. In order to easily illustrate full text usage, we are going to create database with “famous wrong predictions“.

So, lets copy/paste text bellow and save it to your computer in a file wrong_prediction.txt.

Albert Einstein, 1974|There is not the slightest indication that nuclear energy will ever be obtainable. It would mean that the atom would have to be shattered at will
Margaret Thatcher, 1974|It will be years - not in my time - before a woman will become a Prime Minister.
Alexander Graham Bell, c.1880.|One day there will be a telephone in every major city in the USA.
Popular Mechanics, 1949|Computers in the future may weigh no more than 1.5 tons.
Thomas Watson, IBM Computers,1943|I think there is a world market for as many as 5 computers.
Marshal Foch, France, 1912|Aircraft are interesting toys, but of no military value.

Now, lets create proper table, import predictions, and add indexes. Start MySQL command line client (you have to star it from the same dir where you saved your wrong predictions file), and type (or copy/paste) this:

create table wrong_predictions(
who varchar(100) not null,
prediction text not null
) engine = MyISAM;

load data local infile 'wrong_predictions.txt' into table wrong_predictions
fields terminated by '|' lines terminated by 'rn';

ALTER TABLE wrong_predictions
ADD FULLTEXT (who),
ADD FULLTEXT (prediction),
ADD FULLTEXT (who, prediction);

As you can see, first we’ve imported data into table, then we’ve created indexes. The reason why we didn’t create indexes during table creation is perfomance – insert is always slower if table have indexes created. Of course, you are not going to see any difference here, but pay attention to that when you import some million record file 🙂

Now, let’s see how Natural Search works:

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who) AGAINST('Thatcher');
+-------------------------+---------------------------------------------------------------------------------+
| who                     | prediction                                                                      |
+-------------------------+---------------------------------------------------------------------------------+
| Margaret Thatcher, 1974 | It will be years - not in my time - before a womanill become a Prime Minister.  |
+-------------------------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  SELECT * FROM wrong_predictions WHERE MATCH(who,prediction) AGAINST('computers');
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
| Popular Mechanics, 1949 | Computers in the future may weigh no more than 1.5 tons.    |
+-------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

When using MATCH in where clause, order of columns in output is defined with relevance of matched records. We can see that if we issue this query:

mysql> SELECT prediction, MATCH(prediction) AGAINST('computers') AS relevance FROM wrong_predictions;
+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| prediction                                                                                                                                        | relevance        |
+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
| There is not the slightest indication that nuclear energy will ever be obtainable. It would mean that the atom would have to be shattered at will | 0                |
| It will be years - not in my time - before a woman will become a Prime Minister.                                                                  | 0                |
| One day there will be a telephone in every major city in the USA.                                                                                 | 0                |
| Computers in the future may weigh no more than 1.5 tons.                                                                                          | 0.66266459031789 |
| I think there is a world market for as many as 5 computers.                                                                                       | 0.67003110026735 |
| Aircraft are interesting toys, but of no military value.                                                                                          | 0                |
+---------------------------------------------------------------------------------------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)

Boolean Mode allow us to make seach pattern in a simmilar way as in some search engines on Internet, since you can add words that must be presented (+foo) as well as words that must not be there (-foo).

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('+computers -popular' IN BOOLEAN MODE);
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
+-------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

You can also use jocker like:
pre>mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST(‘comp*’ IN BOOLEAN MODE);
+————————-+————————————————————-+
| who | prediction |
+————————-+————————————————————-+
| Popular Mechanics, 1949 | Computers in the future may weigh no more than 1.5 tons. |
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
+————————-+————————————————————-+
2 rows in set (0.00 sec)

Full description of boolean mode is available in coresponding section of MySQL Manual.

And last but not least, let’s see how Expansion Search works:

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('market');
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
+-------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM wrong_predictions WHERE MATCH(who,prediction)
-> AGAINST('market' WITH QUERY EXPANSION);
+-------------------------+-------------------------------------------------------------+
| who                     | prediction                                                  |
+-------------------------+-------------------------------------------------------------+
| Thomas Watson, IBM,1943 | I think there is a world market for as many as 5 computers. |
| Popular Mechanics, 1949 | Computers in the future may weigh no more than 1.5 tons.    |
+-------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)

In first query we’ve looked for word ‘market’ with natural seach, not really needed for query expansion, but usefull so we can track how exactly QUERY EXPANSION works. In second query we used QUERY EXPANSION which works in two phases. First it run natural search (like we did in first query), and then in 2nd phase it form new search phrase with original search phrase (market) concatenated with most highly relevant records we got from first phase. As a result we get more records than with natural search that are relevant with original search. More info about expansion search you find in MySQL Manual.

This is all for this introductory tutorial. Examples I used here serve only to “get your feet wet”, for full explanation of MySQL Full Text indexes you might want to consult MySQL Manual, or some book on subject (I recommend MySQL by Paul Dubois).

MySQL::Foreign Keys and Referential Integrity

Today we are going to continue with exploring those “rarely used” MySQL features. In previous post we were talking about MySQL Transactions and how to use them. Today our focus is on Foreign Keys and Referential Integrity.

Like with transactions, referential integrity support is not available in MyISAM tables, so InnoDB tables has to be used. Many developers probably don’t know that this feature is available since version 3.23.44, which is available since 2001. Anyway, let’s go with a (very) little theory, then we are going to move to some practice.

A foreign key (FK) is a field or group of fields in a database record that point to a key field or group of fields forming a key of another database record in some (usually different) table. The rule of “Referential Integrity” says that foreign key must not have a value which doesn’t exist in table where it refers to. That’s all about theory from me (I told you it will be very short :). If you need more theory feel free to use google for more informations. Now lets see some examples.

Imagine a database with cell phones, which among other things contains tables with phone vendors and phones. Let say something like this:

Vendors
---------------------
ID     Vendor
1.     Nokia
2.     Sony-Ericson
3.     Samsung
4.     Siemens

Phones
-------------------------------
ID.     Model     VID
1.      6600       1
2.      6630       1
3.      3650       1
4.      p800       2
5.      p900       2
...

VID column in ‘phones’ table is in direct relation with ID column of table ‘vendors’, in other words in this particular case VID column is so called foreign key. VID values in our example are related with smarth phones of vendors Nokia (6600, 6630 i 3650) and Sony-Ericson (p800, p900).
In order to keep so called referential integrity of our tables, we need to make sure that values of VID columns of table phones contains only those values that exists in ID column of vendors table. In case we remove some particular vendor from first table, all records referencing to it from phones table must be removed as well (this is known as cascade delete). Simmilar stands for update and insert operations, which means if we update ID in first table, foreign key VID must be updated too. Also, during inserting records into 2nd (phones) table we need to take care that VID value have proper reference in ID column of vendors table. If your RDBMS doesn’t have support for Referential Integrity you have to take care about all these things. Fortunately, by using MySQL InnoDB tables, we can let MySQL do da job for us.

Foreign Key is set during initial table creation. Syntax looks like this:

[CONSTRAINT symbol]
FOREIGN KEY [id] (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}]

CONSTRAINT symbol is optional constraint name for this foreign key.
FOREIGN KEY stands for table column which represent foreign key
REFERENCES tbl_name (index_col_name, …) stands for table and exact column where foreign key is referencing
ON DELETE and ON UPDATE defines actions that will be taken in case of delete/update in main table. Possible values are:
RESTRICT deleting records in main table will be refused if there are foreign keys in 2nd table referencing to them
CASCADEdeleting records in main table will result in deleting related records in 2nd table (table with foreign key)
SET NULL simmilar to CASCADE except that records in 2nd table will not be removed, they will be set to NULL instead

In order to demonstrate concept, I am going to create database with cell phones I mentioned above.

create table vendors(
	id int unsigned primary key not null auto_increment,
	vendor varchar(255) not null unique
	) engine = InnoDB;


create table phones(
	id int unsigned primary key not null auto_increment,
	model varchar(255) not null,
	vid int unsigned not null,
	foreign key(vid) references vendors(id)
		on delete cascade
		on update cascade
	) engine = InnoDB;

insert into vendors(vendor) values ('Nokia'),('Sony-Ericson'),('Samsung'),('Siemens');
insert into phones (model,vid) values ('6600',1),('6630',1),('3650',1),('p800',2),('p900',2);

In this example we used option cascade, which means that deleting any record in vendors table will result in removing all records from phones table that refer to them. Now, let try to do some “cheating”, by trying to insert phone with unexistant vendor ID:

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  1 | Nokia        |
|  3 | Samsung      |
|  4 | Siemens      |
|  2 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  1 | 6600  |   1 |
|  2 | 6630  |   1 |
|  3 | 3650  |   1 |
|  4 | p800  |   2 |
|  5 | p900  |   2 |
+----+-------+-----+
5 rows in set (0.02 sec)

mysql> insert into phones(model,vid) values ('unexistant',5);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
mysql>

As you see, it didn’t work. We’ve tried to insert new phone model into phones table, but with vendor id (vid) 5 which doesn’t exist in vendor table, so MySQL complained in order to keep referential integrity of database.

Now, let see how cascade delete/update works:

mysql> update vendors set id=5 where vendor='sony-ericson';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  1 | Nokia        |
|  3 | Samsung      |
|  4 | Siemens      |
|  5 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  1 | 6600  |   1 |
|  2 | 6630  |   1 |
|  3 | 3650  |   1 |
|  4 | p800  |   5 |
|  5 | p900  |   5 |
+----+-------+-----+
5 rows in set (0.00 sec)
mysql>

So, we’ve updated vendor id (vid in vendors table) for ‘sony-ericsson’ to value 5. Thanks to on update cascade options we used during table creation, value of changed field is also updated for all ‘sony-ericsson’ phones. Simmilar would happen if we try to delete some vendor, let’s try Nokia:

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  1 | Nokia        |
|  3 | Samsung      |
|  4 | Siemens      |
|  5 | Sony-Ericson |
+----+--------------+
4 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  1 | 6600  |   1 |
|  2 | 6630  |   1 |
|  3 | 3650  |   1 |
|  4 | p800  |   5 |
|  5 | p900  |   5 |
+----+-------+-----+
5 rows in set (0.00 sec)

mysql> delete from vendors where vendor='Nokia';
Query OK, 1 row affected (0.03 sec)

mysql> select * from vendors;
+----+--------------+
| id | vendor  |
+----+--------------+
|  3 | Samsung      |
|  4 | Siemens      |
|  5 | Sony-Ericson |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from phones;
+----+-------+-----+
| id | model | vid |
+----+-------+-----+
|  4 | p800  |   5 |
|  5 | p900  |   5 |
+----+-------+-----+
2 rows in set (0.00 sec)
mysql>

So, as you see all precious Nokia phones are deleted from phones table, since we’ve removed Nokia from vendors table.

This is all for today. I think this tutorial should be enough for introduction, for more information you should consult proper section of MySQL manual.

MySQL Transactions

As I announced in my previous post, from today I am starting with series of tutorials about those rarely used MySQL features like transactions, referential integrity, fulltext searches etc. Developers working with MySQL don’t use these features very much. Many of them don’t know that those features even exists, so very often we have a situation that on various advocacy like discussions MySQL is declared as “uncomplete” database, because it lack support for above features. However, a lot of people doesn’t know that MySQL support transactions since version 3.23.15 (which is out since May 2000). Simmilar stands with other features I mentioned above.

My goal in this and follow up tutorials is to break some prejudices about MySQL, as well to encourage developers to start using all features available in their favorite RDBMS.

Today we are starting with transactions.

In SQL parlance, transaction stands for series of sql queries that should be run as a whole. If all transaction queries are succesfully executed, transaction is succesfull, otherwise transaction has failed, and we are reverting to previous state before transaction began. Many people think that transaction is something money related, so I will use such example to describe this whole thing in easiest possible way.

Imagine a situation where John Doe needs to pay it’s client Mickey a 1000USD for some service. Here is how this situation can be described with tipical MySQL parlance:

update bank_accounts set amount = amount - 1000
where user = 'JohnDoe';

update bank_accounts set amount = amount + 1000
where user = 'Mickey';

Now, what if some of queries above has failed? We would have a situation that John left short for 1000 bucks without to paying Mickey for his services, or that Mickey get free credit from his bank in case first query is not done. By using transactions in case some error occured, system is reverting back to previous state (immediately before transaction started), so in this particular scenario, both queries or none will be executed.

RDBMS’s with transaction capability typically needs to provide 4 behaviours, which are usually refered as ACID:
Atomicity
Either all of the tasks of a transaction are performed or none of them are.
Consistency
Database is in a legal state before as well as after transaction end.
Isolation
Transaction doesn’t have any effect to other processes. That also means that effect of single queries are not visible to other clients untill transaction is finished.
Durability
Once transaction success, it persist and can’t be undone.

Now, enough theory, let’s do some real life usage. Syntax you will see here is customized to work with MySQL 4.1.x. Depending of your version of MySQL server, you might need to make certain changes. If you want to find out which version you use, run following query in your mysql client program:

select version();

Using transactions is currently possible only with InnoDB and BDB tables. If you’re unsure whether your server supports them, use show engines query which will list supported table types. If you need to enable support for InnoDB (BDB) tables, please consult MySQL Manual how to do that (or contact support if you use MySQL server on your hosting account). In our examples here we are going to use InnoDB tables, but the same principle applies to BDB tables as well.

For start, let’s create one test table:

create table test(
name char(20) not null unique)
engine = innodb;

And let’s try to use one transaction:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('John');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(name) values('Mickey');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.02 sec)

As you can see, start transaction is used for beggining of transaction. After 2 succesfully executed queries we used commit for the end of transaction, which is done succesfully in this case. Important thing to note is that none of changes during transaction (let say inserting new name John) were not visible to the other clients until commit has executed. That’s Isolation property I mentioned above.

Now, lets look at one failed transaction:

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.02 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Peter');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('John');
ERROR 1062 (23000): Duplicate entry 'John' for key 1
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.00 sec)

In this case during transaction error has occurred, because we’ve tried to enter duplicate value (‘John’) to a field defined as unique. As a result we got an error, so we issued rollback query. As you can see, after rollback table contains the same records as before transaction begun, which mean the name ‘Peter’ we entered during transaction is not left there, as transaction as a whole has failed.

Starting from MySQL 4.1.1 it is possible to set savepoint during transaction so we can revert to it with rollback command. Here is an example:

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
+--------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Peter');
Query OK, 1 row affected (0.00 sec)

mysql> savepoint point1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Bryan');
Query OK, 1 row affected (0.00 sec)

mysql> rollback to savepoint point1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(name) values('Dragan');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from test;
+--------+
| name   |
+--------+
| John   |
| Mickey |
| Peter  |
| Dragan |
+--------+
4 rows in set (0.00 sec)

As you can see, after we entered first name (Peter) we’ve saved position as point1. Other name ‘Bryan’ is not present since we reverted back to point1. Also after reverting we’ve succesfully entered one more name (Dragan) before finishing transaction with commit.

This is all about transactions in this short tutorial, I hope it contains enough informations to get your feet wet, so you can start using them with MySQL. If you are looking for more informations about transactions, you can look at MySQL Manual or try some good book on subject. My personal recommendation is MySQL 3rd Edition by Paul DuBois.