This is an aggregated feed of various blogs of Oracle support staff who work and support MySQL customers and users. There is an RSS feed to which you can subscribe.
Readers of my blog know that I like how MySQL Shell allows you to customize it and use it’s Python and JavaScript support to create custom libraries with tools that help with your daily tasks and even creating auto-refreshing reports. Lefred has even taken this a step further and started to port Innotop to MySQL Shell.
One disadvantage of …
MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.
Which convention that is the correct depends on your personal
preference and use case. Between case sensitivity and case
insensitivity, it basically boils down to whether
mydb, MyDB, and MYDB
should be the same identifier or three different ones. …
Today is World Backup Day, so I thought I would use the opportunity to discuss some best practices and general considerations regarding backing up MySQL instances. While I focus on MySQL, several of these tips apply to backups in general.
…
When you write stored procedures in MySQL, you sometimes need to
generate queries on the fly, for example as you process the
result of another query. This is supported using prepared statements. This blog explores how
you can take advantage of the sys schema to simplify
the use of dynamic queries.
Executing a query using the sys.execute_prepared_stmt() …
A great way to install MySQL when you need to do quick tests is to use a sandbox tool. This allows you to perform all the installation steps with a single command making the whole process very simple, and it allows for automation of the test. Giuseppe Maxia (also known as the Data Charmer, @datacharmer on Twitter) has for many years maintained sandbox tools for MySQL, first with MySQL Sandbox and now with dbdeployer.
One of the most recent features of dbdeployer is the support for MySQL NDB Cluster. In this blog, I will take this feature and test it. First, I will …
I few months ago, I wrote about using the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog, I will look at using SQLAlchemy with MySQL 8.
In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will go through the installations, then I will look at a code example.
…
MySQL changed the timestamp format in the log files in MySQL 5.7.
Since then, I have a few times seen questions about the new
format, and how to change the time zone that is used. Latest in a
comment to my blog about log_slow_extra in
8.0.14, where the question was what T and Z in the timestamp
(for example 2019-01-31T07:24:06.100447Z) means. In
this blog, I will discuss the timestamp format and show you how
you can change the time zone used.
…
The X DevAPI allows you to work with JSON documents and SQL
tables at the same time. Furthermore, the CRUD style API is more
intuitive than SQL statements for some programmers. Either way,
the X DevAPI allows you to mix JSON documents, SQL tables, CRUD
methods, and SQL statements to give you the best of all worlds.
In MySQL Connector/Python, the X DevAPI is implemented in the
mysqlx module.
This blog will look at how MySQL Connector/Python handles
expressions, and how you in version 8.0.14 and later need to use
the mysqlx.expr() method to explicitly
define expressions.
…
When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.
Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good …
MySQL Server has a feature where you can insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data, for example when restoring a backup or migrating data from one system to another including from another database product than MySQL. The mysqldump backup utility is an example of a program that supports exporting the data to delimited text files.
The statement to load the data is LOAD DATA INFILE. By default the file must
be server-side and MySQL Server will load it without …
The slow query log is the trusted old method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been overshadowed by the Performance Schema which has lower overhead and thus allows collecting statistics about all queries. The slow query log has one major advantage though: the data is persisted. In MySQL 8.0.14 which was recently released, there is an improvement for the slow query log: additional statistics about the recorded queries.
…
One of the new features in MySQL 8.0.14 is support for encrypting
the binary logs. While encryption makes the data more secure
(provided the key is secret of course), it can make life a bit
more difficult in terms of how easy it is to do tasks such as
point-in-time recoveries. This blog shows how you can use the
binlog_decrypt.py Python script to decrypt the
binary logs as long as you have the keyring that was used to
encrypt it.
Introduction and Background
…
MySQL 8.0.14 was released earlier in the week, and again we (Oracle) received several contributions that either made it into the release or at least inspired a feature or bug fix. This blog will go through the the changelog notes for these changes. Thank you for the contributions.
Two of the contributions are new features with patches submitted by Facebook, one by Daniel Black, and one of Facebook’s patches from 8.0.13 has updated:
log_slow_extra, if enabled,
causes the server to write additional fields to slow query log
lines that provide information about slow statements. In …
The MySQL X DevAPI is the new API that provides a uniform API across the supported programming languages. It has from the beginning supported that you can specify a default schema, when you connect. Originally it was not used for SQL statements. Starting with MySQL 8.0.14 the feature has been extended, so SQL statements take the default schema into consideration as well. This blog will explore how this works using MySQL Connector/Python. If you use a different programming language, the change will work in a similar way.
In order to explore the feature, a sample program is needed. A
simple program that prints the MySQL Connector/Python version,
queries the city
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 so. Examples of manipulating the tables …
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.
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.
…
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.
Somebody on Freenode wanted this:
Source Result +----+------+ +----+------+ | id | x | | id | c | +----+------+ +----+------+ | 1 | 1 | | 1 | 2 | | 2 | 1 | | 2 | 2 | | 3 | NULL | | 3 | NULL | | 4 | NULL | -> | 4 | NULL | | 5 | 1 | | 5 | 1 | | 6 | NULL | | 6 | NULL | | 7 | 1 | | 7 | 3 | | 9 | 1 | | 9 | 3 | | 10 | 1 | | 10 | 3 | +----+------+ +----+------+
The result uses the NULL values in x as boundaries of windows, and counts the number of rows within each window. I don't know why anyone wants such a thing; it is not ours to reason why...
Anyway, the point is that you can use arbitrary expressions, even subqueries, to define your window partitions.
SELECT
id,
-- Count of rows in windows bound by NULL values in x
IF(
x IS NULL,
NULL,
COUNT(*) OVER (PARTITION BY (
-- …
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 the database option in
settings.py to use MySQL Connector/Python and your
database settings, for example:
…
Content reproduced on this page is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.