Home

MySQL 8: Drop Several Stored Events, Procedures, or Functions

Maybe the biggest new feature in MySQL 8 is the new transaction data dictionary that improves the consistency of schema objects among other things. To further protect the data in the data dictionary, the data dictionary tables are hidden and their content only exposed through the Information Schema. (One exception is when you use the debug binary, then it is possible to get direct access to the data dictionary tables. This is not recommended at all on production systems!) A side effect of the data dictionary tables being hidden is that those that have had a habit of manipulating the tables directly in MySQL 5.7 and earlier (I will not recommend doing that) will no longer be able to do

Read More »

MySQL X DevAPI Connection Pool with Connector/Python

If you have an application that need to use multiple connections to the MySQL database for short periods of times, it can be a good to use a connection pool to avoid creating a new connection and going through the whole authentication process every time a connection is needed. For the Python Database API (PEP249), MySQL Connector/Python has had support for connection pools for a long time. With the release of MySQL 8.0.13, the new X DevAPI also has support for connection pools. This blog will first cover the background of the X DevAPI connection pool feature in MySQL Connector/Python. Then provide an example. Background You create a connection pool using the mysqlx.get_client() function. You may wonder why you are

Read More »

Auto-Refreshing Reports in MySQL Shell

MySQL Shell makes it easy to develop tools you can use for example to generate reports. In a previous blog, I showed how to use external modules in MySQL Shell. In this blog, I will take it one step further and use the curses Python module to create auto-refreshing reports. The first example will be kept very simple to show the idea, then a more realistic example will be shown where the top N files sorted by I/O will be returned. Basic Example As a basic example, consider the query SELECT NOW(). This returns the date and time. Let’s query that every second for 10 seconds, then return to the prompt. The easiest is to look at the example and

Read More »

Tracking Foreign Keys

The other day, I was reading a blog by Magnus Hagander about tracking foreign keys throughout a schema in PostgreSQL. I thought it was a good idea, so I decided to look at how you can track foreign key in MySQL. The way I decided to do it was to start out with a table, then find all tables referencing the table by a foreign key. From this basic (and simple as it will be shown) query, it is possible to create a chain of relations. The key table for the queries is information_schema.KEY_COLUMN_USAGE which has information about all the foreign key relationships. However, before getting that far, it is necessary to have some tables to work with. Sample Schema

Read More »

Using Django with MySQL 8

A framework can be a great way to allow you to spend more time on the actual application or web site and less time on standard tasks. It can also greatly reduce the amount of custom code needed. Django is one of the best known web frameworks for Python, and the good news is that it works out of the box with MySQL Server 8 and MySQL Connector/Python 8. This blog will look at how to use Django with MySQL 8. There actually is very little to get Django to work with MySQL 8. Just install it, configure Django to use MySQL Connector/Python as a backend, and that’s it. From the Django point of view, you just have to configure

Read More »

Slides and Workbooks From Oracle OpenWorld & CodeOne

First of all, thanks to everyone who attended my sessions at the recent Oracle OpenWorld and Code One in San Francisco. It was a great privilege to be allowed to make a number of presentations. All of the workbooks and scripts from the hands-on labs (HOL) and the slides from the talks have been made available at OpenWorld’s and Code One’s session catalogues. You can download the files by using the OpenWorld catalog searching for my name or the session number (see below). Click on the download icon for each of the presentations you are interested in. For the hands-on labs the downloadable file includes the workbook as well as the scripts related to the exercises. The workbook contains the

Read More »

MySQL Shell 8.0.13 Prompt: Now with New Line Support

I have already blogged a couple of times about the MySQL Shell prompt. In the first blog, I wrote about how in general to configure it, and in the second blog, I showed how to install the necessary fonts to use the prompt with the Awesome and Powerline fonts. In this blog, I will show a new feature of MySQL Shell 8.0.13 which adds support to have a line break in the prompt and still get multi line statements align correctly. I will first discuss why you may want to use the new feature, then go through the new templates using this feature, and finally show my current favourite prompt. Why Use a New Line in the Prompt? You may

Read More »

MySQL Server 8.0.13: Thanks for the 10 Facebook and Community Contributions

MySQL 8.0.13 was released this week. There are several exciting changes including functional indexes and using general expressions as the default value for your columns. So, I will recommend you to get MySQL 8.0.13 installed and try out the new features. You can read about changed in the release notes section of the MySQL documentation and in Geir’s release blog. However, what I would like to focus on in this blog is the external contributions that has been included in this release. There are five patches contributed by Facebook as well as five contributions from other MySQL users. The patches contributed by Facebook are: The MySQL client library now returns better error messages for OpenSSL errors. (Bug #27855668, Bug #90418)

Read More »

MySQL 8: Performance Schema Digests Improvements

Since MySQL 5.6, the digest feature of the MySQL Performance Schema has provided a convenient and effective way to obtain statistics of queries based on their normalized form. The feature works so well that it has almost completely (from my experience) replaced the connector extensions and proxy for collecting query statistics for the Query Analyzer (Quan) in MySQL Enterprise Monitor (MEM). MySQL 8 adds further improvements to the digest feature in the Performance Schema including a sample query with statistics for each digest, percentile information, and a histogram summary. This blog will explore these new features. Let’s start out looking at the the good old summary by digest table. Query Sample The base table for digest summary information is the

Read More »

Replication Monitoring with the Performance Schema

The traditional way to monitor replication in MySQL is the SHOW SLAVE STATUS command. However as it will be shown, it has its limitations and in MySQL 5.7 and 8.0 the MySQL developers have started to implement the information as Performance Schema tables. This has several advantages including better monitoring of the replication delay in MySQL 8.0. This blog discusses why SHOW SLAVE STATUS should be replaced with the Performance Schema tables. The Setup The replication setup that will be used for the examples in this blog can be seen in the following figure. There are two source instances (replication masters). Source 1 replicates to the Relay instance (i.e. it acts both as a replica and source in the setup).

Read More »