Peter Gulutzan tries out some trigger (MySQL 5.0) basics in the article here:
http://www.onlamp.com/pub/a/onlamp/2005/02/03/triggers.html

There’s a poll about what db do you reckon “SunDB” might (or should) be based on?
So come on everybody, get your vote in…
http://www.manageability.org/polls/sun-db/mpoll_poll

The story (much as there is) of Scott McNealy floating the ‘SunDB’ idea:
http://news.com.com/Sun+floats+open-source+database+idea/2100-7344_3-5562799.html

So what do I think about it? I don’t really have an opinion on whether having MySQL in there is desirable or not… it was all very vague. But you might as well regard the poll as a “what db would you like to see go even further”.

John Coggeshall has written a useful bit of PHP code that will allow an application written for the mysql extension (PHP4) to be used with the new mysqli extension (PHP5).
John writes this:

MySQL2i is a relatively simple compatiability layer between legacy code in PHP 4 which uses the old ext/mysql extension to the new MySQLi extension in PHP 5. Just include this file at the top of your scripts when you only have MySQLi installed and your ext/mysql code should work just fine.

You can grab MySQL2i from here: http://www.coggeshall.org/oss/mysql2i/

And yes, I believe it is possible to link both the mysql and mysqli extensions into PHP5, but it can be tricky in some situations. With the above, it’s no longer necessary. Very nice work, John!

So, you do

GRANT REPLICATION SLAVE ON *.* to 'repl'@'192.168.0.%' IDENTIFIED BY 'slavepwd';

and then

SHOW GRANTS FOR 'repl'@'192.168.0.%';

still shows only

GRANT USAGE ON *.* TO  'repl'@'192.168.0.%' IDENTIFIED BY ...

So the grant just disappeared… can have you going for while, eh?
The reason will be that you did an upgrade of the MySQL Server, without updating your privilege tables.
You always need to run the mysql_fix_privilege_tables script after doing an upgrade. Only the 4.1 Windows installer and the RPMs automatically do this for you. If you install from the binary tarball, or from source, you definitely need to run it yourself. It’s just part of the normal procedure.

I suppose MySQL could handle this better… I filed a bug report for it (#8308). Meanwhile, this may save you some headache.
This is one reason why MySQL developers hang out on public IRC channels like #mysql on irc.freenode.net… it’s educational.

Many applications need to find all rows before or after a specific date, or within a certain range. Of course you need to define a DATE, TIME, DATETIME or TIMESTAMP column in the table.

To find all rows where the date is within the last week:

  SELECT ... WHERE d >= (CURRENT_DATE - INTERVAL 7 DAY)

Easy, isn’t it? There are also keywords for CURRENT_TIME, CURRENT_TIMESTAMP, and also the commonly used NOW() which is a synonym for CURRENT_TIMESTAMP.
The INTERVAL clause can also be used with temporal entities other than DAY. The keyword is always specified in singular (i.e., DAY not DAYS).

For an overview of all temporal functions, see http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

To efficiently find all rows from a specific day if you have a DATETIME column:

  SELECT ... WHERE ts BETWEEN '2005-02-03 00:00:00' AND '2005-02-03 23:59:59'

If instead you did processing on the ts column to get the date separately, MySQL would’t be able to use an index for the lookup. Of course, in this case it would be more appropriate to use separate DATE and TIME columns instead. Always design your tables according to what your later queries need!

For an overview of all temporal column types and their use, see http://dev.mysql.com/doc/mysql/en/date-and-time-types.html

Naturally, you can also use such WHERE clauses in UPDATE and DELETE statements, for instance to delete old articles, or expire certain privileges.

Take a peek at http://www.ensembl.org/, the Ensembl Genome Browser.
There’s the human genome, and a variety of other animals as well as diseases.
What’s the link? Well, the data is stored on and searched with MySQL, and you can directly download a MySQL format SQL dump.

As I understand it, some genomes have DNA sequences longer than 1GB (that need to stay as a single entity for analysis), and MySQL is one of the few RDBMS that can a) handle such large TEXT/BLOB fields and b) has the ability to search for substrings fairly efficiently.

