We often recommend to our clients to convert their current database from MyISAM tables to InnoDB.
The transfer by itself in most cases is almost plain, however the application can be broken by new unexpected errors
1205 (ER_LOCK_WAIT_TIMEOUT)

Lock wait timeout expired. Transaction was rolled back.

1213 (ER_LOCK_DEADLOCK)

Transaction deadlock. You should rerun the transaction.

It is not hard to handle these errors, but you should be aware of.
This is some thing we do in our PHP applications:

PLAIN TEXT CODE:

1. class mysqlx extends mysqli {
2.  
3. ...
4.  
5.   function deadlock_query($query) {
6.           $MAX_ATTEMPS = 100;
7.           $current = 0;
8.           while ($current++ <$MAX_ATTEMPS) …

I've been given the go ahead to release my NDB/Connectors code. These connectors wrap the NdbApi for a variety of languages, including Python, Perl, Java and C# at the moment. I'm managing development using Launchpad, so go to

https://launchpad.net/ndb-connectors

To get the latest version or status of the code. If you would like to contribute, feel free to branch a copy of the source using bzr and send me a revision bundle. There is also an ndb-connectors team on launchpad you can join if you'd like to participate more directly in the development. For either of these options to work, you need to first sign the MySQL Code Contributor License Agreement to assign copyright of your contributions to MySQL, Inc.

I hope to have a mailing list set up soon for discussion.

I came back from a two-week snowboarding trip to Zermatt -- my first to Switzerland, and what an excellent trip that was. Great powder, great freeriding, great weather. Will post some photos once I've sorted through them, but in the meantime, Sanna took some as well and posted them to her moblog.

On another note, I haven't mentioned Jim Starkey's comments to my previous post, but they're good reading to everyone interested in MySQL, with clarifications to some things I misunderstood in the documentation. I'm glad to hear that the "serial writes" don't in fact mean just one thread writing, as well as that he believes the engine will at a later stage allow multiple tablespaces per logical database.

I was poking around at credit cards this morning and came across this one that seemed like it would be a good fit for folks who would like to have Sakila (the MySQL dolphin) in their wallet wherever they go.

The colors aren't quite right, and the dolphin isn't moving upward like most of the MySQL logos, but you get the idea.

(I did not get a card, decided against it)

The problem of how to handle trees in SQL has been talked about alot. The basic 3 ways are:

• store the full path for each entry
• store the parent for each node
• use nested tree

Nested tree is good for read-many-write-less applications where the tree doesn't check over time too much as a write-operation is heavy-weight most of the time.

Referencing the Parent through the full path

Using the variant of the path involves a lot of string handling and is always slow. See below:

# use a full path to each node
CREATE TABLE tree_path (
  node_path VARCHAR(1024) PRIMARY KEY,
  name VARCHAR(32) NOT NULL,
  INDEX (name)
) ENGINE = innodb;

INSERT INTO tree_path VALUES
  ( '/0',     'Earth' ),
  ( '/0/0',   'Europe' ),
  ( '/0/0/1', 'Germany' ),
  ( '/1',     'Moon' ),
  ( '/0/1',   'Asia' );

# search for parent of 'Asia'
SELECT t1.name
  FROM tree_path AS t1
 WHERE t1.node_path = ( …

This time it's a quick function to validate email addresses, based on regexp.
It can be used in a trigger to add data validation, or to check data already in your database that needs a clean up ... really a simple wrapper around a simple regexp query, but it can be helpful.
Here it is:

1. DELIMITER $$
2. DROP FUNCTION IF EXISTS `test`.`is_valid_email` $$
3. CREATE DEFINER=`root`@`localhost` FUNCTION `is_valid_email`(p_email varchar(64)) RETURNS tinyint(1)
4. BEGIN
5. CASE
6. WHEN NOT (SELECT p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')
7. THEN
8. -- bad data
9. RETURN FALSE;
10. ELSE
11. -- good email
12. RETURN TRUE;
13. END CASE;
14. END $$
15. DELIMITER ;
    

As per Mushu's comment, this is much cleaner, oops:

I was browsing the web today and stumbled upon this. This oven line is great! but come to think of it, how many people can afford to buy this $7k+ gadget (see this MSN coverage). Even if they could buy it, how many will actually would call their oven or email their oven and tell it to cook? I do have to say its a great concept and honestly if I was a millionaire who could afford to buy cool gadgets, I wouldn’t mind getting one of these. Heck, if I owned a house here in LA, it would be something I would consider putting in my kitchen. It would be interesting to see what happens in next few years when gadgets like these might become normal appliances you get for your high end kitchen. Just like how everybody loves to get stainless steel appliances right now for …

After this post I've got a question how one can tell if his outer join was converted to inner. You can find it out by looking at the warning generated by EXPLAIN EXTENDED. If the outer join wasn't converted, you'll see it in the rewritten query in the warning:

mysql> explain extended select * from t1 left join (t2, t3) on t2.a= t1.a;
...
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a`
AS `a` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on ((`test`.`t2`.
`a` = `test`.`t1`.`a`)) where 1

In this query LEFT JOIN is not converted to inner.

Now let's try a query where outer join will be converted:

mysql> explain extended select * from t1 left join (t2, t3) on …

I’ll be speaking at the upcoming 2007 MySQL Conference & Expo (Why they dropped the word User, who knows), this time with Guy Harrison (Author of many books including MySQL Stored Procedures). We will be talking on MySQL for Oracle DBAs and Developers.

Anyway, good friend Paul McCullagh, creator of PBXT will be also speaking on PrimeBase XT: Design and Implementation of a Transactional Storage Engine. He coined to me in an email “CU at the UC”. I’ve done a further level of refactoring, and added marketing. …

Changing hats


Note: I have updated this article as of April 24, 2008 to indicate that you do not have to set auto_increment_increment or auto_increment_offset for the slaves. If you see this in the diagrams, please ignore.

The master sets the auto_increment value in it's binary log which the slave reads via replication. I may write an update to this article at some point soon.


I have put on the hat of a MySQL user, a customer, as opposed to a developer (Federated Storage Engine, DBD::mysql) these last couple months with my new employer, Grazr. One of the things we need is a whole replication set-up, with read-only slaves, and write masters. Thats master(s), with an 's', plural. We need a set-up where we have two data centers. In each of the data centers, there is a master and its slaves, all on the same network. That master needs …