On what was said by Monty.

I think that in any dispute there are never two separate positions; there’s no a “black position”, there’s no a “white position”. I think about a “gray gradient” where there exists several different positions and opinions. Maybe the truth in this case is “gray” colored!
As a good Italian I stay in the middle thinking that there are some truths in the criticism of Monty, but at the same time I think there’s also exaggeration.
But mine is not a relevant opinion! I don’t have a deep knowledge of MySQL AB organization and I don’t know all the facts and the people involved. My contribution to the discussion is very simple and humble, just to say that I’m using 5.1 version since August 2008 in a more than 2 million queries per day production environment and I never experienced a server crash (ok, sometimes it crashed … but that was my own fault!).
For the goals of my applications I found partitioning useful and powerful. In my experience MySQL 5.1 works!

Well, it is certainly true that there are bugs, but there has never been a software product without them, even when declared stable.

In an optimistic way, I feel Monty wanted to encourage MySQL AB and all the developers to do their best in the future to develop a better product. If there were errors somewhere in the development of 5.1 I think it’s time to correct them. Errors are a significant part of human nature, we couldn’t do anything without them. There is no progress without errors.

I’m one of the winners of the MySQL 5.1 Use Case Competition and as an award I won a dinner with Monty. I would dine with him ardently (note for the cook: I like fish ) and I hope this could be still possible.

Follow the link: MySQL 5.1 Use Case Competion

… California dreamin’

Here is just a simple stored function to capitalize the first letter of each word in a string.

Sometimes it’is useful to convert name of cities and persons using the correct capitalization.

DROP FUNCTION IF EXISTS `capitalize`;
DELIMITER $$
CREATE FUNCTION `capitalize`(stringa TEXT) RETURNS text
    DETERMINISTIC
BEGIN

        DECLARE i INT DEFAULT 2;
        DECLARE sout TEXT;
        DECLARE ucn INT DEFAULT 0;

        SET stringa=LCASE(TRIM(BOTH ' ' FROM stringa));
        SET sout=UCASE(LEFT(stringa,1));

        WHILE i <= LENGTH(stringa) DO

                SET sout = CONCAT(sout,IF(ucn=1,UCASE(SUBSTRING(stringa,i,
1)),SUBSTRING(stringa,i,1)));

                SET ucn = IF(SUBSTRING(stringa,i,1)=' ',1,0);

                SET i = i + 1;

        END WHILE;

        RETURN sout;

END
$$

Example:

mysql> SELECT capitalize(' new york ');
+--------------------------+
| capitalize(' new york ') |
+--------------------------+
| New York                 |
+--------------------------+

I started to use MySQL 5.1 in a production environment at the beginning of August 2008. My purpose was to log all my web visits (more than 1 million pages per day) and to calculate real-time and on-demand statistics.
New features such as partitioning and event scheduler gave to me the opportunity to solve some problem.

I sent my “use case” to the competition proposed by mysql.com and now there’s an article on the developer section of the site where you can read what I did.
If you like, please read it.

(If you are here following the link on the article on mysql.com … don’t mind … and thanks)

In MySQL 5.1 Cluster Certification Study Guide at page 151 there’s the following sentence: “InnoDB operates with the REPEATABLE READ isolation level, which still allows phantom reads but suppresses non-repeatable reads”.

The sentence is wrong: the REPEATABLE READ isolation level for InnoDB engine doesn’t allow phantom reads.

In some database systems, REPEATABLE READ isolation level allows phantoms, such that if another transaction inserts new rows in the interval between the SELECT statements, the second SELECT will see them. This is not true for InnoDB; phantoms do not occur for the REPEATABLE READ level. SERIALIZABLE isolation level is similar to REPEATABLE READ with the additional restriction that rows selected by one transaction cannot be changed by another until the first transaction finishes. (as stated by MySQL 5 Certification Guide)

Here is an example to demonstrate such a behaviour.

To distinguish two different sessions I use two different prompts: s1> and s2> setting the mysql’s PROMPT statement.

Create a table on s1 and disable autocommit mode to begin a new transaction:

mysql> prompt s1>
PROMPT set to 's1> '
s1> 

s1> create table t_idb( id int) engine=innodb;
Query OK, 0 rows affected (0.10 sec)

s1> set autocommit=OFF;
Query OK, 0 rows affected (0.00 sec)

Disable autocommit mode on s2 too to begin a new transaction

mysql> prompt s2>
PROMPT set to 's2> '

s2> set autocommit=OFF;
Query OK, 0 rows affected (0.00 sec)

Insert some values on s1

s1> insert into t_idb values(1);
Query OK, 1 row affected (0.00 sec)

s1> insert into t_idb values(2);
Query OK, 1 row affected (0.00 sec)

Now, on s2 we won’t be able to see these values because s1 is still in the middle of the transaction (not yet committed). For example, this wouldn’t be true in the case of READ UNCOMMITTED mode.

s2> select * from t_idb;
Empty set (0.00 sec)

Now, we commit the transaction on s1.

s1> commit;
Query OK, 0 rows affected (0.01 sec)

Read again the table on s2.

s2> select * from t_idb;
Empty set (0.00 sec)

s2 is still in the middle of his transaction and thus it doesn’t see committed inserts by the another one. This is in contrast with the sentence on the Cluster guide.

s2 will be able to see the values inserted by s1 only after his transaction will be finished (committed or rollbacked)

s2> commit;
Query OK, 0 rows affected (0.00 sec)

s2> select * from t_idb;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)