If you’re interested in how these three databases measure up in terms of feature sets, take a look at part one in a two part series I wrote over at Database Journal.

I discuss stored procedures, views, materialized views or snapshots, triggers, and security. Stored procedures and functions are supported on all three databases, as are views and triggers. Although MySQL and Postgres aren’t there in terms of default snapshot support, there are ways to get that functionality in a somewhat roundabout way.

Security is always a tricky question, as all the bugs out there aren’t always publicized. It’s sort of a cat and mouse game. All three databases support user based authentication to login to the database, and various privilege levels to control access to objects and data. Oracle also supports FGA or fine grained access control for …

I was just searching for something and found this MySQL bug report:

Other JDBC drivers I have used make toast for breakfast... MySQL Connector/J doesn't make toast, it can only pour a bowl of froot loops...

...Thank you for taking the time to write to us, but this is not a bug... I believe you should look into a device called a \"toaster\" to make your toast.

Maty Asay points us to .. yet another incomplete list of Open Source events on this planet

Now if one could just make either an RSS feed of this or a create a calender out of it so we can integrated it in our favourite calendaring client. Or just create a group on upcoming and keep that group consistently filled up.

They probably should already add Fosdem.org and Linuxtag.org 2007
as the dates for those conferences are already announced.

They should also keep an eye on Sven Guckes page as he has some updates on events.
and als on Martin Schulzes page

Anyone else knows some locations where to …

I’ve been investigating some recent issues with installations of MySQL on Mac OS X and the Perl DBD::mysql module for accessing MySQL from within Perl through DBI.

The problem exists only with binary distributions of MySQL and is related to the installation location of the libraries for the MySQL client that DBD::mysql uses.

By default these are installed into /usr/local/mysql/lib, but the dynamic libraries are configured to be located within /usr/local/mysql/lib/mysql.

It’s possible for DBD::mysql to build and link correctly, but trying to use the library will fail because it can’t find the library in the latter directory, even though it linked to the library in the former location. To get round this, the easiest method is to create a link within the directory that points to the parent. For example:

$ cd /usr/local/mysql/lib
$ ln -s . mysql

That …

Use MySQL? Fancy meeting other database geeks? In Melbourne? Free on Wednesday evening? Then come to the MySQL Meetup on Wednesday, October 17, from 7:00PM onwards. Its at Building 10, Level 8, Room 4, at RMIT University, so there’s easy access for all.

The agenda this month is simple:

• Using MySQL and JDBC, presented by Minh Van Nguyen
• A quiz of what not to do in MySQL, presented by Arjen Lentz (guest, from Brisbane)

There’s a trip to the pub later that night. There might even be dinner before-hand. If you have suggestions, make sure its vegan-friendly (hi Stewart!).

Technorati Tags: mysql, meetup

I've been trying to circle back and clean up things I left for later in several chapters of High Performance MySQL, second edition. This includes a lot of material in chapter 4, Schema Optimization and Indexing. At some point I'll write more about the process of writing this book, and what we've done well and what we've learned to do better, but for right now I wanted to complete the picture of what material we have on schema, index, and query optimization. The last two chapters I've written about (Query Performance Optimization and Advanced MySQL Features) have generated lots of feed back along the lines …

I was just searching for something and found this MySQL bug report: Other JDBC drivers I have used make toast for breakfast... MySQL Connector/J doesn't make toast, it can only pour a bowl of froot loops... ...Thank you for taking the time to write to us, but this is not a bug... I believe you should look into a device called a "toaster" to make your toast.

The new release of MySQL Toolkit (version 1051) updates MySQL Parallel Dump in minor ways, but more importantly, it adds MySQL Parallel Restore. MySQL Parallel Restore is the reverse of MySQL Parallel Dump. You give it one or more files and/or directories, and it discovers all the files contained within them and loads them in parallel. It understands how to load SQL and/or TXT/CSV files. If you give it some of both, it loads the SQL first and then loads the TXT/CSV as delimited files with LOAD DATA INFILE.

Have you ever needed a random timestamp in MySQL? For example to create demo data programmatically? Here’s my solution:

SELECT FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP('2007-01-01') +
        RAND() *
        (UNIX_TIMESTAMP('2007-01-03')-UNIX_TIMESTAMP('2007-01-01'))
    )
) as random_timestamp;

Or if you prefer a function:

CREATE FUNCTION random_timestamp (start TIMESTAMP, end TIMESTAMP)
RETURNS TIMESTAMP NOT DETERMINISTIC
RETURN FROM_UNIXTIME(
  FLOOR(
    UNIX_TIMESTAMP(start) +
    RAND() *
    (UNIX_TIMESTAMP(end)-UNIX_TIMESTAMP(start))
  )
);

mysql> select random_timestamp('2007-10-01', NOW());
+---------------------------------------+
| random_timestamp('2007-10-01', NOW()) |
+---------------------------------------+
| 2007-10-05 23:07:11                   |
+---------------------------------------+
1 row in set (0.00 sec)

I've been doing a few experiments with ENUMs and SQL_MODE.

Not a lot of people realise that in MySQL, an ENUM can actually contain one other value as well as the specified ones - no, not NULL, but ANOTHER value, specifically, the empty string ''.

This can lead to trouble. The empty string is entered by MySQL when it tries to insert an invalid value into the column. It does also give a warning, but nobody takes any notice of those right?

Demo schema:

CREATE TABLE enumtest (
 id int not null auto_increment,
 name varchar(100) NOT NULL,
 status ENUM('ok','broken','decommissioned','narnia'),
 PRIMARY KEY(id),
 KEY(name),
 KEY(status)
);

Now let's try some inserts...

INSERT INTO enumtest (name,status) VALUES
 ('Mark','ok'),
 ('Fred','broken'),
 ('Bob','decommissioned'); …