Year: 2012

MySQL 5.6 RC: further thoughts and questions

MySQL

Here are a few questions I came up with while experimenting with MySQL 5.6.7 & 5.6.8. They are the impressions of a first-time encounter with 5.6, which is a single opportunity for a person to point out the things that strike as odd.

Bugs-wise, just submitted another crashing bug for 5.6.8. I’m just one man, so I extrapolate to realize there is still much work to be done.

The below list does not necessarily make for a bug list; mostly things that puzzle me. I hope it can stir some additional thinking.

  1. Transportable tablespace: what’s the difference between FLUSH TABLES my_table WITH READ LOCK and FLUSH TABLES my_table FOR EXPORT? Both create the .cfg file, and both seem to operate just as well. One document says READ LOCK, another says FOR EXPORT.
  2. What’s the ALGORITHM=? flag in online ALTER TABLE? Apparently one can write to altered table even on ALGORITHM=COPY. There’s not enough documentation to explain.
  3. How come there’s not a single example of online InnoDB DDL in official docs?
  4. Why the inconsistency of putting ALGORITHM=…, LOCK=… in between commas, as opposed to other flags/commands not between commas? For example: ALTER TABLE my_table ADD COLUMN i INT, ALGORITHM=COPY, LOCK=SHARED, ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
  5. Why would anyone care about FULLTEXT search word proximity by bytes? Typically, one would want proximity by words. I can find the excuse for proximity by characters. By bytes? A user is not interested in the low level representation of the text!
  6. Could we get a distinct tablespace for the mysql internal InnoDB tables? (I understand there’s a separate tablespace for UNDO logs)
  7. Why the need to configure gtid_mode=ON as well as disable-gtid-unsafe-statements so as to enable GTID replication? If only the first is set, an error is produced upon CHANGE MASTER TO MASTER_AUTO_POSITION=1
  8. And when said error is produced, why does it not mention disable-gtid-unsafe-statements, and instead read out a cryptic message? Also note this post by Giuseppe Maxia.

Purging old rows with QueryScript: three use cases

MySQL

Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy.

You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server side scripting with QueryScript, offering a very simple syntax with no external scripting, dependencies and command line options.

I wish to present three cases of row deletion, with three different solutions. In all cases we assume some TIMESTAMP column exists in table, by which we choose to purge the row. In all cases we assume we wish to purge rows older than 1 month.

We assume the naive query is this:

DELETE FROM my_schema.my_table WHERE row_timestamp < CURDATE() - INTERVAL 1 MONTH

Case 1: TIMESTAMP column is indexed

I almost always index a timestamp column, if only for being able to quickly purge data (but usually also to slice data by date). In this case where the column is indexed, it’s very easy to figure out which rows are older than 1 month.

We break the naive query into smaller parts, and execute these in sequence: Continue reading » “Purging old rows with QueryScript: three use cases”

common_schema 1.2: security, partition management, processes, QueryScript goodies

MySQL

common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies:

  • Account blocking
  • Security audit
  • RANGE partition management
  • Slave status
  • Better blocking and idle transaction management
  • QueryScript goodies:
    • echo, report
    • while-otherwise statement; foreach-otherwise statement
    • Better variable scope handling
    • Complete support for variable expansion
    • Transaction support within QueryScript
  • More summary info and SQL statements in processlist-related views

A closer look at these follows:

Account blocking

A new view called sql_accounts, inspired by oak-block-account (also see here and here) provides with the means of blocking use accounts (and releasing them, of course) without revoking their privileges. It offers the SQL statements to block an account (by modifying its password in a symmetric way) and to release an account (by modifying its password back to normal). It really works like a charm. Together with killall() and sql_accounts this gives the administrator great control over accounts.

Security audit

Imported from openark kit, and implemented via QueryScript, the security_audit() procedure will audit your accounts, passwords and general settings to find problems, pitfalls and security hazards. I will write more on this later.

RANGE partition management

The sql_range_partitions view manages your RANGE and RANGE COLUMNS partitioned tables by providing with the SQL statements to drop oldest partitions and to create the next (in sequence) partitions. See my earlier post. Continue reading » “common_schema 1.2: security, partition management, processes, QueryScript goodies”

My take on privatized MySQL security bugs

MySQL

A couple weeks ago I submitted Bug #67315: Crashing server by stored function referencing user defined variable in query. If you press that link, you can’t see the bug (though I can as I submitted it).

This is due to Oracle’s policy for security-related bugs. Tomas Ulin, Vice President MySQL Development at Oracle , was kind enough to discuss Oracle’s policy with me, and these are the key points as I understand them:

Oracle’s basic approach is to protect its customers. By publicizing security-bugs, Oracle’s customers are vulnerable to black hatters attacks. Therefore Oracle takes measures and privatizes security bugs (crashing bugs can be treated as security bugs since a crash is a form of Denial of Service).

But what of a bug reported in a RC version, as was in my case? There is no strict policy there, according to Ulin. However with a version this close to GA, it is uncertain that a specific bug would be fixed in time. It may happen, then, that a bug would find itself well into GA releases, thereby exposing customers to attacks.

Moreover, GA bugs that are already fixed may remain private, as customers will not necessarily haste to upgrade their working servers for every bug fix.

My take

Bug privatization has disadvantages, as well: Continue reading » “My take on privatized MySQL security bugs”

Pop quiz: funny syntax

MySQL

The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript.

Can you guess the results of the following statements?

Pop quiz 1

SET @x := 7;
SELECT ++@x;

What is the computation result? What will be the value of @x?

Pop quiz 2

