Category Archives: Archives

Blog posts from db4free.blogspot.com

The first week on my new job

This Monday, I started my new job at a web & software developing company.

Most of the time, the first week on a new job is very tough – everything is unfamilar, you don’t know everybody and everything seems strange. I know that from the time when I started another job in 1996 in a Controlling departement (something very different, business stuff). There was a lot of frustration in those days, but not this time. I could describe my feelings best to simply call it “excited”.

However, it’s still tough, but not because of the job itself, but for the fact that the company resides about 60 kilometers from my hometown (that are 120 km/day, 600 km/week). Where I live, weather is often very bad in winter, roads are slippery, much wind, much snow and most of the time I have to drive in the dark (just the last few minutes before I arrive in the morning it’s lightening up). It’s sometimes quite dangerous and requires careful driving, but I hope that these problems get solved – partly by nature (it will soon light up earlier and get dark later and winter will hopefully end some day), partly maybe by an accommodation near the company.

The job is great, because it deals mostly with things that I’m familiar with, but still offers great opportunities to learn new things. On Thursday I received my first productive project – a web application written in PHP using Smarty (nothing new to me, since db4free.net is built up the same way). But one thing is a “first time” for me – it’s my first project with PostgreSQL instead of MySQL, as PostgreSQL is the primary RDBMS used in this company and MySQL only the secondary (but I hope that I can still do some work on MySQL, too ;-)).

Of course I would have liked to mainly work with MySQL as this gave me a chance to bring a lot of excellent skills into my job (although most of my MySQL skills can also be used for PostgreSQL), but every medal with a bad side does also have one with a good side – I will have to also learn PostgreSQL very well and will get a wider perspective of RDBMS in general. More about that in (a) separate article(s).

I hope that I will get the chance to use and learn other techniques and programming languages as well. Primarily I hope that I will be able to use Java and I would very much like it if I get the chance to learn C/C++ to achieve a professional level as well, as my current skills of C/C++ are very basic. But I’m confident that they won’t disallow me to use things that I’d like to use, so this will probably be a great chance to become a professional developer for many languages and techniques.

You will certainly read more about that!

MySQL – PostgreSQL

As I just told, my new job requires me to do a lot of work with PostgreSQL, which is relatively new to me, since I have done almost all of my database work with MySQL so far.

I also told that this will give me a wider perspective on RDBMS in general and I would like to use this opportunity to discuss about how features are implemented in MySQL and how in PostgreSQL.

Don’t worry, this will not end up in a MySQL vs. PostgreSQL fight. As I know MySQL much better than PostgreSQL, I do understand that this position doesn’t allow me to make representative judgements on which database system is better (most people would naturally prefer the one they know better). I also believe that both of them are great RDBMS and none of them deserves to be involved in a “A rulez, B suckz” struggle (I love MySQL and I try to fall in love with PostgreSQL, too). I would rather like to show, how certain features work with MySQL and how with PostgreSQL and how the differences affect me personally – which could mean that I miss a feature in one or the other database system, but that does never mean that this product sucks.

