TiDB now supported in DBdeployer 1.19.0!

It is now easier to evaluate if your application will work with TiDB.

DBdeployer 1.19.0 now supports installing the tidb-server in a single sandbox mode.  Unlike a production install of TiDB, this deployment mode does not require PD or TiKV.  This also makes for a much smaller footprint (a ~20MB download, and a server that can run with 200MB RAM).

Internally it uses goleveldb (instead of RocksDB), so it is by my measure 5x slower than a production deployment using TiKV.  So please use this only for development purposes as intended.

For those of you unfamiliar with TiDB’s architecture, see this manual page.

Example Usage

Using daily tarballs built from TiDB’s master branch*:

# Mac OS X
curl -O https://download.pingcap.org/tidb-master-darwin-amd64.tar.gz
dbdeployer unpack tidb-master-darwin-amd64.tar.gz --unpack-version=3.0.0
dbdeployer deploy single 3.0.0 --client-from=5.7.25

# Linux
wget https://download.pingcap.org/tidb-master-linux-amd64.tar.gz
dbdeployer unpack tidb-master-linux-amd64.tar.gz --unpack-version=3.0.0
dbdeployer deploy single 3.0.0 --client-from=5.7.25

* Notes:

  • The –unpack-version is required because these are builds from master branch, which are currently unversioned.  When TiDB 3.0 is released, I will update with examples on stable packages.
  • The –client-from option is required because TiDB does not ship with client libraries. I am omitting a step, which is that you will first need to download and install a MySQL 5.7.25 sandbox as in the example shown.


Thank you to Giuseppe Maxia for building and maintaining dbdeployer.  I’ve been a long time MySQL Sandbox fan. Support for TiDB was a collaboration we started at FOSDEM this year.  

Since dbdeployer supports templates, we actually managed to get a working prototype on the first day, without changing any code.  i.e. we just had to write new shell scripts for start, stop, status and tell dbdeployer to install them.

I recommend product vendors work closer with package maintainers.  Adding dbdeployer support has also led to several minor bug fixes and documentation improvements, that benefit all TiDB users.

This blog, now Powered by WordPress + TiDB

In case you missed my tweet, I’ve joined PingCAP! We are building an open-source horizontally scalable, MySQL-compatible database named TiDB.  TiDB is designed for hybrid transaction and analytics processing (HTAP) workloads.

I am a strong believer of drinking your own champagne, aka dogfooding. So I’ve switched my blog from using MySQL to using TiDB.  This gives me an opportunity to test out new features through the eyes of an end-user.

Continue reading “This blog, now Powered by WordPress + TiDB”

Moving to Product Management & European Tour

Welcome all to 2016.

Just a quick update to say that I’m moving teams inside Oracle. My new position is as Product Manager for the MySQL Server 🙂 This is a great opportunity for me to be more directly involved in developing MySQL, and something I’m very much looking forward to. It will also mean many of my future blog posts will appear on the MySQL Server Team blog instead of here on my personal blog.

(The MySQL Community Team is now looking to fill a vacancy. The ideal candidate will be based in Europe. Please let me know if you are interested. I’ll put you in touch!)

The second part to this update is to say that I’ll be touring Europe for almost all of February. You can catch me in Brussels, Amsterdam, Helsinki, Munich, Berlin, Madrid, Paris, Dublin, Manchester & London. Please check out the announcement blog post for more details!

What would you like to see in MySQL 5.8?

By my count MySQL 5.7 added over 150 new features, and some of my favourite ones are based on community ideas and contributions:

  1. Statement Timeout (contributed by Davi Arnaut)
  2. Disabled Storage Engines (suggested by the community)
  3. MySQL Command Line client Ctrl+C support fixed (bug reported by the community)
  4. Barracuda + Dynamic row format enabled by default (change made to better support WordPress and Drupal communities)
  5. Multiple GET_LOCK locks can be acquired per connection (Contributed by Konstantin Osipov)

With 5.7 now in RC2, it is time for us to start planning for MySQL 5.8, and we are actively seeking community input and feature requests. There are multiple ways you can leave feedback:

  • Via the comments here
  • In person: Both myself and colleagues will be at Percona Live next week, and MySQL Central at the end of October.
  • Via the Bugs Database: We pay close attention to bugs with a high ‘affects me’ count. If you file a feature request, please blog about it so that we can all provide input 🙂
  • Via email

Thank you!

A followup on show_compatibility_56

Giuseppe and Shlomi both blogged on one of the recent changes introduced to MySQL 5.7.8-rc, where the setting show_compatibility_56 is now set OFF by default.

Both raise very good points. Here is how we plan to address them:

  1. The permissions issue reported by Giuseppe will be fixed.
  2. When selecting from information_schema tables in show_compatibility_56=OFF mode, an error will now be produced:

    mysql> select * from information_schema.global_variables;
    ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

    (Previously this was a warning + empty set returned)
  3. When show_compatibility_56=ON, users will now be able to select from either information_schema or performance_schema tables. This presents a more viable upgrade path for users that need some period to transition.

The show_compatibility_56 setting itself will remain deprecated, as it was from its introduction. This signifies that we intend to remove INFORMATION_SCHEMA.GLOBAL_VARIABLES in a future release.

Outside of the scope of today’s update, my colleague Mark Leith has also volunteered to blog with examples of how the new performance_schema tables now expose variables down to the thread-level. This will add more context as to why we are moving this meta data from information_schema to performance_schema. Thanks Mark!

So thank you again to Giuseppe and Shlomi for helping make a better MySQL. We’re delighted to incorporate your feedback!

Proposal to extend binary operators in MySQL

In order to make it easier to work with data stored as binary strings (BINARY/VARBINARY) we are considering extending the &,|,^,~,<<,>> operators, to accept any-size binary strings and return binary string data as response. This can be useful for several complex data types not covered by the basic SQL data types (e.g. working with IPV6 addresses, manipulating UUID, etc).


Let’s say we’re interested in getting all the networks that contain the given IP address. With ipv4 the common practice is to store the IP addresses as INT and execute:

SELECT inet_ntoa(network) AS network, inet_ntoa(netmask) AS netmask FROM network_table WHERE (inet_aton('') & netmask) = network;

At the moment you are not able to do the same with ipv6 because inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334') & netmask converts both operands from VARBINARY to BIGINT resulting in data truncation and when the & operation gets executed, the result is incorrect. But, if the & operator could have worked directly on BINARY/VARBINARY data, this would have been possible:

