emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Working Time Calculator

Echo JS - 3 hours 30 min ago
Categories: Web Technologies

JSON Schema Validation with MySQL 8.0.17

Planet MySQL - 3 hours 46 min ago
JSON has become the standard document interchange format over the last several years.  MySQL 5.7 added a native JSON data type and it has been  greatly enhanced with version 8.0.  But many in the relational world have complained the the NoSQL approach does not allow you to have rigor on your data. That is to make sure an integer value is really an integer and within specified ranges or string of the proper length. And there was no way to make sure that email addresses are not listed under a combination of E-mail, e-mail, eMail, and eMAIL.  JSON is great for many things but traditional, normalized data was better for making certain that your data matched what was specified.

If only there was a way to enforce come rigor on JSON data! Or a way to annotate (pronounced 'document') your JSON data. Well there is. MySQL 8.0.17 has added the ability to validate JSON documents against a schema following the guidelines of the JSON-Schema.org's fourth draft standard. You can find both the manual page 12.17.7 JSON Schema Validation Functions and the JSON Schema information online.


Valid JSON and Really Valid JSON
As you are probably already aware, MySQL will reject an invalid JSON document when using the JSON data type.  But there is a difference between syntactically valid and validation against a schema. With schema validation you can define how the data should be formatted. This will help with automated testing and help ensure the quality of your data.


Overly Simple Example
Lets create a simple document schema that looks at a key named 'myage' and set up rules that the minimum value is 28 and the maximum value is 99.

set @s='{"type": "object",
     "properties": {
       "myage": {
       "type" : "number",
       "minimum": 28,
       "maximum": 99
   }
}
}';

And here is our test document where we use a value for 'myage' what is between the minimum and the maximum.

set @d='{  "myage": 33}';

Now we use JSON_SCHEMA_VALID() to test if the test document passes the validation test, with 1 or true as a pass and 0 or false as a fail.

select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)


Now try with a non-numeric value.

set @d='{  "myage": "foo"}';
Query OK, 0 rows affected (0.00 sec)

mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
|                        0 |
+--------------------------+

And a value below the minimum.

mysql> set @d='{  "myage": 16}';
Query OK, 0 rows affected (0.00 sec)

mysql> select JSON_SCHEMA_VALID(@s,@d);
+--------------------------+
| JSON_SCHEMA_VALID(@s,@d) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

Validity Report

We can use JSON_SCHEMA_VALIDATION_REPORT() to get more information on why a document is failing with JSON_SCHEMA_VALID().

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

And, you should note, the response is in JSON format. And you can neaten the output up with JSON_PRETTY() wrapped around the above query.


select JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d))\G
*************************** 1. row ***************************
JSON_PRETTY(JSON_SCHEMA_VALIDATION_REPORT(@s,@d)): {
  "valid": false,
  "reason": "The JSON document location '#/myage' failed requirement 'minimum' at JSON Schema location '#/properties/myage'",
  "schema-location": "#/properties/myage",
  "document-location": "#/myage",
  "schema-failed-keyword": "minimum"
}
Required Keys
If you want to make sure certain keys are included in a document, you can use a the required option in your schema definition.  So if you are working with GIS information, you can specify requiring longitude and latitude.

""required": ["latitude", "longitude"]


So we can no have required fields and specify their value ranges. And we can verify BEFORE committing the JSON document to the MySQL server that the data conforms to our schema.





Using JSON SCHEMA Validation with Check ConstraintSO the next logical step is to use the CONSTRAINT CHECK option on table creation to assure that we are not only getting a valid JSON document but a verified JSON document.

 
CREATE TABLE `testx` (
`col` JSON,
CONSTRAINT `myage_inRange`
CHECK (JSON_SCHEMA_VALID('{"type": "object",
"properties": {
"myage": {
"type" : "number",
"minimum": 28,
"maximum": 99
}
},"required": ["myage"]
}', `col`) = 1)
);

And the proof that it works.

mysql> insert into testx values('{"myage":27}');
ERROR 3819 (HY000): Check constraint 'myage_inRange' is violated.
mysql> insert into testx values('{"myage":97}');
Query OK, 1 row affected (0.02 sec)
 

So two of the big criticisms on using JSON in a relational database are now gone. We can add rigor and value checks.  While not as easy to do as with normalized  relational data, this is a huge win for those using JSON.
 


More on JSON Schema
I highly recommend going through the basics of JSON Schema as they is a lot of material that can not be covered in a simple blog.











Categories: Web Technologies

Three New JSON Functions in MySQL 8.0.17

Planet MySQL - 3 hours 47 min ago
MySQL 8.0.17 adds three new functions to the JSON repertoire.  All three can take advantage of the new Multi-Value Index feature or can be used on JSON arrays.

JSON_CONTAINS(target, candiate[, path])
This function indicates with a 1 or 0 if a  candidate document is contained in the target document. The optional path argument lets you seek information in embedded documents.  And please note the 'haystack' is before the 'needle' for this function.

mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}');
+------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Moe": 1}') |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}');
+--------------------------------------------------------+
| JSON_CONTAINS('{"Moe": 1, "Larry": 2}','{"Shemp": 1}') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Objects as must match both key and value. Be careful as an array is considered to be contained in a target array only if every element in the candidate is contained in some element of the target. So JSON_CONTAINS("[1,2,3]","[2,3]") will return a '1' while JSON_CONTAINS("[1,2,3]","[3,4]") will return a '0'.

