Category: MySQL

MySQL related posts

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

MySQL

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

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

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

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

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

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

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

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

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”

MySQL security tasks easily solved with common_schema

MySQL

Here are three security tasks I handled, which I’m happy to say were easily solved with common_schema‘s views and routines (with no prior planning). Two are so easy, that I actually now integrated them into common_schema 1.3:

  • Duplicate a user (create new user with same privileges as another’s)
  • Find users with identical set of grants (same roles)
  • Finding redundant users (users who only have privileges on non-existing objects); I was approached on this by Sheeri K. Cabral from Mozilla.

Duplicate user

How would you duplicate a grantee? That’s easy! Just get the SHOW GRANTS output, then do text search and replace: replace the existing account (e.g. ‘existing’@’localhost’) with the new account (e.g. ‘newcomer’@’localhost’).

Ahem. And how would you get the output of SHOW GRANTS? That’s right: you can’t do this from within the server. You have to go outside the server, incoke mysql client, sed your way into it, then connect to MySQL again to invoke the GRANT query… Or you can do this by hand, of course, or you can use the new mysqluserclone tool from MySQL utilities. Bottom line: you have to go outside the server. You can’t directly do this with your favorite GUI tool unless it has this function integrated.

But to have a truly automated, scriptable, server-side user-duplication you don’t need to go far, since the sql_show_grants view simulates a SHOW GRANTS output, but using plain old SQL. It produces the GRANT statement as SQL output. Which means you can REPLACE() the grantee. It’s actually a one liner, but is such a common operation that I created the duplicate_grantee() function for convenience. Just: Continue reading » “MySQL security tasks easily solved with common_schema”