Bug #79432 ALTER TABLE sometimes converts TEXT to JSON without syntax checking
Submitted: 27 Nov 2015 8:14 Modified: 2 Dec 2015 12:49
Reporter: Knut Anders Hatlen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: json

[27 Nov 2015 8:14] Knut Anders Hatlen 
Description:
When using ALTER TABLE to modify a column from TEXT to JSON, syntax checks are not always performed, which could lead to JSON columns with invalid JSON data. It seems to happen if the TEXT column has utf8mb4_bin collation.

How to repeat:
create table t(x text character set utf8mb4 collate utf8mb4_bin);
insert into t values ('not json');
alter table t modify column x json;

The last statement should have raised an error, but it does not.
[27 Nov 2015 8:16] Knut Anders Hatlen 
Originally reported here: http://ocelot.ca/blog/blog/2015/11/26/standard-sqljson-and-mysql-5-7-json/
[2 Dec 2015 12:49] Jon Stephens 
Fixed in MySQL 5.8.0. Documented in the 5.8.0 changelog as follows:

    Syntax checks were not always performed when an ALTER TABLE
    changed a column's type from TEXT to JSON. This could lead
    to JSON columns containing invalid JSON data. This issue was
    observed when the original TEXT column used the utf8mb4_bin
    collation.
      
Closed.
[2 Dec 2015 18:19] Jon Stephens 
Also fixed in MySQL 5.7.11, and noted in the relevant changelog.

Status unchanged.