I would like to start with listing up some differences that I have discovered so far. If you find something that’s not true as a fact, please don’t hesitate to contact me (m.popp@db4free.net) or comment this article.

  • MySQL uses auto_increment to create numeric values that iterate for every inserted record, PostgreSQL uses sequences. It’s possible to use the serial data type to automate this in PostgreSQL so I think there are no big advantages or disadvantages for each particular implementation.
  • When a transaction is started and you write a SQL statement that has a syntax error in it, PostgreSQL rolls back the transaction immediately, while MySQL shows an error message, but still continues the transaction (it’s up to the user to do error handling and roll back the transaction, if desired). I think, both ways have their own advantages and disadvantages. It would be great if both database systems could create a setting to let the user decide how to handle syntax errors in transactions.
  • PostgreSQL allows check constraints. I have heard that check constraints will be implemented in MySQL 5.1 (can somebody confirm this?).
  • MySQL has an ‘enum’ data type. I haven’t found a similar data type in PostgreSQL, however, ‘enum’s can be emulated with check constraints.
  • Other than PostgreSQL, MySQL allows to insert more than one row in one INSERT statement. MySQL’s implementation is a very useful extension to SQL standard.
  • There are several storage engines in MySQL with several features. Starting with MySQL 5.1, storage engines will be pluggable. PostgreSQL doesn’t allow to choose between storage engines, there’s only one way to store the data, however, all the features of PostgreSQL are available within this data storage method. This is an advantage and disadvantage as well. You can tailor the requirements of a particular table better with MySQL, on the other hand, if you need a feature from two storage engines in one table (for example foreign keys and full text indexing), you have a problem. As MySQL implements more and more features into the available storage engines, this will be less of a problem in the future.
  • MySQL allows to set @@foreign_key_checks to 0 to disable foreign key checking. It’s sometimes necessary to change the structures of related tables and temporarily disable these checks. One thing that I miss in MySQL is that enabling @@foreign_key_checks again doesn’t verify referential integrity (please correct me, if I’m wrong or if that has changed). In PostgreSQL, you can add “INITIALLY DEFERRED” to the foreign key definition, so this allows that referential integrity may be violated inside a transaction, but the data must be correct when the transaction is being committed (I will possibly write more about this later).
  • PostgreSQL is (or has been) more restrictive checking the data that’s inserted. MySQL has introduced SQL mode settings to enable restrictive error checking. However, in older versions of MySQL (or if SQL mode is not set), MySQL allows obviously invalid data to be inserted.
  • PostgreSQL allows to write stored procedures, functions and triggers in different languages, however, these languages are not installed by default. MySQL offers only one language, but more languages will probably be available in later versions.
  • MySQL stores user permission data inside a MySQL database (called mysql). This makes it very simple for a system administrator to edit permissions or look up how they are set (besides the SQL commands GRANT, REVOKE, SHOW GRANTS etc.). I haven’t yet figured out where PostgreSQL stores this data and I think it’s only possible to use the SQL commands to set the permissions accordingly.
  • MySQL and PostgreSQL do of course have different client programs and it’s up to somebody’s preferences which ones to like more. But I found out that if you are logged in as a “normal” user (without administrator privileges and access to all databases) in PostgreSQL, you can always see all databases, including those to where no access is granted. In MySQL, a particular user can only see the databases that he has access to. Maybe there’s something in PostgreSQL that I don’t know yet to also make only those databases visible that I user has permissions to access (please tell me, if you know a solution).

This list can and will be continued and if you know some more important differences that I haven’t listed, or if you find errors or things that should be added, I would appreciate your comments.

Several Open Source database system producers (including MySQL and PostgreSQL, beside some others) have agreed to found the Open Source Database Consortium (a website is planned at http://www.osdbconsortium.org/). Find detailed information on what it’s all about in Kaj Arnö’s article about the foundation of the Consortium. I hope that this project makes it easier for people who work with more than one Open Source database product. It’s a great thing to keep track of as soon as the site becomes available and if there is a chance, I would be happy to contribute to this fascinating project in one way or another.

TX isolation levels (3) REPEATABLE READ, SERIALIZABLE

The third part of this series shows how transactions work when the REPEATABLE READ and SERIALIZABLE transaction levels are used. I show these two together, because they work very similar and can easily be shown in one example.

REPEATABLE READ is the default transaction level in MySQL and the one that’s most commonly used. We start with the same data as in the last two examples – a simple InnoDB table called tt with one integer field called d, which holds three rows with the values 1, 2 and 3. Again, we need two MySQL monitor client windows to perform following steps:

CLIENT 1
========

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

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)


CLIENT 2
========

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

After client 2 has modified a row, client 1, who’s in transaction mode, still sees the unchanged data. This makes it possible for client 1 to always receive consistent (the C of ACID) data, while performing a transaction.

This example showed how REPEATABLE READ works. So what’s the difference to SERIALIZABLE? Let’s do a rollback on client 1 and change the transaction isolation level to SERIALIZABLE and let client 2 try to change another row:

