Got number two ...

Today I have made my second step towards my goal of getting a row of IT certificates. After passing the MySQL Core Certificate three weeks ago, I passed the Zend PHP Certification today.

For me, today's exam was harder than the MySQL exam. One reason is certainly that I love to do things with MySQL and I do much more with MySQL than my actual work requires. So I have collected more experiences with MySQL than with PHP, which I use to do my work, but not so much for experimenting.

The second reason - the MySQL Study Guide covers the topics that will be on the exam very exactly. Therefore, it's also much bigger than the PHP Certification Study Guide. The latter also indicates what you will find on the exam, but don't expect that every little detail is mentioned in the PHP Study Guide. Comparing them, learning with the MySQL Study Guide was much easier for me. Finally, both exams might require quite the same amount of knowledge - I haven't found a big difference on this.

Well, now it's time to get myself ready for the MySQL Professional Exam. It might take some time, it's certainly not easy and even some good people haven't taken the exam at the first time - so I'll try to learn the material very closely. When it's done, you'll hear a loud 'wouhoooo' from me :-).

Upgrading from older versions to MySQL 5.0

I sometimes read the posts in the MySQL Newsgroup (mailing.database.mysql) and take a look if there are any questions where I can help other MySQL users. That's a good think for many purposes: I can help people, I can learn new things for myself and last but not least - there are often questions that give me interesting material for this weblog. Such as this:

There was a question, how to update to the latest MySQL 5.0 version - not only from 4.1 (which is described perfectly in the MySQL manual), but also from older versions like 3.23. Here's how I would do it:

* make a dump of all your data except the mysql database

* make a separate dump of the mysql database

* uninstall this old MySQL version

* install the latest MySQL version from scratch

* create a separate database for the previously dumped mysql database (e.g.
mysql_old) and restore this dump

* sync the old mysql database with the new one (this might require some
manual work, because the structures will be slightly different)

* when the new mysql database is OK, restore the dump of the data

If you have a chance, try to install the new version on a separate machine,
before you uninstall the old MySQL version to make sure that everythink works.

Selecting data by user in views

I have found an interesting request in the MySQL newsgroup asking, how data can be selected as of a user column in a table. Only the user who is stored in a record should be able to access and alter this row.

So this is a perfect example to demonstrate, how this can be accomplished by a view.

First I created a table called tt in the database test and filled it with sample data:

create table tt (
id int unsigned not null auto_increment primary key,
charvalue char(1) not null,
intvalue int unsigned not null,
allowed_user char(6) not null);

