Sometimes we have some special need for a stored procedure to call another to do something. That is fine, but what if the second stored proc failed for some reason? Maybe you want to halt the first stored procedure (the caller) and not proceed with the work until the problem is verified and resolved. So How do you do it?

Simple, get a return code from the called procedure to determine if it worked or not!

Here’s a sample piece of code to explain better:


DROP PROCEDURE IF EXISTS `test`.`testing123`;
DELIMITER $$

CREATE
PROCEDURE `test`.`testing123`(OUT a INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET a=2;
END;
SET a=0;

# toggle the below as comment or not to see the call at the bottom working
# if you uncomment select abc you'll see the error, otherwise all is cool

# select abc;

…

MySQL Connector/J has a useful feature which executes a lightweight ping against a server (or, in the case of load-balanced connections, all active pooled internal connections that are retained) to validate the connection. As you might guess, this feature is useful for Java apps which use connection pools, so that the pool can validate the connection. Depending on your connection pool and configuration, this can be done at different times:

• before the pool returns a connection to the application
• when the application returns a connection to the pool
• during periodic checks of idle connections

So, if you want to use this magic light-weight ping process, here’s how you do it:

Specifying a “validation query” in your connection pool that starts with “/* ping */” _exactly_ will cause the driver to instead send a ping to the server and return a fake result set (much lighter weight), …

The basic data structure that powers databases is called a B-tree. This is where you actually store the user's data. B-trees are great because you can put huge amounts of data in them and access remains fast. In fact, the naive strategy of putting a whole data set into one B-Tree doesn't break down because of access time. It does, however, break down when you try to support a multiaccess paradigm.

Six years ago, multiaccess was nice. Now that processors have multiple cores, it's crucial. Four cores fighting over one B-tree means a lot of wasted processor time. In a multiaccess scheme, different cores can concurrently access data. This gets tricky. You can go looking for a piece of data only to find that someone has moved it since you started; that's trouble: for all you know it was deleted. You could start the search over, but without guarantees—maybe you’ll get unlucky and it will be plucked out from under you again. How do you know …

The basic data structure that powers databases is called a B-tree. This is where you actually store the user’s data. B-trees are great because you can put huge amounts of data in them and access remains fast. In fact, the naive strategy of putting a whole data set into one B-Tree doesn’t break down because of access time. It does, however, break down when you try to support a multiaccess paradigm.

Six years ago, multiaccess was nice. Now that processors have multiple cores, it’s crucial. Four cores fighting over one B-tree means a lot of wasted processor time. In a multiaccess scheme, different cores can concurrently access data. This gets tricky. You can go looking for a piece of data only to find that someone has moved it since you started; that’s trouble: for all you know it was deleted. You could start the search over, but without guarantees—maybe you’ll get unlucky and it will be plucked out from under you again. How do you …

In the previous post, I showed how the "virtual tables" that the InnoDB Plugin use for finding lock contention works, and what they look like. I also showed what an InnoDB lock graph looks will look like when monitored from MyQuery 3.3, which is not yet, but soon, released.

So, we are looking at three tables, the transaction table, where each transaction has no, one or more locks. For our intents and purposes, we will disregard transactions without locks, so we can join transactions table to the locks table. Then we need to link up the locks table to the lock waiters, to see if the lock in question is waiting on another lock, Each lock is either not waiting on one, or, as we have seen, more, locks, or it is not waiting at all. As a lock might not …

Implement multi-master to single-slave replication by periodically saving state and changing masters, and do it all in SQL.*

Just a proof of concept. I suggest you add some error checking (like, don't make the switch if the slave isn't running).

Remember that all this stuff goes on the SLAVE. If you absent-mindedly log in to the master, like I did a few times, you might wonder why your relay_log.info file isn't read.

Set up a table to keep track of each master file. Mine use the same user name and password for all hosts; you can add those fields and adjust the procedure accordingly if you like.

USE mysql;
DROP TABLE IF EXISTS rotate_master;
CREATE TABLE rotate_master (
id int auto_increment primary key,
master_host varchar(255),
master_port int unsigned,
master_log_file varchar(255),
master_log_pos int unsigned,
in_use boolean DEFAULT 0
…

When you use InnoDB, or any other RDBMS or storage engine that supports row level locking and transactions, you get to realize a few things:

• The chance of lock contention is less, as the lock is much more granual that page or table level locking.
• On the other hand, when you get into lock contention, maybe because you have structured your data in such a way so there may well be as many locks as before (for example if one small table with very few rows is updated frequently by many threads), then the effect is worse.
• With transactions, which are a good thing by the way, I'm not promoting not transactions datastores here and assuming auto commit isn't used, then locks are held longer, and as each transaction may well hold several locks, because of multiple rows being updated or multiple statements are part of one transaction for example.
• With InnoDB then, fining the root cause when you end up with …

we're now at a stage so we can test/benchmark scan/scan queries, i.e push most queries. there are still know bugs and things that we can't push.

to see how we performs, we tested a very old query/dataset, that a customer tried in the 4.1 days. the result at that time was a disaster, and they at that time decided to use something else.

it's a 11-way join with mainly REF accesses (i.e not many lookups)

i tested it on same hardware/configuration as for my UC presentation.
without join pushdown the average query time is 3.6s.
with join pushdown it's ~500ms, i.e ~7x improvement.

the nights of kni are deeply disappointed, and that's an attitude i like :-)

FYI: we also optimized TPCW::getBestSeller …

-----------
If you are not familiar with OpenSQLCamp, here's a description from the home page at http://opensqlcamp.org/:

OpenSQL Camp is open to all – sessions have included PostgreSQL, SQLite, MySQL (and storage engines and forks thereof), Drizzle (and tools such as Google Proto Buffers), DBIx::Cache, Gearman, cloud computing, Unix tips, query optimization, Apache Derby, BlackRay, Continuent Tungsten, DB Clustering, Firebird, CouchDB, MongoDB, Cassandra, Firewater, how to version schemas, Waffle Grid / Storm Cloud, databases on SSDs, and even "soft" topics like the Open Database Alliance.

read more

Recently I wrote about detecting the preferred language or locale of a web site visitor, using Zend Framework.

Well, I have to start with one correction. In my last blog post about this topic I talked about the User Agent String, but since I wrote the article, I figured out that the User Agent String doesn’t play any role at all. Neither in the Zend Framework variant that I blogged about earlier, nor in today’s code. And that is good so, because both Internet Explorer and …