Meet MySQL Support at Oracle OpenWorld and Code One 2018

Oracle MySQL Support will this year again take part in the Oracle OpenWorld conference in San Francisco. Additionally, we will present at the developer focused Code One that is held at the same time. Oracle OpenWorld and Code One 2018 takes place at the Moscone Center and nearby hotels in San Francisco on Monday 22 October to Thursday 25 October.

Oracle OpenWorld and Code One 2018

MySQL Support will be represented by Lig Isler-turmelle and myself (Jesper Wisborg Krogh), and we will organize three hands-on labs (HOL) in Oracle OpenWorld, two talks in Code One, and two mini-briefings at the Support Stars Bar. The hands-on lab and Code One talks are:

TimeSpeaker(s)SessionTitle
Monday
22 October
12:15 pm - 13:15 pm
Lig Isler-turmelle
Jesper Wisborg Krogh
HOL1703A Practical Introduction to the MySQL Document Store
Tuesday
23 October
11:15 am - 12:15pm
Lig Isler-turmelle
Jesper Wisborg Krogh
HOL1706Developing Modern Applications with the MySQL Document Store and NodeJS
Wednesday
24 October
09:00 am - 09:45 am
Jesper Wisborg KroghDEV5957Develop Python Application with MySQL Connector/Python
Wednesday
24 October
02:30 pm - 03:15 am
Jesper Wisborg KroghDEV5959Python and the MySQL Document Store
Thursday
25 October
09:00 am - 10:00 am
Lig Isler-turmelle
Jesper Wisborg Krogh
HOL2986Using MySQL Common Table Expressions and Window Functions

The HOL sessions are in Salon 9A at the Mariott Marquis on the Yearba Buena Level and the DEV sessions are the Code One talks and will be in Room 2014 at Moscone West.

Additionally, we will be present at the Support Stars Bar. This is an area full of people from Oracle Support. Throughout the four days there will be 15-minute mini-briefings. Attendees to the mini-briefings can enter a drawing with a chance to win a prize. MySQL Support will have two mini-briefings (the schedule is still to be finalized). The Support Stars Bar will be located at level 3 in Moscone West. You can read more about the Support Stars Bar at the Oracle Support homepage.

There are of course many more talks both by Oracle developers, product management, as well as MySQL users. Registration is open, and I will encourage you to register and come and meet the people behind MySQL and hear about the new features and how MySQL is used. You can read more about MySQL’s general presence in LeFred’s blog and see a list of all the MySQL sessions at both OpenWorld and Code One in the Code One session catalogue.

Register for Oracle OpenWorld 2018

NoSQL/X DevAPI Tutorial with MySQL Connector/Python 8.0

The MySQL Document Store became general available (GA) with MySQL 8. One of the nice features of the MySQL Document Store is the X DevAPI that allows you to query the data from a multitude of programming languages using the same API (but while retaining the conventions of the language). The programming languages with support for the X DevAPI includes JavaScript (Node.js), PHP, Java, DotNet, and C++.

I will be using MySQL Connector/Python 8.0.12 for the example in this blog. The example is executed on Microsoft Windows with Python 3.6 installed, but it has also been tested on Oracle Linux 7 with Python 2.7. I do assume that MySQL Connector/Python has been installed. If that is not the case, you can read how to do it in the Installing Connector/Python from a Binary Distribution section in the manual or Chapter 1 of MySQL Connector/Python Revealed from Apress.

The output of the example program
The output of the example program

The example will go through the following steps:

  • Getting Ready:
    1. Load the mysqlx module.
    2. Create a database connection.
  • Setup:
    1. Create a schema.
    2. Create a collection.
  • CRUD – Create:
    1. Insert some documents into the collection.
  • CRUD – Read:
    1. Query the documents.
  • Cleanup:
    1. Drop the schema.
You can download the complete example program here: Example Code for Tutorial

The program uses the pyuser@localhost user. The connection parameters can be changed as described in the “Getting Ready” section. A user that fulfills the requirement to the example program can be created using the following SQL statements:

Warning: This program is not an example of using best practices. Do not store the password and preferably also the other connection options in the source code. There is also very limited handling or errors and warnings in order to keep the example simple. You should not skip those steps in a production program.

Getting Ready

The first thing is to get ready by importing MySQL Connector/Python’s mysqlx module and connect to MySQL. This is simple to do as shown in the below code snippet (the line numbers refer to the full example):

The mysqlx module in imported in line 38. This is where the MySQL Connector/Python implementation of the X DevAPI resides. The module includes support for CRUD statements both for documents and SQL tables, schema and collection manipulations, as well as executing SQL statements. In this example, only the CRUD implementation for documents and the schema and collection manipulation will be used.

The warning_levels variable is uses to convert numeric warning levels returns by the X DevAPI to names. There is an example of how to handle warnings after the first document has been added.