CLIENT 1
========

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

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
+------+
3 rows in set (0.02 sec)

CLIENT 2
========

mysql> update tt set d=2 where d=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This time, client 2 cannot update the row. After a few seconds of waiting, the attempt to update a row ends with the error message “Lock wait timeout exceeded”.

To sum this up: both REPEATABLE READ and SERIALIZABLE don’t allow dirty reads, nonrepeatable reads and phantom reads to happen. While REPEATABLE READ still allows another client to modify data, while he performs a transaction, SERIALIZABLE strictly disallows it. Because of this, REPEATABLE READ is in most cases the best compromise between fulfilling the ACID (atomicy, consistency, isolation, durability) principles and still giving the best possible performence.

TX isolation levels (4) How to produce an artificial deadlock

The last article of this series shows, how to produce an artificial deadlock. We have to make a little modification to the table that we’ve used for the examples before – we need to add a primary key. Without primary key, the whole table will be locked, which makes it impossible to produce a deadlock (that’s also, why deadlocks can’t occur on MyISAM tables, because MyISAM uses table locking instead of row locking).

mysql> alter table tt add primary key(d);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

Starting from a table tt with three rows 1, 2 and 3, do following steps:

CLIENT 1
========

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

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 2
========

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

mysql> update tt set d=5 where d=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> update tt set d=6 where d=1;
Query OK, 1 row affected (10.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 2
========

mysql> update tt set d=7 where d=2;
ERROR 1213 (40001): Deadlock found when trying to get lock; 
try restarting transaction

After client 2 tries to get a lock on a row that’s already locked from client 1, a deadlock occurs, because client 1 is already waiting to get a lock on a row that has been changed by client 2.

Thanks, congratulations and continue the good work!

As one of the three winners I’d like to thank the entire MySQL team and congratulate my co-winners, Roland Bouman and Beat Vontobel.

I’d also like to thank other honourable Community members that I had the pleasure to have contact with, like Andrew Gilfrin, Giuseppe Maxia and Jay Pipes. These people and the MySQL company have all done great work and I’m sure that a lot of great things are yet to come :-). You never make life boring.

This prize is a big honour – it’s not such a long time that I have been active in the Community, maybe half a year or so. I still consider myself to be at the beginning and I never thought it could happen so quickly that I would be recognized and awarded from the MySQL team and other Community members. I don’t have decades of development experience like some others (actually, I started programming about 2 1/2 years ago) and I’m still discovering different techniques and ways how to become a REALLY skilled developer. I believed (and still believe), I was at the beginning of a very long way – so it’s absolutely a phantastic and motivating thing to be rewarded so extremely soon in a worldwide contest from the ‘World’s Most Popular Open Source Database’ company.

So this shows, don’t be afraid if you are new to MySQL and new in the MySQL Community. It doesn’t only take people with many years of experience, there’s also a lot that “fresh” people can do to help!

Well, what are my current plans and activities? My next big step will hopefully be to pass the MySQL Professional Exam, which I plan to take around the middle of December (it requires tough work, it’s definetly no easy thing, but there was a lot to learn which I probably wouldn’t have learnt without taking the Core and soon the Professional exam). At the moment I, together with Roland Bouman, am writing an article and a code sample (that includes all the common methods that exist) about data access to MySQL using C#.NET for Andrew’s mysqldevelopment.com site.

I also have some ideas for db4free.net, so if time allows me, there could be a completely new version with new possibilities next year. It’s hard to promise it, because you never know, what’s coming up – but I’ll try hard to make it real :-). At least, the great feedback I received is very motivating and inspiring!

Well, and finally there’s my current series in my weblog about Transaction Isolation Levels, which will soon continue with the article about the REPEATABLE READ. So stay tuned!

TX isolation levels (2) READ COMMITTED

Lets continue this series about transaction isolation levels and table a look at READ COMMITTED.

We start with the same data and perform the same steps as we did with READ UNCOMMITTED and take a look at the differences. So prepare the table ‘tt’ that it looks like this:

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Again, open 2 MySQL monitor windows and change the transaction isolation level of the 1st window (which we call CLIENT 1) to READ COMMITTED:

