Before there was PERFORMANCE_SCHEMA, there was SHOW ENGINE INNODB MUTEX. As I wrote yesterday, these commands are useful for exposing internal locking operations that happen inside of MySQL. They are mainly useful for MySQL developers, but have some appeal for DBAs as well.

As part of an ongoing effort to cleanup the MySQL code-base and increase usability, the MySQL development team is interested in hearing feedback from users who currently use the command SHOW ENGINE INNODB MUTEX.

Specifically:

• Is there a use case for the command that is not well satisfied by Performance Schema?
• Would you be adversely affected if SHOW ENGINE INNODB MUTEX were to be deprecated?
• What tools do you use to analyze mutex contention, and what features could we add to benefit you?

Please leave a comment, or get in touch. We would love to hear from you.

The MySQL development team is seeking feedback on how you use mysqldump!

Here is some of the feedback I have to pass along:

• With InnoDB now the default (since MySQL 5.5) I would love to see mysqldump change to take advantage of this. For example:
  • mysqldump can do a hot backup with all InnoDB tables, all you need to do is include --single-transaction. We need to find a way to make this the default behaviour so that applications are not blocked waiting.
  • Adding indexes should be delayed until after all data has been inserted into the table to take advantage of the InnoDB fast-index creation feature (InnoDB Plugin/5.5+).
  • Option to dump as MyISAM, restore as InnoDB.
• An option to have progress reports while running. Even in the form 34/60 tables backed up, this is incredibly useful for beginners.
• Take inspiration from mydumper. The user should be able to dump and restore in parallel 🙂

As with some of my other posts, please leave a comment or get in touch!

Before there was information_schema and performance_schema, there were InnoDB Monitor tables.

What the monitor tables are, is a way of communicating directly to the InnoDB storage engine by creating a table of a special name. Additional debugging information is then written to MySQL’s error log. For example:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

/* view to the error log */

DROP TABLE innodb_monitor;

There’s a number of problems with this approach:

1. By using a magic table name, there is no immediate feedback for typos or spelling errors in monitor table names. The user has to wait to see if the error log is written to, and then debug if not.
2. The monitors will continually write to the error log as long as the magic table still exists. This can reduce the error log readability.
3. Parsing and using the error log is non-trivial for automated tooling. It also requires local filesystem access, and the log-file path can be changed by configuration.
4. It is difficult to apply privileges to the creation of magic tables, other than those required to CREATE or DROP tables. This is not specifically a security risk (local file system access is still required to view the log file), but semantically it makes more sense for these to require either the PROCESS or SUPER privilege.
5. information_schema is the SQL Standard for meta-data access and should be used wherever possible.

Deprecation Plan

The deprecation plan proposed for MySQL 5.7 is:

• innodb_monitor. To be replaced with SET GLOBAL innodb_monitor=ON|OFF.
• innodb_lock_monitor. To be replaced with SET GLOBAL innodb_lock_monitor=ON|OFF.
• innodb_tablespace_monitor. To be removed. information_schema will become the recommended alternative.
• innodb_table_monitor. To be removed. information_schema will become the recommended alternative.
• innodb_mem_validate. To be removed. This depends on UNIV_MEM_DEBUG, which is not normally enabled even in debug builds.

