MySQL 5.7.13 Hidden Gems

June 28, 2016

The Release Notes for MySQL 5.7.13 has some interesting notices about account length, JSON short cuts, and more.

A Rose By Any Other Name

Way back in 5.7.8 the account username was increased from sixteen to thirty two characters. Yeah! For many years this has been heavily requested. Now this has been incorporated in the definer fields of event and proc tables plus the grantor field of procs_priv and tables_priv tables. To do this these columns where bumped up from CHAR(77)) to CHAR(93) to hold the longer username@hostname data.

JSON Inline Path

An unquoting extraction operator ->>, called an inline path operator, is for use with JSON documents stored in MySQL. The new operator is similar to the -> operator which is a shotcut for JSON_EXTRACT. But it goes a step further in that it performs JSON unquoting of the value as well. For a JSON column mycol and JSON path expression mypath, the following three expressions are equivalent:


JSON_UNQUOTE( JSON_EXTRACT(jsoncolumn, "$.jsondata") )
JSON_UNQUOTE(jsoncol->"$.jsondata")
mycol->>"$.jsondata"

This new ->> operator can be used in SQL statements where JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.

Systemd and Multiple Severs

Those of you running Systemd equipped distros need to read Configuring Multiple MySQL Instances Using systemd if you desire to run multiple servers on a single host.

And there is more

The release notes for ’13 cover bugs fixed, audit log granularity, and more. Please take a few minutes to read this.


A Quick Review of Percona Live 2016

April 21, 2016

Percona Live 2016 was the most impressive show in a long line of shows that started with the old MySQL Users Conference. There were a lot of old familiar faces or familiar faces grown old. During my stint as MySQL Certification Manager I usually was at the other end of the conference center / hotel complex administering exams. But this year I would be able to attend sessions, network, and see the heart of the MySQL Community.

Bill Nye

Bill Nye

From the opening keynotes featuring TV personality Bill Nye to the final drawing where I won a Bill Nye autographed book, the show was filled with information on several data stores just not MySQL. This show was more about managing data with the proper tool and not just managing data with MySQL. All the session were recorded and will be posted — have patience as it takes time to get all 195 speakers’s material formatted.

MySQLEnginnering

Meet the MySQL Engineering Team Birds of a Feather Session

The highlight of the first day was having the available MySQL engineers reveal their plans and answer questions from an audience that was universally happy with the course Oracle has set the MySQL ship on for the next several years.

Tuesday started with Tomas Ulin reiterating the the release of MySQL 5.7 and then covering many of the new software features revealed last week like the ability to use MySQL as a document store. IMG_5352

2016 featured sessions on many facets of MySQL and a great many on MongoDB, Cassandra, RocksDB, and more. I am note if the show will encompass all the open source data stores but there was enough information on many of technologies to get any journeyman level DBA started on becoming a polyglot DBA. IMG_5376 And the new 5.7.12 document store features are going to be a big game changer as MySQL become more of a universal storage tool rather than just a relational database.

And a big salute to the show organizers and the entire Percona staff for showing their commitment and love to the open source world by providing such an awesome event.


Mysqlsh on Windows

April 20, 2016

Been seeing references to the new document store features of MySQL 5.7 and want to try it on Windows? The new features of MySQL require a download of the new mysqlsh msi or zip.

welcome

Welcome to MySQL Shell 1.0.3.5

Installation is a snap.

You can also determine where to keep your new mysql

You can also determine where to keep your new mysql

The installation msi quickly guides you through the choices and your best bet for now is to pick the defaults. If you have used the 5.7 msi server install before there are no surprises. But you are offered the opportunity to change the options for such things such as location of the binaries. Again the defaults worked well for me. In a very short time you can launch the new shell.

And then you are ready to use mysqlsh.

new shell

And then you have the new shell with the new protocol!


Webinar, Talks, and more in April

April 5, 2016

April is busy.

  • First I speak at LonestarPHP on Relational Theory for Budding Einsteins April 6th at 1PM.
  • Then: 5 MySQL Security Vulnerabilities and What to Do About Them
    Live Webinar: Thursday, April 7, 2016 at 10am ET/2pm GMT Regsiter Here
  • Collaborate is the BIG Oracle User Groups conference in Las Vegaand you can find me in the Oracle DemoPod Area or at my talk on MySQL Certification.
  • Percona Live Data Performance Conference
  • Linuxfest Northwest where I will be speaking on the MySQL JSON Data Type and The Proper Care and Feeding of a MySQL Database for Linux Admins.
  • And finally it is MySQL Innovation Day
  • And I was interviewed By Cloudways.


MySQL and JSON at Great Wide Open

March 15, 2016

I will be speaking in Atlanta this week at the Great Wide Open conference. This is a fantastic multi-track conference but I hope you bought your tickets as Thursday is sold out and Wednesday about to be.

