Skip to content

# Category: MySQL

MySQL related posts

## Purging old rows with QueryScript: three use cases

MySQL Bulk operations common_schema Indexing QueryScript scripts November 14, 2012November 14, 2012

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 Open Source partitioning QueryScript Security November 13, 2012

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 Bugs Opinions November 8, 2012November 8, 2012

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 SQL Syntax November 5, 2012

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 New Features Opinions October 22, 2012

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 InnoDB New Features October 18, 2012

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 InnoDB New Features openark kit Opinions scripts October 18, 2012October 18, 2012

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 conference PerconaLive October 16, 2012

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 Configuration InnoDB New Features Opinions Replication October 15, 2012

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”

## Your magical RANGE partitioning maintenance query

MySQL common_schema partitioning October 9, 2012

If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the “next” partition? How and when do you drop your older partitions?

Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):

>     mysql> SELECT * FROM sql_range_partitions \G
>     *************************** 1. row ***************************
>                 table_schema: test
>                   table_name: city
>     sql_drop_first_partition: alter table `test`.`city` drop partition `p3`
>       sql_add_next_partition: alter table `test`.`city` add partition (partition `p_20160101000000` values less than (736329) /* 2016-01-01 00:00:00 */ )
>     *************************** 2. row ***************************
>                 table_schema: test
>                   table_name: quarterly_report_status
>     sql_drop_first_partition: alter table `test`.`quarterly_report_status` drop partition `p3`
>       sql_add_next_partition: alter table `test`.`quarterly_report_status` reorganize partition `p_maxvalue` into (partition `p_20110401000000` values less than (1301608800) /* 2011-04-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE)

#### A closer look at why this is magic

This query just gave you the DROP PARTITION and ADD PARTITION for all tables in your databases that use a RANGE partitioning scheme. But, consider: Continue reading » “Your magical RANGE partitioning maintenance query”