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 …

This release fixes a few bugs, adds minor features, and adds some debugging support to shared code. I'm working on the Nibble sync algorithm for mk-table-sync, and someone has found a few more bugs with mk-parallel-dump, but those might take me a while to complete.

With InnoDB, I was trying to figure out if ANALYZE TABLE blocks other clients or if it uses MVCC ("in theory" it probably could). The answer is that it does seem to lock for a small amount of time, but the results are not as expected. A second execution of ANALYZE TABLE takes considerably longer than the first:


mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| 1 | root | localhost | test | Query | 235 | Sending data | select count(*) from …

I was coincidentally doing a show table status on couple of tables when I saw that one of the MyISAM tables has reach the 4 GB default limit. The table stores all my show processlist parsed data and apparently my parsing and importing script did not notify me of any errors like the table was full or something. Anyway, I solved the problem by running the command below.

ALTER TABLE MAX_ROWS = 1000000000;

A good discussion on MyISAM 4 GB default limit can be found on this link:

http://jeremy.zawodny.com/blog/archives/000796.html
http://dev.mysql.com/doc/refman/5.0/en/full-table.html

We (me coding, Igor and Evgen reviewing) have fixed BUG#32198. According to Support/Bugs team, the bug was causing pain to a number of people, so I thought it deserved a post. The problem was as follows:

• Before MySQL 5.0.42, comparisons in form

    date_column CMP datetime_const

  were interpreted as comparisons of DATEs (CMP is one of =, <, > , <=, or >=). The time part of datetime_const was ignored.

• In 5.0.42 we've fixed it to perform in a way that is closer to the SQL standard: the comparisons are now resolved by comparing values as DATETIMEs. Our fault was that "date_column CMP datetime_const" stopped being sargable. It turned out there quite a few people who had queries like

        SELECT ... WHERE date_column < NOW() ... 

  and those queries became awfully slow.

  …

Let's say you have a pool of ~100 connections to your database. A thread in your application does something like this:


START TRANSACTION;
INSERT INTO my_table (col1) VALUES ('val1');
COMMIT;
START TRANSACTION;


That last transaction was never committed and remains open. This means that InnoDB (which keeps multiple versions of each row) can not clean up and free space:


mysql> SHOW INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 981654
Purge done for trx's n:o < 0 979531 undo n:o < 0 0
....


One 'symptom' of this problem may be if the user complains that their database is much faster after they restart, since …