Finally, the connection is created in line 52 using the get_session() method in the mysqlx module.  With a connection object in place, let’s move on to set up the schema and collection.

Setup

The X DevAPI has support for creating and dropping schemas and collections (but currently not SQL tables). This is used in the example to set up the my_collections schema with a single collection called my_docs:

The create_schema() method on the database (session) object is used to create the schema. It will succeed even if the schema already exists. In that case the existing schema will be returned.

The collection is similarly created with the create_collection() method from the schema object. This will by default fail if the collection already exists. It can be overwritten with the reuse argument (the second argument).

That is it. A collection is always defined internally in the same way when it is created. You can add indexes – I hope to get back to that in a future blog – but there is no need to think of columns and data types at this point. That is the advantage of using a schemaless database (but also one of the dangers – now the whole responsibility of staying consistent is up to you as a developer).

Let’s continue to add some documents.

CRUD – Create

For this example, three documents will be added to the my_docs collection. The documents contain information about three persons including their name, birthday, and hobbies. The documents can be defined as Python dictionaries with JSON arrays represented as Python lists:

This is the beauty of working with JSON documents in Python. They just work.

The birthdays are written in the ISO 8601 format (the same as MySQL’s date data type uses – but not datetime!). As the MySQL Document Store is schemaless, you are free to chose whatever format you feel like, however, it is strongly recommended to use a standard format. This YYYY-mm-dd format has the advantage that it will sort correctly, so alone for that reason, it is a strong candidate.

The documents will be inserted in two rounds. First Adam will be added, then Kate and Jane.

Adding a Single Document

There are a few ways to add documents (all working in the same basic way). This example will show two of them. First let’s look at how Adam is added:

The document is added inside a transaction. The X DevAPI connection inherits the value of autocommit from the server-side (defaults to ON), so to be sure the create action can be tested for warnings before committing, an explicit transaction is used. (Errors cause an exception, so since that is not handled here, it would cause an automatic rollback.)

The document is added using a chained statement. When you build an X DevAPI statement, you can choose between calling the method one by one or chaining them together as it is done in this case. Or you can choose a combination with some parts chained and some not. When the documents for Kate and Jane are added, it will be done without chaining.

The statement is submitted to the database using the execute() method. If you are used to executing Python statements in MySQL Shell, you may not be familiar with execute() as MySQL Shell allows you to skip it for interactive statements where the result is not assigned to a variable. The result is stored in the result variable which will be used to examine whether any warnings were triggered by the statement.

Tip: In MySQL Connector/Python, you must always call execute() to execute an X DevAPI statement.

It is best practice to verify whether queries cause any warnings. A warning will still allow the statement to execute, but it is in general a sign that not everything is as it should be. So, take warnings seriously. The earlier you include tests for warnings, the easier it is to handle them.

In line 99, the get_warnings_count() method of the result object is used to check if any warnings occurred. If so, the number of warnings is printed and each warning if retrieved using the get_warnings() method. A warning is a dictionary with three elements:

  • level: 1 for note and 2 for warning. This is what the warning_levels variable was created for at the start of the example.
  • code: The MySQL error number. The mysqlx.errorcode module contains string symbols for all the error numbers. This can be useful in order to check whether it is an expected error number that can be ignored.
  • msg: A string message describing the problem.

In this case, if any warnings occur, the transaction is rolled back, and the script exists.

Tip: Include handling of warnings from the beginning of coding your program. Handling warnings from the get go makes it much easier to handle them. They are usually a sign of something not working as expected and it is important that you know exactly why the warnings occur. All warnings include an error code that you can check against to verify whether it is an expected warning. If some warning is expected and you are confident, it is acceptable to ignore it.

If no error occurs, some information from the result is printed. An example output looks like (the ID will be different):

As expected one document has been added. The number of documents is printed using the get_affected_items_count() method. More interesting is the document ID. As the document did not include an element named _id, MySQL added one automatically and assigned a value to it. I will not go into how the ID is generated here, but just note that it includes three parts that together ensure global uniqueness even if you use multiple clients against multiple MySQL Server instances. At the same time, the IDs are still being generated in a way that is optimal for the InnoDB storage engine that is used for the underlying storage of the documents. The IDs are returned as a list; in this case there is only one element in the list, but if more than one document is inserted without an _id value, then there will be one generated ID per document.

The final step is to commit the transaction, so the document is persisted in the collection.

Adding Multiple Documents

When you want to add multiple documents using a single CRUD statement, you can essentially do it in two ways. You can add all of the documents in one go in the initial add() call similar to what was done for a single document with Adam. This can for example be done by having the documents in a tuple or list.

The other way, which will be used here, is to repeatably call add() to add the documents. Let’s see how that works:

