Upgrading JSON data stored in TEXT columns

One of the more frequently asked questions with MySQL 5.7 is “How can I upgrade my JSON data from using TEXT in an earlier version of MySQL to use the native JSON data type?”. Today I wanted to show an example of how to do so, using sample data from SF OpenData.

Step 1: Preflight Checks

Since the JSON data type converts data to a native format for storage, it requires that all data inserted into it be valid. This check can be done before running the ALTER TABLEcommand by using the function JSON_VALID():

Searching by JSON_VALID(feature)=0 will return all invalid JSON documents, and I have intentionally corrupted one row to show an example. I will be required to manually fix this before changing the data type to JSON:


Provided the data is all valid, it is now time to change the column definition:

As expected, whitespace information is lost in the conversion to a native type. I should note however that since JSON does not support comments, no other information will be lost*

(* Steven Roussey brings up a good point in the comments, that character set may change).

The ALTER TABLE operation here will need to rebuild the primary key internally, and change data type is one of the few operations currently not covered by Online DDL. For large tables you may consider performing this operation first on a slave, or emulating online ddl via triggers.


[Source:- Mysqlserverteam]

General Tablespaces in MySQL 5.7 – Details and Tips



InnoDB in MySQL 5.7 introduced for the first time the ability to create a general tablespace and assign multiple tables to it.  These tablespaces can be assigned anywhere on the system.  They can even be assigned a smaller block size so that they can contain compressed tables that use that size as their key_block_size.

You can create a new tablespace with a command like this;

If the current innodb-page-size is 16KB then the BLOCK_SIZE phrase is optional.

A few comments about datafile names
Notice that the extension .ibd is added to the file name.  This is required. InnoDB will only accept a file name that ends with .ibd.  This helps to ensure that the filename is the one you want since not just anything can be put after ADD DATAFILE.  It also enforces the convention that all InnoDB datafiles other than the system tablespace will end in .ibdwhich helps them to be recognized.

Notice also that there is no path on the datafile above.  Relative paths like this will be relative to the datadir which is found in your configuration file. This is the same location as the system tablespace and log files.

You can also use an absolute path to create the file anywhere else on your system.  There are two restrictions concerning where a general tablespace can be located:

  1. It cannot be on the root directory.  Our design engineers thought it would be wise to prevent this. It comes mainly from the unix perspective but it is generally a good idea on Windows also.
  2. A general tablespace datafile cannot be located in a directory under the datadir.  This is where datafiles for file-per-table tablespaces are located.  In MySQL, traditionally, directories under the datadir are there to contain files related to a database or schema.  These datafiles and directories are created automatically when you create a table while innodb-file-per-table is ON.  The file name is the same as the tablename with an .ibd extension added.

It is possible to create a tablespace with the same datafile name as a file-per-table datafile.  For example:

The result will be two files named new.ibd.  The general tablespace datafile will be located in the datadir and the file-per-table datafile will be located in a directory called ‘new’ under the datadir.

A word of advice though… Try to give unique names to all your database objects.  A future version of InnoDB may prevent similar datafile names like the two above.  Or it might allow you to start associating a general tablespace with a database which could cause a conflict somehow.  It is much wiser to name different objects differently to avoid any possible conflicts.

General Tablespace Portability
You can move a file-per-table tablespace from one system to another by  following the directions here:  http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

This method uses the following commands:

Discard and Import have never been supported for tables in the system tablespace.  And it is not supported in 5.7 for general tablespaces either.  Since a general tablespace can share multiple tables just like the system tablespace, it is not as easy to transport a datafile from one system to another.

Choosing a tablespace for your table
A table can be created or altered into a general tablespace, a file-per-table tablespace or even the system tablespace by using the TABLESPACE phrase on any CREATE TABLE or ALTER TABLE statement.

This gives you the ability to explicitly choose the tablespace you want for your table and even the ability to move your table around.  You can move any table from any tablespace into any other tablespace with the TABLESPACE phase on the ALTER TABLE statement.  This means that for the first time, you can move a table into the system tablespace.  Also, you can chose to use file-per-table independent of the innodb-file-per-table setting.

So if you were to do this;

the table would be created in its own file-per-table tablespace.

Likewise, this would create the table in the system tablespace;

The tablespace name is a SQL identifier
Notice that there are no quote marks around the tablespace name in these examples.  That is because the tablespace name is a SQL identifier.  The implications are that you can also use the backtick quote marks to enclose this name and that it is always evaluated in a CASE SENSITIVE way.

This means that you can create multiple tablespaces with the same name, but in different cases, like this;

Once again, a word to the wise, try not to name your tablespaces the same with only differences in case.

Reserved Tablespace Names
There are three ‘reserved’ tablespace names that have special meaning, two of which were mentioned earlier:

  1. innodb_file-per-table
  2. innodb_system
  3. innodb_temporary

In 5.7, you can use the first two as I have already shown.  The third one is not available to use.  You do not need to use TABLESPACE=innodb_temporary to put a table into the temporary tablespace.  Just use CREATE TEMPORARY TABLE ...;.

These reserved tablespace names are case sensitive so it is possible to do this;

But once again, please don’t!  You are better off with unique tablespace names.

I hope this discussion has been useful for you to understand General Tablespaces in MySQL 5.7.  There are more tablespace features to come in future releases.

[Source:- Mysqlserverteam]


Making GET_LOCK behavior more predictable cross version with query rewrite

MySQL has supported the GET_LOCK() function for a large part of its history. As the manual notes, GET_LOCK() can be used to implement application locks or to simulate record locks.

Changes in MySQL 5.7

