MySQL documentation

A few days ago I had a situation when I had to run some query on my MySQL server without access to the internet. And what I discovered.

The default server build is compiled with MySQL docs on board :)

Just in case, I needed JSON_SCHEMA_VALIDATION_REPORT method.

Here you are:

mysql> select * from mysql.help_topic where mysql.help_topic.name = 'JSON_SCHEMA_VALIDATION_REPORT'\G
*************************** 1. row ***************************
   help_topic_id: 410
            name: JSON_SCHEMA_VALIDATION_REPORT
help_category_id: 32
     description: JSON_SCHEMA_VALIDATION_REPORT(schema,document)

Validates a JSON document against a JSON schema. Both schema and
document are required. As with JSON_VALID_SCHEMA(), the schema must be
a valid JSON object, and the document must be a valid JSON document.
Provided that these conditions are met, the function returns a report,
as a JSON document, on the outcome of the validation. If the JSON
document is considered valid according to the JSON Schema, the function
returns a JSON object with one property valid having the value "true".
If the JSON document fails validation, the function returns a JSON
object which includes the properties listed here:

o valid: Always "false" for a failed schema validation

o reason: A human-readable string containing the reason for the failure

o schema-location: A JSON pointer URI fragment identifier indicating
  where in the JSON schema the validation failed (see Note following
  this list)

o document-location: A JSON pointer URI fragment identifier indicating
  where in the JSON document the validation failed (see Note following
  this list)

o schema-failed-keyword: A string containing the name of the keyword or
  property in the JSON schema that was violated

*Note*:

JSON pointer URI fragment identifiers are defined in RFC 6901 -
JavaScript Object Notation (JSON) Pointer
(https://tools.ietf.org/html/rfc6901#page-5). (These are not the same
as the JSON path notation used by JSON_EXTRACT() and other MySQL JSON
functions.) In this notation, # represents the entire document, and
#/myprop represents the portion of the document included in the
top-level property named myprop. See the specification just cited and
the examples shown later in this section for more information.

URL: https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html


         example: mysql> SET @schema = '{
    '>  "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> },
    '> "required": ["latitude", "longitude"]
    '>}';
Query OK, 0 rows affected (0.01 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 10.445118
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @document = '{
    '> "latitude": 63.444697,
    '> "longitude": 310.445118
    '> }';

mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#/longitude' failed requirement 'maximum' at JSON Schema location '#/properties/longitude'",
  "schema-location": "#/properties/longitude",
  "document-location": "#/longitude",
  "schema-failed-keyword": "maximum"
}
1 row in set (0.00 sec)

mysql> SET @document = '{}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
}
1 row in set (0.00 sec)

mysql> SET @schema = '{
    '> "id": "http://json-schema.org/geo",
    '> "$schema": "http://json-schema.org/draft-04/schema#",
    '> "description": "A geographical coordinate",
    '> "type": "object",
    '> "properties": {
    '>   "latitude": {
    '>     "type": "number",
    '>     "minimum": -90,
    '>     "maximum": 90
    '>   },
    '>   "longitude": {
    '>     "type": "number",
    '>     "minimum": -180,
    '>     "maximum": 180
    '>   }
    '> }
    '>}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_SCHEMA_VALIDATION_REPORT(@schema, @document);
+---------------------------------------------------+
| JSON_SCHEMA_VALIDATION_REPORT(@schema, @document) |
+---------------------------------------------------+
| {"valid": true}                                   |
+---------------------------------------------------+
1 row in set (0.00 sec)

             url: https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html
1 row in set (0.00 sec)

It was a bit impressive.

Btw, there are a few mysql.help_* tables.

mysql> DESCRIBE mysql.help_category\G
*************************** 1. row ***************************
  Field: help_category_id
   Type: smallint unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: name
   Type: char(64)
   Null: NO
    Key: UNI
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: parent_category_id
   Type: smallint unsigned
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: url
   Type: text
   Null: NO
    Key:
Default: NULL
  Extra:
4 rows in set (0.01 sec)

mysql> DESCRIBE mysql.help_keyword\G
*************************** 1. row ***************************
  Field: help_keyword_id
   Type: int unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: name
   Type: char(64)
   Null: NO
    Key: UNI
Default: NULL
  Extra:
2 rows in set (0.01 sec)

mysql> DESCRIBE mysql.help_relation\G
*************************** 1. row ***************************
  Field: help_topic_id
   Type: int unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: help_keyword_id
   Type: int unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra:
2 rows in set (0.00 sec)

mysql> DESCRIBE mysql.help_topic\G
*************************** 1. row ***************************
  Field: help_topic_id
   Type: int unsigned
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: name
   Type: char(64)
   Null: NO
    Key: UNI
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: help_category_id
   Type: smallint unsigned
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 4. row ***************************
  Field: description
   Type: text
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 5. row ***************************
  Field: example
   Type: text
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 6. row ***************************
  Field: url
   Type: text
   Null: NO
    Key:
Default: NULL
  Extra:
6 rows in set (0.00 sec)

Hope you will find it useful when you need to perform DBA tasks on a MySQL server without interrupting to browser or like in my case no internet connection on the server.