Prepared statements have been with MySQL since version 4.1, including the protocol plumbing that helps support it.  What I didn’t realize – until a recent expedition through a general query log – is that the mysql command-line interface doesn’t implement the protocol commands that support this explicitly.  I came to this realization after observing a byproduct of this behavior.

The initial observation that triggered this exploration was noting that PREPARE and EXECUTE statements, when issued from the mysql command-line interface, result in two entries per command in the general query log:

6 Query    PREPARE stmt FROM 'SELECT RAND()'
6 Prepare    SELECT RAND()
6 Query    EXECUTE stmt
6 Execute    SELECT RAND()

Contrast this behavior with what is seen when a client sends COM_PREPARE and COM_EXECUTE, such as below with Connector/J (and useServerPrepStmts=true):

14 Prepare    SELECT * FROM t1 WHERE …

I was stunned when a SQL query raised an ERROR 1630 (42000) telling me the SUM function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM function must be on the same line as the SUM keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT function and it seems to me that this parsing inconsistency is problematic.

Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE settings. This assumption was borne out by Kolbe Kegel’s comment on this post, and there are 30 …

While we at mysqljoin.com want to provide simply and straightforward tutorials which are easy to understand, we love in depth documentation as well. And since relational databases are very complex, a documentation can be much more technical than our tutorials are.

We just came across such a documentation, a very detailed explanation of SQL join types. We felt that we have to share this with our readers. It’s a very nice follow up reading for everybody who’s currently learning about MySQL joins. Beside many interesting facts you’ll find probably the most interesting visualisation of joined tables we’ve ever seen as well as a visualized classification schemes for SQL joins.

There are different classification schemes and different criteria according to what joins are classified. As a result there is a bit mess in the process of understanding them. …

I thought it would be useful if people could simply ask questions about MySQL joins and have a place where they get an answer. Do you think it’s a useful feature?

http://www.atl-service.kiev.ua sunglass4you.com.ua/

Replicating from MySQL to Oracle Database and Back Again by Robert Hodges and Linas Virbalas

MySQL High Availability: Power and Usability by Giuseppe Maxia

This post isn’t about NuoDB, although it was prompted by the phrase “100% uptime” that I’ve seen them use a few times. I want to suggest that people think slightly differently about uptime and availability.

The key to understanding uptime and thinking clearly about it, in my opinion, is to think instead about downtime. Uptime is the absence of downtime. Therefore, focus your attention on reducing downtime through a two-pronged approach. First, increase the mean time between failures (MTBF). Second, reduce the mean time to recovery (MTTR) when downtime happens. The techniques for achieving these goals are quite different; the second tends to be a technical solution, whereas the first usually requires a management solution.

Now, back to uptime. Is 100% uptime even possible? It depends on how you define it. Play funny with the definition, and you can draw a box around a period on your timeline where there was no downtime. …

Consolidate your sharded database indexes in real-time

by Jeff Mace, Director of Professional Services at Continuent:

How do you find one users’ data when it is located in one of a hundred schemas? Build a global index table that allows you to identify the home data center and schema for any user. Sharding data across many schemas is the lifeblood of highly-scalable websites. Multiple data

I wanted to make sure and point out to everyone that the MySQL Connect Early Bird Discount Ends July 13th!

Don't forget to register now and save yourself a couple hundred dollars.
With over 77 sessions packed into these two days you surely will find several sessions for you.

What more information:

• MySQL Connect
…

In my last post, I wrote that I observed many similarities between TokuDB and MySQL Cluster. Many features that benefit TokuDB also benefit MySQL Cluster, and vice versa, with Hot Column Addition and Deletion (HCAD) being an example. Over my next few posts, I expand on some more of these possibly unexpected similarities.

Today I want to focus on optimizer support for clustering keys. Both MySQL Cluster and TokuDB can benefit from the MySQL optimizer supporting clustering keys. For TokuDB, the benefit is obvious, as TokuDB supports clustering keys. A non-negligible part of our effort is changing the …

This last week was the first time we have this kind event here in Argentina, of course this is a great initiative and a good starting point for next events. My impressions: being the first time that these kind of conferences were done in Argentina I have to say it was great, small but great. [...]