Tweet

The X DevAPI allows you to work with JSON documents and SQL tables at the same time. Furthermore, the CRUD style API is more intuitive than SQL statements for some programmers. Either way, the X DevAPI allows you to mix JSON documents, SQL tables, CRUD methods, and SQL statements to give you the best of all worlds. In MySQL Connector/Python, the X DevAPI is implemented in the mysqlx module.

This blog will look at how MySQL Connector/Python handles expressions, and how you in version 8.0.14 and later need to use the mysqlx.expr() method to explicitly define expressions.

Information

…

The latest release of the MySQL Shell 8.0.14 (GA) improved the JSON import utility to support the conversion of more BSON data types from the strict mode representation of MongoDB Extended JSON. This removes a previous limitation regarding the import of more complex MongoDB data types to MySQL, making it more reliable.…

Facebook Twitter Google+ LinkedIn

The next two weeks are a big change for me compared to the last two weeks.  The last two weeks I was in Belgium and now I am in Miami before heading to the Dominican Republic.  The one thing in common is that I am speaking on the MySQL Document Store.

SunshinePHP has become a favorite conference for many PHP developers for many reasons and this morning I will be running a three hour tutorial on using the PECL MySQL X DevAPI and the MySQL Document Store with PHP and then have a one hour talk later in the show. Plus I am joined by the amazing Kathy Forte who will speak on Driving Innovation With MySQL 8.0 and Docker.

Pycaribbean is a new show hoping to foster a growing developer community into the area.  And i will be showing how to use the MySQL Document Store with Python.All opinions …

… or what I should keep in mind in case of disaster

To retrieve and maintain in SQL format the definition of all tables in a database, is a best practice that we all should adopt. To have that under version control is also another best practice to keep in mind.

While doing that may seem redundant, it can become a life saver in several situations. From the need to review what has historically changed in a table, to knowing who changed what and why… to when you need to recover your data and have your beloved MySQL instance not start…

But let’s be honest, only a few do the right thing, and even fewer keep that information up to date. Given that’s the case, what can we do when we have the need to discover/recover the table structure?

From the beginning, MySQL has used some external files to describe its internal structure.

For instance, if I have a schema named windmills and a table …

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 …

Very often, database performance is affected by the inability to cache all the required data in memory. Disk IO, even when using the fastest devices, takes much more time than a memory access. With MySQL/InnoDB, the main memory cache is the InnoDB buffer pool. There are many strategies we can try to fit as much data as possible in the buffer pool, and one of them is data compression.

With regular MySQL, to compress InnoDB data you can either use “Barracuda page compression” or “transparent page compression with punch holes”. The use of the ZFS filesystem is another possibility, but it is external to MySQL and doesn’t help with caching. All these solutions are transparent, but often they also have performance and management implications. If you are using Percona Server for MySQL, you have yet another option, “column …

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 …

The latest release of the MySQL Shell 8.0.13 (GA) introduced some interesting improvements and features, for more information see the full changelog here: https://dev.mysql.com/doc/relnotes/mysql-shell/8.0/en/mysql-shell-news-8-0-13.html. One of those features was the introduction of a convenient and easy way to import JSON documents to a MySQL Server database.…