This Thursday (June 10th, 14:00 UTC), Darren Cassar will rerun his February 25 presentation of Securich - Security Plugin for MySQL. (Recording of the session failed in February; hopefully it will succeed this time.) According to Darren, the author of the plugin, Securich is an incredibly handy and versatile tool for managing user privileges on MySQL through the use of roles. It basically makes granting and revoking rights a piece of cake, not to mention added security it provides through password expiry and password history, the customization level it permits, the fact that it runs on any MySQL 5.0 or later and it's easily deployable on any official MySQL binary, platform independent.
More information here: …

This Thursday (June 10th, 14:00 UTC), Darren Cassar will rerun his February 25 presentation of Securich - Security Plugin for MySQL. (Recording of the session failed in February; hopefully it will succeed this time.) According to Darren, the author of the plugin, Securich is an incredibly handy and versatile tool for managing user privileges on MySQL through the use of roles. It basically makes granting and revoking rights a piece of cake, not to mention added security it provides through password expiry and password history, the customization level it permits, the fact that it runs on any MySQL 5.0 or later and it's easily deployable on any official MySQL binary, platform independent.
More information here: …

I was trying to setup cacti on my ubuntu desktop to gather metrics from few new database servers we were planning to replace. Installed cacti using the following command

apt-get install cacti

Then the next step was to download and the install the cacti mysql templates from http://code.google.com/p/mysql-cacti-templates/. Thanks to Baron, was able to get this up and running fast using the templates. But had one problem though, while importing the mysql template “cacti_host_template_x_mysql_server_ht_0.8.6i-sver1.1.7.xml” cacti bombed with the below error

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 41 bytes) in /usr/share/cacti/site/lib/xml.php on line 32

The fix was to edit “/etc/php5/apache2/php.ini” change the parameter “memory_limit” from 16M to 32M and …

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.

 This is short chapter which discuss single method of dealing with mysterious DML query.

Chapter 3. Wrong data in database or what to do with problem DML query.

Problems with wrong data happens not only with SELECT queries like
cases we discussed in chapter 2, but in cases of querires which modify
data: DML queries.

Lets discuss example below

mysql> create table t1(f1 int);


Query OK, 0 rows affected (0.01 sec)

…

Quite frequently I see people confused what table locks reported by SHOW INNODB STATUS really mean. Check this out for example:

PLAIN TEXT SQL:

1. ---TRANSACTION 0 4872, ACTIVE 32 sec, process no 7142, OS thread id 1141287232
2. 2 LOCK struct(s), heap size 368
3. MySQL thread id 8, query id 164 localhost root
4. TABLE LOCK TABLE `test/t1` trx id 0 4872 LOCK mode IX

This output gives us an impression Innodb has taken table lock on test/t1 table and many people tend to think Innodb in fact in some circumstances would abandon its row level locking and use table locks instead. I've seen various theories ranging from lock escalation to using table locks in special cases, for example when no indexes are defined on the table. None of this is right.

In fact Innodb uses …

Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of  "Methods for searching errors in SQL application" just published.

 This is short chapter which discuss single method of dealing with mysterious DML query.

Chapter 3. Wrong data in database or what to do with problem DML query.

Problems with wrong data happens not only with SELECT queries like
cases we discussed in chapter 2, but in cases of querires which modify
data: DML queries.

Lets discuss example below

mysql> create table t1(f1 int);


Query OK, 0 rows affected (0.01 sec)

…

In my last post, I discussed how fractal tree data structures can be up to two orders of magnitude faster on deletions over B-trees. I focused on the deletions where the row entry is known (the storage engine API handler::delete_row), but I did not fully analyze how MySQL delete statements can be fast. In this post, I do. Here I show how one can use TokuDB, a storage engine that uses fractal tree data structures, to make MySQL deletions run fast.

Let’s take a step back and analyze the work needed to be done to execute a MySQL delete statement. Suppose we have the table:

create table foo (
        id auto_increment
        a int,
        b int,
        primary key (id)
)

Say we wish to perform the following operation that deletes 100,000 rows:

delete from foo where a=1;

In MySQL, …

While I have spent a lot of time recently helping the MySQL community interact with and integrate with various Oracle User Groups including ODTUG, IOUG, NoCOUG, NYOUG, DAOG I thought I’d share some resources for the MySQL Community that wanted to know more about Oracle.

The Oracle family of products is huge. You only have to look at the acquisitions via Wikipedia to get an idea. The first thing is to narrow your search, e.g. Database, APEX, Middleware, BI, Hyperion, Financials, development via Java, PHP or Oracle Forms etc.

While Oracle is a commercial product you can download all software for FREE via Oracle Technology Network. There is also documentation, forums, blogs and events.

Some Oracle bloggers I have already been reading however I’m expanding my list. People you may want to …

After the acquisition of Sun by Oracle, Oracle had made a commitment to put together a MySQL Storage Engine Advisory Board. Over a month ago, Infobright was invited to join the Oracle MySQL Storage Engine Advisory Board and last week was the first in a series of meetings that Oracle will be hosting for the Advisor Board members. Oracle did a terrific job stepping up to the task of assembling a well known group of companies as part of the board. The meeting last week was clearly a sign of commitment by Oracle to work closely with the storage engine vendors in the market. This resonated very well with the advisory board members.

At this initial meeting we all had an opportunity to learn from each other as key storage engine vendors in the market and to also hear directly from Oracle about their plans for MySQL. Senior members of the Oracle team including Edward Screven, Chief Corporate Architect for Oracle attended the meeting where we had a …

After the acquisition of Sun by Oracle, Oracle had made a commitment to put together a MySQL Storage Engine Advisory Board. Over a month ago, Infobright was invited to join the Oracle MySQL Storage Engine Advisory Board and last week was the first in a series of meetings that Oracle will be hosting for the Advisor Board members. Oracle did a terrific job stepping up to the task of assembling a well known group of companies as part of the board. The meeting last week was clearly a sign of commitment by Oracle to work closely with the storage engine vendors in the market. This resonated very well with the advisory board members.

At this initial meeting we all had an opportunity to learn from each other as key storage engine vendors in the market and to also hear directly from Oracle about their plans for MySQL. Senior members of the Oracle team including Edward Screven, Chief Corporate Architect for Oracle attended the meeting where we had a …