insert into tt (charvalue, intvalue, allowed_user) 
values ('a', 560, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('b', 931, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('c', 661, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('d', 246, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('e', 452, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('f', 130, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('g', 341, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('h', 847, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('i', 417, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('j', 151, 'user_f');
insert into tt (charvalue, intvalue, allowed_user) 
values ('k', 11, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('l', 66, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('m', 375, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('n', 412, 'user_g');
insert into tt (charvalue, intvalue, allowed_user) 
values ('o', 358, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('p', 78, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('q', 463, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('r', 773, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('s', 165, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('t', 740, 'user_e');
insert into tt (charvalue, intvalue, allowed_user) 
values ('u', 875, 'user_c');
insert into tt (charvalue, intvalue, allowed_user) 
values ('v', 793, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('w', 677, 'user_a');
insert into tt (charvalue, intvalue, allowed_user) 
values ('x', 133, 'user_b');
insert into tt (charvalue, intvalue, allowed_user) 
values ('y', 994, 'user_d');
insert into tt (charvalue, intvalue, allowed_user) 
values ('z', 479, 'user_e');

The column allowed_user specifies, which user should be allowed to access this row.

The next step is to wrap this table into a view (called tt_select) and limit it to be accessible for only the user who is logged in:

create view tt_select as
select * from tt
where allowed_user = substring(user(), 1, locate('@', user()) - 1);

The substring and locate functions cut off the host from the user name, so e.g. user@localhost becomes user (if the limit should apply to both user and host, you can also store the complete user@host information in the table and query 'where allowed_user = user()').

Finally I created the user accounts and granted the privileges exclusively for the view (not for the table):

GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_a'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_b'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_c'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_d'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_e'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_f'@'%' IDENTIFIED BY 'abc123';
GRANT SELECT , INSERT , UPDATE , DELETE ON `test`.`tt_select` 
TO 'user_g'@'%' IDENTIFIED BY 'abc123';

That's it :-). Lets test it using the MySQL console client, assuming that the user 'user_g' logs in:

E:\>mysql -u user_g -pabc123 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 181 to server version: 5.0.15-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tt_select      |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      375 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> update tt_select set intvalue=584 where id=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      584 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> delete from tt_select where id=13;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
4 rows in set (0.02 sec)

mysql> insert into tt_select values (13, 'm', 375, 'user_g');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt_select;
+----+-----------+----------+--------------+
| id | charvalue | intvalue | allowed_user |
+----+-----------+----------+--------------+
|  1 | a         |      560 | user_g       |
|  2 | b         |      931 | user_g       |
|  5 | e         |      452 | user_g       |
| 13 | m         |      375 | user_g       |
| 14 | n         |      412 | user_g       |
+----+-----------+----------+--------------+
5 rows in set (0.00 sec)

mysql> select * from tt;
ERROR 1142 (42000): SELECT command denied to user 
'user_g'@'localhost' for table 'tt'
mysql>

As we can see - it works fine!

Information schema diagram

Roland Bouman has created a perfectly organized diagram about the information schema tables:

http://www.xcdsql.org/Misc/MySQL_INFORMATION_SCHEMA.html

Roland's diagram has been awarded in MySQL's beta challenge (congratulations for this prize!).

Information schema has been introduced in MySQL 5 and provides all kinds of information about the database objects.

db4free.net offers MySQL 5.0.15-Max

db4free.net has been updated to MySQL 5.0.15-Max (this enhanced variant offers some additional Storage Engines like BerkeleyDB and Archive). This way, db4free.net is probably the first public MySQL provider of the world that offers a MySQL 5 Production Release.

I hope, many - also commercial - providers will follow soon!

Congratulations

... to MySQL AB for the new "baby" 5.0 and many thanks to everyone who has been involved in the process of making MySQL 5.0 a great product. Keep on the good work!

Web apps ready for MySQL 5?

I think there is no doubt that MySQL 5 is ready for web applications. But I'd like to put this question around and ask, whether the most popular web applications are ready for MySQL 5.

Well, this question is easy to answer, as long as MySQL 5 in not executed with any strict mode options. The behavior is similar to that of MySQL 4.x and so there should be no problems.

However, I have tested some popular web applications like Mambo/Joomla, phpBB and Typo3 with db4free.net, which uses the sql-mode:

"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION"

On Windows, this sql-mode is the default.

I'd like to start with Mambo. There is quite an old problem when multi byte character sets are used. Trying to install Mambo into a database using utf8, the creation of the tables fails here:

CREATE TABLE `mos_core_acl_aro` (
  `aro_id` int(11) NOT NULL auto_increment,
  `section_value` varchar(240) NOT NULL default '0',
  `value` varchar(240) NOT NULL default '',
  `order_value` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `hidden` int(11) NOT NULL default '0',
  PRIMARY KEY  (`aro_id`),
  UNIQUE KEY `section_value_value_aro` (`section_value`,`value`),
  UNIQUE KEY `mos_gacl_section_value_value_aro` (`section_value`,`value`),
  KEY `hidden_aro` (`hidden`),
  KEY `mos_gacl_hidden_aro` (`hidden`)
) TYPE=MyISAM

The reason is quite simple: the index length is limited to 1,000 bytes in MySQL. For single byte character sets, this is a legal statement, because the sums of the indexed column lengths are still less than 1,000 bytes. However, using utf8 or any other multi byte character set, this limit is exceeded. This problem isn't new in MySQL 5, it has also occured in MySQL 4.1.

In Joomla 1.0.3, this problem has been solved - I have been able to install Joomla 1.0.3 without any problems. But also Joomla is not free of MySQL 5 related problems. Creating a new user account, this account is created, but doesn't show up in the user management backend.

I also have found problems trying to log in at the frontend - it simply didn't work. Searching inside the code, I found the source of the problem. They tried to insert a quoted value into a tinyint field - something that's also invalid in strict mode.

The next web app that I've tested was phpBB. It already failed during the installation at this set of queries (as you can see, I've used the German version of phpBB):

CREATE TABLE phpbb_posts_text (
   post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
   bbcode_uid char(10) NOT NULL,
   post_subject char(60),
   post_text text,
   PRIMARY KEY (post_id)
);

INSERT INTO phpbb_posts_text (post_id, post_subject, post_text) VALUES 
(1, NULL, 'Das ist ein Beispiel-Beitrag. Du kannst ihn löschen, wenn Du 
willst, denn so wie es aussieht, hat die Installation geklappt!

Hier noch ein paar wichtige Informationen zu phpBB

Weitere Informationen zu phpBB und seiner Benutzung und Administration 
findest Du auf phpBB.de unter der URL - http://www.phpbb.de/doku/doku.php -.

Wenn Du Fragen hast oder Probleme mit einer Funktion, schau im Forum unter 
der URL - http://www.phpbb.de/index.php - nach.

Da die meisten Fragen schonmal gestellt und beantwortet wurde, nutze bitte 
die Suchfunktion unter der URL - http://www.phpbb.de/search.php - bevor 
Du Deine Frage ins Forum schreibst.

Erweiterungen (Mods) zu phpBB findest Du unter der URL - 
http://www.phpbb.de/moddb/ -.

So, und nun viel Spaß mit phpBB ;-)');

The reason for this can also be found easily. There is no default value for the column bbcode_uid, but no value has been provided for this column in the insert statement.

Without strict mode, MySQL uses a system defined default value (in this case, an empty string), but in strict mode, a value must be provided for any field without a default value.

The third application that I've tested was Typo3. With Typo3, no errors occured during setting up the tables, so this Content Management System could possibly work with MySQL 5 without errors.

I have reported the errors that I've found to Joomla and phpBB and I hope that they solve these problems soon. There's always the danger that MySQL 5 has to take the blame for bad written queries in the web applications, because the users often don't know what the real source of the problem is. So I think that another important job to support MySQL 5 is to make developers of these web applications aware of those problems, so that they can fix them as soon as possible until they all work well with MySQL 5, also.

Florian Müller vs. Bono

It's a tough decision, really! The singer of U2, one of the greatest bands (also see my profile to see, who I mean by that ;-)) who takes every opportunity to speak against poverty and discrimination in the world would of course have been a great candidate to me.

But the fight against software patents is so extremly important to developers and almost everybody involved with software in any way, makes me still believe that Florian Müller is the better choice at the 'Vote for the Europeans of the year'. Even though it wasn't easy not to vote for Bono - I gave my vote to Florian (even though I don't know much about himself, I believe that the subject is so important that it had to be done).

I think, there's also a strong political statement behind the vote for Florian Müller and the NoSoftwarePatents project (without supporting any political party in particular), whether we support freedom and liberty or whether we want to be dictated by a few giant enterprises.

And for U2 - I'll instead buy the new DVD (where I had the great opportunity to see one of their shows on the Vertigo Tour!), so everybody has his benefits :-).

New manuals for each version

There's a split up of the MySQL manuals to one for each version, including MySQL 4.1, 5.0 AND 5.1 (!).

They can be accessed at http://dev.mysql.com/doc/refman/4.1/en/index.html, http://dev.mysql.com/doc/refman/5.0/en/index.html and http://dev.mysql.com/doc/refman/5.1/en/index.html.

Very interesting is the Change History on MySQL 5.1 which you can find at http://dev.mysql.com/doc/refman/5.1/en/news.html. It seems, the release of the first 5.1 alpha version is not far ahead.

What a great week ...

... in terms of MySQL :-))). On Friday I passed the MySQL Core Exam (on my first try) and today I was announced as the winner of the 2nd IPod on the MySQL 5.0 contest (http://www.planetmysql.org/kaj/?p=21.

That's even greater if you know that I'm just a little self-employed developer, starting just 2 1/2 years ago with no big company behind me. I once used to believe that there is some software on the market and you can use it - but you have to take it like it is and if there's something you'd think that could be improved, you have to live with it. MySQL convinced me that I was wrong. When the first alpha versions of MySQL 5 came out, I was curious about it and I started testing - I wrote bug reports when I found that something wasn't working the way I expected and also made recommandations how I thought that features could work better or which features I was missing. And I was surprised to see how quickly developers reacted on my input, cleared bugs and discussed about suggestions - and I saw that even I little guy can make a contribution to improve a product that's used by some million users around the world. That's one of the things that I love about MySQL - a great community of a product that's great and still trying to become better and better and a product that comes with a license that's fair to users with a good balance between freedom and a commercial approach (http://www.mysql.com/network/compare.html) - giving every user the choice to choose what's best for him.

How many people love to use MySQL was easy to see when I together with two friends of mine started the db4free.net project at the end of June. Even without any budget for advertising, there are now more than 1,100 users from 53 countries from all continents. db4free provides the very latest version of MySQL 5.0 and is always being updated immediately when a new version is released. There are plans to also install a second MySQL server with MySQL 5.1 soon after its first alpha release and to give users the option whether to create a database account on the MySQL 5.0 server or on the 5.1 server.

So there's still a lot of great things to come and a lot of work to do. I'd like to thank everyone who's involved in making MySQL a great product and ask everyone to continue doing great work. I'm looking forward to seeing the first MySQL 5.0 Production Release and I'm very curious about the MySQL 5.1 version :-).

MySQL 5 certifications

There were a few days of silence in my weblog - but for a good reason. Yesterday I've passed my MySQL Core Exam - and there are plans for more :-). My goal is to end up with the new Developer and Administrator Certificates for MySQL 5.0, as well as some other certificates (PHP, Java).

As it happens, I also received my new MySQL 5.0 Study Guide yesterday. I've read a lot about the MySQL certifications on the MySQL website and in the forum, so I'd like to take this opportunity to sum up the most important details that I've learned about the ways to get certified.

There are two certification levels based on MySQL 4.1 that are currently offered - there's a Core Exam which covers the topics that a developer needs to know and a Professional Exam which is mostly about administration of a MySQL server. The Core Certificate is a requirement that someone can take the Professional Exam.

I've read that the new MySQL 5 certificates will be available about 2 or 3 months after MySQL 5 becomes a Production Release. There will be 2 exams for each of the Developer Exam and the Administration exam. As soon as somebody has successfully taken both exams for either the Developer Exam or the Administration Exam, he will be entitled as CMDEV which is a 'Certified MySQL Developer' or a CMADM which is a 'Certified MySQL Administrator'. At the beginning, the 5.0 exams will be offered as Beta exams - probably with a price discount. Therefore, it might take some time until the candidate knows whether he has passed or failed on the exam.

There will also be upgrade exams from the 4.x exams to the 5.x exams, which might also come at a discount price. I don't know all the details about them, but as far as I know at the moment, there might be one upgrade exam from Core Certificate to CMDEV and another from Professional Certificate to CMADM. For the MySQL 5 exams, it will not be necessary to take the exams in any particular order, as it was required for the 4.x exams.

So if I take my personal example (having already passed the Core exam and planning for both new 5.0 exams), there are 2 ways to achieve this goal. I can either take the Professional Exam and then upgrade both exams to the new 5.0 exams, or I can wait until the 5.0 exams are out and then take the 2 Administration exams and upgrade my Core Certificate to the new Developer Certificate. Both ways end up with 3 more exams.

The most important benefit for me is the following. Even if you know a lot about the technices (e.g. MySQL, but also others) and have good skills on these, it's still very hard to proof your skills to other people - mainly to people who are not developers themselves. It's very hard to convince your customers, but also employers, if you apply for a new job, that you have good skills. There are many competitors around and there are many who pretend that they are good on their job, even though they are only good in selling themselves. Often, these guys get a job more easily than somebody who has better skills.

But with certificates, there's a big advantage against those people who are good in selling, but not as good in developing.

Addition to my Locate IP addresses article

This refers to my article Locate IP addresses and to Arjen's article Storing an IP address in a database table to show you, how the function getCountry can be written more efficiently:

DELIMITER $$

DROP FUNCTION IF EXISTS `getCountry`$$
CREATE FUNCTION `getCountry`(pIp CHAR(16)) RETURNS varchar(100)
BEGIN
  DECLARE _ip_value INT UNSIGNED;
  DECLARE _country VARCHAR(100);

  SELECT INET_ATON(pIp) 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 AND
      ip_to >= _ip_value
    INTO _country;

  RETURN _country;

END$$

DELIMITER ;

Locate IP addresses

This blog post has moved. Please find it at:

http://www.mpopp.net/2005/10/locate-ip-addresses/.