You can always use JSON_CONTAINS_PATH() to test if any matches exist on the entire path and JSON_CONTAINS() for a simple match.

JSON_OVERLAPS(document1, document2)
 This functions compares two JSON documents and returns 1 if it has any key/value pairs or array elements in common.

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,3,4,5]") |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[2,4,6]");
+--------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[2,4,6]") |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

So what is the difference between these two new functions? JSON_CONTAINS() requires ALL elements of the array searched for to be present while JSON_OVERLAPS() looks for any matches. So think JSON_CONTAINS() as the AND operation on KEYS while JSON_OVERLAP is the OR operator.

mysql> SELECT JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_OVERLAPS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)


mysql> SELECT JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]");
+----------------------------------------+
| JSON_CONTAINS("[1,3,5,7]","[1,3,5,9]") |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.00 sec)

value MEMBER OF(json_array)
This function returns a 1 if the value is an element of the json_array.


mysql> SELECT 3 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 3 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 2 MEMBER OF('[1, 3, 5, 7, "Moe"]');
+------------------------------------+
| 2 MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (0.00 sec)

This function does not convert to and from strings for you so do not try something like this.

mysql> SELECT "3" MEMBER OF('[1, 3, 5, 7, "Moe"]');
+--------------------------------------+
| "3" MEMBER OF('[1, 3, 5, 7, "Moe"]') |
+--------------------------------------+
|                                    0 |
+--------------------------------------+

So "3" is not equal to 3.  And you may have to explicitly cast the value as an array or use JSON_ARRAY().

mysql> SELECT CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------------+
| CAST('[3,4]' AS JSON) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]');
+---------------------------------------------+
| JSON_ARRAY(3,4) MEMBER OF ('[[1,2],[3,4]]') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.00 sec)

Use with Multi-Value IndexesQueries using JSON_CONTAINS(), JSON_OVERLAPS(), or MEMBER OF() on JSON columns of an InnoDB table can be optimized to use Multi-Valued Indexes.  More on MVIs in another blog post!







Categories: Web Technologies

MySQL InnoDB Cluster from scratch – even more easy since 8.0.17

Planet MySQL - 4 hours 2 min ago

Create a MySQL InnoDB Cluster using MySQL 8.0 has always been very easy. Certainly thanks to MySQL Shell and server enhancements like SET PERSIST and RESTART statement (see this post).

The most complicated part to deal with was the existing and none existing data. In fact GTID sets must be compatible.

Let me explain that with some examples:

Example 1 – empty servers

If you have empty servers with GTID enabled, manually creating credentials to connect to each MySQL instances will generate GTIDs that will prevent nodes to join the cluster. Before 8.0.17 if you were doing this, you had to explicitly avoid to write the users in binary log.

Example 2 – servers with data but purged binary logs

When you want to add a new servers to a cluster, before 8.0.17 when the new server (joiner) wants to join the cluster, this is a very high summary of what they say:

- joiner: hello I don't have any GTID - group: ok we are now at trx (gtid sequence) 1000, you will then need from 1 to 1000, let's see if somebody part of the group as those trx - member1: no, I don't, I've purged my inital binlogs, I've from 500 - member2: no, I don't, the first binlog I've starts with gtid 600 - joiner: damn ! I can't join then, bye !

And this is the same when you prepare a new member with a backup, you need to be sure that next gtid since the backup was made is still available at least in one of the members.

My colleague Ivan blogged about that too.

Clone Plugin

Since MySQL 8.0.17, all this is not necessary anymore, the clone plugin can handle the provisioning automatically. If we take the second example above, instead of saying bye , the joiner will copy all the data (clone) from another member directly, without calling an external shell script or program, but directly in the server using the clone plugin !

10 minutes to install MySQL and setup an InnoDB Cluster

Let’s see this in action:

Since MySQL InnoDB Cluster is out, we received a lot of very good feedback, but the main feature request was always the same: automatic provisioning ! This is now part of the reality ! Wooohoooo \o/

And of course it’s all integrated directly into MySQL.

Categories: Web Technologies

