Wednesday, November 28, 2018

The Symfony Demo Application and MySQL 8

The Symfony Frame work is very popular with PHP developers and it has a very nice Demo Application to help novices learn the intricacies involved. But the demo does not use MySQL. So this blog entry is about re configuring the demo so it works with MySQL 8. And I am using Ubuntu 18.04 LTS to you may have to adjust the following commands to work with your operating system.

This is not difficult but there are some steps that are not exactly self evident that this blog will show you how to get the demo working.  


Preliminaries


The first thing to do is to make sure you have PHP 7.2 or better installed including the php7.2-intl (sudo apt-get install php7.2-intl) package as well as the PDO connector. I will admit I have been using PHP since it appeared and this is the first time I have had to install this package.


And you will want Composer to do the behind the scenes lifting for you and Doctrine to map the relations in the PHP code to the database.  Please see my previous blog on getting Doctrine to work with MySQL 8 (Big hint for the TL;DR crowd, set your .env to DATABASE_URL=mysql://account:password@localhost:3306/databasename ).


You will want to create an account on the MySQL server for use with this demo and then make sure it will have the proper rights to use the new schema.


CREATE USER 'demouser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'S3cr3t#'; 

and  

GRANT ALL on 'databasename'.* to 'demouser'@'localhost'; 


The Demo

Now we can start work on the demo itself. 

The first thing to do in a terminal window is type composer create-project symfony/symfony-demo.  Composer will get the demo code ready for you.  Now cd cymfony-demo.   


Change the .env file (you may have to copy the .env-dist to .env and edit it) as noted above DATABASE_URL=mysql://demouser:S3c3t#@localhost:3306/databasename 

Now it is time to use Doctrine to build create the database, the schemas, and load the data.

$ php bin/console doctrine:database:create
$ php bin/console doctrine:schema:create
$ php bin/console doctrine:fixtures:load


Finally enter php bin/console server:run to start the demo. You will get a notice about the URL to use to get to the demo via a web browser. Use that URL in your web browser to get to the actual demo and below you can see that URL is http://127.0.0.1:8000. 


Symfony Demo started
Running the Symfony Demo Application. Note that the 
application is listening on http:127.0.0.1:8000
Demo in Browser
The Symfony Demo to help you explore this popular PHP Freamwork

Monday, November 19, 2018

Updated: Doctrine and MySQL 8 - An Odd Connection Refused Error

I am currently working my way through the many PHP Frameworks to see how they get on with MySQL 8.  The Frameworks that can take advantage of the MySQL Improved Extension or mysqli can take advantage of the SHA256 Caching Authentication method. But those that are PDO based need to use the older MySQL Native Authentication method.

I wanted to check the PDO based frameworks and today I just happened to be wearing the very nice Symfony shirt I received as part of my presentation at Symfony USA.  So I started with a fresh install of Symfony.  All was going well until it came time to get it to work with MySQL 8 through Doctrine.

Doctrine


Symfony uses Doctrine as an ORM (Object Relational Mapper) and DBAL  (Database Abstraction Layer) as an intermediary to the database.  While I myself am not a big fan of ORMs Doctrine does manage version migration very nicely.  When I tried to tie the frame work and the database together I received a stern connection refused error.

So I double checked the database connection parameters, making sure that I could get to where I wanted using the old MySQL shell.  Yes, the account to be used is identified by the native passwords and I had spelled the account name correctly. Then I double checked for fat-fingering on my part on the .env file where the connection details are stored. Then I did some searching and found someone else had stumbled onto the answer.

What does not work:
DATABASE_URL=mysql://account:password@127.0.0.1:3306/databasename

What does work:
DATABASE_URL=mysql://account:password@localhost:3306/databasename

So a simple s/127.0.0.1/hostname/ got things going.  I double checked the /etc/hosts file to make sure that alias was there (it was).


From then on I was able to create a table with VARCHAR and JSON columns and go about my merry way.

Update: An Oracle MySQL Engineer who works with the PHP connectors informed me that libmysql and all derived clients interpret "localhost" to mean "don't use TCP/ip, but Unix domain socket". And there was a kind post on the Doctrine mailing list informing me that the problems was upstream from Doctrine. Thanks to all who responded to solve this mystery for me.





Tuesday, November 13, 2018

Pager for the New MySQL Shell

I love the new shell but the one thing I missed from the old shell was the ability to use a pager like more or less to throttle the output of the screen.  Low and behold the engineers have added paging in MySQL Shell 8.0.13!!

Turn Paging On


Things can scroll off the screen quickly and pagination programs keep the output to small chunks.  To pick the more program as you pagination program, simply enter \pager more or for less enter \pager less

See the first illustration



How to turn paging on and off with the MySQL Shell 8.0.13




Yeah!

A big thanks to the MySQL Engineers!!


Turn Paging Off

And sometimes you do not need pagination and simply typing \pager and return will clear the setting. See the first illustration.


And you can determine how many lines to be output before output stops. \pager more -6 stops every six lines of output.

Setting the output of more to six lines


Other Pagination Programs

You can declare your desire to use another program for pagination on the command line like so mysqlsh --pager=foo

Tip: if you set the pager to \pager less -S, it will page horizontally too, so you can view wide results even without \G

The Documentation



The manual page for the pager is ->Using a Pager





Friday, November 9, 2018

A Tale of Two JSON Implementations - MySQL and MariaDB

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 converted to an internal format that permits quick read access to document elements.  The binary format is structured to enable the server to look up sub-objects or nested values directly by key or array index without reading all values before or after them in the document.  From a practical standpoint the big thing most people notice is that the data is alphabetized.

MariaDB does no such optimization and their documentation states the claim that the performance is at least equivalent.

JSON Tables

The first comparison is 'how hard is it to put non-JSON data into a JSON column?'  The standard (see below under 'extra'), by the way, states that the data must be in the UTF8MB4 character set. And what is going on 'underneath the cover'?

The first step is to create tables to hold JSON data.


MySQL:

create table t1 (j json);
Query OK, 0 rows affected (0.1826 sec)

 MySQL  localhost:33060+ ssl  json  SQL > show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `j` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.0149 sec)

 MySQL  localhost:33060+ ssl  json  SQL > desc t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| j     | json | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.0028 sec)