set session transaction isolation level read committed;

So let’s start with the first example. Client 1 starts a transaction and performs a SELECT statement. Then, client 2 updates row 2 to value 4.

CLIENT 1
========

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

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

CLIENT 2
========

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

As we see, the result is exactly the same as with READ UNCOMMITTED. It’s still possible to see changes of the data applied by another client.

Now, client 2 inserts a row and client 1 performs another SELECT statement:

CLIENT 2
========

mysql> insert into tt values (5);
Query OK, 1 row affected (0.00 sec)

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

Again, there’s no difference to READ UNCOMMITTED. Finally client 2 also starts a transaction and updates and inserts a few rows:

CLIENT 2
========

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)


mysql> insert into tt values (6), (7);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql> update tt set d=8 where d=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

Here’s the difference between READ UNCOMMITTED and READ COMMITTED. While client 2 is in transaction mode, client 1 is not able to see the changes done by client 2.

To sum this up: READ COMMITTED allows non repeatable reads and phantom reads to happen, but in contrast to READ UNCOMMITTED, it does not allow dirty reads.

TX isolation levels (1) READ UNCOMMITTED

This series of articles will show you how the different transaction isolation levels work in practice. Most people who work with databases on a regular basis might have heared that there is a distiction how transactions can deal with concurrent reads and writes. However, their names and exact (but simular) functionality differ between the database systems. This shows, how the isolation levels work in MySQL.

To execute the examples, you need one simple InnoDB table and two opened MySQL monitor clients. With them, we can simulate slowly what in real world applications with multiple concurrent reads and writes can happen in less than a blink of an eye.

Here’s the definition of the table – very simple, indeed. Insert three rows with values from 1 to 3:

create table tt (d int) engine=innodb;
insert into tt values (1), (2), (3);

We start with the least restrictive isolation level, the READ UNCOMMITTED.

To output the current isolation level, execute the command

select @@tx_isolation;

To change the isolation level, use the command

set session transaction isolation level read uncommitted;

Now open 2 MySQL monitor clients and execute following commands:

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

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

CLIENT 2
========

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

What we see here is a non repeatable read. After client 1 started the transaction and selected the data, client 2 changed one row. When client 1, still in transaction mode, performed a second SELECT statement, he got a different result.

We continue this example:

CLIENT 2
========

mysql> insert into tt values (5);
Query OK, 1 row affected (0.00 sec)

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.02 sec)

After client 2 inserts a row, client 1 can immediately see this new row, while he’s still inside the transaction. This is called a phantom read.

As this example continues, client 2 also starts a transaction:

CLIENT 2
========

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

