If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE …

Last year a wrote a blog entry on holiday gifts for DBAs that went over quite well with the relatives and loved ones of DBAs. So if you are a MySQL DBA, print out a copy of this, circle the items you like, and get something you like this year. And yes, shopping for you is worse than styling Donald Trump’s hair in a high wind or explaining foreign keys to any cast member of any popular reality or music show. So take some pity on those who are buying you gifts.

• T-Shirts
• Cafepress has a series of DBA shirts including ‘Not Normalized’ , ‘Kiss me — I’m the DBA’, and the Key Rule shirt.
• Think Geek has some good shirts including ‘SELECT * FROM users WHERE clue > 0. Uh oh, zero rows were returned!’

and ‘Go away …

MySQL 5.6 adds support for EXPLAIN UPDATE. This is a useful feature, so we want to have it in MariaDB 10.0, too. Besides that, MariaDB 10.0 has SHOW EXPLAIN feature, and we want it work for UPDATE commands, too.

Now, a bit of code history. Why didn’t MySQL have EXPLAIN UPDATE from the start, like other database systems? To the uninformed, lack of EXPLAIN UPDATE looks like simple lazyness. After all, everyone who has read a database textbook can imagine that the code should have this form:

run_update_query(UpdateQuery q) {
   QueryPlan qp= optimize_query(q);
   run_query_plan(qp);
}

and adding EXPLAIN UPDATE is a matter of adding another function:

run_explain_update(UpdateQuery q) {
   QueryPlan qp= optimize_query(q);
   print_update_query_plan(qp);
}

print_update_query_plan(QueryPlan qp)
{
  // print the plan for …

If you plan to use the MySQL Workbench Migration Wizard to migrate databases from PostgreSQL to MySQL you first need to configure an ODBC driver to connect to your PostgreSQL server.

In this post I’ll cover how to set up and configure psqlODBC, the official ODBC driver for PostgreSQL.

Installing the psqlODBC Driver

The procedure is different for every platform so make sure to follow the instructions that correspond to the OS where you have MySQL Workbench installed. The driver needs to be installed in that machine, as explained in the MySQL Workbench documentation.

Windows

If you are running MySQL Workbench on Windows download the MSI package for psqlODBC. Go to its download page ( …

Parallel Universe is now available as part of Linux OS images at Amazon Web Services.
Amazon Web Services is a scalable cloud service provider at http://aws.amazon.com .

Launch Instance -> Classic Wizard -> Community AMIs -> type in “parallel universe” (All Images)

Host OS:
Amazon Linux
Red Hat Enterprise Linux
SUSE Linux Enterprise Server
Ubuntu Server
Cluster Compute Amazon Linux
Cluster GPU Amazon Linux
Cluster Instances SUSE Linux Enterprise Server
Cluster Instances Ubuntu Server

One of the optimizations we have introduced in MariaDB 5.5 is Extended keys. The idea behind it is rather simple. Inside InnoDB, every secondary index has an invisible suffix of primary key columns. That is, when you create an index:

ALTER TABLE innodb_tbl ADD INDEX (column1, column2);

you’re actually creating this

ALTER TABLE innodb_tbl ADD INDEX (column1, column2, primary_key_column1, …, primary_key_columnN);

The index is extended with primary key columns. SHOW KEYS does not show these extra key parts, but they are there.

Traditionally, MySQL optimizer was half-aware of these extra columns. It knew that doing an index-only scan on InnoDB’s secondary key would read the primary key columns also, and used this property. On the other hand, the optimizer was not able to use the extra columns to do a …

In my previous post, I shared my finding that MySQL 5.6.7 does not make a cost-based choice between Materialization and IN-to-EXISTS strategies for subqueries.

It turns out I was wrong. As Guilhem Bichot has blogged here, he has implemented cost-based choice between Materialization and IN->EXISTS in MySQL 5.6.7. Igor Babaev also wrote about the topic, and covered the reasons I didn’t see the feature - it isn’t mentioned in the documentation, development process at Oracle is quite closed, and the feature didn’t work for a basic example that I have tried.

Let’s try to focus on the technical part of it. Looking at the source code, I see that …

Stockholm will host the Swedish MySQL Users Group on November 21st and the details can be found at http://www.facebook.com/events/468752256510179/

We will talk about news from MySQL Connect, which was a few weeks ago, especially if 5.6RC course! * We will have a review of partitioning with a developer who has worked with just that. * Our last point is a review of a beloved children – character sets in MySQL. One common source of headaches.

Two cons against NoSQL data stores read like this: 1. It’s very hard to move data out from one NoSQL to some other system, even other NoSQL. There is a very hard lock in when it comes to NoSQL. If you ever have to move to another database, you have basically to re-implement a lot [...]

“For analytical things, eventual consistency is ok (as long as you can know after you have run them if they were consistent or not). For real world involving money or resources it’s not necessarily the case.” — Michael “Monty” Widenius. In a recent interview, I asked Justin Sheehy, Chief Technology Officer at Basho Technologies, maker [...]