JSON and MySQL 5.7

October 8, 2015

In the past few months I have been inundated with questions about the new JSON data type. The man page for JSON is fascinating reading. So grab your favorite beverage, the JSON manual page, and take a comfy seat for reading. There are many highlights but be sure to check out some of these highlights.

  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error and this is a big advantage over putting JSON data in a string column.
  • JSON columns can not have a DEFAULT value, can not be indexed (virtual columns for secondary indexes are okay)
  • MySQL handles strings used in a JSON columnusing the utf8mb4 character set and utf8mb4_bin collation.

One thing to note is that strings are normalized. If you duplicate a key the new value is discarded.
SELECT JSON_OBJECT('first', 1, 'second', 2, 'first', 3, 'third', 4);
Will return
'{\"first\": 1, \"third\": 4, \"second\": 2}'

The second ‘first’ goes away. So not only does your JSON need to be well formed but you can not reuse keys in a sting.

If you use JSON_MERGE the values for that key are merged into an array.
SELECT JSON_MERGE('{"first": 1}','{"first": 2}')
Here First-1 and First-2 are merged into a single array.
'{\"first\": [1, 2]}'

A little more complex example for illustration.
SELECT JSON_MERGE('{"first": 1}','{"second": 3}', '{"first": 2}')
will produce
'{\"first\": [1, 2], \"second\": 3}'

And how big ban a JSON doc be? IT can not be stored longer than the max_allowed_packet system variable setting but it can be longer in memory for manipulation. And the protocol limit for max_allowed_packet is one gigabyte. So docs longer than 1GB will need to be saved in 1GB pieces.

Webinar: The Proper Care and Feeding Of MySQL Databases for Busy Linux Administrators with David Stokes

September 15, 2015

Wednesday, September 16, 2015 12 PM EST / 4 PM GMT

Are you a Linux Admin who ‘also has’ MySQL DBA responsibilities but no DBA training? Do you wonder why databases are the problem children of all the software services you are responsible for? Then this webinar is for you. You’ll learn all about:

  • Why databases do not play well with others
  • How (and how often) you should be doing backups
  • How MySQL use authentication is ‘too permissive’
  • Which configuration hints keep the server happy
  • Other best practices to keep your MySQL instances happy and shiny for years to come

Join us for this exciting live webinar on what you should know about the proper care and feeding of MySQL databases, with Dave Stokes, MySQL Community Manager at Oracle, and Janis Griffin, Database Performance Evangelist at SolarWinds. Register here!

Indexing MySQL JSON Data

June 9, 2015

“MySQL’s JSON data type is great! But how do you index the JSON data?” I was recently presenting at the CakePHP Cakefest Conference and was asked that very question. And I had to admit I had not been able to play, er, experiment with the JSON datatype to that level. Now I have and it is fairly easy.

1. Create a simple table
mysql> desc colors;
| Field | Type | Null | Key | Default | Extra |
| popular_name | char(10) | YES | | NULL | |
| hue | json | YES | | NULL | |
2 rows in set (0.00 sec)

2. Add in some data
INSERT INTO `colors` VALUES ('red','{\"value\": \"f00\"}'),('green','{\"value\": \"0f0\"}'),('blue','{\"value\": \"00f\"}'),('cyan','{\"value\": \"0ff\"}'),('magenta','{\"value\": \"f0f\"}'),('yellow','{\"value\": \"ff0\"}'),('black','{\"value\": \"000\"}');

3. SELECT some data
Use the jsb_extract function to efficiently search for the row desired.
mysql> select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
| jsn_extract(hue, '$.value') |
| "f0f" |
1 row in set (0.00 sec)

But how efficient is that? Turns out we end up doing a full table scan.

mysql> explain select jsn_extract(hue, '$.value') from colors where jsn_extract(hue, '$.value')="f0f";
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | colors | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |

4 Add a VIRTUAL column to index quickly
mysql> ALTER TABLE colors ADD value_ext char(10) GENERATED ALWAYS AS (jsn_extract(hue, '$.value')) VIRTUAL;
This will add a virtual column from the value data in the hue column.

5 Index the New Column
mysql> CRATE INDEX value_ext_index ON colors(value_ext);

