During the past two weeks, I've been quite busy getting to know Ubuntu 5.10 "Breezy Badger".

(In the remainder of this entry, the phrases within parentheses are probably a "duh!" to Ubuntu, Debian and other Linux users, but they may be of interest to Microsoft Windows users).

Non-linuxers and Microsoft Windows users (I am a Windows XP Professional User) particularly: Ubuntu is a Linux distribution that comes with a "Desktop" environment. The operating system and the applications can be controlled entirely using a Look and Feel that is quite similar to the Microsoft Windows Environment. Standard Ubuntu comes with the Gnome desktop environment. (I don't know how it is for other Linux Desktop environments, and I don't even know if there are differences between different Gnome deployments, but I've got a desktop …

MySQL has announced a new user survey over the holidays.  The survey will take about 15-20 minutes to complete and has questions about how you're using MySQL, what features are important to you and what areas you'd like MySQL to work on.  This is a great way to influence our strategy and directions.  It helps our product managers and engineers focus on the features that are important to you.  And you may even win an iPod Nano in the process! 

• MySQL: User Survey

MySQL has announced a new user survey over the holidays.  The survey will take about 15-20 minutes to complete and has questions about how you're using MySQL, what features are important to you and what areas you'd like MySQL to work on.  This is a great way to influence our strategy and directions.  It helps our product managers and engineers focus on the features that are important to you.  And you may even win an iPod Nano in the process! 

• MySQL: User Survey

Some quickies that have been sitting in my blog poster for a while…

• Quick Polls are an interesting bit of the MySQL site. Look at all the interesting polls, and see where the community come from!
• I was doing a search on “migrate from Postgresql to MySQL” on Google, and all I seemed to have found in general were sites that showed how to do it the other way around!
• mytop is great. RPM distributions have /var/run/mysqld/mysqld.sock, but it is located at /tmp/mysqld.sock if it comes out of the tarball. Run it as mytop -S /tmp/mysqld.sock if you’ve installed it from the tarball.
• Seeing: Starting MySQL * Couldn't find MySQL manager or server as an error? Suggest you look at /etc/my.cnf, under [mysql.server], comment out (#) the …

Today I found a question in a newsgroup asking for help with creating a join. This join should take a date out of table a and find the record in table b which has a date less than that of table a, but still closest to that date. The tables should be joined through the id column.

Here's my example data (I've changed it a bit from the original request):

mysql> SELECT * FROM a;
+-----+----+------------+
| row | id | d          |
+-----+----+------------+
|   1 | 15 | 2005-12-25 |
|   2 | 20 | 2005-12-29 |
|   3 | 25 | 2006-01-12 |
|   4 | 30 | 2006-01-15 |
+-----+----+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM b;
+-----+----+------------+
| row | id | d          |
+-----+----+------------+
|   1 | 15 | 2005-12-19 |
|   2 | 15 | 2005-12-21 |
|   3 | 15 | 2005-12-27 |
|   4 | 20 | 2005-12-11 |
|   5 | 20 | 2005-12-30 | …

The information_schema tables offer all kinds of useful metadata. Also all information about indexes are included in them, although they are split up among several tables.

I've built up a query that shows all the information about indexes, here it is:

SELECT a.table_schema, a.table_name, a.constraint_name, 
       a.constraint_type,
       convert(group_concat(DISTINCT b.column_name
       ORDER BY b.ordinal_position SEPARATOR ', '), char) 
       as column_list,
       b.referenced_table_name, b.referenced_column_name
FROM information_schema.table_constraints a
INNER JOIN information_schema.key_column_usage b
ON a.constraint_name = b.constraint_name AND
   a.table_schema = b.table_schema AND
   a.table_name = b.table_name
GROUP BY a.table_schema, a.table_name, a.constraint_name, a.constraint_type,
         b.referenced_table_name, b.referenced_column_name
UNION
SELECT …

One of my readers asked for a demonstration of installing an using the new MySQL Workbench Alpha. I thought it would be ideal to make a quick demo of the process with Camtasia and provide it for anyone looking to use MySQL Workbench.

This is more of a quick off the cuff demo, but hopefully it gives you a feel for what is coming in the new Workbench tool.

For those new to Workbench, MySQL Workbench is an entity design tool you can use to design a new MySQL schema.

To watch the video, click here. Macromedia Flash is required. I have also added this video to my video page.

i?ll be giving a talk about the latest mysql features at the southern california linux expo next month (out near lax), and then giving at least one talk (on embedded mysql) at the mysql users conference 2006 in april up in the bay area.

early admission for both conferences is still available. scale4x is $50 until january 16 (or less if you get your hands on a promotional code), the mysql users conference is $945 until march 6 (with various discounts available, like the 15% o?reilly conference alumni discount).

This article is somewhat long. Interestingly, it does not actually cover my entire talk, as there is much to talk about besides the mechanics of each backup option. I wonder what I’d need to do to make this into a white paper or an article?

The backup presentation was finished last night. I may decide to go back and put some extra stuff in there, but that would be syntax and code and stuff. The logic is all in there, and the notes have been printed. I will post the slides (in .pdf and .swf (flash, the file is very small that way) formats) after the talk on Monday, as I may yet revise them.

I am very excited about one slide in particular, and I’ll share it here. It’s really a slide that I end with, but I feel as though it’s a great starting point as well as a summary point. I haven’t seen this information encapsulated this way before, so here goes:

Comparison Table of MySQL Backup Methods …

Yesterday I finished my travel and accomodation arrangements for my trip to the FOSDEM 2006 conference in Brussels, which will take place on February 25th-26th. So if you would like to meet and discuss MySQL-related issues, please get in touch with me! Looking forward to see you there.