Showing entries 1 to 10 of 117
10 Older Entries »
Displaying posts with tag: json (reset)
Partial (Optimised) JSON updates and Replication in MySQL 8.0

           MySQL is the most widely used  Relational Database Management System in the open source world. MySQL stepped into the NoSQL world by introducing the JSON Data Type in MySQL 5.7 release. In this blog post I am going to explain one of the major advantage of optimisation made in JSON Replication .

This was done from the MySQL 8.0.3 release.

What happened before 8.0.3 ?

Before MySQL 8.0.3, Whatever changes (Updates) made in JSON document, it will log as a full document in binary log & replicate the same into slave. The JSON data is stored as a blob internally. Below is an example of how it is logged as full document in binary log ?

Example –

Server version - 5.7.22-log MySQL Community Server (GPL)

My Binlog …
[Read more]
Array Ranges in MySQL JSON

Pretend you have a JSON array of data that looks roughly like the following.

mysql> insert into x(y) values('["a","b","c","d"]');
Query OK, 1 row affected (0.10 sec)


You could get all the values from that array using $[*]


mysql> select y->"$[*]" from x;
+----------------------+
| y->"$[*]" |
+----------------------+
| ["a", "b", "c", "d"] |
+----------------------+
1 row in set (0.00 sec)

Or the individual members of the array with an index that starts with zero.


mysql> select y->"$[0]" from x;
+-----------+
| y->"$[0]" |
+-----------+
| "a" |
+-----------+
1 row in set (0.00 sec)


But what about the times you want the last item in the array and really do not want to loop through all the items? How about using …

[Read more]
This Week in Data with Colin Charles 33: Reporting from FOSSASIA 2018 and Azure Announces Database Services for MySQL and PostgreSQL

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Writing to you on the ground from FOSSASIA 2018, where I gave a track introduction yesterday since we have a pretty awesome database track most Saturday, and generally, all MySQL focused on Sunday. There’s even a list of talks by Oracle MySQL’ers (yes, there’s more than just Oracle folk, but for that, you got to get the schedule).

The Percona Live Community Dinner happens again this year during Percona Live 2018, at Pedro’s on 24 April 2018. It starts at 7 pm, and I highly recommend you purchase the …

[Read more]
Fun With Bugs #62 - On Bugs Related to JSON Support in MySQL

Comparing to "online ALTER" or FULLTEXT indexes in InnoDB, I am not that much exposed to using JSON in MySQL. OK, there is EXPLAIN ... FORMAT=JSON statement that is quite useful, optimizer trace in MySQL is also in JSON format, but otherwise I rarely have to even read this kind of data since my lame attempt to pass one introductory MongoDB training couse back in 2015 (when I mostly enjoyed mongo shell). Even less I care about storing JSON in MySQL.

But it …

[Read more]
Visualize This! MySQL Tools That Explain Queries

In this blog post, I want to go over some of the day-to-day MySQL tools and methods DBAs use to analyze queries and visualize “what is going on?” I won’t be going into the nitty-gritty details of each of these tools, I just want to introduce you to them and show you what they look like so you will know what types of information they provide.

This isn’t a comprehensive list by any means but consider it a primer for those starting with MySQL and wanting to know what a query is going to do or is doing.

The two sides of query analysis are examining a query BEFORE you run it, and then analyzing what actually happened AFTER you run it.

Let’s start with the tools you can use to predict a query’s future.

In the beginning, there was EXPLAIN. The venerable EXPLAIN command has been with us a long time as a built-in MySQL utility statement. Its purpose is to explain that what the optimizer …

[Read more]
TOP 10 MySQL 8.0 features for developers

MySQL 8.0 RC2 has just been released with a lot of new features compared to MySQL 5.7. In this blog post, I will list the top 10 new features that should excite developers.

These functionalities are presented in descending order of preference of our MySQL Community.

TOP 10

  1. MySQL Document Store
  2. Default to utf8mb4
  3. JSON enhancements
  4. CTEs
  5. Window Functions
  6. Descending Indexes
  7. Better Optimizer Cost Model
  8. MySQL Server Components
  9. Improvement in GIS
  10. InnoDB NO WAIT & SKIP LOCKED

MySQL Document Store

This is the most expected and liked feature in MySQL 8.0 … and …

[Read more]
JSON_TABLE

JSON data is a wonderful way to store data without needing a schema but what about when you have to yank that data out of the database and apply some sort of formatting to that data?  Well, then you need JSON_TABLE.

JSON_TABLE takes free form JSON data and applies some formatting to it.  For this example we will use the world_x sample database's countryinfo table.  What is desired is the name of the country and the year of independence but only for the years after 1992.  Sound like a SQL query against JSON data, right? Well that is exactly what we are doing.

We tell the MySQL server that we are going to take the $.Name and $.IndepYear key's values from the JSON formatted doc column in  the table, format them into a string and a integer respectively, and alias the key value's name to a table column name that we can use for qualifiers in an SQL statement.

[Read more]
InnoDB Page Compression: the Good, the Bad and the Ugly

In this blog post, we’ll look at some of the facets of InnoDB page compression.

Somebody recently asked me about the best way to handle JSON data compression in MySQL. I took a quick look at InnoDB page compression and wanted to share my findings.

There is also some great material on this topic that was prepared and presented by Yura Sorokin at Percona Live Europe 2017: https://www.percona.com/live/e17/sessions/percona-xtradb-compressed-columns-with-dictionaries-an-alternative-to-innodb-table-compression. Yura also implemented …

[Read more]
Efficient JSON Replication in MySQL 8.0

MySQL is not only a relational database, but can also be used as a schemaless/NOSQL document store, or a mix of both. This is realized by the JSON datatype, and is useful for any data that is hard to fit in the ”tabular” format of a traditional table.…

Handy JSON to MySQL Loading Script

JSON in Flat File to MySQL DatabaseSo how do you load that JSON data file into MySQL. Recently I had this question presented to me and I thought I would share a handy script I use to do such work. For this example I will use the US Zip (postal) codes from JSONAR. Download and unzip the file. The data file is named zips.json and it can not be bread directly into MySQL using the SOURCE command. It needs to have the information wrapped in a more palatable fashion.

head zips.json 
{ "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA", "_id" : "01001" }
{ "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA", "_id" : "01002" }
{ "city" : "BARRE", "loc" : [ -72.10835400000001, 42.409698 ], "pop" : 4546, "state" : "MA", "_id" : "01005" }
{ "city" : "BELCHERTOWN", "loc" : [ -72.41095300000001, …
[Read more]
Showing entries 1 to 10 of 117
10 Older Entries »