The latest from the MySQL community

TechIQ, which focuses on the VAR IT channel business, has identified the top 10 open source companies they expect to succeed in 2008. It's a good article and although it includes some obvious choices, it also includes a few well-thought out surprises. So it's not your typical end-of-year round up. This one has some creative thinking behind it. I think there's a lot of potential for VARs to get behind the open source movement and help customers with implementations as well as integration of open source offerings. (Disclaimer: I admit that I blushed to see MySQL in the list. But... READ MORE

Article from Slashdot:
http://developers.slashdot.org/article.pl?sid=07/12/17/1635200&from=rss

The point is that to make use of multiple way systems, aka machines with a mix of processors and cores, you have to put more thought into your designs.

I've always thought that perl, PHP, and to some extent Java came about because developers could write code in weakly typed/garbage collected systems faster.
Sure, Java is not weakly typed, but the way I see developers program with it, you would not know the difference.

These languages gave us tools to develop against systems that had plenty of memory. Until the advent of 32bit systems, you really had to watch the amount of memory you were using.

Memory issues Today?

"Not so much".

Memory is …

I prepared the release builds for 5.0.11 yesterday evening and they have been uploaded to the mirrors. All automated tests passed without error and my initial manual tests showed everything working. Nevertheless Tax found a show-stopper bug later today following our manual testing procedures.

The problem is located in the SQL generation code for tables. We introduced a new internal index type for foreign key index columns that is maintained automatically by the tool. This new type caused the generated SQL to be corrupt. We tried a simply fix today but were not able to fully solve it. We will take time tomorrow to properly fix the issue and I will trigger new build after that. Then it will take about 20h till the mirrors have catched up and we can announce the new release.

Sorry for the delay.

We presented a webinar along with MySQL last week. The topic was MySQL Backup from the perspective of an Oracle DBA. We got some really good questions at the end of the webinar. Here is a transcript of Q&A:

Q from Trey: Will this Webinar be available online for viewing later?

A from MySQL: Yes. It will be on our site in a few days at http://www.mysql.com/news-and-events/on-demand-webinars/

Q from Randy: Is a copy of presentation available on web? Where?

A from Zmanda : slides are available on Zmanda Network: http://network.zmanda.com/

Q from Jing: Converting database from oracle to MySQL, any limitation regards the version compatibility?

A from MySQL: We provide a free GUI migration tool that helps migrate Oracle data objects to MySQL. For a complete and very detailed …

I made a mistake in using the delete command in our production environment. I had 17 million rows and I only needed to keep 3 million rows, so I issued a delete sql to erase the 15 million rows. Bad move! Because I immediately saw how the I/O of the machine went up very high and the delete sql caused a huge load spike. On top of that, I had to run an optimize table command because MySQL did not free the deleted space and this caused another load spike.

In retrospect, I should have done the following items:

1. Instead of delete sql, I should have issued a create new table and select the 3 million rows from the old table. This would have been faster and it would not be necessary to run optimize table.

2. If I have to use delete and and optimize table, I could have copied the table to a non-production database and run the commands there. After it has finished executing all the processes, I can copy the new …

Sun’s Allan Packer (author of Configuring and Tuning Databases on the Solaris Platform) has published a lengthy and fascinating blog post on the future of proprietary databases in which he discusses the likelihood of MySQL, PostgreSQL et al challenging Oracle, IBM and Microsoft.

Packer’s multi-part post covers a number of areas including:

Feature Stagnation In The Traditional Database Market
License Costs: the Soft Underbelly of Proprietary Databases
…

This is a quiz that has a aha! solution. Not so trivial, though. It requires some thinking.

Given this table:

create table t1 (
dt datetime not null,
primary key (dt)
);

Task: insert exactly 1 million records in table t1, with the following constraints:

• Use a maximum of 5 (five) SQL statements;
• Use only the MySQL interactive command line client;
• No shell commands;
  
• No loading of scripts;
• No inserts from existing tables in your system. You must assume that t1 is the only table in your entire database;
  
• No MySQL Proxy;
• No stored routines, triggers or events;
• Each statement must be not longer than 75 characters;
• UPDATE. No modification of table t1;
• No LOAD DATA.
  

Prize: fame and fortune (i.e. your name quoted in these …

A lot of you may already know this, but I am willing to bet there are more that don’t. I’m talking about the tee command in the bash shell, and in MySQL. For our purposes, we’ll talk about the tee command in MySQL.

Problem: You have a series of SQL statements whose results take up a few screens worth of output, and you need to take this output and send it to someone else (A DBA, MySQL Support, your mentor). You could just do a copy/paste from your terminal, but what if you realized in the end that your scroll back buffer isn’t as large as you thought it was?

Solution: Tee. Apparently, the mysql client comes with tee.

mysqlshell> tee mysqlog.sql ;
Logging to file ‘mysqlog.sql’
use dbname;
select foo from bar;
….
mysqlshell> notee;
…

I believe I found a new method to calculate the median in MySQL. I would not be surprised if this method has been figured out by somebody else already. However, I can't seem to find any resources on the internet describing this method, so for now I flatter myself by assuming the method is original.

(Please do post your comments to this blog to correct me on that should I be wrong so I have a chance to rectify.)

The method I'm describing is a one-pass, pure SQL method. It does not require subqueries, cursors or user variables. However, it does rely on the MySQL specific functions GROUP_CONCAT() and SUBSTRING_INDEX()

I'll be maintaining …

Last week we’ve found that our application runs not so fast as we wished. This was a complicated webservice application with many services/methods and each serivce method is accessing MySQL (InnoDB) database through the Hibernate layer.
In Hibernate layer we used Criteria API to make queries, so in the source code is impossible to view exact queries which will run.
So I’ve found two solutions how can we profile our application.

1. It is possible to turn on hibernate option “hibernate.show_sql=false” and check each query in the mysql console with MySQL query profiling

2. And the second solution was Elvyx.

I’ve chose the second one.

It is needed just to replace jdbc driver with the Elvyx’s one, setup Elvyx driver to use the driver you …