For the third year in a row, we will take advantage of the mass presence of our MySQL Engineers during FOSDEM to organize the pre-FOSDEM MySQL Day.

The program of this 3rd edition is already on track, thank you to all the speakers who already confirmed their participation.

JSON has proven to be a very import data format with immense popularity. A good part of my time for the last two or so years has been dedicated to this area and I even wrote a book on the subject.  This is a comparison of the implementations of handling JSON data in MySQL and MariaDB. I had requests from the community and customers for this evaluation.


JSON Data Types Are Not All Equal
MySQL added a JSON data type in version 5.7 and it has proven to be very popular.  MariaDB has  JSON support  version 10.0.16 but is actually an alias to a longtext data type so that statement based replication from MySQL to MariaDB is possible.

MySQL stores  JSON documents are …

Tweet

First of all, thanks to everyone who attended my sessions at the recent Oracle OpenWorld and Code One in San Francisco. It was a great privilege to be allowed to make a number of presentations.

All of the workbooks and scripts from the hands-on labs (HOL) and the slides from the talks have been made available at OpenWorld’s and Code One’s session catalogues. You can download the files by using the OpenWorld catalog searching for my name or the session number (see below). Click on the download icon for each of the presentations you are interested in.

…

With new MySQL Shell 8.0.13 comes a new way to quickly load JSON data sets very quickly.  In a past blog and in several talks I have shown how to use the shell with the Python mode to pull in the data.  But now there is a much faster way to load JSON

Load JSON Quickly Start a copy of the new shell with mysqlsh. Connect to your favorite server \c dave@localhost and then create a new schema session.createSchema('bulk'). Then point you session to the schema just created with \use bulk.  Version 8.0.13 has a new utility function named importJson that does the work.  The first argument is the path to the data set (here the MongoDB restaurant collection) and the second allows you to designate the schema and collection where you wish to have the data stored.  In this example the data set was in the downloads directory of my laptop and I wanted to put it in the newly created 'bulk' schema …

Tweet

The MySQL Document Store became general available (GA) with MySQL 8. One of the nice features of the MySQL Document Store is the X DevAPI that allows you to query the data from a multitude of programming languages using the same API (but while retaining the conventions of the language). The programming languages with support for the X DevAPI includes JavaScript (Node.js), PHP, Java, DotNet, and C++.

I will be using MySQL Connector/Python 8.0.12 for the example in this blog. The example is executed on Microsoft Windows with Python 3.6 installed, but it has also been tested on Oracle Linux 7 with Python 2.7. I do assume that MySQL Connector/Python has been installed. If that is not the case, you can read how to do it in the …

JSON (JavaScript Object Notation) is a popular way for moving data between various systems, including databases. Starting with 5.7 MySQL implemented a native JSON data type and a set of JSON functions that allows you to perform operations on JSON values.

There was an interesting but hard to read post on StackOverflow about how 'insert select delete' data from a MySQL JSON data type column.  The first line of the writer's problem is a little confusing '
In order to record user mac_address and count mac_address to restrict user login's pc or notebook to control user available max for example (it's work)' but the examples reveled more about what was desired. The idea was to track MAC address used by various users and the author of the question was wondering how to up data a JSON Array of values with JSON_INSERT.  INSERT is for inserting and the better choice would be JSON_ARRAY_APPEND or JSON_ARRAY_INSERT.    But what caught my eye was the second question: Select sql command for json column ? could be …

The MySQL Document Store is a NoSQL JSON document store built upon well known MySQL database technology.  PHP runs about eight percent of the Internet.  So putting the two together is a big priority for me. So this blog post is about getting all this together on a Ubuntu 18.04 system.

Note that I will be teaching PHP and the X DevAPI at Oracle Code One and hopefully in some tutorials/workshops this year.  These session will feature the X DevAPI installed on Virtual Box images and I probably will not have time to cover these steps in detail but I will point to this as reference material.


PHP 7.2 PHP's performance has really skyrocketed with the seven series and the newer betas are looking very impressive.  But to use the new X Devapi you will need to get the shared object for it into your PHP server. 

The MySQL X DevAPI PECL Extension
…

As you know, one of the great new feature in MySQL 8.0 is the Document Store. Now with MySQL you can store your JSON documents in collections and manage them using CRUD operations. NoSQL is now part of MySQL ! Instead of a mix of MongoDB and MySQL, now you can eliminate MongoDB and consolidate with MySQL !

This is a historical meeting of NoSQL and SQL in the same database server!

To use MySQL 8.0 as Document Store, you need to have the X plugin installed (by default since 8.0.11). This plugin enables the X DevAPI that offers a modern programming interface. Clients that communicate with a …

This time we will look at the differences in updating records between MongoDB and the MySQL Document Store.  Syntactically they are pretty different.  I am still following the Getting Started With MongoDB article for example queries.

Updating Records
In Mongo we update thusly:
> db.restaurants.update(
... { "name" : "Juni" },
... {
...  $set: { "cuisine" : "American (new)" },
...  $currentDate: { "lastModified" : true }
... }
... )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>

The same update in the MySQL Document Store can be a lot different.  We could update using SQL or NoSQL.  I would like to update the document with the change to the cuisine and …