Happens a lot these days, like when people (finally) discover the virtues of InnoDB. Of course there's more to it than just the conversion, for instance some server tuning will be required also.

Anyway, there are a few methods:

1. Most MySQL installations will have the mysql_convert_table_format script (Perl) included. The script does not contain an option to tweak server parameters though. For instance, when converting to MyISAM (as an intermediate step for moving to innodb_file_per_table), index building will be much faster if you have a large myisam_sort_buffer_size setting. So you'd have to either tweak the script or make that change globally (SET GLOBAL myisam_sort_buffer_size=1024*1024*1024 for 1G). I prefer to have such changes local to a setting as they can be dangerous, but in this case it should be ok since this particular buffer is only used for alter table and repairs.
   
…

How well do you know MAX_ROWS?


mysql> CREATE TABLE cube2 ( cube INT(1)) MAX_ROWS=2;
Query OK, 0 rows affected (0.12 sec)


So I inserted a row INSERT INTO cube2 values(3); and then another with INSERT INTO cub2(4);. So what happened when I tried to insert a third row?

Did the server complain I was trying to fit a third row in a two row table? Did one of the existing values in the table get replaced?

Nope. The server gladly took the third row.

MAX_ROWS is usually used with MyISAM tables to change the row pointer size from the default to access more disk space. Using a MAX_ROW value absurdly lower than this pointer size is politely ignored by the server.

Drat! I I thought I had a cute trick to use on a certification exam.

In the realm of the daily WTF, this SQL doozy popped up earlier in the year: Oklahoma Leaks Tens of Thousands of Social Security Numbers, Other Sensitive Data

SQL queries were part of the URLs, so anybody could see which tables/cols were present, and modify at will to extract lots of privileged data including social security numbers. I particularly cried around the bit where the developers, after being informed of the problem, merely changed the social security number column name to start with caps, while leaving the whole "SQL in URL" thing in place. They only actually took the thing off-line "for routine maintenance" after it was then proven that the developers' own personal information was also retrievable. Apparently it had to have such a personal connection to "hit home".

If you thought the activity around planning OpenSQL Camp 2008 had slacked off, it’s only because I’m both moving to a new house and traveling for business, so I haven’t blogged about it. In reality I’m not (and shouldn’t be!) the main driving force behind this event, so my lack of blogging doesn’t reflect [...]

For those people starting with Kettle (Pentaho Data Integration) we created a Getting Started page on our Wiki.

Since I realized that for some people, simple and easy can never be simple and easy enough I created 8 mini-flash demos :

• Create a new transformation - create a step (flash demo, 0′20″)
• Create a hop between 2 steps (flash demo, 0′20″)
• Create a database connection (flash demo, 0′45″)
• …

To have online backups of MySQL, We recently bought a license for InnoBase/Oracle's InnoDB Hot Backup Tool, ibbackup. This tool, used in conjunction with innobackup, has worked great in creating a nightly backup, with no downtime during the backup. Not even nagios messages!

I run innobackup/ibbackup on one of our slaves (well, it's also a dual master, but not used by apps). innobackup produces a backup in a directory that I specified, and when run results in a time-stamped directory, as show below:


ls -l 2008-09-17_03-00-03/
total 276272
-rw-r--r-- 1 root root 349 2008-09-17 03:00 backup-my.cnf
drwxr-x--- 2 root root 4096 2008-09-17 03:55 grazr
-rw-r--r-- 1 root root 27 2008-09-17 03:55 ibbackup_binlog_info
-rw-r----- 1 root root 186109952 2008-09-17 03:55 ibbackup_logfile
-rw-r----- 1 root root 10485760 2008-09-17 03:00 ibdata1
-rw-r----- 1 root root …

Adobe support of Zend Framework announced at PHP conference READ MORE

TOTD #42 explained how to create a simple Java Server Faces application using NetBeans 6.1 and deploy on GlassFish. In the process it explained some basic JSF concepts as well. If you remember, it built an application that allows you to create a database of cities/country of your choice. In that application, any city/country combination can be entered twice and no errors are reported.

This blog entry extends TOTD #42 and show the list of cities, that have already been entered, starting with the letters entered in the text box. And instead of refreshing the entire page, it uses JSF Extensions to make an Ajax call to the endpoint and show the list of cities based upon …

Its no secret that all the MySQL Sun Database Group developers are here in Riga, Latvia for the developer’s meeting. I have my video camera on hand, and plan to conduct bite-sized interviews.

But that’s not why I’m writing this. This is for you. Since you can’t be here, is there something you’d like to ask a MySQL developer with regards to a bug you filed? A new feature request? A worklog item?

Write a comment, or drop me email at colin[at]mysql[dot]com. Be quick though, as the Q&A sessions I imagine, will be fairly impromptu.

There is a new tool that comes with MySQL that does backup / restore (MySQL 6.0 Backup Utility).   DBAs can now do backup / restore directly from the mysql command prompt.  For full details, see the documentation at:

 http://dev.mysql.com/doc/refman/6.0/en/backup-database-restore.html

 Additionally, Robin Schmacher, Director of Product Management at MySQL, gives a good introduction to the utility in this article.

Note: Version 6.0 is still in alpha, however, so changes are expected.