MySQL Support Blogs

Time zone handling can sometimes generate confusion, especially when dealing with data migrations to different host running on a different time zone, or when switching to a Daylight Saving Time (DST) time zone or when leap seconds are introduced. Will the stored date still make sense after changing a system-wide configuration? What happens when you migrate a server to another host? What is actually stored in the database?

There’s plenty of literature around about MySQL and time zone management, but there’s also missing information, because new features and fixes are constantly introduced into MySQL Server in this area. So I thought that spending a few words here to summarize how to best deal with time zones, and keeping this information up to date to reflect the …

[Read More]

MySQL Server offers, among the different security features, encryption at rest (or Transparent Data Encryption, TDE) in the commercial release (find the differences with Community here).

While testing TDE with MySQL InnoDB Cluster, I was wondering what inner mechanism was implemented to deal with TDE, master keys and keyrings, and the clone plugin. If I need to clone an instance, how would everything work so to guarantee my InnoDB Cluster instances will still be encrypted, and the keyring chosen would still use a good master key to decrypt tablespace keys and finally tablespace pages?

The answer is obviously that this is …

[Read More]

While testing a XA transactions based workload, I noticed in the output of SHOW ENGINE INNODB STATUS;

---TRANSACTION 108674, ACTIVE (PREPARED) 35 sec
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

So far, so good. A XA transaction has been PREPARED, and reported as such. But after a while (and I was not really looking at the session), I repeated the command and the output turned to:

---TRANSACTION 108674, ACTIVE (PREPARED) 91 sec recovered trx
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

I went to check logs, because XA transactions are …

[Read More]

You only have to choose your favorite flavour to setup, administer, configure, backup or attach an analytics cluster to your MDS DB System, as Oracle maintains SDKs in several languages to integrate your software with Oracle Cloud Infrastructure.

And in particular today I’d like to test the SDK for Python, you can find an introduction here (but you have also SDKs for Java, Go, Ruby, Javascript, .NET). Read an introduction to SDKs.

First of all let’s create a virtual environment (venv) to work in a containerized environment.

…

[Read More]

Running a MySQL Database Service (MDS) DB System, the MySQL managed solution by Oracle MySQL on Oracle Cloud Infrastructure, means forgetting about time-consuming duties such as upgrades, backups and configuration, among others. It is so possible to focus on the service, on data and at the same time take advantage of the usual monitoring assets to configure and scale the service optimally. That’s what I want to talk about in this post: how to monitor a DB System in a few steps.

OCI MDS OS Metrics Charts

An MDS DB System compute instance file system is protected from any access, but you don’t need to access it to create system metrics, as these are already exposed on your DB System administration page in OCI dashboard.

…

[Read More]

If you started looking at running your MySQL database on the cloud and thought that taking your data to Oracle Cloud Infrastructure (OCI) is an option (and wondering about integrating to the new MySQL Acnalytics service), well that is probably the natural choice, because MySQL can integrate easily with OCI services like cloud backups in OCI Object Storage service and OCI keyring for transparent data encryption. I will share a few options with advantages of every solution, so to put the idea into practice.

• MySQL Database Service (MDS)
• MySQL on the OCI Marketplace
• MySQL on OCI do-it-yourself

MySQL Database Service (MDS)

The brand new MySQL Database Service is the easiest way to run your Server in OCI, as it offers …

[Read More]

OCI CLI is a powerful tool that does it all for OCI administration: from creating a compute instance to configuring a Virtual Cloud Network (VCN), from setting up Identity Management Service (IAM) to managing all the different storage flavours. Consult the OCI CLI command reference.

MySQL Database Service (MDS) is not an exception, everything related to the new MySQL Server service running over OCI can be easily managed with a set of commands using OCI CLI, check it here. …

[Read More]

MySQL highly available solutions, InnoDB Cluster (it uses InnoDB storage engine and is based on Group Replication plugin) and NDB Cluster (NDB storage engine), offer high scalability and redundant topologies.

• InnoDB Cluster can be configured with up to 9 replicas, in single primary configuration or multi-primary.
• NDB …

[Read More]

It’s now more than two months since MDS, the managed MySQL Server solution by Oracle MySQL team, hosted on Oracle Cloud Infrastructure (OCI), became available in several regions on the 1st of September. You can find online several resources online, such as:

• MDS documentation
• Oracle MySQL blog
• MySQL Database Service Release …

[Read More]

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

A second approach to finding the non-0 values from a JSON object is to turn the keys and values into separate columns of a table. This is cleaner, but a little wordier.

We’ll get the keys in one table:

SELECT ord, keyname 
FROM JSON_TABLE(JSON_KEYS(@j), '$[*]' COLUMNS (
    ord FOR ORDINALITY,
    keyname VARCHAR(100) PATH '$')
) AS keyTable;
 +------+----------+
 | ord  | keyname  |
 +------+----------+
 |    1 | RETRO    |
 |    2 | BOOSTER  |
 |    3 | SURGEON  |
 |    4 | GUIDANCE |
 |    5 | RECOVERY |
 +------+----------+

And the values in a second table:

…

[Read More]

Given a JSON object:
SET @j := '{"BOOSTER": 1, "RETRO": 2, "GUIDANCE": 0, "SURGEON": 1, "RECOVERY": 0}';

