1

I keep getting an error when trying to run a statement for the MySql database. What I am trying to do is change column name's type from BLOB to JSON.

Here is the statement:

ALTER TABLE `main_db`.`main_table` 
CHANGE COLUMN `name` `name` JSON CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

This is something that workbench automatically created for me and when I ran it... I get this error:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `main_db`.`main_table` 
CHANGE COLUMN `name` `name` JSON CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET 'utf8mb4' NULL DEFAULT NULL' at line 2
SQL Statement:
ALTER TABLE `main_db`.`main_table` 
CHANGE COLUMN `name` `name` JSON CHARACTER SET 'utf8mb4' NULL DEFAULT NULL

Any help will be appreciated.

Thanks :)

dani.nykh
  • 37
  • 1
  • 8
  • Possible duplicate of [this](http://stackoverflow.com/questions/1356866/how-do-i-change-the-data-type-for-a-column-in-mysql) SO question? – Jeroen Heier Feb 18 '17 at 06:52
  • No, that did not answer my question. If I run a statement from that answer I get another error:
    ERROR 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'. SQL Statement: ALTER TABLE `main_db`.`main_table` MODIFY `name` JSON     – dani.nykh Feb 18 '17 at 18:23

4 Answers4

3

How about converting the column to JSON in two steps via TEXT?

ALTER TABLE `main_db`.`main_table` MODIFY `name` TEXT;
ALTER TABLE `main_db`.`main_table` MODIFY `name` JSON;

Using MODIFY instead of CHANGE because you did not need to rename the column.

David
  • 31
  • 4
2

Try to simplify the command and eliminate the parts you aren't changing:

ALTER TABLE `main_db`.`main_table` 
CHANGE COLUMN `name` `name` JSON;
Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40
  • 2
    I tried that, the problem is this error: ERROR 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'. SQL Statement: ALTER TABLE `main_db`.`main_table` CHANGE COLUMN `name` `name` JSON – dani.nykh Feb 18 '17 at 18:08
  • Sounds like the table is populated, and the existing data can't be converted. You may have to create a new table, and write a query that will copy the data over, with explicit conversion of the JSON data. – Sloan Thrasher Feb 18 '17 at 21:13
  • You're the best. It worked after I cleared my table. Thanks <3 – dani.nykh Feb 19 '17 at 01:13
  • how can i convert explicitly from blob to json? I got the same error Cannot create a JSON value from a string with CHARACTER SET 'binary'. – Sathiya Vasagam S Jan 22 '19 at 04:39
  • @SathiyaVasagamS, see David's answer below. You may have to do it in multiple steps. – Sloan Thrasher Jan 22 '19 at 20:44
  • no. when I modify also got the same error. direct conversion via query its not working for me – Sathiya Vasagam S Jan 23 '19 at 03:27
1

This is what I do, explicitly convert, drop old column, and rename temp column:

alter table testjson add jdata json;
update testjson set jdata = cast(convert(cast(xdata as char(999999)) using utf8) as json);
alter table testjson drop column xdata;
alter table testjson change column jdata xdata json;

Worth noting here There's a related trick in LOAD..INFILE:

LOAD DATA LOCAL INFILE "/tmp/another.csv" INTO TABLE another(@hid, @htype, @bindata) set id=unhex(@hid), type=unhex(@htype), jdata=cast(convert(cast(@bindata as char(999999)) using utf8) as json);
jmullee
  • 390
  • 3
  • 6
1

To get the existing data you need to create a new temp column of json format. copy the data in blob column into the temp column by casting it into string type.

update main_db set temp = CAST(name AS CHAR(100000));

now rename both the columns and you will get back the existing data.

aRvi
  • 2,203
  • 1
  • 14
  • 30