Skip to content

## Speaking on the media: OurSQL, Reversim

MySQL PerconaLive Speaking March 21, 2013March 21, 2013

I had the pleasure of speaking on two excellent podcasts:

#### OurSQL

No need for me to introduce this podcast. I just have to say it was only when I interviewed that I realized the amount of work taken for producing one single cast. Kudos to Sheeri & Gerry and Rich Goyette the sound producer and thank you for your kind hospitality!

You can find the cast on OurSQL Episode 129: New and Extended, where we speak about the upcoming Percona Live MySQL Conference & Expo, 2013.

#### Reversim

This is an Israeli, Hebrew speaking podcast, and is undoubtedly the queen of Software Development podcasts here. Run by two enthusiast, veteran, open source keen developers, this is a must hear podcast if your Hebrew isn’t rusty 😉 Thanks Ori & Ran for having me again!

I was honored to be interviewed for the 3rd time there, again speaking about MySQL and its ecosystem. In this recent podcast, I speak about the new 5.6 release, highlighting some features; about MariaDB; the Galera cluster technology; LGPL connectors; Percona Live 2013.

Find the cast on the reversim website: 171 MySQL 5.6

My next speaking event will be at the conference itself; more on that later.

## MySQL Stored Routines Debugger & Debugging API: sneak preview II, video

MySQL common_schema debug rdebug Stored routines March 21, 2013March 29, 2013

This is the 2nd sneak preview of common_schema‘s rdebug: debugger & debugging API for MySQL stored routines (see 1st preview here).

rdebug will be released as part of common_schema, free and open sourced.

In this sneak preview I present:

- Compiling multiple routines with debug info
- Starting/stopping a debug session
- Step-over, step-in, step-out
- Showing stack trace
- Showing the next-statement to execute
- Viewing and manipulating local routine variables
- Misc. meta routines

#### The quick technical overview

rdebug is a server-side mechanism, itself written in MySQL stored routines. It manipulates your routines by injecting debug code (easily removed afterwards).

To debug a routine you will need two connections: one is the debugging connection, and the other is the worker connection. The debugger connection attaches itself to the worker connection, where your routines execute.

rdebug is controlled by an API of stored routines. This means any GUI tool may choose to use rdebug as its routine debugging mechanism. Your are not bound to a specific tool, a specific OS or framework. You may choose to invoke the API via command line, if you like; it’s all in your server.

#### A video is worth a thousand blogs Continue reading » “MySQL Stored Routines Debugger & Debugging API: sneak preview II, video”

## Cheating mysqlsandbox to install MariaDB 10.0

MySQL Hack tools March 17, 2013

mysqlsandbox is version-aware. The new 5.6 version, for example, requires special care because of the system InnoDB tables or otherwise modified system tables.

At this moment, it will refuse to install MariaDB 10.0 (alpha):

>     bash$ make_sandbox /tmp/mariadb-10.0.1-linux-x86_64.tar.gz
>     unpacking /tmp/mariadb-10.0.1-linux-x86_64.tar.gz
>     unsupported version 10.0

This is perfectly legitimate, and I have no quarrel with this fact. However, I did want to setup MariaDB 10.0 as a sandbox.

As it turns out mysqlsandbox relies on MySQL package naming conventions to detect the version: the fact that a tgz file distribution is named mariadb-10.0.1-linux-x86_64.tar.gz and extracts onto mariadb-10.0.1-linux-x86_64, tells mysqlsandbox that this is version 10.0.1. Easy enough to cheat, then: pick a version that mysqlsandbox will work with, and which is compatible sandbox-wise with your own, and do the renames. For that matter, I picked 5.5, and, actually, made it 5.5.100: Continue reading » “Cheating mysqlsandbox to install MariaDB 10.0”

## Percona Live 2013 news: Oracle, BoF, Lightning Talks

MySQL PerconaLive March 12, 2013March 12, 2013

#### Oracle

Oracle? Oracle! We’re extremely happy to have Oracle as presenter in Percona Live 2013!