In MySQL 5.7 we improved GET_LOCK() to be based on our internal meta-data locking system (MDL). This allowed us to lift the restriction that each subsequent call to GET_LOCK()would release all previous locks.

However, there is a chance that some applications depended on the previous behaviour of locks being released. For this subset of users, it is not always easy to inspect an application to see when this behaviour is required. This creates challenges in upgrading:

MySQL 5.6 Behavior

MySQL 5.7 Behavior

An additional incompatibility between the previous behaviour and new, is that lock names are now restricted to 64 characters in length.

Creating an upgrade path

The easiest way to be able to upgrade, is to make MySQL 5.7 behave more like MySQL 5.6 (at least initially). For new functionality, or over time as code has been inspected, the MySQL 5.7 behavior can be slowly introduced.

Using version-specific syntax it is possible to modify application code to use an implementation of GET_LOCK() that does exactly this:

(The /*!50700 means “only run this code in 5.7+”)

The second behavior change can be overcome by running the lock name through a hashing function. For example:

Previous Usage Backwards Compatible Usage
GET_LOCK(‘abc’, 10) GET_LOCK(sha1(‘abc’), 10)
RELEASE_LOCK(‘abc’) RELEASE_LOCK(sha1(‘abc’))
IS_FREE_LOCK(‘abc’) IS_FREE_LOCK(sha1(‘abc’))
IS_USED_LOCK(‘abc’) IS_USED_LOCK(sha1(‘abc’))

To combine both, the usage for GET_LOCK() would become:

Using Query Rewrite

MySQL 5.7 also supports a feature called Query rewrite (manual). With this feature it is possible to also make legacy application preserve the old get_lock behavior without needing to make any code changes. Here is a very simple example:

With this query rewrite rule installed I can repeat the original test-case as in MySQL 5.6. I have enabled warnings to be printed to assist in readability of when rewrites are happening:


I expect that this behavior change will affect only a small percentage of applications, as in most cases the previous behavior was not something that was useful to rely upon. None the less, it was a feature that had not changed in perhaps 10+ years, so it is good to be able to have a safe upgrade path.

[Source:- Mysqlserverteam]

MySQL creator says 14,000 against Oracle-Sun deal

Michael Widenius, the creator of the MySQL database and a vocal opponent of Oracle Corp’s USD 7 billion takeover of Sun Microsystems Inc, has handed 14,000 signatures opposing the deal to regulators in Europe, China and Russia. Widenius, one of the most respected developers of open-source software, left Sun last year to set up database firm Monty Program Ab, which competes directly with MySQL. The European Commission initially objected to Oracle’s acquisition of Sun, saying it was concerned Oracle’s takeover of the MySQL database could hurt competition in that market. But the Commission signaled in mid-December that it would likely clear the deal after some of Oracle’s largest customers said they believed the takeover would not hurt competition. Since then, Oracle has said it expects to win unconditional EU clearance to close the deal by the end of January. Widenius, who delivered the signatures on Monday, said he would continue to gather signatures until the commission makes a final ruling, which is due by Jan. 27. “Our signatories don’t have faith that Oracle could be a good steward of MySQL,” Widenius said in a statement. Still, Beau Buffier, a partner in the anti-trust practice of law firm Shearman & Sterling, said signature drives carry little weight with the commission. He said regulators generally want each person who weighs in on pending cases to provide specifics on how an acquisition might affect their particular business. “What you would need is detailed statements from significant developers,” he said. More than 5,000 signatures are from self-employed developers and more than 3,000 from employees of companies and other organizations using MySQL, according to Widenius. He did not disclose the names of the people who signed the petition. The signatures were gathered during the first week of the campaign and were delivered to the European Commission and other European institutions, including the European Parliament and the competition authorities of the 27 member states, as well as to the Chinese Ministry of Commerce and the Russian Federal Antimonopoly Service. Officials with Oracle, the world’s No. 3 software maker, and Sun, the No. 4 server maker, declined comment. The acquisition will transform Oracle from a maker of software into a technology powerhouse that sells computers and storage equipment preloaded with its programs. Oracle Chief Executive Larry Ellison is betting the combination will give his company an edge over rivals such as IBM Corp, Hewlett-Packard Co, Microsoft Corp and EMC Corp. Sun bought MySQL for USD 1 billion in 2008.

[Source:- Moneycontrol]

What to do with optimizer hints after an upgrade?

At a recent optimizer webinar, I talked about MySQL introducing a new style for hints, and that MySQL 5.7 also added support for more hints, see Sergey Glukhov’s blog. A question I got at the end of the webinar was what to do with the hints in the application code after an upgrade?

The MySQL optimizer makes decisions on which query plan to choose based on the built-in cost model and statistics from storage engines, in addition to dictionary information. For each release, the optimizer gets smarter and smarter, the cost model becomes more advanced, statistics gets more fine grained, and with the planned histogram support, the optimizer will also get more information on data distribution.

While optimizer improves for each release, query plans a user has specified using hints in the application code remain unchanged. These hints may not be needed after an upgrade, and in the worst case, they can become counterproductive. We advise users to retest hints during upgrade and drop those that are no longer needed. This works fine if you have full control over the application code, but it doesn’t work if you don’t.

In 5.7, MySQL introduced a query rewrite plugin which can be very useful in such a scenario. Instead of adding hints directly into the application code, it allows users to rewrite a query by adding hints through the query rewrite plugin. Martin’s blog shows how to rewrite a query using this new plugin. During an upgrade, users can simply get a list of all rewrite rules from the rewrite_rules table, and then they can easily enable and disable each rewrite rule to verify if the hint is still useful. If not, the rewrite rule can be removed without changing any application code.


[Source:- Mysqlserverteam]