mysql> insert into tt values (6), (7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update tt set d=8 where d=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    8 |
|    4 |
|    3 |
|    5 |
|    6 |
|    7 |
+------+
6 rows in set (0.00 sec)

As client 2 starts the transaction, inserts 2 rows and updates another one, client 1 can immediately see the changes. Now, client 2 performs a rollback on his transaction:

CLIENT 2
========

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

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

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

As client 2 rolls back his transaction, client 1 sees the same result as before client 2 started his transaction. This way, client 2 sees records that have never really existed in the table. This is called a dirty read.

Compiling PHP 5 with mysql and mysqli

I just came across an interesting article from John Berns about Compiling PHP5 with mysql and mysqli Database Drivers.

As Andrew Gilfrin told in his Weblog recently, it’s still hard to find web hosters who support the mysqli extension. I’m also struggeling with my web hoster to convince him to install the mysqli extension (and of course to update MySQL to 5.0) – but it’s always a long process until it actually happens. So maybe, this article is a great starting point for the administrators at web hosting companies to extend their offers. I’ve posted the link to this article into the forum of my web hoster – cross your fingers that it helps to speed things up.

Locate IP addresses

It’s very easy to log the visitor’s IP address in a web application. In PHP, for example, a simple $_SERVER[‘REMOTE_ADDR’] returns the desired value.

But the IP address alone doesn’t tell us much about the person, sometimes it’s not even a person, but a search robot, who just entered the website. More interesting would be, where he (the person or the machine) lives.

I’d like to show you how MySQL 5 can help you to easily query the country from some source data that can be downloaded for free from MaxMind at http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip.

First I’d recommand you to create a new database, if you have the privilege to do so. I have called my database geoips. Download the file mentioned above and extract the file GeoIPCountryWhois.csv from this zip file. The file looks like this:

"2.6.190.56","2.6.190.63","33996344","33996351","GB","United Kingdom"
"3.0.0.0","4.17.135.31","50331648","68257567","US","United States"
"4.17.135.32","4.17.135.63","68257568","68257599","CA","Canada"
"4.17.135.64","4.17.142.255","68257600","68259583","US","United States"
...

First we have to get this data into a new table, which I call ip_ranges. We create it in the geoips database using this CREATE command:

CREATE TABLE ip_ranges (
   ip1_temp char(16), 
   ip2_temp char(16), 
   ip_from int unsigned NOT NULL PRIMARY KEY, 
   ip_to int unsigned NOT NULL UNIQUE, 
   code char(2) NOT NULL, 
   country varchar(100) NOT NULL, 
   INDEX (code)) ENGINE = InnoDB;

I didn’t take much care about the ip1_temp and ip2_temp columns, because we only need them temporarily. We will make our searches based on the values in the third and forth column of the data in the csv file. Later we will put the code and country pairs into a separate table to normalize the data. Because we will put a foreign key on the code column, we put an index on it and we use the InnoDB storage engine. This isn’t a must, but it’s a good way to also show you how to accomplish this task ;-).

To import the csv file into the table we just created we have two possibilities, either via a SQL command or via the mysqlimport program that’s included in MySQL. I’ll show you both ways. You need the FILE privilege for both variants.

Open a console window and change to the directory where the GeoIPCountryWhois.csv file resides. Start your mysql client program with the command

mysql -h [host] -u [username] -p[password] geoips

Then execute this SQL statement:

LOAD DATA LOCAL INFILE 'GeoIPCountryWhois.csv' 
   INTO TABLE ip_ranges 
   FIELDS TERMINATED BY ',' 
   ENCLOSED BY '"' 
   LINES TERMINATED BY '\n';

Instead of starting your mysql client program, you can also use the mysqlimport program. Therefore, the name of the input file must be equal to the table name, so we rename the file GeoIPCountryWhois.csv to ip_ranges.csv.

move GeoIPCountryWhois.csv ip_ranges.csv (in Windows)
mv GeoIPCountryWhois.csv ip_ranges.csv (in Linux)

Then we can use this command (please write it in one line) to import the csv file:

mysqlimport -h [host] -u [username] -p[password] --local 
--fields-terminated-by="," --fields-enclosed-by="\"" 
--lines-terminated-by="\n" geoips ip_ranges.csv

You should now have somthing like 74,000 records in your ip_ranges table.

Change back to your mysql client and the geoips database. We can now remove the columns ip1_temp and ip2_temp from the ip_ranges table, because we won’t need them for searching. If you want to leave them to verify the results, that’s no problem either.

ALTER TABLE ip_ranges
   DROP ip1_temp,
   DROP ip2_temp;

Now we have an interesting task ahead of us. We want to move a list of country codes and country names into a separate table called ip_countries. That’s very easy to do with only one command.

CREATE TABLE ip_countries
   SELECT DISTINCT code, country
      FROM ip_ranges
      ORDER BY code;

We can now

* add a primary key to the code column of the ip_countries table
* remove the country column from the ip_ranges table
* add a foreign key constraint to the code column of the ip_ranges table, which refers to the code column of the ip_countries table.

We need two more SQL commands to do this:

ALTER TABLE ip_countries ADD PRIMARY KEY (code);
ALTER TABLE ip_ranges 
   DROP country, 
   ADD FOREIGN KEY (code) 
      REFERENCES ip_countries(code);