How do you find the keys which do NOT contain a value of 0?

One approach is to find the keys which do match 0, and then remove them.

Sounds like JSON_SEARCH and JSON_REMOVE? But there’s a gotcha: JSON_SEARCH works only with strings. Ok, so we REPLACE(@j, ‘0’, ‘”0″‘) – but that doesn’t help, because JSON_REMOVE can’t accept an array of paths like JSON_SEARCH would return; it requires each path as a separate parameter.

Instead, JSON_MERGE_PATCH will suffice. It has the feature that values from the second parameter are preserved only if they are not NULL. So, the approach becomes turning the 0s into NULLs, and then merging that document into an empty one.

SELECT REPLACE(@j, 0, 'null') AS j; -- case matters! null, not NULL …

[Read More]

Monitoring a database is a critical mission: collecting information over time allows tracking how's behaving certain instance in response to a determined workload. There are several solution on the market, and MySQL offers its own solution in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I'd like to remember that there's also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and "as is"). I'm talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline metrics captures.

Extensive instructions to setup, …

[Read More]

Monitoring a database is a critical mission: collecting information over time allows tracking how’s behaving certain instance in response to a determined workload. There are several solutions on the market, and MySQL offers its own in the Enterprise Edition, MySQL Enterprise Monitor (MEM). Today I’d like to remember that there’s also another nice solution that is free, and designed by MySQL performance architect, Dimitri Kravtchuk (hence not to be considered as an Oracle MySQL solution, not supported and “as is”). I’m talking about dim_STAT. dim_STAT has a slightly different purpose than MEM as it is specialized to make some low level analysis of the impact of the workload on our MySQL Server instance (e.g. can profile mysqld process), and above all, permits offline …

[Read More]

Given: a table with JSON arrays

CREATE TABLE t (id int auto_increment primary key, d json);
INSERT INTO t VALUES (1, '["apple", "apple", "orange"]');
INSERT INTO t VALUES (2, '["apple", "banana", "orange", "orange", "orange"]');

The desired output is each row with a count of the unique objects:

+------+----------------------------------------+
| id   | fruitCount                             |
+------+----------------------------------------+
|    1 | {"apple": 2, "orange": 1}              |
|    2 | {"apple": 1, "banana": 1, "orange": 3} |
+------+----------------------------------------+

JSON_TABLE() can transform the array into rows.

SELECT id, fruit
FROM t,
JSON_TABLE(d,
    "$[*]" COLUMNS (
        fruit VARCHAR(100) PATH "$"
    )
) AS dt;

+----+--------+
| id | fruit  |
+----+--------+
|  1 | apple  |
|  1 | apple  |
|  1 | orange |
|  2 | apple  |
|  2 | banana |
|  2 | orange |
|  2 | orange |
|  2 | orange | …

[Read More]

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and functions under the same umbrella: the …

[Read More]

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate …

[Read More]

When data and concurrency grow, together with queries complexity, a standard configuration does not fit anymore. This content walks through the ways MySQL has to adapt to an ever-increasing amount of data in context of tuning, under the heading “multiplication”:
—Scaling up through partitioning and how partitioning can help manage/archive data
—How to relocate binlog, undo, redo, tablespaces, or disk temporary tables and more on different mount points to take advantage of multiple storages and tackle I/O bottlenecks
—All improvements to parallel slave replication
—A quick OS approach, to verify swapping and affinity tuning take the most out of the machine.

All of this with different approaches to monitor the instance to spot what parts of the system and what queries need to be considered, mainly using:

—Sys Schema
— …

[Read More]

When data and concurrency grow, together with queries complexity, a standard configuration does not fit anymore. This content walks through the ways MySQL has to adapt to an ever-increasing amount of data in context of tuning, under the heading “multiplication”:
—Scaling up through partitioning and how partitioning can help manage/archive data
—How to relocate binlog, undo, redo, tablespaces, or disk temporary tables and more on different mount points to take advantage of multiple storages and tackle I/O bottlenecks
—All improvements to parallel slave replication
—A quick OS approach, to verify swapping and affinity tuning take the most out of the machine.

All of this with different approaches to monitor the instance to spot what parts of the system and what queries need to be considered, mainly using:

—Sys Schema
— …

[Read More]

If you need to do bulk importing into InnoDB Cluster, it is certainly possible to do so by using any of:

• mysqldump logical dump
• tab separated dump, exported with SELECT..INTO OUTFILE statement or by using mysqldump with --tab option. And import with LOAD DATA..INFILE

Unfortunately both imports will add load to instances and to channels interconnecting instances: data imported on the primary instance needs to be replicated to the rest of instances. And the bigger the data to import, the higher the load (and this could end up affecting performance). The import operation could be batched to reduce load, and Group Replication allows at least to throttle workload with …

[Read More]

If you need to do bulk importing into InnoDB Cluster, it is certainly possible to do so by using any of:

• mysqldump logical dump
• tab separated dump, exported with SELECT..INTO OUTFILE statement or by using mysqldump with –tab option. And import with LOAD DATA..INFILE

Unfortunately both imports will add load to instances and to channels interconnecting instances: data imported on the primary instance needs to be replicated to the rest of instances. And the bigger the data to import, the higher the load (and this could end up affecting performance). The import operation could be batched to reduce load, and Group …

[Read More]