MySQL 8.0.17 was released Monday and it includes great features such as the Clone feature and multi-valued indexes. There are also several nice contributions from the community. These are the changes that this blog is about.
…
MySQL Support Blogs
Support Blogs:
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.
MySQL Connector/Python 8 made the C Extension the default for the
platform/Python version combinations supporting it. One thing
that was missing from the C Extension implementation (unless you
used the _mysql_connector module) was support for
prepared statements. That has been taken care of with the release
of version 8.0.17.
The two main advantages of using prepared statements are security and performance. The security comes in as you can pass query parameters and have them applied server-side, so you are sure they are quoted and escaped correctly taking the data type into consideration. The performance benefit happens, when you execute the same query (except for the parameters) several times as MySQL will prepare it …
The sys schema was created to make it easier to use
the Performance Schema. This included several functions, for
example to convert the picoseconds used by the Performance Schema
into human readable strings. In MySQL 8.0.16, three of these
functions have been implemented as native functions in MySQL
Server.
…
MySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth.
In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let’s take a look at how the attributes are exposed in MySQL Server.
…
MySQL 8.0.16 was released last week and includes many great
features including support for CHECK constraints and
upgrades without the need of mysql_upgrade. As usual
there are also several contributions from the community. These
are the ones, I would like to highlight in this blog to say
“thank you for the contributions”.
…
In my blog yesterday, I wrote about the new
reporting framework in MySQL Shell. It is part of the 8.0.16
release. I also noted that it includes the possibility to create
your own custom reports and use those with the \show
and \watch commands. This blog will explore how you
can create a report and register it, so it automatically is
available when you start MySQL Shell.
…
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:
- A new system variable,
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
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.