Now we have all the data we need to start writing a User Defined Function which queries the country based on an IP address that we pass as parameter. Of course we can’t pass the IP address as such a number like we have the values in the ip_from and ip_to columns of the ip_ranges table. So we need to calculate the corresponding number from the IP address.

The page http://www.maxmind.com/app/csv tells us how this value can be calculated. We split up the 4 parts of our IP address and calculate it like this (ip1 = 1st part, ip2 = 2nd part, ip3 = 3rd part, ip4 = 4th part):

value = 16777216 x ip1 + 65536 x ip2 + 256 x ip3 + ip4

I use my current IP address 62.46.14.132 to try this out and get the value 1,043,205,764.

As soon we have this value, we can query the country like this:

SELECT b.country
   FROM ip_ranges a INNER JOIN ip_countries b
   ON a.code = b.code
   WHERE ip_from = 1043205764

The result is Austria, which is the place where I live.

Even though we query the data from two tables with one storing more than 74,000 records, the query only takes about 0.0025 seconds on my machine. That’s because of the indexes we’ve set. Prefixing the keyword EXPLAIN to the last query shows us that MySQL was able to query the data very efficiently.

mysql> EXPLAIN SELECT b.country
    -> FROM ip_ranges a INNER JOIN ip_countries b
    -> ON a.code = b.code
    -> WHERE ip_from  ip_to >= 1043205764 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: range
possible_keys: PRIMARY,ip_to,code
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2322
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 6
          ref: geoips.a.code
         rows: 1
        Extra:
2 rows in set (0.00 sec)

Finally, we write the User Defined Function, which puts this all together:

DELIMITER $$

DROP FUNCTION IF EXISTS `geoips`.`getCountry`$$
CREATE FUNCTION `geoips`.`getCountry` (pIp CHAR(16)) 
RETURNS VARCHAR(100)
BEGIN
  DECLARE _ip1, _ip2, _ip3, _ip4, _ip_value INT UNSIGNED;
  DECLARE _country VARCHAR(100);

  SELECT substring_index(pIp, '.', 1),
     substring_index(pIp, '.', -3),
     substring_index(pIp, '.', -2),
     substring_index(pIp, '.', -1) INTO _ip1, _ip2, _ip3, _ip4;

  SELECT 16777216 * _ip1 + 65536 * _ip2 + 256 * _ip3 + _ip4 
    INTO _ip_value;

  SELECT b.country
    FROM ip_ranges a INNER JOIN ip_countries b
    ON a.code = b.code
    WHERE ip_from = _ip_value
    INTO _country;

  RETURN _country;

END$$

DELIMITER ;

I used the substring_index function to extract the 4 parts of the IP address. With ‘help substring_index’ we can ask the mysql client to give us an explanation of this function:

mysql> help substring_index;
Name: 'SUBSTRING_INDEX'
Description:
   SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count
occurrences of the delimiter delim.
If count is positive, everything to the left of the final 
delimiter (counting from the left) is returned.
If count is negative, everything to the right of the final 
delimiter (counting from the right) is returned.
Examples:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

Finally, a little test whether the getCountry UDF really works:

mysql> SELECT getCountry('62.46.14.132');
+----------------------------+
| getCountry('62.46.14.132') |
+----------------------------+
| Austria                    |
+----------------------------+
1 row in set (0.00 sec)

Great, it does – and that very quickly :-)!

Menu management with stored procedures (without Nested Set Model)

I’ve tried to create a database driven menu management. The menu items are stored in the database with an ID (menuitem) and a mother ID (motherid) which refers to its upper menu item or to 0, if it is a main menu item. Further we store the description, that’s the text that should be displayed in the menu and a filename, which we can use to store, where we want to link the menu item to. Finally we have a column itemsorder where we store, in which order the menu items should be displayed.