To keep the example from getting too long, the check for warnings have been removed, and the example will just focus on adding the documents.

After the transaction has been started, the statement object is created by calling add() on the collection object. In this case, no arguments are given, so at that point in time, the statement will not insert any documents.

Then the two documents are added one by one by calling add() on the statement object, first with the kate document, then with the jane document. An advantage of this approach is that if you for example generate the documents inside a loop, then you can add them as they are ready.

When both documents have been added, the execute() method is called to submit the documents to the database and the transaction is committed. Again, some information from the result is printed (the IDs will be different):

So, two documents are inserted (again as expected) and two IDs are generated.

The way that the add statement was used to insert the two documents is an example of the opposite of chaining. Here, one action at a time is performed and the result is stored in the stmt_add variable.

Now that there are some documents to work with, it is time to query them.

CRUD – Read

When you want to query documents in a collation, you use the find() method of the collection object. The resulting find statement support all of the usual refinements such as filtering, sorting, grouping, etc. In this example, three queries will be executed. The first will find the total number of documents in the collection. The second, will find the persons born on 9 August 1982. The third will find the persons who has hiking as a hobby.

Total Number of Documents

The X DevAPI makes it easy to determine the number of documents in the document – the count() method of the collection will return the value as an integer. In practice the count() method goes through the same steps as you will see in the two subsequent queries, but they are hidden inside the implementation. The code snippet is:

It cannot get much easier than that. The output is:

Let’s move on and see some of the steps that were hidden in the first query.

Finding Documents Based on Simple Comparison

The persons (in this case just one person) born on 9 August 1982 can be found by creating a find statement and adding a simple filter. The example code is:

The filter clause is added in the call to find(). The syntax :birthday means that a parameter is used and the value will be added later. That has two advantages: it makes it easier to reuse the statement, and importantly it makes the statement safer as MySQL will ensure the value is escaped correctly – this is similar to the mysql_real_escape_string() function in the MySQL C API. The value of the parameter is given using the bind() method that has two arguments: the parameter name and value. If you use multiple parameters, call bind() once for each of them.

Otherwise the statement is simple to use. The filtering condition may seem too simple given it is a JSON document it applies to. However, Birthday in the condition is interpreted as $.Birthday (the $. part is optional) – that is the object named Birthday and is a child of the root of the document, which is just what is needed in this case. The next example includes a more complicated filter condition.

The fields to include are specified in a similar manner to the filter condition. You specify the path to the element you want to include. You can optionally rename the element using the AS keyword, for example: Surname AS Last_name. As for the condition, the $. part is optional.

The resulting row is retrieved using the fetch_one() method on the result object. This is fine here as we know there is only one resulting row. However, in a more general case you should use fetch_one() is a loop and continue until it returns None at which point all rows have been fetched.

The output is:

Querying with Condition on Element in Array

A more complicated find statement is to look into the Hobbies array and see if any of the elements is Hiking. This query also matches two of the persons in the collection, so a loop is required to handle them. The code is:

There are two main differences between this example and the previous: the filter condition and how the result documents are handled.

The filter uses the JSON_CONTAINS() function to check whether the $.Hobbies elements contains the value specified by the :hobby parameter. In the call to bind(), the parameter value is set to "Hiking". Note that Hiking must be quoted with double quotes as it is a JSON string. In this case, $. is included in the document path. However, it is still optional.

After executing the query, the resulting documents are fetched using the fetch_all() method. This will return all of the documents as a list. This makes it simpler to loop over the resulting rows, however be aware that for large result sets, it can cause a high memory usage on the application server.

Warning: Be careful with the fetch_all() method if the query can return a large result set. It will require the remaining part of the result to be stored in-memory on the application-side.

One advantage of the fetch_all() method is that it will allow you to get the total number of documents in the result using the count property of the result. The count property will show 0 until fetch_all() have completed. Once the documents have been fetched, it is possible to print the names of the persons who like to hike. The output is:

Other than a bit of cleanup, there is nothing more to do.

Cleanup

The final part of the example is to clean up. The my_collections schema is dropped so the database is left in the same state as at the start, and the connection is closed:

Dropping a schema is done in the same way as creating it, just that the drop_schema() method is used instead. The drop_schema() method will also work if the schema does not exist. In that case it is a null-operation.

It is important always to close the database connection. Have you ever seen the MySQL Server error log full of notes about aborted connections? If you do not explicitly close the database connection when you are done with it, one of those notes will be generated (provided the server is configured with error_log_verbosity = 3).

Additionally, not closing the connection will keep the connection alive until the program terminates. That is not a problem here, but in other cases, it may take a long time before the application shuts down. In the meantime, the connection count is higher than it needs to be, and if you happen to have an ongoing transaction (can very easily happen with autocommit = OFF), the connection may cause lock issues or slowness for the other connections.