You can expect three sessions by Oracle engineers:

• MySQL 5.6: Performance Benchmarks, Tuning, and Best Practices – Dmitri Kravtchuk

• MySQL 5.6: What’s New in InnoDB – Sunny Bains

• MySQL 5.6: Redefining Replication – Luís Soares

And Tomas Ulin will present the obligatory “State of the Dolphin” keynote.

Skip my part in this conference for the moment; I’m very happy this is happening. For the community this is an obvious gain. I also wish to congratulate both Oracle and Percona for pulling this off; both parties deserve warm words here.

See announcement here.

#### BoF

Birds of a Feather schedule is published. This happens on Tuesday, second day of the conference and first day of conference sessions, 6:00pm to 7:00pm. The BoF sessions are reviewed by the conference committee.

#### Lightning Talks

Lightning talks are very short, rapid talks. We have 9 talks, totaling at about one hour. Giuseppe Maxia is orchestrating the talks. The talks are presented during the Community evening, Wednesday, 5:30pm to 8:30pm, where community awards are also presented. Giuseppe will naturally elaborate on that.

For me excitement is quickly building up!

## Merging tables with INSERT…ON DUPLICATE KEY UPDATE

MySQL SQL Syntax February 21, 2013

Had a case recently where I had to merge data from two identically structured tables containing nearly identical data.

“Nearly identical” meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one tables (while others can be NULL in the second).

Otherwise no contradicting data: it was not possible for some data to be “3” in one table and “4” in the other.

#### How do you create a merge of the tables, such that all missing rows are completed, and NULLs replaced by actual values when possible?

pt-table-sync comes to mind: one can do a bidirectional syncing of two tables, and actually stating how to resolve ambiguities (like “greater value wins”). Very powerful! An example would be:

>     pt-table-sync --bidirectional --conflict-column=a --conflict-comparison=greatest --tables ...

However I didn’t actually have any problem with the tables themselves. The two tables were just fine as they were; missing or NULL data does not indicate an error on their part. I wanted to get their merge. pt-table-sync is still up for the job: we can duplicate them, merge on the copy… But I prefer a query over an external script when possible.

#### INSERT…ON DUPLICATE KEY UPDATE

This MySQL-specific syntax is actually quite powerful. It basically says “if the insert fails due to unique constraint, you get a chance to update the row causing the failure”. But it also allows for smart setting of the column via the VALUES() clause. Let’s present some sample data and then see the solution. Continue reading » “Merging tables with INSERT…ON DUPLICATE KEY UPDATE”

## MySQL Stored Routines Debugger & Debugging API: sneak preview video

MySQL common_schema debug Stored routines video February 19, 2013

This is a sneak peek video introduction/preview of an in-development free and open source server side debugger & debugging API for MySQL stored routines.

MySQL does not provide server side debugging capabilities for stored routines. Some tools exist, including MySQL’s own, that assist in stored routine debugging. These are all GUI based and, to the best of my knowledge, MS Windows based. There is one solution in alpha stage that is developed for Java/eclipse; I did not look at the code. See discussion here and here.

An ideal solution would be to have debugging API in the server itself – independently of your client, programming language or operating system. To the best of my knowledge, nothing like that is being developed.

I’m now presenting a rdebug: a stored routines server-side debugger, Pure-SQL, based on stored routines. rdebug is developed as part of common_schema, and actually relies on some of its power.

Like some other tools, it uses code injection and manipulation: it injects debugging info into your stored routine. You need to “compile” your routine with debugging info.

Unlike some other tools, it actually runs your stored routines. It does not mimic or simulate them on client side. It does not break them into smaller routines, attempting to assemble the original behavior from lego bricks.

The quick technical overview is that you use two processes (MySQL threads): the worker process running the routine (your natural call my_routine()), and the debugger process. The debugger process attaches itself to the worker process; it controls the worker by commands like “step over”; it gets data from the worker: what’s the current stack trace? What variables are now available and what are their values?; it manipulates the worker’s data: it can utilize breakpoints to modify worker’s local & session variables. Continue reading » “MySQL Stored Routines Debugger & Debugging API: sneak preview video”