SELECT inet6_ntoa(network) AS network, inet6_ntoa(netmask) AS netmask FROM network_table WHERE (inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334') & netmask) = network;

The SQL standard does not define bitwise operations over any-size binary string data but it does define binary string comparison:

All binary string values are comparable. When binary large object string values are compared, they shall have exactly the same length (in octets) to be considered equal. Binary large object string values can be compared only for equality. For binary string values other than binary large object string values, it is implementation-defined whether trailing X’00’s are considered significant when comparing two binary string values that are otherwise equivalent.

Thus, the standard allows binary strings to be zero-padded in the least significant part for comparisons (right-side). If you’re interpreting binary data as a hexadecimal unsigned integer, you would expect the operand with smaller size to be zero-padded to the left side. So an easy approach to avoid confusion would be to allow the operators(^,&,|) to only work with same size operands, thus avoiding any confusion over whether padding occurs in most or least significant part.

Another aspect to be mentioned is that the old behavior with INT arguments would be preserved. Example SELECT 23 & 4 would still return a numeric BIGINT response: 4.

MySQL has two ways of representing hexadecimal string literals: x'val' and 0xval (where val contains hexadecimal digits 0-9, a-f, A-F). The difference between the two is that the first is SQL standard and has a constraint: the number of hexadecimal digits must be even, the second version is not standard and does not require an even number of characters (it will be zero-padded on the left side in case of even number of characters). But there is one issue, in numeric contexts, hexadecimal values act like integers (64-bit precision) and in string contexts, they act like binary strings. So currently when executing SELECT x'10000001' & x'00000001' the operands get converted from VARBINARY to BIGINT(int64), with loss of any parts beyond 64 bits, and this returns BIGINT; with our change, this would return BINARY(4), breaking existing applications. That’s something that can be solved with a new sql_mode named BINARY_BIT_OPS_YIELD_BINARY, off by default for backward-compatibility; if turned on, bit operation on binary strings will yield BINARY result (or an error if operators are not of the same size).

The alternative would be to introduce new functions. Example bin_and(x,y), bin_or(x,y), bin_xor(x,y) that can take two binary string arguments of same length and return binary string. We are considering also other names like binary_and(x,y), binary_or(x,y), binary_xor(x,y).


  • No compatibility issues
  • Bitwise operators (&, |, ^) would remain operators that yield integer results (preserving existing functionality), whereas the new functions would yield binary string results


  • It’s longer to type bin_and()/binary_and() than &
  • This creates new syntax for users to learn
  • bit_and(x) already exists, it’s an aggregate function; we fear this could confuse users, though, fortunately both functions do a similar thing (they AND bits).

Please let us know in a comment below what are your opinions on this:

  • Is it a good idea to implement bitwise operations for binary strings?
  • Is the BINARY_BIT_OPS_YIELD_BINARY sql_mode necessary?
  • Can you think of other use cases where this can be useful (UUID handling is one such case)?

Thanks to Catalin Besleaga on the optimizer team for ghost writing this post.

MySQL 5.7.8 – mysqlpump caveat

MySQL 5.7.8-rc2 was released today, and features a new server utility called mysqlpump. This utility contains a number of major improvements over mysqldump including:

  • Parallel processing of databases, and of objects within databases, to speed up the dump process
  • For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted
  • Better control over which databases and database objects (tables, views, stored programs, user accounts) to dump
  • Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database
  • Capability of creating compressed output
  • Progress indicator

What I wanted to caution however, is that mysqlpump is not currently consistent. That is to say that currently each of the dump threads lack a synchronization point before they start backing up the data. This makes it currently unsafe as a general purpose backup replacement.

The mysqlpump developers are aware of this limitation, and are busy adding this feature into the utility.

Edit: This functionality was added to MySQL 5.7.11.

The MySQL 5.7 Optimizer Challenge

In the MySQL team, we have been working really hard on refactoring the optimizer and improving the cost model. The hacks of storage engines lying to the optimizer are being rolled back, and your chances of getting an optimal query plan should now be much higher than in prior releases of MySQL.

The optimizer team has also allowed cost constants to be configurable on both a server and a storage engine basis, and we are confident that the default InnoDB engine will always work “as good as MyISAM” (which has a natural advantage, in that the optimizer was originally largely built around it.)

Today, I want to issue a challenge:

Find an example where the optimizer picks the wrong execution plan for InnoDB tables but is correct for MyISAM. If you can demonstrate a reproducible testcase, I have a polo with MySQL 5.7 Community Contributor on it waiting for you.

The supplies of this special edition t-shirt are limited, but I will ship it to you anywhere in the world 🙂

The MySQL 5.7 Community Contributor Polo, as modeled by Daniël van Eeden.
The MySQL 5.7 Community Contributor Polo, as modeled by Daniël van Eeden. I’m the guy on the left.

MySQL 5.7.8 – Now featuring super_read_only and disabled_storage_engines

I wanted to highlight two new features that are making their way into MySQL 5.7 via the not-yet-released 5.7.8-rc2:

  • A new system variable super_read_only allows a more strict definition of ‘read-only’ which also applies to super users.
  • A new disabled_storage_engines setting offers a way to prevent an enumerated list of storage engines from being used. For example, a DBA may wish to enforce an InnoDB-only policy to simplify common operations such as backups, but it’s possible MyISAM may sneak back in via new code-deployments. This setting allows more active enforcement.

These features are the fruits of our engineering team meeting with our users at Percona Live this year. Thank you to Percona for once again hosting a great conference, and in particular thank you to @isamlambert (and the GitHub Engineering team), @John_Cesario, @denshikarasu & Rob Wultsch for specifically requesting these two features 🙂