Tip: Always close the database connection when you are done with it.

Want to Learn More?

I hope this has triggered your curiosity and you are ready to dive deeper into the world of MySQL Connector/Python, the X DevAPI, and the MySQL Document Store. If so, there are two recently released books that you may find useful.

Disclaimer: I am the author of one of these books.

One book is MySQL Connector/Python Revealed (Apress) written by me. It goes through MySQL Connector/Python both for the legacy PEP249 API (mainly the mysql.connector module) and the new X DevAPI (the mysqlx module). There are three chapters dedicated to the X DevAPI.

The other book is Introducing the MySQL 8 Document Store (Apress) written by Dr. Charles Bell (MySQL developer). This book goes through how JSON works in MySQL including information about the X DevAPI and its siblings the X Protocol and the X Plugin.

Both books are more than 500 pages and comes with code examples that will help bring you up to speed with MySQL Connector/Python and the MySQL Document Store.

Awesome MySQL Shell Prompt

A month ago, I wrote a blog on how you can configure the MySQL Shell prompt to suit your needs.  One thing I did not go into details with are the prompt templates prompt_256pl.json and prompt_256pl+aw.json. Common for both of these templates is that they require custom fonts to work.

In the two file names, pl stands for the PowerLine fonts and aw for the Awesome font. These fonts add symbols that are useful to create rich prompts, not only for MySQL Shell, but also for Bash, zsh, vim, etc. The symbols include an angle separator, a lock (that in MySQL Shell is used to symbolize an TLS/SSL connection), and so on.

The MySQL Shell Using Both the Powerline Fonts and the Awesome Terminal Font
If you want a MySQL Shell prompt like this, then read on.

This blog will show you how to install the necessary fonts to get an awesome MySQL Shell prompt on Oracle Linux 7, Red Hat Enterprise Linux (RHEL) 7, and CentOS 7. For other Linux distributions, the steps are the same or similar.

Specifying the Prompt Template

Before diving into installing the necessary fonts, let’s recall from the previous blog, how you choose how the prompt should look. Two ways to specify the prompt are:

  • Set the MYSQLSH_PROMPT_THEME environment variable to the path to the file with the required template. For the prompt_256pl+aw.json template using the el7 MySQL Shell RPM, the full path is /usr/share/mysqlsh/prompt/prompt_256pl+aw.json. This method is useful while testing.
  • Copy the prompt_256pl+aw.json file to ~/.mysqlsh/prompt.json. This way MySQL Shell will automatically pick up the new prompt definition. This is very useful when you have decided on your preferred prompt.

Awesome Terminal Fonts

If you want to use the prompt_256pl+aw.json template, then you will need to install the awesome font. One option is to use the patching-strategy branch of gabrielelana’s awesome-terminal-fonts project on GitHub. The steps to install the required font for the user are:

This installs just the font required for MySQL Shell. You can however choose to install all of the fonts if you like.

It is necessary to restart the desktop environment for the fonts to be available. The resulting prompt (when logged in a default schema has been set) looks like:

The MySQL Prompt with the Awesome Terminal Font Installed
The MySQL Prompt with the Awesome Terminal Font Installed

Personally I am not entirely keen on how the separator looks, so I install another font to replace the separator.

Powerline Font

The Powerline fonts serve a similar purpose as the awesome font, but they are missing some of the symbols used by the prompt_256pl+aw.json template. It does however include the separator used by the template, and I personally prefer it over the Awesome Terminal Font separator. If you choose the prompt_256pl.json template, you can just install the Powerline Fonts and skip the Awesome Terminal Font.

The installation instructions in the manual are quite good. However, they assume you want to use the fonts for more than just MySQL Shell. For MySQL Shell the following steps are enough:

After restarting the desktop environment, the prompt looks like this:

The MySQL Shell Using Both the Powerline Fonts and the Awesome Terminal Font
The MySQL Shell Using Both the Powerline Fonts and the Awesome Terminal Font

Fantasque Awesome Powerline

A third option is the Fantasque Awesome Powerline fonts which as the name suggest include all the fonts needed for the prompt_256pl+aw.json template.

Thanks to Lefred for this suggestion.

The steps to install the Fantasque Awesome Powerline fonts are familiar by now. The fonts can be downloaded using the ztomer’s GitHub repository and installed using the fc-cache command:

Then restart and the font is ready. The resulting prompt is very similar to the previous and is arguable simpler to install as it is just one source:

MySQL Shell Prompt with the Fantasque Powerline Awesome Fonts
MySQL Shell Prompt with the Fantasque Powerline Awesome Fonts

You can of course also choose to play with the other symbols in the Powerline and Awesome Terminal fonts and make your own custom MySQL Shell prompt configuration file. Whichever is your preferred prompt, have fun playing with it.