MariaDB:

MariaDB [json]> create table t2 (j json);
Query OK, 0 rows affected (0.046 sec)

MariaDB [json]> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
 `j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [json]> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| j     | longtext | YES  | | NULL |      |
+-------+----------+------+-----+---------+-------+
1 row in set (0.001 sec)

MariaDB [json]>





Conclusion: Both use UTF8MB4 and the underlying tables are roughly equivalent.

Checking Constraints

Ensuring that only JSON gets into the JSON columns is essential. MySQL does this by default for the JSON data type but MariaDB does not.

MySQL:

insert into t1 values('junk');

ERROR: 3140: Invalid JSON text: "Invalid value." at position 0 in value for column 't1.j'.

MariaDB:



MariaDB [json]> insert into t2 values ('junk');

Opps! We now have a NON-JSON value in the table.

To be fair, MariaDB does let you establish a constraint check on the column to avoid this issue.


MariaDB [json]> drop table t2;
Query OK, 0 rows affected (0.046 sec)
MariaDB [json]> create table t2 (j json, check (json_valid(j)));
Query OK, 0 rows affected (0.049 sec)


MariaDB [json]> insert into t2 values ('junk');
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `json`.`t2`

Conclusion: MySQL does as advertised by rejecting non-JSON data by default while MariaDB can do so with a little extra work.

Loading Data


The only major complaint about JSON data is that there is just so much of it. So having a way to bulk load is important.

MySQL:

MySQL's new shell (mysqlsh) has Python, JavaScipt, and SQL modes.  It is very easy to use either the Python or JavaScript modes to write a quick script to read bulk data sets line by line.  Giuseppe 'Datacharmer' Maxia has a great example of converting data from MongoDB to MySQL using the shell and I have used that example code extensively in the past. But now 8.0.13 has a bulk loader built into the shell.
The New MySQL Shell's utility to bulk load JSON

This utility functions allows JSON data to be stores in a JSON Document Collection (MySQL Document Store) or in a JSON column of s structured table. Very handy.


MariaDB:

Maria does not have an equivalent to the new MySQL Shell nor does it have a bulk loader utility for JSON data. 

I tired to use the Connect Storage Engine (not installed by default) and follow the examples on the CONNECT JSON Table Type page without luck. The Connect engine is supposed to have the capability to auto discover a table structure and define the table itself.  Mea Cupla for not getting this to work and I would encourage those who do use this feature to send me pointers PLEASE! But after several hours and tedious attempts to follow the examples exactly it was time to move on to something else.

Conclusion: MySQL does better loading data, especially in bulk.

JSON Functions


Both databases have functions and there are some differences.


  1. Functions That Create JSON Values
    1. JSON_ARRAY, JSON_OBJECT and JSON_QUOTE  are found in both and work the same.
  2. Functions That Search JSON Values
    1. JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_EXTRACT, JSON_KEYS, and JSON_SEARCH are found in both and work the same.  However only MySQL has the -> and ->>  shortcuts for JSON_EXTRACT and JSON_UNQUOTE(JSON_EXTRACT)).. 
  3. Functions That Modify JSON Values
    1. JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_INSERT, JSON_REMOVE, JSON_REPLACE, JSON_SET, and JSON_UNQUOTE are in both and work the same.  
    2. MariaDB has JSON_MERGE which has been deprecated in MYSQL 8.0.3 and replaced with JSON_MERGE_PRESERVE & JSON_MERGE_PATCH.   MySQL 8.0 supports the JSON Merge Patch format defined in RFC 7396 function.
  4. Functions That Return JSON Value Attributes
    1. JSON_DEPTH, JSON_LENGTH, JSON_TYPE, and JSON_VALID are found in both and work the same.
  5. Table Functions
    1. JSON_TABLE which allows you to use unstructured JSON data in a structured temporary table is in MySQL only.
  6. JSON Utility Functions
    1. JSON_PRETTY, JSON_STORAGE_FREE, and JSON_STORAGE_SIZE are only in MySQL.
  7. Other Functions
    1. JSON_ARRAYAGG and JSON_OBJECTAGG are only in MySQL and are very handy for turning non JSON data into JSON.
    2. JSON_VALUE and JSON_QUERY are MariaDB specific and return an object/array or a scalar respectively.

Conclusion:  I tested both database's functions and found they worked as advertised.  JSON_PRETTY is much missed by my eyes when dealing with documents with several levels of embedding with MariaDB.  The merging functions are richer for MySQL especially for those who need to follow the merge patch standard. And JSON_TABLE is a valuable tool when trying to treat unstructured data as structured data temporarily especially combined with CTEs and Windowing Functions that were introduced in MySQL 8.   

Updating Data


Updating data can be expensive and JSON data can be expansive.

MySQL:

MySQL 5.7 required a complete re-write of the document.  If this is something you do a lot then you need to consider upgrading to MySQL 8.

MySQL 8.0's  the optimizer can perform a partial, in-place update of a JSON column instead of removing the old document and writing the new document in its entirety to the column. 
Replication. But there are conditions to this: 1) It has to be a JSON column, 2) The UPDATE statement uses any of the three functions JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() to update the column but a direct set like UPDATE mytable SET jcol = '{"a": 10, "b": 25'}) does not work, 3) The input column and the target column must be the same column, 4) All changes replace existing array or object values with new ones, and do not add any new elements to the parent object or array, and 5) The value being replaced must be at least as large as the replacement value. In other words, the new value cannot be any larger than the old one (An exception to this requirement occurs when a previous partial update has left sufficient space for the larger value. You can use the function JSON_STORAGE_FREE() see how much space has been freed by any partial update). If you can follow those rules the optimizer will do partial rewrites.

MariaDB:

I searched the MariaDB docs and Jira but found no mention of partial JSON column updates.  If anyone has links, please send them to me. So it appears that MariaDB does a full rewrite.

Conclusion:  MySQL is more efficient here.

Replication 

Efficient replication is a must and goes double for JSON with the potential of very large document payloads having to cross network connections.

MySQL:

In MySQL 5.7 an update to a JSON column was written to the binary log as the complete document. In MySQL 8.0, it is possible to log partial updates to JSON documents.   In statement based replication JSON partial updates are always logged as partial updates. 

But in row based replication they are logged as complete documents. To enable the logging of partial updates, set binlog_row_value_options=PARTIAL_JSON. Please note that the replication master has this variable set, partial updates received from that master are handled and applied by a replication slave regardless of the slave's own setting for the variable.


MariaDB:

I searched the MariaDB docs and Jira but found no mention of partial JSON Replication updates.  If anyone has links, please send them to me. So expect the full document to be replicated.

Conclusion: MySQL can be more efficient.

X DevAPI / Document Store

Only MySQL has the Document Store and the underlying Document Store and it is proving to be very popular with customers.  The ability to use a database as a NoSQL Document Store and a relational database is popular.  Not having embedded strings in their code and having the IDEs help is driving developers to this approach. 

MariaDB's CTO said at Zendcon that they will support if customers demand.

Conclusion:  MySQL is the sole choice here.


Overall Conclusion

I have been using MySQL's JSON data type since the 5.7 DMRs and know them well.  MariaDB's implementation seems very familiar and works as expected.  MySQL is superior in the partial updates of data and replication, functions such as JSON_TABLE, the X DevAPI, and bulk loading of data. 

Extra


The IETF's standard is here and makes pretty quick reading.

Tuesday, November 6, 2018

Common Table Expressions: A Shocking Difference Between MySQL and MariaDB

Common Table Expressions (CTEs) are a very useful tool and frankly a big improvement on sub-queries.  But there are differences in how they are implemented in MySQL and MariaDB.  That  is not too surprising since the code fork many years ago. Different engineers implementing the same idea will have different approaches (and sometimes results). But differences in implementation are often important and, in this case, shockingly different.

Jesper Wisborg Krogh at Oracle OpenWorld and CodeOne gave a series of presentations and hands on labs that were excellent. He is an amazing Support Engineer and a great presenter of material at conferences.  In the lab for Common Table Expressions he did point out to me an interesting problem in MariaDB's implementation of CTEs. 

The Problem In a Nutshell


On the PostgreSQL Wiki, there is a
an SQL query (requires PostgreSQL 8.4 or MySQL 8.0) that produces an ASCII-art image of the Mandelbrot set written entirely in SQL 2008 conforming SQL.

-- Based on: https://wiki.postgresql.org/wiki/Mandelbrot_set

WITH RECURSIVE x(i) AS (
    SELECT CAST(0 AS DECIMAL(13, 10))
     UNION ALL
    SELECT i + 1
      FROM x
     WHERE i < 101
),
Z(Ix, Iy, Cx, Cy, X, Y, I) AS (
    SELECT Ix, Iy, X, Y, X, Y, 0
      FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X,
                  i AS Ix FROM x) AS xgen
           CROSS JOIN (
               SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y,
                      i AS iY FROM x
           ) AS ygen
    UNION ALL
    SELECT Ix, Iy, Cx, Cy,
           CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X,
           CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1
      FROM Z
     WHERE X * X + Y * Y < 16.0
           AND I < 27
),
Zt (Ix, Iy, I) AS (
    SELECT Ix, Iy, MAX(I) AS I
      FROM Z
     GROUP BY Iy, Ix
     ORDER BY Iy, Ix
)
SELECT GROUP_CONCAT(
           SUBSTRING(
               ' .,,,-----++++%%%%@@@@#### ',
               GREATEST(I, 1),
               1
           ) ORDER BY Ix SEPARATOR ''
       ) AS 'Mandelbrot Set'
  FROM Zt
 GROUP BY Iy
 ORDER BY Iy;


The code is best run on the new MySQL Shell or MySQL Workbench but works well on the old MySQL shell but with desegregated output.

An abbreviated image of  the Mandelbot SQL output (See above for listing) , truncated for size. Produced with the new MySQL Shell (mysqlsh) on MySQL 8.0.13 

But then Jesper mention he had tested the SQL the night before the lab and it runs quickly on MySQL - 0.7445 seconds on my Windows laptop.
The Mandelbrot SQL code ran in 0.74445 seconds on MySQL 8.0.13



But not on MariaDB.  Jesper said he ran the same code on MariaDB 10.3 but killed it after fifteen minutes.  It was late and he had to get up early to get to San Francisco.

Double Check


With a fresh install of Fedora 29 and MariaDB 10.3.10, I ran the Mandelbrot SQL code.  And I waited for the result.  After an hour I went to lunch. But the query was still running when I returned.  I went on to other work an occasionally checking back and running SHOW PROCESSLIST  from time to time to make sure it had not died. 

But after two hours I hit control-C as I had other tasks for that system.  There are some interesting Recursive CTE problems listed on Jira,MariaDB.org but nothing obviously relevant.

But I was able to confirm that MySQL's implementation of Recursive CTEs works well but I can not say that about MariaDB's implementation. 

Saturday, October 27, 2018

Quickly Load JSON Data into The MySQL Document Store with util.importJson

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 in a collection named 'restaurants'

An Example of using util.importJson to quickly load JSON data into the MySQL Document Store
It took just over 2 seconds to read in over 25,000 records, not bad.

And a quick check of the data shows that is loaded successfully!



Friday, October 12, 2018

MySQL 2018 Community Reception

The 2018 MySQL Community Reception is October 23rd in a new venue at Samovar Tea, 730 Howard Street in San Francisco at 7:00 PM.   Right in the heart of the Moscone Center activities for Oracle OpenWorld and Oracle Code one activities.

The MySQL Community Reception is not part of Oracle OpenWorld or Oracle Code One (you do not need a badge for either event) but you do need to RSVP.  Food, drinks, and a really amazing group of attendees!   And there will be more than tea to drink.

Plus we have a few new surprises this year! 

Wednesday, October 10, 2018

Two More MySQL Books for 2018

Last time I mentioned four great MySQL books for 2018.  I was tactfully reminded of two books I overlooked. First is Dr. Charles Bell's Introducing InnoDB Cluster which I have not read (but it is on order).
Introducing InnoDB Cluster
And last, but not least, is Mikael Ronstrum's MySQL Cluster 7.5 Inside and Out.  This is another book on NDB cluster and is a 'msut have' for those running NDB clusters.

MySQL Cluster 7.5 Inside and Out
I apologize to both authors and take full blame for not mentioning these two find books.  Now I just have to wait for Amazon to send me the copies I ordered!


Tuesday, October 9, 2018

MySQL Books - 2018 has been a very good year

Someone once told me you can tell how healthy a software project is by the number of new books each year.  For the past few years the MySQL community has been blessed with one or two books each year. Part of that was the major shift with MySQL 8 changes but part of it was that the vast majority of the changes were fairly minor and did not need detailed explanations. But this year we have been blessed with four new books.  Four very good books on new facets of MySQL.

Introducing the MySQL 8 Document Store is the latest book from Dr. Charles Bell on MySQL.  If you have read any other of Dr. Chuck's book you know they are well written with lots of examples.  This is more than a simple introduction with many intermediate and advanced concepts covered in detail.

Introducing the MySQL Document Store -- Dr. Charles Bell
Introducing the MySQL 8 Document Store
MySQL & JSON - A Practical Programming Guide by yours truly is a guide for developers who want to get the most of the JSON data type introduced in MySQL 5.7 and improved in MySQL 8.  While I love MySQL's documentation, I wanted to provide detailed examples on how to use the various functions and features of the JSON data type. 

MySQL and JSON A Practical Programming Guide

Jesper Wisborg Krogh is a busy man at work and somehow found the time to author and co-author two books.  The newest is MySQL Connector/Python Revealed: SQL and NoSQL Data Storage Using MySQL for Python Programmers which I have only just received.  If you are a Python Programmer (or want to be) then you need to order your copy today.  A few chapters in and I am already finding it a great, informative read.
MySQL Connector/Python Revealed

Jesper and Mikiya Okuno produced a definitive guide to the MySQL NDB cluster with Pro MySQL NDB Cluster.  NDB cluster is often confusing and just different enough from 'regular' MySQL to make you want to have a clear, concise guidebook by your side.  And this is that book.

Pro MySQL NDB Cluster

Recommendation

Each of these books have their own primary MySQL niche (Docstore, JSON, Python & Docstore, and NDB Cluster) but also have deeper breath in that they cover material you either will not find in the documentation or have to distill that information for yourself.  They not only provide valuable tools to learn their primary facets of technology but also provide double service as a reference guide. 



Monday, October 1, 2018

Prepared Statements for MySQL: PDO, MySQLi, and X DevAPI

Recently I ran across a prominent PHP Developer who incorrectly claimed that only PDO allows binding values to variables for prepared statements.  A lot of developer use prepared statements to reduce the potential of SQL Injection and it is a good first step.  But there are some features that you do no kno

What is a Prepared Statement?


The MySQL Manual states The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

So far, so good. Well there is also a performance issue to consider too.  From the same source The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.

So it is a two step process.  Set up the query as a template and then plug in the value. If you need to reuse the query, just plug in a new value into the template.

So lets look at how it is done.

PDO


On PHP.NET, there are a lot of really great examples. Question marks (?) are used as placeholders that will be filled in at execution time. 

$sth $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?'
);$sth->execute(array(150'red'));$red $sth->fetchAll();$sth->execute(array(175'yellow'));$yellow $sth->fetchAll();

So that is the basics. But what do they look like with the other two extensions?


MySQLi


So what does the MySQLi version look like? Once again question marks are used as placeholders.

$stmt $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");

$stmt->bind_param('sssd'$code$language$official$percent);$code 'DEU';$language 'Bavarian';$official "F";$percent 11.2;/* execute prepared statement */$stmt->execute();printf("%d Row inserted.\n"$stmt->affected_rows);

But what is that sssd stuff?  That is where you declare the type of variable you are want to use.  Use 's' for string, 'i' for integer, 'd' for double, and 'b' for a blob (binary large object).  So you get the advantage of type checking.

X DevAPI

The much newer X DevAPI is for the new X Protocol and the MySQL Document Store.  Unlike the other two examples it is not Structured Query Language (SQL) based.


$res $coll->modify('name like :name')->arrayInsert('job[0]''Calciatore')->bind(['name' => 'ENTITY'])->execute();

$res $table->delete()->orderby('age desc')->where('age < 20 and age > 12 and name != :name')->bind(['name' => 'Tierney'])->limit(2)->execute();

Note that this is not an object relational mapper as it is the protocol itself and not something mapping the object to the SQL.

Wrap Up

So now you know how to use prepared statements with all three PHP MySQL Extensions.


Thursday, September 27, 2018

MySQL Track at Southern California Linux Expo 2019 CFP Open

The Call For Papers  for the next Southern California Linux Expo aka SCaLE is open and I need your help with the MySQL Track.  We have had a MySQL track for the past few years and this year I have gotten permission from the organizers of SCaLE to get a group of MySQL community members to review the talks for this track.  This year was pretty good but to make 2019 even better we need more submissions from more people AND THIS MEANS YOU!!

The link above has the details on how to register to submit a talk submission and the process is fairly simple.  But if you would like help with your submission, want to 'rubber duck' ideas, or want a quick review before you submission please contact me  (@stoker, david.stokes @ Oracle.com) or find me at a show.

So what type of talks do we need?  We need to cover material for newbies, intermediate, and the experienced.  There are lots of developers, Devops folks, hobbyists, and students just waiting for you to share your knowledge with them. For a large show, we have a fairly intimate talk space which is friendly to first time presenters.

Some ideas:

1. So how do you do backups and restores?  What sort of things get deleted and what steps do you take to keep your data safe? Policies? Procedures?  How does a developer who fudged a row/table get their data back in your organization?

2. How do you reduce SQL injects, the N+1 problem and 0(N) searches in your code?

3. How is your Kubernetes/Docker/Ansible environment for your developers managed?

4. Five things about being an 'accidental' MySQL DBA I wish I knew when I took over the databases

5. MySQL Replication best practices.

6. Data normalization how-to

7. Query optimization for novices. Query optimization for the advanced SQL coder.

And anything else  you think that at least one other person would like to hear about (and there are more - believe me).

So please make SCaLE 17x even better! You have until the end of October to submit.

Thursday, September 6, 2018

JSON Paths and the MySQL JSON Functions

I wrote MySQL and JSON: A Practical Programming Guide to help developers find their way around the MySQL JSON data type and the supporting functions. The MySQL Documentation on the subject is very good but I had to puzzle through the examples to see how things worked.  I might be a bit 'thick' but good examples always make things easier.  Others seem to have similar difficulties.
MySQL and JSON a Practical Programming Guide should be on your desk as a handy reference to MySQL's JSON data type.

 There was a recent post on Stackoverflow.com where someone had this JSON document:

{  
   "textures":[  
      {  
         "label":"test",
         "types":{  
            "t_1":0,
            "t_2":0
         }
      },
      {  
         "label":"KEK",
         "types":{  
            "t_1":0,
            "t_2":0
         }
      }
   ],
   "weapons":[  
      {  
         "name":"WW_SHT",
         "ammo":0
      },
      {  
         "name":"WW_DSS",
         "ammo":0
      }
   ]
}

And they wanted want to update t_1 to change value from 0 to 1.  I will not repost their code but to my eyes it looked convoluted. 

So How Do You Get There From Here?

Trying to figure out how to get down to a key or value is easy.  To see the top level keys simply use JSON_KEYS()

SELECT JSON_KEYS(doc) FROM zz1 LIMIT 1;

'[\"weapons\", \"textures\"]'

But how to get deeper??

By using select doc->>"$.textures[*]" from zz1 limit 1; we get all the info under the textures key.

[{"label": "test", "types": {"t_1": 0, "t_2": 0}}, {"label": "KEK", "types": {"t_1": 0, "t_2": 0}}]

Okay so we are getting closer to the target!    Now to take one more step closer with select doc->"$.textures[*].types" from zz1 limit 1;

[{"t_1": 0, "t_2": 0}, {"t_1": 0, "t_2": 0}]

I like to use JSON_PRETTY to get a enhanced view:

select json_pretty(doc>"$.textures[*].types")  
from zz1 limit 1;
 [
  {
    "t_1": 0,
    "t_2": 0
  },
  {
    "t_1": 0,
    "t_2": 0
  }


But there are two t_1s!

The next step is to get just those t_1 values and that is done with select 
doc->"$.textures[*].types.t_1" from zz1;

Which gives us:

 [0, 0]

Not really confidence inspiring ,eh? So lets change one of those zeros to a nine.

update zz1 set doc = json_set(doc,"$.textures[0].types.t_1",9);
 
So did we change the first or the second t_1??

select json_pretty(doc>"$.textures[*].types")  
from zz1 ;
 [
  {
    "t_1": 9,
    "t_2": 0
  },
  {
    "t_1": 0,
    "t_2": 0
  }

But lets double check and change the second t_1 also. 

update zz1 set doc = json_set(doc,"$.textures[1].types.t_1",7) ;

Hopefully that second one will end up with a value of seven.

select 
json_pretty(doc->"$.textures[*].types") from zz1;
 [
  {
    "t_1": 9,
    "t_2": 0
  },
  {
    "t_1": 7,
    "t_2": 0
  }

So now we can get to the exact values we want.

Annotated JSON Document

So lets look at the section of the JSON document and annotate in red the paths in the document.

"textures":[  
      {  -- textures[0]
         "label":"test",  
         "types":{  -- textures[0].types
            "t_1":0,--textures[0].types.t_1
            "t_2":0
         }
      },
      {  -- textures[1]
         "label":"KEK",
         "types":{  
            "t_1":0,--textures[1].types.t_1
            "t_2":0
         }
      }
   ]
Hopefully this will ease someone's confusion down the line.  And please do buy my book.