MySQL Connector/J 8.0.17 has been released

Planet MySQL - 4 hours 40 min ago

Dear MySQL users,

MySQL Connector/J 8.0.17 is the latest General Availability
release of the MySQL Connector/J 8.0 series.  It is suitable
for use with MySQL Server versions 8.0, 5.7, and 5.6.
It supports the Java Database Connectivity (JDBC) 4.2 API,
and implements the X DevAPI.

This release includes the following new features and changes, also
described in more detail on

https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-17.html

As always, we recommend that you check the “CHANGES” file in the
download archive to be aware of changes in behavior that might affect
your application.

To download MySQL Connector/J 8.0.17 GA, see the “Generally Available
(GA) Releases” tab at http://dev.mysql.com/downloads/connector/j/

Enjoy!

———————————————————————–

Changes in MySQL Connector/J 8.0.17 (2019-07-22, General
Availability)

Functionality Added or Changed

     * X DevAPI: The following methods have been deprecated:

          + Collection.find().where()

          + Collection.modify().where()

          + Collection.remove().where()

     * X DevAPI: Two new operators for JSON objects and arrays,
       overlaps and not_overlaps, are now supported. See the X
       DevAPI User Guide
       (https://dev.mysql.com/doc/x-devapi-userguide/en/) for
       details.

     * X DevAPI: Indexing for array fields is now supported. See
       Indexing Array Fields
       (https://dev.mysql.com/doc/x-devapi-userguide/en/collecti
       on-indexing.html#collection-indexing-array) in the X
       DevAPI User Guide
       (https://dev.mysql.com/doc/x-devapi-userguide/en/) for
       details.

     * The README and LICENSE files are now included inside the
       Connector/J JAR archive delivered in the
       platform-independent tarballs and zip files. (Bug
       #29591275)

     * A number of private parameters of ProfilerEvents (for
       example, hostname) had no getters for accessing them from
       outside of the class instance. Getter methods have now
       been added for all the parameters of the class. (Bug
       #20010454, Bug #74690)

     * A new connection property, databaseTerm, sets which of
       the two terms is used in an application to refer to a
       database. The property takes one of the two values
       CATALOG or SCHEMA and uses it to determine which
       Connection methods can be used to set/get the current
       database, which arguments can be used within the various
       DatabaseMetaData methods to filter results, and which
       fields in the ResultSet returned by DatabaseMetaData
       methods contain the database identification information.
       See the entry for databaseTerm in Configuration
       Properties
(https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html)
       for details. Also, the connection property
       nullCatalogMeansCurrent has been renamed to
       nullDatabaseMeansCurrent. The old name remains an alias
       for the connection property. Thanks to Harald Aamot for
       contributing to the patch.
       (Bug #11891000, Bug #27356869, Bug #89133)

     * A new CONTRIBUTING file has been added to the Connector/J
       repository on GitHub
       (https://github.com/mysql/mysql-connector-j), which
       provides guidelines for code contribution and bug
       reporting.

     * The MySQL Connector/J X DevAPI Reference can now be
       generated from the Connector/J source code as an Ant
       target, xdevapi-docs.

     * Added support for host names that are longer than 60
       characters (up to 255 characters), as they are now
       supported by MySQL Server 8.0.17.

     * Added support for the utf8mb4_0900_bin collation, which
       is now supported by MySQL Server 8.0.17.

     * A cached server-side prepared statement can no longer be
       effectively closed by calling Statement.close() twice. To
       close and de-cache the statement, do one of the
       following:

          + Close the connection (assuming the connection is
            tracking all open resources).

          + Use the implementation-specific method
            JdbcPreparedStatement.realClose().

          + Set the statement as non-poolable by calling the
            method Statement.setPoolable(false) before or after
            closing it.

Bugs Fixed

     * X DevAPI: The IN operator in X DevAPI expressions, when
       followed by a square bracket ([), got mapped onto the
       wrong operation in X Protocol. (Bug #29821029)

     * When using a replication connection, retrieving data from
       BlobFromLocator resulted in a ClassCastException. It was
       due to some wrong and unnecessary casting, which has been
       removed by this fix. (Bug #29807741, Bug #95210)

     * ResultSetMetaData.getTableName() returned null when no
       applicable results could be returned for a column.
       However, the JDBC documentation specified an empty string
       to be returned in that case. This fix makes the method
       behave as documented. The same correction has been made
       for getCatalogName() and getSchemaName(). (Bug #29452669,
       Bug #94585)

     * ResultSetImpl.getObject(), when autoboxing a value of a
       primitive type retrieved from a column, returned a
       non-null object when the retrieved value was null. (Bug
       #29446100, Bug #94533)

     * ResultSetImpl.getDouble() was very inefficient because it
       called FloatingPointBoundsEnforcer.createFromBigDecimal,
       which needlessly recreated BigDecimal objects for the
       fixed minimum and maximum bounds. With this fix, the
       objects BigDecimal.valueOf(min) and
       BigDecimal.valueOf(max) are cached after they are first
       created, thus avoiding their recreations. (Bug #29446059,
       Bug #94442)

     * Enabling logSlowQueries resulted in many unnecessary
       calls of LogUtils.findCallingClassAndMethod(). With this
       fix, LogUtils.findCallingClassAndMethod() is called only
       when profileSQL is true and even in that case, the number
       of calls are reduced to a minimal to avoid the excessive
       stack trace data the function used to generate. Thanks to
       Florian Agsteiner for contributing to the fix. (Bug
       #29277648, Bug #94101, Bug #17640628, Bug #70677)

     * Characters returned in a ResultSet were garbled when a
       server-side PreparedStatement was used, and the query
       involved concatenation of a number and a string with
       multi-byte characters. That was due to an issue with the
       number-to-string conversion involved, which has been
       corrected by this fix. (Bug #27453692)

     * Calling ProfilerEvent.pack() resulted in an
       ArrayIndexOutOfBoundsException. It was due to a
       mishandling of data types, which has been corrected by
       this fix. (Bug #11750577, Bug #41172)

Enjoy and thanks for the support!

On Behalf of MySQL/ORACLE RE Team
Sreedhar S

Categories: Web Technologies

MySQL InnoDB Cluster – Automatic Node Provisioning

Planet MySQL - 7 hours 3 min ago

The MySQL Development Team is very excited and proud of what was achieved in this 8.0.17 GA release!

The spotlight is on… A game-changer feature – Automatic Node provisioning!

This has been an extremely desired and important feature, and it has been accomplished, once again, with tight integration and cooperation of MySQL Components:

  • The new MySQL Clone Plugin: To take a physical snapshot of the database and transfer it over the network to provision a server, all integrated into the server, using regular MySQL connections.
Tweet Google Plus Share
Categories: Web Technologies

MySQL 8.0.17 Replication Enhancements

Planet MySQL - 7 hours 4 min ago

MySQL 8.0.17 is out. In addition to fixing a few bugs here and there, we also have a couple of new replication features that I would like to present. Thence, allow me to give you a quick summary. As usual, there shall be follow-up blog posts providing details, so stay tuned.…

Tweet Google Plus Share
Categories: Web Technologies

MySQL InnoDB Cluster – What’s new in Shell AdminAPI 8.0.17 release

Planet MySQL - 7 hours 5 min ago

The MySQL Development Team is very excited and proud to announce a new 8.0 Maintenance Release of InnoDB Cluster – 8.0.17!

In addition to important bug fixes and improvements, 8.0.17 brings a game-changer feature!

This blog post will cover MySQL Shell and the AdminAPI, for detailed information of what’s new in MySQL Router stay tuned for an upcoming blog post!…

Facebook Twitter Google+ LinkedIn
Categories: Web Technologies

MySQL InnoDB Cluster – What’s new in Shell AdminAPI 8.0.17 release

MySQL Server Blog - 7 hours 5 min ago

The MySQL Development Team is very excited and proud to announce a new 8.0 Maintenance Release of InnoDB Cluster – 8.0.17!

In addition to important bug fixes and improvements, 8.0.17 brings a game-changer feature!

This blog post will cover MySQL Shell and the AdminAPI, for detailed information of what’s new in MySQL Router stay tuned for an upcoming blog post!…

Facebook Twitter Google+ LinkedIn
Categories: Web Technologies

MySQL Shell 8.0.17 – What’s New?

Planet MySQL - 7 hours 6 min ago

The MySQL Development team is proud to announce a new version of the MySQL Shell in which the following new features can be highlighted:

  • MySQL Shell Plugins
  • Parallel table import
  • In InnoDB Cluster:
    • Automatic instance provisioning through cloning
    • Automatic server version compatibility handling
    • Simplification of internal recovery accounts

The following enhancements were also introduced:

  • On the X DevAPI area:
    • Support for array indexes in collections
    • Support for overlaps operator in expressions
  • Uniform SQL execution API in classic and X protocol
  • Support for connection attributes
  • New utility functions:
    • shell.unparseUri(…)
    • shell.dumpRows(…)
  • Support for –verbose output
  • Upgrade Checker: Addition of checks for variables on the configuration files
MySQL Shell Plugins

The MySQL Shell now supports user extensions through MySQL Shell Plugins, which includes User Defined Reports (Introduced in 8.0.16) as well as the new Extension Objects introduced in 8.0.17.…

Facebook Twitter Google+ LinkedIn
Categories: Web Technologies

Pages

1 2 3 4 5 6 7 8 9 next › last »