To give you some background info on the latter, MySQL version 4.0 and above implements the Boyer-Moore optimization for LIKE “%pattern%” searches. As you know, a LIKE pattern that starts with a wildcard can never use an index (except FULLTEXT indexes, see below). It’s like searching the phonebook for people whose second letter of their name is “e”, you need to go through all the pages anyway…
So MySQL needs to do a table scan, looking at the relevant column in each row of the table. With Boyer-Moore, this becomes less tedious. For example, if the pattern to search for is “bla”, MySQL only has to check each 3rd character in the stored data, checking if it is a “b”, an “l”, or an “a” (it does that through a reverse lookup using a binary search, but don’t worry about that). If it is none of those, it can safely skip another 3 characters.

Now, you may forget all of this techie detail if you want, the important thing to remember is that when you need to do a search on a pattern starting with a wildcard, MySQL cannot use an index, but will use the abovementioned optimization. And the consequence of the Boyer-Moore algorithm is that the long the pattern, the bigger the hops that MySQL can take through the column…
(if you’re not yet using at least a 4.x version of MySQL, all the more reason to upgrade!)

Of course, if you’re searching for whole words (i.e. in a text) rather than part of a contiguous string (like a DNA sequence), FULLTEXT indexes are a better choice. But that’s another story, perhaps for another day. The manual info for FULLTEXT is at http://dev.mysql.com/doc/mysql/en/fulltext-search.html

At the MySQL User Conference, Matt Raible (http://raibledesigns.com/) will be doing a tutorial about AppFuse. Now, I’m not a Java expert myself, but people tell me it’s hot stuff, and Matt is the guy who wrote AppGen and AppFuse so he definitely knows what he’s talking about.

Here’s a more complete description about the tutorial:

One of the hardest parts about J2EE development is getting started. There is an immense amount of open source tools for web app development. Making a decision on which technologies to use can be tough–actually beginning to use them can be even more difficult.

Once you’ve decided to use Struts and Hibernate, how do you go about implementing them? If you look on the Hibernate site or the Struts site, you’ll probably have a hard time finding any information on integrating the two. What if you want to throw Spring into the mix? For developers, one of the best ways to learn is by viewing sample apps and tutorials that explain how to extend those applications. In order to learn (and remember) how to integrate open source technologies such as Hibernate, Spring, Struts, and Ant/XDoclet, Raible created AppFuse.

The beauty of AppFuse is you can actually get started with Hibernate, Spring, and Struts without even knowing much about them. Using test-driven development, AppFuse and its tutorials will show you how to develop a J2EE web application quickly and efficiently.

Tip of the day… many users have trouble accessing their MySQL Server from another machine. Most commonly, they are running the server on a Debian Linux system which, by default, disables networked access to the server – so only local connections are possible.

(the following info is somewhat Debian specific, as it uses paths different from some other distros)

• Go to /etc/mysql/ and edit the my.cnf file.
• Find the line that says skip-networking and comment it out with a # at the start of the line.
• If you want to limit access to one specific interface on the server machine, you can use e.g. bind-address=192.168.0.110.
• If you want to limit access to a number of machines, you can set up your GRANTs accordingly, and/oror set up your firewall rules according to your specific needs. The default MySQL port number is 3306.

Tip of the day… if your application uses features such as transactions or foreign keys, and you use the InnoDB storage engine for some (or all) of the tables, do
SHOW VARIABLES LIKE “have_innodb”
and check for
have_innodb having the value of YES
at the start of your application.
That covers InnoDB not having been excluded during compile (shown as NO), nor disabled in config (shown as DISABLED). Depending on your distribution of the MySQL server, such cases happen quite frequently, and of course you want your application to a) be immune to this and b) alert the user so the config be fixed.
Good programming practice!

Any version 4.0 or up of MySQL Server has InnoDB compiled in by default. For older versions (3.23), this was optional. InnoDB is a standard part of the server, and not meant to be excluded for normal operation. MySQL has a modular architecture, allowing certain standard features to be excluded/disabled for embedded and other applications that have specific requirements.

People, I need some help here. Many users run into the problem that on Gentoo, InnoDB is disabled. It’s not even compiled in. So, no transactions, no foreign keys. Unless you recompile.

Someone reported it as a Gentoo bug: http://bugs.gentoo.org/show_bug.cgi?id=44592
It was closed as “Won’t Fix” and the argument was
“USE=innodb greatly increases the MySQL compile time.
So for those that don’t want it, I see no reason to include it.”

I added a kind note asking for this to be changed/fixed. Please add your vote to this.
(you’ll need to login to the Gentoo bug system, or register if you’re new to it)
Thanks!