Having supported a number of successful load-balanced JDBC applications using MySQL Cluster and MySQL Connector/J over the years, I’ve found a few problems that are unique to specific Java app servers.  A recent customer inquiry reminded me of a GlassFish-specific issue, and the Connector/J connection property we introduced to help solve it.  I thought it might be useful to document this here for any GlassFish users looking to deploy a load-balanced JDBC application with MySQL (Cluster or multi-master replication).

If you’re entirely new to the load-balancing functionality in MySQL Connector/J, you may want to review some earlier posts.  In particular, it’s important to understand how a …

Adminer is also capable of serching across all tables in database. http://www.adminer.org/en/

Here comes the next open source mysqlnd plugin prototype: PECL/mysqlnd_mux. The PHP mysqlnd connection multiplexing plugin shares a connection to MySQL between multiple user connection handles. Connection multiplexing aims to reduce connect time and lowers the total number of concurrenly opened connections. The first is primarily a benefit for the client, the latter is great for the server. Multiplexing may be most desired in environments with many short-lived connections and weak client-side pooling - such as a PHP web application.

PHP mysqlnd connection multiplexing plugin . More about PHP …

If you ever had a replication slave that is severely behind, you probably noticed that it’s not catching up with a busy master at a steady pace. Instead, the “Seconds behind master” is going up and down so you can’t really tell whether the replica is catching up or not by looking at just few samples, unless these are spread apart. And even then you can’t tell at a glance when it is going to catch up.

Normally, the “severely behind” thing should not happen, but it does often happen in our consulting practice:

• sometimes replication would break and then it needs to catch up after it is fixed,
• other times new replication slave is built from a backup which is normally hours behind,
• or, it could be that replication slave became too slow to catch up due to missing index

Whatever the case is, single question I am being asked by the customer every time this happens is this: …

Justin Swanhart wrote a blog on Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels and I thought I'd give my view on this issue.
To begin with, from a technical standpoint, Justin is correct in his statements, that is not why I am writing this (and this is the reason I'm writing a separate blogpost instead of just commenting his), but there is more to it than that.
What Justin really writes about is how these isolation levels are implemented in a particular database, in this case InnoDB. For example the sentence "There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking." makes this very clear, these isolation levels, as defined by ANSI SQL as well as how they are implemented by other databases, …

Oracle offers best of breed technology at every layer of the stack, from servers and storage to applications.

Discover why MySQL is a best of breed database solution for:

• Web-based applications, including the next generation of highly demanding web, cloud, mobile and social application
• Distributed applications requiring a powerful and reliable embedded database
• Custom and departmental enterprise applications on Windows and other platforms

Check out our Resource Center to get access to white papers and other resources.

And, remember to register for …

It is possible with MySQL Connector/Python to define your own cursor classes. A very good use case is to return rows as dictionary instead of tuples. This post shows how to do this using MySQL Connector/Python v1.0 and is an update for an older blog entry.

In the example below we are subclassing the MySQLCursor class to create a new class called MySQLCursorDict. We change the _row_to_python() method to return a dictionary instead of a tuple. The keys of the dictionary will be (unicode) column names.

from pprint import pprint
import mysql.connector

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if …

Suddenly your application starts throwing "error 157" and performance degrades or is non-existing. It is easy to panic then and try all sorts of actions to get past the problem. We have seen several users doing:

• rolling restart
• stop cluster / start cluster

because they also see this in the error logs:

120828 13:15:11 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure

That is not a really a nice error message. To begin with, it is a WARNING when something is obviously wrong. IMHO, it should be CRITICAL. Secondly, the message ‘Cluster Failure’ is misleading.  The cluster may not really have failed, so there is no point trying to restart it before we know more.

So what does error 157 mean and what can we do about it?

By using perror we can get a hint what it means:

$ …

The MySQL Server has a few options to help deal with transactions holding InnoDB locks for excessive periods of time.  The –innodb-lock-wait-timeout option is one such option, but that just affects statements waiting on locks already held by another transaction.  If you want to ensure your application isn’t holding locks for long periods of time to start with, what options do you have?

Well, the manual has a useful example that leverages InnoDB tables in INFORMATION_SCHEMA to show which transactions are locking rows needed by other transactions.  That’s cool stuff, and you can take this a step further by looking for all connections with open transactions …

As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels.  There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.

Extra locking (not gap locking)
It is important to remember that InnoDB actually locks index entries, not rows. During the execution of a statement InnoDB must lock every entry in the index that it traverses to find the rows it is modifying. It must do this to prevent deadlocks and maintain the isolation level.

If you run an UPDATE that is not well indexed you will lock many rows:

update employees set store_id = 0 where store_id = 1;
---TRANSACTION 1EAB04, ACTIVE 7 sec
633 lock struct(s), heap size 96696, 218786 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 localhost root …