The latest from the MySQL community

It recently came up that it would be helpful if we had a cheat sheet to find out the machine names for any given UNIX. I knew these off the top of my head but it would be great if people added more as comments.

HP/HP-UX: /bin/uname -i
IBM/AIX: /bin/uname -m
SGI/IRIX: /sbin/sysinfo -s
Sun/Solaris: /usr/ucb/hostid

When dealing with multicolumn indexes the order of the columns in the index definition is very important. Fpr example if you have such an index

INDEX (a,b,c)

and you issue a query such

... WHERE b>1000;

MySQL will not be able to use the index. In case ol multicolumn indexes MySQL will always use the left-most part of the index. So, the index will be used for example if the query would be:

... WHERE a>100 AND b>1000;

So, MySQL will use the index only for search conditions that involve the follwing columns:

• a ,b ,c
• a, b
• a

The mulitcolumn indexes are also useful in solving ORDER BY and GROUP BY operations.
Let’s see the folliwing example.
Here is my customer table (for sake of simplicity it’s just a subset of my real table).

corra@localhost> show create table customer\G …

Avid readers of my blog know that I have been pondering how to best approach schema evolution. I ported the schema management from Metabase to PEAR::MDB2_Schema. I even gave a talk that admittedly only did a good job of defining the problem and solutions that all suffered from severe limitations. Now for the first time I am starting to feel somewhat good about an approach to migrations. I wrote a post to the Doctrine developers mailinglist detailing the key pieces that I want to add to Doctrine's migrations. The idea is to use the migrations approach made popular by …

Table joins are fun and useful, but did you know that there are circumstances that a php loop will beat out table joins?For those of you who already know this, feel free to return to www.PlanetMySql.com. But for the rest of us, read on...The Setup (fictional, but mimics my production environment)I have two tables, employees and termination_info. Employees has 150,000 records and

A

We have taken over an application which had "pretend" failover. Essentially it had two servers with automatic failover between the two. However they both relied on another server for providing the data storage via NFS all the way down to the MySQL server. Interesting how one can manage to provide no real failover with 3 servers. Obviously we want to fix this and actually a number of servers have already been bought. So now we have moved the MySQL server with a cold standby on separate machines. We also have 3 frontend servers which we want to load balance. We will probably use memcache to manage the sessions, as we are not so worried about a lost session when a crash occurs. But now comes the problem: There are a lot of places where administrators and end users can upload files, which end up in the file system. Now how do we replicate those files across all the nodes?

The temporary approach we will be taking will be using NFS with rsync. One …

If you do serious web development, you sure have multiple working environments. This is true for ruby on rails, but I’ll guess you’ll do the same using other frameworks or languages. I’ve found a quite useful Firefox extension to switch from development to production (and back) with just a click (or a keystroke: Ctrl-Shift-X): Server Switcher

This is helping me redesigning the MySQL training part of our website, and can be useful in some other situations.

I’ve written in the past about how enterprise management vendors can act as “Gatekeepers of the Datacenter” by virtue of what technologies they do or don’t support as part of their management solutions. This rather lame dynamic is a big part of the reason why a lot of otherwise great technologies dont make it all the way into the traditional enterprise.

The problem gets further compounded when one of these “Gatekeepers” is also a platform or stack vendor. See, it’s hard to resist the temptation of delivering the absolute best management for IBM products from a Tivoli solution while shortchanging non-IBM ones. Or, to lay this on one of the aspiring members of the big 4… how about getting support for SQL Server on Oracle’s Enterprise Manager. Hmmm… I’m gonna guess it sucks because Oracle wants you using their database. Besides, who uses OEM that isnt already an Oracle db customer?

Lucky for us, Hyperic has always …

Today I answered to a problem regarding fulltext indexes on an italian newsgroup. The guy was in trouble in building a multicolumn fulltext index. MySQL always said that a column cannot be part of the index. Why?

Remember that all the columns in a fulltext index must have the same charset and the same collation.
Let’s try if it is true!

Create a sample news table and try to build the fulltext index on (news_title, news_text):

mysql> CREATE TABLE news(
        -> id INT auto_increment,
        -> news_title VARCHAR(100),
        -> news_text TEXT,
        -> PRIMARY KEY(id))
        -> CHARSET latin1 COLLATE latin1_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FULLTEXT INDEX ft_idx ON news(news_title,news_text);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Great, it works!

And now, change the charset of one of the field and try …

After successfully passing the MySQL 5.0 Developer exams earlier this year the next step was preparing for the Zend Certified Engineer.

For preparation I first took php|architect's Zend PHP 5 Certification Study Guide from the bookshelf and started reading. The book gives a good overview of the topics covered in the exam and I even learned a few things while reading that I did not knew of before. Unfortunately it contains too many errors and mistakes in the printed examples and there is no errata on the internet.

After reading through the book I first did not really knew how to proceed in preparing for the exam. I did not have the feeling of being prepared to pass an exam, although I would think myself of a quite good and experienced PHP programmer. A really good thing is that everyone at Mayflower has the possibility to take some text exams at …