SET @ := 4;
SELECT @ + @'' + @``

What is the computation result?

Pop quiz 3

SET @a := 2;
SELECT @a = @'a' = @`a`

Do we get TRUE or FALSE? When? Continue reading » “Pop quiz: funny syntax”

Further experiments with MySQL 5.6.7-RC: submit your bugs

MySQL

Here’s the background: I’m testing many features of MySQL 5.6.7-RC due to two reasons:

  • I’m verifying my common_schema installs and works properly on 5.6
  • I promised I would present a 45 minute “what’s new in MySQL 5.6” seminar in the upcoming OracleWeek (Israel)

In the case of common_schema, I have managed to find one weird bug (a behavior regression from 5.5) and one server-crashing bug, by merely running the project’s tests, known to pass on 5.1 and 5.5 (and not utilizing any 5.6 features).

In the case of my presentation, I’m getting acquainted with new syntax and functionality, and am getting unexpected results. I’ve hit replication issues and locking issues in online DDL.

If I am able to find these bugs within a few hours of experimenting & testing, then it is safe to assume we can extrapolate to many more bugs. This is not surprising; 5.6 boasts some 120 new features (I didn’t actually count them). Of course they would introduce new bugs.

The moral is this: if you’re interested in 5.6 as I am – and there’s a lot to wait for – please consider experimenting with it, and report as many bugs to bugs.mysql.com as possible. Reporting bugs is the user’s authoritative way of improving an open source product and pushing towards a stable release. Provide your feedback today!

InnoDB DDL: kudos to quick responders on bugs.mysql.com

MySQL

Continuing my experiments with 5.6 InnoDB online DDL, a bug which I’ve opened, and another which I commented on were quickly answered and explained by the Oracle/MySQL team.

On both accounts I’m happy to acknowledge the issue is resolved; in both cases I failed to produce a real bug scenario. Good lesson. Kudos for quick and informative responses!

What’s left of my experiment, then? Still a lot to check.

I am mainly still confused with which operations exactly can use LOCK=NONE (allowing for updated to table while ALTERing). So far I am only able to produce ALTERs with LOCK=SHARED, meaning table is readable, but cannot be updated.

I will want to test speeds. I’ve so far been content with slow response times for queries over altered tables. How well will that endure under heavy load?

Experimenting with 5.6 InnoDB Online DDL (bugs included)

MySQL

MySQL 5.6 offers the groundbreaking online DDL operations for InnoDB. Most common use cases will enjoy this feature, and the need for online alter table scripts will decrease. This is a killer feature!

I’ve put this new feature to the usability test. How did it go? Not too well, I’m afraid.

[Updates to this text inline], also see this followup.

sakila & DDL

sakila is still a very useful database. I say “still” because it is not very large, and computing power is getting stronger; yet on my laptop some operations can still take many seconds to complete, which is just fine for my tests.

Sakila tables are mostly InnoDB, and rental being the largest, I do:

node1 (sakila) > alter table sakila.rental engine=InnoDB;
Query OK, 16044 rows affected (6.94 sec)
Records: 16044  Duplicates: 0  Warnings: 0

So what can be executed during these 6.94 seconds? In a second terminal, I try the following: Continue reading » “Experimenting with 5.6 InnoDB Online DDL (bugs included)”

Percona Live MySQL Conference and Expo 2013, Call for Papers

MySQL

I’m honored to accept the role of conference chairman in the upcoming Percona Live MySQL Conference and Expo, April 22th-25th, 2013, Santa Clara, CA. Here are a few administrative announcements:

Call for Papers

Call for papers is extended until Oct 31st. We’ve already received a good number of submissions, but wish to provide with another chance for latecomers. Now is a good time to submit!

The committee will begin the long reviewing process shortly.

Committee

Members of the reviewing committee are:

  • Roland Bouman (Pentaho)
  • Ronald Bradford (Continuent)
  • Laine Campbell (PalominoDB)
  • Colin Charles (MontyProgram)
  • Jeremey Cole (Twitter)
  • Patrick Galbraith (HP)
  • Henrik Ingo (Nokia)
  • Jay Janssen (Percona)
  • Giuseppe Maxia (Continuent)
  • Domas Mituzas (Facebook)
  • Ivan Zoratti (SkySQL)

Continue reading » “Percona Live MySQL Conference and Expo 2013, Call for Papers”

Thoughts on MySQL 5.6 new replication features

MySQL

After playing a little bit with MySQL 5.6 (RC), and following closely on Giuseppe’s MySQL 5.6 replication gotchas (and bugs), I was having some thoughts.

These are shared for a few reasons:

  • Maybe I didn’t understand it well, and someone could correct me
  • Or I understood it well, and my input could be of service to the developers
  • Or it could be of service to the users

InnoDB tables in mysql schema

The introduction of InnoDB tables in mysql makes for crash-safe replication information: the exact replication position (master log file+pos, relay log file+pos etc.) is updated on InnoDB tables; with innodb_flush_logs_at_trx_commit=1 this means replication status is durable and consistent with server data. This is great news!

However, the introduction of InnoDB tables to the mysql schema also breaks some common usage on installation and setup of MySQL servers. You can’t just drop your ib_data1 file upon dump+restore, since it also contains internal data. Giuseppe outlines the workaround for that.

I was thinking: would it be possible to have a completely different tablespace for MySQL’s internal InnoDB tables? That could be a single tablespace file (who cares about file-per-table on a few internal tables). And I’m throwing an idea without being intimate with the internals: you know how it is possible to span the shared tablespace across multiple files, as in: Continue reading » “Thoughts on MySQL 5.6 new replication features”