0

In MySQL 8.0.4 and later, there is a function named "JSON_TABLE".

I tried the examples from the official mysql docs, but JSON_TABLE returned an error:

SELECT *
FROM
  JSON_TABLE(
    '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    "$[*]" COLUMNS(
      xval VARCHAR(100) PATH "$.x",
      yval VARCHAR(100) PATH "$.y"
    )
  ) AS  jt1;

ERROR 1142 (42000): SELECT command denied to user 'test'@'127.0.0.1' for table 'json_table'

this sql statement was executed in mysql docker container (latest).

mysql> SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.11                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
9 rows in set (0.06 sec)

Does anyone know why JSON_TABLE isn't working?

user2458995
  • 161
  • 2
  • 3
  • Wierd error do you really have a table called `json_table` ? – Raymond Nijland Apr 24 '18 at 12:23
  • no, i don't. its a fresh setup (docker container). `docker run --name mysql -e MYSQL_USER=test -e MYSQL_PASSWORD=test -e MYSQL_DATABASE=test -d mysql:8` > `docker exec -it mysql bash` > `mysql -u test -p` > `use test;` > execute sql statement with JSON_TABLE – user2458995 Apr 24 '18 at 12:56
  • "i tried the examples from the official mysql docs, but JSON_TABLE returned an error:" -> think you should report a bug on the mysql forum.. – Raymond Nijland Apr 24 '18 at 13:41
  • I reported this bug. I found out that json_table is working with root user. So maybe just a permission problem? I will update as soon as i found a fix. – user2458995 Apr 24 '18 at 16:23
  • Funny, the error says that your user is denied `SELECT` command, but you reported a bug and you thought `JSON_TABLE` isn't working. Is reading that hard these days? – N.B. Apr 25 '18 at 08:45
  • it's not that hard. but i can't find a solution to use json_table without grant select privileges to all databases. for me it looks like a bug. if you have any idea how to solve this, your welcome. – user2458995 Apr 25 '18 at 09:51
  • 1
    update: it is a software bug. https://bugs.mysql.com/bug.php?id=90610 – user2458995 Apr 27 '18 at 07:57

0 Answers0