So in all cases the magic table name (point #1 above) is removed, but in some cases the monitors remain to offer compatibility where the corresponding information_schema functionality may differ dramatically in meta-data available.

Conclusion

As with our other upcoming changes planned, we are seeing community feedback as to how this change will affect users.

Do you use the InnoDB Monitors?
Will the deprecation plan affect you negatively?

Please leave a comment, or get in touch with me. Thanks!

As part of the ongoing effort to refactor MySQL and make it better, we are defining the behaviour of certain features and in some cases reimplimenting them in a more maintainable fashion.

I have previously blogged on:
* A proposal to simplify SQL_MODE options.
* The Federated Storage Engine.
* The Query Cache.
* The deprecation of EXPLAIN PARTITIONS and EXPLAIN EXTENDED.
* The deprecation of NULL synonym \N.

Today what I want to write about, is the IGNORE option in ALTER TABLE. From the MySQL manual:

“IGNORE is a MySQL extension to standard SQL. It controls how ALTER
TABLE works if there are duplicates on unique keys in the new table
or if warnings occur when strict mode is enabled. If IGNORE is not
specified, the copy is aborted and rolled back if duplicate-key
errors occur. If IGNORE is specified, only the first row is used of
rows with duplicates on a unique key. The other conflicting rows are
deleted. Incorrect values are truncated to the closest matching
acceptable value.”

This creates several issues for the MySQL server team:

1. IGNORE could remove rows from a parent table when using a foreign key relationship.
2. IGNORE makes it impossible to use InnoDB Online DDL for several operations, for example adding a PRIMARY KEY or UNIQUE INDEX.
3. IGNORE has some strange side-effects for replication. For example: DDL is always replicated via statement-based replication, and since SQL does not imply ordering, it’s not clear which rows will be deleted as part of the ignore step. I also see cross-version replication problematic if future MySQL versions were to introduce more strictness, since a slave may de-duplicate more rows.

The most common case

We believe that the most common use case for IGNORE is to be able to add a UNIQUE INDEX on a table which currently has duplicate values present. i.e.

ALTER IGNORE TABLE users ADD UNIQUE INDEX (emailaddress);

In this scenario, a novice user manages to avoid auditing each entry in the users table, and simply lets MySQL pick a row to be kept, with all duplicates automatically removed.

There are two other ways to be able to do that:

Hand removal

Using the same an example as above, return a list of email addresses and PRIMARY KEY values for records that conflict:

SELECT GROUP_CONCAT(id), emailaddress, count(*) as count FROM users 
GROUP BY emailaddress HAVING count >= 2;

/* delete or merge duplicate from above query */

ALTER TABLE users ADD UNIQUE INDEX (emailaddress);

Note: This method will be the fastest way, since when not using IGNORE, MySQL is able to use InnoDB’s Online DDL.

New table + INSERT IGNORE

While this method looks very similar, internally it’s semantics are quite different:

CREATE TABLE users_new LIKE users;
ALTER TABLE users ADD UNIQUE INDEX (emailaddress);
INSERT IGNORE INTO users_new SELECT * FROM users;
DROP TABLE users;
RENAME TABLE users_new TO users;

By creating a table first, the MySQL server will not have to manage rows in a foreign key relationship. The rows will also be re-sent to the slave using row-based replication, so issue (3) I mentioned above does not come into play.

Conclusion

We are looking for feedback on how you use this feature.

Is there another common use-case we are not accounting for?

Would it be acceptable to add a feature to MySQL Workbench to assist in preparing a table for adding a unique index (similar to hand removal)?

Help shape the future of MySQL. Please leave a comment or contact me.

In the MySQL team, we’ve observed that having a large number of possible SQL modes creates confusion amongst users. For example, if you follow my advice on ‘recommended defaults for new applications’, it is:

sql-mode="STRICT_TRANS_TABLES,
  ERROR_FOR_DIVISION_BY_ZERO,
  NO_AUTO_CREATE_USER,
  NO_AUTO_VALUE_ON_ZERO,
  NO_ENGINE_SUBSTITUTION,
  NO_ZERO_DATE,
  NO_ZERO_IN_DATE,
  ONLY_FULL_GROUP_BY"

This is quite specific, and may be too much to expect of a novice user to set in configuration.

One proposal we are putting forward to address this is to simply reduce the number of options that are available. Namely:

• Remove the options ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE.
• Have these behaviours be enabled when turning on the strict mode (STRICT_TRANS_TABLES for transactional tables, STRICT_ALL_TABLES for all tables).

Which would then bring the list down to:

sql-mode="STRICT_TRANS_TABLES,
  NO_AUTO_CREATE_USER,
  NO_AUTO_VALUE_ON_ZERO,
  NO_ENGINE_SUBSTITUTION,
  ONLY_FULL_GROUP_BY"

This has some compatibility consequences of course. To understand the change a little more, let me explain what these options do:

---

ERROR_FOR_DIVISION_BY_ZERO

Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL.

For SELECT, division by zero returns NULL. Enabling this mode causes a warning to be generated as well.

NO_ZERO_DATE

In strict mode, do not permit '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated.

NO_ZERO_IN_DATE

In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If used with the IGNORE option, MySQL inserts a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated.

---

So in a sense, we are proposing that strict mode will become stricter and that each of these individual behaviours will no longer have their own on/off switches.

What do you think –

• Do you agree that SQL_MODE usability is an issue?
• Would this change cause you any compatibility issues? i.e. We would be interested to hear a use-case where you need to enable these options individually.
• Do you have any other suggestions on how to improve SQL_MODE usability?

We value your input. Please leave a comment, or get in touch.

For those who were unaware, in MySQL the following statements are currently identical:

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName;
SELECT MAX(Population), CountryName FROM City GROUP BY CountryName ORDER BY CountryName;

That is to say that regardless of whether or not you asked for it, whenever you chose to GROUP BY, you will also have data sorted and returned in that order too.

The problem with this, is that it can result in worse performing queries. Sorting either reduces the number of execution plans possible, or requires an additional step to sort the data. Which is why many DBAs advocate writing group by queries with ORDER BY NULL. i.e.

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName;

Should be written as:

SELECT MAX(Population), CountryName FROM City GROUP BY CountryName ORDER BY NULL;

But, as Roland Bouman notes in BUG #30477 there is no standard which requires MySQL to order data in this way, and this behaviour is not present in other databases. So in MySQL 5.6, it was decided that relying on this implicit sorting should be deprecated. From
the manual:

“Relying on implicit GROUP BY sorting in MySQL 5.6 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.”

So nothing has changed yet, but please make sure you are using GROUP BY CountryName ORDER BY CountryName if your application requires it.

We are looking for community feedback on the use-cases for the Federated Storage Engine in MySQL.

Specifically, I would be interesting to hear which users find that the introduction of multi-source replication meets (or does not meet) their requirements.

For a bit of background:

The Federated Storage Engine was introduced in 5.0, but has long been disabled by default. It is useful for ad-hoc queries across MySQL servers, but it misses some of MySQL’s newer optimizations, and does not perform as well as we would like. Among it’s limitations it also does not support transactions.

By using multi-source replication, many of the downsides mentioned above are negated, since the tables can be made available to query locally. The disadvantage of course, is that there is a need to provision more storage (however, given the performance limitations of federated it is not certain it could fill this use-case either).

So please leave a comment, or get in touch. I would love to hear how you are using the Federated Engine, and what you plan to use multi-source replication for.