Now the EXPLAIN shows us that we are more efficient.
mysql> explain select jsn_extract(hue, '$.value') from colors where value_ext="f0f";
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | colors | NULL | ref | value_ext_index | value_ext_index | 11 | const | 1 | 100.00 | NULL |
1 row in set, 1 warning (0.00 sec)


Color Your MySQL Console

January 13, 2015

I started programming in the era of punch cards and learned to love monochrome screens quickly. The standard MySQL client console is a echo of those days. But we can get color.

One of the really interesting parts of my job is looking at the new submissions to Planet.MySQL.Com and seeing what interesting things people are doing with MySQL. Today I found Nicola Strappzaaon’s Swapbytes.com in the approval queue and his top article at the time was Give a little color to the MySQL Console. Actually the tile read Darle un poco de color a la consola de MySQL but Google translate overcame my insufficient skills.

Basically you install grcat ‘universal coulouriser’, install a configuration file, and tweak the .my.cnf file to use grcat as the pager. Nicola’s blog goes into more detail but here is the gist.

apt-get install grc
https://raw.githubusercontent.com/nicola51980/myterm/master/bash/dotfiles/grcat wget -O ~ / .grcat

And the .my.cnf file

pager = grcat ~/.grcat | less -RSFXin

Colorized MySQL Console

Color Added MySQL Console is quick and easy to do do.

I had change the .grcat configuration file as there was a complaint about a bad color ‘whit’ but replacing the first ‘white’ in the file with red solved that quickly. So change white to red under the section #data in ( ) and ‘ ‘.

So hats off to Nicola Strappzaaon and all the other new bloggers on Planet! And I encourage everyone to check out the different language blogs as you never know what gems you will find there.

MySQL Marinate for the Holiday Season

October 27, 2014

Just a friendly reminder that you can pick up MySQL Marinate whenever you want! You can use the master list at http://www.meetup.com/Virtual-Tech-Self-Study/messages/boards/thread/38423162 for reference – just ignore the dates and work at your own pace!

We found that very few people were taking the dates to heart, so we stopped trying to organize around them. The message boards are still valid, so feel free to ask if you have any questions – we are here to help!

The above was a quick note from Sheeri Cabral about the wonderful MySQL Marinate program that arrived in my email. This is a great way for novices to learn MySQL and for old war horses to find some new insights.

Does MySQL need a mentoring program?

August 29, 2014

Does MySQL need a mentoring program? I get calls, emails, and other requests for trained MySQL DBAs and Developers. Human Resources, managers, team leads, and entrepreneurs have the need but can not find the bodies. It is easy enough to download MySQL, get it running, and use it. But the next steps seem problematic for many. There are programs like MySQL Marinate and Girl Develop It to provide some hands on help for beginners. Autodidacts can find tons of MySQL Books and on line information. But how do we take the beginners and get them to intermediate or beyond?

How do we support these new comers, give them a hand if needed, a shoulder to cry on, or just provide someone who has been there before to bounce ideas around when needed? How do we pull them into social networks to warn them of pitfalls, pass on information about new technologies, or just be there as a friendly voice when the air movement device is being impacted by non optimal material? How do we pass on best practices, professional guidance, and the norms of our community? There is only so much forums, IRC, and Stack Overflow can handle. Local users groups are good if you have a local user group.

A good place to start is to see what other Open Source projects are doing. PHP Mentorting is a formal, personal, long term, peer to peer mentorship organization focused on creating networks of skilled developers from all walks of life. Read their info and let me know if you think the MySQL Community needs something similar.

Being a mentor has benefits too. There is an old saying that you really do not know a subject until you can pass on your knowledge to someone else. It also helps bring along someone who could replace you if you decided to climb the corporate ladder. Plus you never know what you fledgling might teach you.

So do we need a MySQL mentoring program?

Changes in MySQL 5.6.20

August 5, 2014

The MySQL Release Notes should be part of any DBA’s regular reading list. The Changes in MySQL 5.6.20 came out last week and there are some interesting goodies.

  • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel.
  • A new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error.
  • The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL

5.6.20 has a slew of bug fixes, functionality changes, and notes.

So why should you be reading the changes on a regular basis? There isa goldmine of information in them. For instance, if you use blobs, consider this:

Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.

As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in “Row size too large” errors. No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data. (Bug #16963396, Bug #19030353, Bug #69477)

That is golden information for those of us who used a lot of blobs and great info for configuring servers.


Get every new post delivered to your Inbox.

Join 834 other followers