Skip to content

# Category: MySQL

MySQL related posts

## Slides from my talk: “Programmatic Queries: things you can code with SQL”

MySQL Speaking SQL October 27, 2011

Here are the slides from my talk Programmatic Queries: things you can code with SQL held on October 25th on the Percona Live event, London.

> Programmatic Queries: PDF

I wish to thank those who attended my talk.

The topic of the talk was irregular and, to some extent, controversial. Should one really rely on internal implementation for optimizing her queries? Sergei Golubchik was quick to suggest that even in current and coming versions of MariaDB, some things I spoke about may not hold true. I accept.

I was in urge to complete my talk within the time frame. I was happy to hear later on that my talk was well received.

There were homework to my talk. I thought I would make some contest picking the best answers, stay tuned.

## Test-driven SQL development

Development MySQL Ant common_schema Development Linux scripts Stored routines October 20, 2011

I’m having a lot of fun writing common_schema, an SQL project which includes views, tables and stored routines.

As the project grows (and it’s taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I’ve turned the development on common_schema to be test driven.

Now, just how do you test drive an SQL project?

Well, much like the way you test any other project in your favorite programming language. If its functions you’re testing, that’s all too familiar: functions get some input and provide some output. Hmmm, they might be changing SQL data during that time. With procedures it’s slightly more complex, since they do not directly return output but result sets.

Here’s the testing scheme I use: Continue reading » “Test-driven SQL development”

## Speaking on Percona Live, London: “Programmatic Queries: things you can code with SQL”

MySQL Execution plan Speaking SQL October 10, 2011

I’ll be speaking at the Percona Live event, held in London, October 24, 25, 2011.

My session is called Programmatic Queries: things you can code with SQL. It’s a short 30 minute talk, in which I present underlying knowledge of the programmatic nature of SQL queries within MySQL, and how to take advantage of such knowledge so as to build faster, shorter, and sometimes unexpected queries.

This is not about stored routine programming, a classic programmatic aspect of MySQL, but rather about expected order of execution: of row evaluation, of control flow statements, of table inference, of time issues.

I have far too many examples, some real-world problem solvers, and some less common in daily use, to be able to deliver them all on this session. I will pick up those which seem most interesting to me, or those best presenting the programmatic nature of the query. As time allows I may add more examples, or look into interesting future possibilities.

I hope to see you there.

## Three wishes for a new year

MySQL SQL September 27, 2011

It’s another new year by Jewish calendar. And what do I wish for in the following year?

1. World peace
2. Good health to all
3. Have some way to turn SHOW commands into SELECT statements, server side. I’m fervently trying to hack around this. Stored routines, export/import from file, text manipulation, I don’t care! I want to SELECT seconds_behind_master somehow. Without plugins.

PS, none of my last year’s wishes came true. I’ll settle for two out of three.

## common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()

MySQL common_schema Indexing Open Source Security September 6, 2011

Revision 68 of common_schema is out, and includes some interesting features:

- eval(): Evaluates the queries generated by a given query
- match_grantee(): Match an existing account based on user+host
- processlist_grantees: Assigning of GRANTEEs for connected processes
- candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
- easter_day(): Returns DATE of easter day in given DATETIME’s year.

Let’s take a slightly closer look at these:

#### eval()

I’ve dedicated this blog post on MySQL eval() to describe it. In simple summary: eval() takes a query which generates queries (most common use queries on INFORMATION_SCHEMA) and auto-evaluates (executes) those queries. Read more

#### match_grantee()

As presented in Finding CURRENT_USER for any user, I’ve developed the algorithm to match a connected user+host details (as presented with PROCESSLIST) with the grantee tables (i.e. the mysql.user table), in a manner which simulates the MySQL server account matching algorithm.

This is now available as a stored function: given a user+host, the function returns with the best matched grantee. Read more

#### processlist_grantees

This view relies on the above, and maps the entire PROCESSLIST onto GRANTEEs. The view maps each process onto the GRANTEE (MySQL account) which is the owner of that process. Surprisingly, MySQL does not provide one with such information. Continue reading » “common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()”

## MySQL eval()

MySQL common_schema SQL Stored routines September 6, 2011

I’ve just implemented an eval() call for MySQL. It is implemented with SQL, using a stored procedure. So this is not some plugin: you can use it from within your normal database server.

#### Just what is an eval() call?

In some programming languages it would mean: get some text, and execute it as though it were complied code. So, dynamic coding.

In SQL: get the text of query which generates SQL statements in itself (either DML or DDL), and invoke those implied SQL statements.

#### A simple example

Best if I present Mass killing of MySQL Connections by Peter Zaitsev. The thing is to execute a query, typically on INFORMATION_SCHEMA, which uses metadata so as to generate SQL queries/commands. Peter’s example is:

>     select concat('KILL ',id,';') from information_schema.processlist where user='root'

The above query generates KILL commands for all users called ‘root’. I do many such queries in common_schema: like creating the GRANT statements for accounts, the DROP KEY statements for redundant keys, the ADD and DROP statements for foreign keys etc.

So the problem is you have to export those statements to file, then execute them from file: either using SOURCE, as in Peter’s example, or from shell prompt, piping file contents into mysql client.

#### You can now eval()

I’ve been on family holiday for a couple of weeks, which meant no need to think of work. Which means more time to think of SQL (darn!). And I’ve found the way to do it completely within the server (no external files required). Continue reading » “MySQL eval()”

## You know you’ve been doing too much SQL when…

MySQL Humor SQL September 1, 2011

…you’re traveling on family holiday, and each time you wish to suggest you should check up on the tourist information center, you repeatedly come up with: “Let’s ask at the INFORMATION_SCHEMA and see what we can get”.

Also, doing so while wearing an OpenSQL Camp T-shirt doesn’t help.

## MySQL Global status difference using single query

MySQL Analysis common_schema INFORMATION_SCHEMA SQL August 12, 2011January 20, 2015

Have just read MySQL Global status difference using MySQL procedures / functions, by Andres Karlsson. Have commented, but realized I did not provide with a direct answer. In the comment, I suggested checking out a solution based on views, found in common_schema. But the solution in common_schema is split into two views, due to the fact views cannot handle derived tables subqueries.

Well, here’s a single query to do that: it checks GLOBAL_STATUS twice, 10 seconds apart in the following sample. It uses SLEEP() to actually wait between the two reads. Yes, you can do that with a query.

The following query shows all GLOBAL_STATUS values that have changed during the sample period.

[UPDATE] query updated to work with MySQL 5.6 optimizer Continue reading » “MySQL Global status difference using single query”

## Finding CURRENT_USER for any user

MySQL common_schema Security SQL August 9, 2011

A MySQL account is a user/host combination. A MySQL connection is done by a user connecting from some host.

However, the user/host from which the connection is made are not the same as the user/host as specified in the account. For example, the account may be created thus:

>     CREATE USER 'temp'@'10.0.0.%' IDENTIFIED BY '123456';

The host as specified in the above account is a wildcard host. A connection by the ‘temp’ user from ‘10.0.0.3’ can map into that account. It thus happens that the connected user is ‘temp’@’10.0.0.3’, yet the assigned account is ‘temp’@’10.0.0.%’.

MySQL provides with the USER() and CURRENT_USER() which map to the connected user and the assigned account, respectively, and which lets the current session identify the relation between the two. Read more on this on the MySQL docs.

#### The problem

And the trouble is: MySQL only provides this functionality for the current session. Surprisingly, given a user/host combination, I cannot get MySQL to tell me which account matches those details.

#### The inconsistency

And I care because there is an inconsistency. Namely, when I do SHOW PROCESSLIST MySQL tells me the user & host from which the connection is made. It does not tell me the account for which the process is assigned. Continue reading » “Finding CURRENT_USER for any user”

## Speaking at “August Penguin 2011”

MySQL Development Open Source Opinions August 3, 2011August 3, 2011

I will be speaking at August Penguin 2011 (אוגוסט פינגווין), on August 12th in Ramat-Gan, Israel.

August Penguin is the annual meeting of Hamakor society: an Israeli society for Free Software and Open-Source Code (read more here).

I’ll be holding a non-technical talk about MySQL, titled “MySQL and the Open Source Sphere”. In this talk I will be presenting my impressions of the nature of open source development of MySQL and surroundings: the core server, the various forks, patches, 3rd party tools, companies involved, etc. So this is a general “get to know who’s who & what’s what in the MySQL world”.

This is a 30 minutes talk. I will surely not cover every open source development in this field, so my apologies in advance to those I leave out. The truth is, there’s so much going on lately I can hardly keep up with reading the announcements. Truly, this is a wonderful time for open source development with MySQL.

Talk will be held in Hebrew.

See you there!