This table can store more than one separate menus, so we use the first column mainmenu to indicate, which menu we want to display.

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
`mainmenu` int(10) unsigned NOT NULL,
`menuitem` int(10) unsigned NOT NULL,
`motherid` int(10) unsigned NOT NULL,
`description` varchar(100) NOT NULL,
`filename` varchar(100) NOT NULL,
`itemsorder` int(10) unsigned NOT NULL,
PRIMARY KEY  (`mainmenu`,`menuitem`),
KEY `motherid` (`motherid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `menu` VALUES (1, 1, 0, 'Menu 1', 'menu1', 10);
INSERT INTO `menu` VALUES (1, 2, 0, 'Menu 2', 'menu2', 20);
INSERT INTO `menu` VALUES (1, 3, 2, 'Menu 2/1', 'menu2_1', 30);
INSERT INTO `menu` VALUES (1, 4, 2, 'Menu 2/2', 'menu2_2', 40);
INSERT INTO `menu` VALUES (1, 5, 4, 'Menu 2/2/1', 'menu2_2_1', 50);
INSERT INTO `menu` VALUES (1, 6, 4, 'Menu 2/2/2', 'menu2_2_2', 60);
INSERT INTO `menu` VALUES (1, 7, 6, 'Menu 2/2/2/1', 'menu2_2_2_1', 70);
INSERT INTO `menu` VALUES (1, 8, 4, 'Menu 2/2/3', 'menu2_2_3', 80);
INSERT INTO `menu` VALUES (1, 9, 2, 'Menu 2/3', 'menu2_3', 90);
INSERT INTO `menu` VALUES (1, 10, 2, 'Menu 2/4', 'menu2_4', 100);
INSERT INTO `menu` VALUES (1, 11, 10, 'Menu 2/4/1', 'menu2_4_1', 110);
INSERT INTO `menu` VALUES (1, 12, 11, 'Menu 2/4/1/1', 'menu2_4_1_1', 120);
INSERT INTO `menu` VALUES (1, 13, 11, 'Menu 2/4/1/2', 'menu2_4_1_2', 130);
INSERT INTO `menu` VALUES (1, 14, 13, 'Menu 2/4/1/2/1', 'menu2_4_1_2_1', 140);
INSERT INTO `menu` VALUES (1, 15, 11, 'Menu 2/4/1/3', 'menu2_4_1_3', 150);
INSERT INTO `menu` VALUES (1, 16, 10, 'Menu 2/4/2', 'menu2_4_2', 160);
INSERT INTO `menu` VALUES (1, 17, 2, 'Menu 2/5', 'menu2_5', 170);
INSERT INTO `menu` VALUES (1, 18, 0, 'Menu 3', 'menu3', 180);
INSERT INTO `menu` VALUES (2, 1, 0, 'Menu 1', 'menu1', 10);
INSERT INTO `menu` VALUES (2, 2, 1, 'Menu 1/1', 'menu1_1', 20);
INSERT INTO `menu` VALUES (2, 3, 2, 'Menu 1/1/1', 'menu1_1_1', 30);
INSERT INTO `menu` VALUES (2, 4, 1, 'Menu 1/2', 'menu1_2', 40);
INSERT INTO `menu` VALUES (2, 5, 4, 'Menu 1/2/1', 'menu1_2_1', 50);
INSERT INTO `menu` VALUES (2, 6, 4, 'Menu 1/2/2', 'menu1_2_2', 60);
INSERT INTO `menu` VALUES (2, 7, 1, 'Menu 1/3', 'menu1_3', 70);
INSERT INTO `menu` VALUES (2, 8, 0, 'Menu 2', 'menu2', 80);
INSERT INTO `menu` VALUES (2, 9, 0, 'Menu 3', 'menu3', 90);
INSERT INTO `menu` VALUES (2, 10, 9, 'Menu 3/1', 'menu3_1', 100);

Up to now, MySQL users had to do quite a lot of work on the client side to get all the information that’s necessary to build the menu. We need to know at which level a menu item resides and which menu items should be displayed and which should not. This example should only make the top level items, the items at the same level as the active item and the items right below the active menu item visible.

One choice would be to store redundant data, but if we want to have good database design, we don’t get along without doing some extra work. I’ve tried to get this done by a stored procedure. It was not easy, because there are still some limits on SPs that require some tricky parts to pass by some troubles. Some of them were solved by temporary tables.

Here’s the code of the stored procedure called getMenu. It takes 2 parameters, first which menu should be used (column mainmenu) and the second, which menu item should be active. If the second parameter is set to 0, the complete menu will be displayed.

DELIMITER $$

DROP PROCEDURE IF EXISTS getMenu$$
CREATE PROCEDURE getMenu(IN p_menu INT, IN p_active INT)
BEGIN
 declare activeMotherId INT;
 declare countrec INT;
 declare counter INT default 1;

 drop temporary table if exists t1;

 create temporary table t1
   select menuitem,
       0 as level,
       'n' as display,
       '-' as haschildren,
       motherid,
       description,
       filename,
       itemsorder
     from menu
     where mainmenu = p_menu
     order by itemsorder;

 update t1 set level=1, display='y' where motherid = 0;

 update t1 set display='y' where menuitem = p_active;

 drop temporary table if exists t2;

 create temporary table t2 select * from t1;
 update t1 set haschildren='+' where (select count(*)
   from t2 where t1.menuitem=t2.motherid) > 0;

 drop temporary table if exists t2;

 set countrec = countrec - (select count(*)
   from t1 where motherid = 0);

 select count(*) from t1 into countrec;

 while countrec > 0 do
   drop temporary table if exists t2;

   create temporary table t2
     select menuitem from t1 where level = counter;

   update t1 set level = (counter + 1)
     where motherid in (select menuitem from t2);

   drop temporary table if exists t2;

   select count(*) from t1 where level=0 into countrec;

   set counter = counter + 1;
 end while;

 if p_active = 0 then
   update t1 set display='y';
 else

   select motherid from t1
     where menuitem = p_active into activeMotherId;

   update t1 set display='y'
     where motherid = activeMotherId or motherId = p_active;

   while activeMotherId != 0 do
     update t1 set display='y'
       where motherid = activeMotherId;

     select motherid
       from t1
       where menuitem = activeMotherId
       into activeMotherId;
   end while;
 end if;
 select * from t1;
 drop temporary table t1;
END$$

DELIMITER ;

First I move the relevant data to a temporary table called t1. This temporary table has 3 additional fields: level (which indicates the level of the menu item), display (‘y'(es)/’n'(o) – whether the menu item should be displayed or not) and haschildren (‘+’ – has child items, ‘-‘ – has no child items).

The following are some quite complex processes to get the right data into these new fields. Therefore I had to create 2 more temporary tables to store some data for a short amount of time. At the end, the result is being displayed with all data that’s required for comfortable processing in a PHP script like this:

<?php
ini_set('display_errors', 1);

$mysqli = 
   new mysqli('localhost', 'username', 'password', 'dbname');

if (mysqli_connect_error())
die ("Connection error: ".mysqli_connect_errno());

$activeId = isset($_GET['activeid']) ? $_GET['activeid'] : 1;

$sql = "call getMenu(1, ".$activeId.")";

$mysqli->multi_query($sql);

$result = $mysqli->use_result();

echo "<table>\n";

while ($data = $result->fetch_array(MYSQLI_ASSOC))
{
if ($data['display'] == 'y')
   {
   echo "<tr>\n";    
   echo "<td>(".$data['menuitem'].")</td>\n<td>".
      $data['haschildren']."</td>\n<td>";
 
   for ($i = 0; $i < $data['level']; $i++)
      echo "   ";
 
   if ($data['menuitem'] == $activeId)
      echo "<b>";
   
   echo "<a href='sp_menutest.php?activeid=".$data['menuitem']."'>".
      $data['description']."</a>";
 
   if ($data['menuitem'] == $activeId)
      echo "</b>";
 
   echo "</td>\n";
   echo "</tr>\n";
   }
}
echo "</table>\n";

$result = $mysqli->next_result();
$result = null;

$mysqli->close();
?>

Enjoy! Further examples using the Nested Set Model will follow.