I will be speaking on the new JSON Data Type in MySQL 5.7 and Percon’s Peter Zaitsev will be speaking on Running MySQL on Linux. Please drop by the MySQL booth for some great swag (MySQL baseball hats, Boogiebots, and more).


PHP and MySQL 5.7 JSON

February 11, 2016

I have been presenting on MySQL 5.7 and the new JSON data type to PHP Programmers at several shows and the one big request has been MORE EXAMPLES!!!!!!

Part of my presentation includes the big differences between PHP’s json_encode and the various MySQL supplied functions like JSON_ARRAY and JSON_OBJECT.  In my preliminary test I found the native PHP function did not always encode the information in a way that the MySQL server found palatable. The MySQL server wants properly valid JSON docs and unfortunately we have an impedance mismatch here.

Lets start with a simple associative array 
$alpha = array('a' => 1, 'b' => 2, 'c' => 3);  
and we want a JSON document that looks like 
{"a": 1, "b": 2, "c": 3}
 If we pass this array through json_encode (echo “json_encoded: ” . json_encode($alpha) . “\n”;)  we will end up with json_encoded: {“a”:1,”b”:2,”c”:3} which is not what is needed and the MySQL server will reject it with the ol’ ‘You have an error in your SQL syntax’ message.  Other php functions like serializeprint_r, and var_dump are pretty good with associative arrays but do not come close to providing the needed valid JSON.

THE JSON_HEX_QUOTE argument to json_encode changes the data to “a”, “1”, “b”, “2”, “c”, “3” }which is not any better. And it is a subtle change that seems, to me at least, to change keys and values to all keys, no values.

With no built in function, it is time to roll the array into a string formatted as desired.

$alphaz = "";
foreach ($alpha as $key => $value) {
                 $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphax,0,(strlen($alphaz) - 2)); 
/* Need to remove last ,\n */

This can be passed to the MySQL JSON_OBJECT function. Since it is valid JSON we can insert it into the database.

INSERT INTO foobar (mydata) VALUES (JSON_OBJECT('a', 1, 'b', 2, 'c', 3))

And in the database is the desired document.


mysql> select * from foobar;
+--------------------------+
| mydata |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

mysql>

I will write more on this subject. Meanwhile feel free to try my little test program.

#!/usr/bin/php
 1, 'b' => 2, 'c' => 3);
echo var_dump($alpha);
echo "json_encoded: " . json_encode($alpha) . "\n";
echo "json_encoded HEX: " . json_encode($alpha,JSON_HEX_QUOT) . "\n";

$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */
$query1 = "SELECT JSON_OBJECT($alphaz)";
echo "$query1\n";
if ($result = $mysqli->query($query1)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
        echo "FOO!!!!\n";
}
$query2 = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query2\n";
if ($result = $mysqli->query($query2)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}
$query3 = "SELECT * FROM foobar";
if ($result = $mysqli->query($query3)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}

$mysqli->close();
?>


Growing the MySQL Community

January 28, 2016

I will the first speaker at the FOSDEM MySQL and Friends Devroom and unlike the other speakers I will not be presenting on new technical developments, better ways to run MySQL, or anything technical. But it may be the most important talk of the day. How can we grow the MySQL Community?

How do we attract new developers and database administrators and then guide them through the steps to a ‘journeyman’ level before showing them the way to becoming seniors in their fields? There is a shortage of talent and this cuts two ways. First hiring managers get frustrated in their searches for talent and then they reduce the scope of what they want out of their database. Second the same hiring manager later will assume there is still no talent for them to hire and alter their demand accordingly. I want to bring in as many people as possible into the MySQL ecosystem because there are jobs out there in an ever increasingly tough economy and because I think MySQL solves so many problems efficiently. Nobody starts as a fully fledged developer or DBA and the start of the career path needs exposition, easing, and encouragement. So how do we grow our numbers?

There is a very similar community to the MySQL Community and that is found in the PHP world. In the last few years they have really pulled together to bring in newbies, tighten relationship between subgroups (efforts like the Framework Interop group), provide mentoring at all personal development levels, establishing active career coaching for experienced developers, and even mental health support. This has all been done by the PHP Community and they are growing, cohesive, and frankly I am envious.

A strong user community brings value to a product. I am seeking your input on how can get we more of ‘us’? What can we do to improve certification to make it a truce touchstone of a job candidates value? How can we best leverage groups like the International Oracle User Group’s MySQL Council and other sources of customer feedback to guide the development of the MySQL Products. What information are you seeking that you are not getting and what is the best way to get it to you? Are their better ways to train novices in subjects like data normalization, replication, account management, security or what have you that we have missed. What can we do to help you network with you peers? Do need a mentor or want to mentor (and yes you can be both at the same time).

So how do we grow the MySQL Community and help grow MySQL?