## Win a free Percona Live 2013 pass — unveiling riddle hints

MySQL PerconaLive February 11, 2013February 12, 2013

Apparently my first attempt at rhyming proved to be unsuccessful: only two courageous men attempted solving the riddle. As I’m pretty sure a free pass would appeal to many, and I do have a few readers for my blog, I must conclude my riddle was just too hard. Obscure, perhaps.

Hope I didn’t scare anyone off. Without further ado I present some hints. This post will update with more hints as the day progresses — please refresh to see changes. I start with two hints.

But first, recap of the riddle:

> Who will open your present,

> Make you play pleasant,

> Tidy your mess,

> Do the same for all else?

It has something to do with the MySQL world. Continue reading » “Win a free Percona Live 2013 pass — unveiling riddle hints”

## Sessions of interest in Percona Live 2013

MySQL Opinions PerconaLive February 5, 2013February 5, 2013

Percona Live 2013 is shortly upon us, and it might be a good idea to watch for what’s ahead of us.

#### Talks of interest

There is no way I can do justice to all. I wish to point out a small number of sessions I am personally interested in attending. I will not be able to attend them all, since there are too many sessions of interest and too few instances of myself (merely one).

I’ve tried to list some talks which are not absolutely obvious (when Peter Zaitsev speaks of MySQL performance, or Monty speaks about MariaDB, or Robert Hodges or Domas speak about replication — well — you’re certain to have the ins and outs, right?). I can also expect Galera or Percona XtraDB Cluster talks to attract a lot of attention. There is a lot of good content for each.

But I was happy to find some very special talks this year, which are not the “every conference has got to have one talk about this” type. Here’s a hybrid collection of both types.

After constructing the list I’ve intentionally dropped two random sessions. If you are speaking, and not mentioned here, your talk must be one of those two! Continue reading » “Sessions of interest in Percona Live 2013”

## Looking for a hack: share data between MySQL sessions

MySQL Hack January 28, 2013

I’m looking for a way to share data between two MySQL connections/sessions. Obviously tables are the trivial answer, but for reasons of security (possibly insufficient privileges) I wish to avoid that.

The type of data to be passed can vary. Ideally I would be able to pass multiple pieces of information (dates, texts, integers, etc.) around. If impossible, I would do with texts only, and if impossible yet, I could do with a single text (but reasonably long).

There is a way to do so: by writing to the file system (SELECT INTO OUTFILE + LOAD_FILE()). However I wish to avoid it, since writing to files from within MySQL requires creation of a new file each time; no overwrite and no purging; this litters the file system.

So: any other tricks? Is there some way to pass data via GET_LOCK()/RELEASE_LOCK() (none that I can see other than Morse code)?

Is there some global variable that is unused, can be changed dynamically, and has enough space? (I couldn’t find one)

I appreciate any input.

## Re: MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation

MySQL Data Types Open Source Percona Toolkit January 24, 2013

Reading Sheeri’s MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation, I was baffled at this change of floating point number notation.

However, I was also concerned about the final action taken: using “–ignore-columns” to avoid comparing the FLOAT/DOUBLE types.

The –float-precision option for pt-table-checksum currently only uses ROUND() so as to disregard minor rounding issues. But it can very easily extend to handle the difference in floating point notation. Consider again the problem:

>     mysql> create table tf(f float);
>     Query OK, 0 rows affected (0.11 sec)

>     mysql> insert into tf values(0.0000958084);
>     Query OK, 1 row affected (0.04 sec)

>     mysql-5.1> select * from tf;
>     +-------------+
>     | f           |
>     +-------------+
>     | 9.58084e-05 |
>     +-------------+

>     mysql-5.5> select * from tf;
>     +--------------+
>     | f            |
>     +--------------+
>     | 0.0000958084 |
>     +--------------+

How can we normalize the notation?

Easily: CAST it as DECIMAL. Consider: Continue reading » “Re: MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation”