MySQL has been making huge progress on the JSON/NoSQL front as well ie. supporting many of the JSON access and manipulation features and operations. In this how-to tutorial, I am going to show you how to add a new JSON column with default value in an existing MySQL table using the ALTER TABLE statement. And, how to create a new table using CREATE TABLE with JSON column along with its default value.

The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network.

In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.

At the beginning, the mechanism, called mysql_old_password, was pretty insecure: it’s based on a broken hashing function and the password is 16 bytes long. It was not so complex for attackers to find a plaintext password from the hash stored in the password column of …

Hello MySQL Connector/NET community,

Keeping an ongoing pace to support the latest technologies, we are pleased to announce the latest updates regarding MySQL Connector/NET and Entity Framework Core.

As you may know, Entity Framework Core 3.1 is the latest and long-term support (LTS) release, meaning that it will be supported for at least 3 years. It has a primary goal to polish the EF Core 3.0 version and reintroduces support for .NET Standard 2.0, so it will run on .NET Frameworks versions that support the standard. In our continuous effort to keep up on the latest technologies, we would like to share with you that our latest GA release, Connector/NET 8.0.20, comes with full support for EF Core 3.1.

Let’s have a short demonstration on how to use Connector/NET and EF Core. We are going to use the Code First process to create the database in our server and build up our Entity Model in a Console …

The authentication, the first level of security for each IT system, is the stage to verify the user identity through the basic username and password scheme. It is crucial to have a mechanism to protect and secure password storing and transmitting over network.

In MySQL, there is plenty of different authentication methods available, and last versions improved the security of this concept.

At the beginning, the mechanism, called mysql_old_password, was pretty insecure: it’s based on a broken hashing function and the password is 16 bytes long. It was not so complex for attackers to find a plaintext password from the hash stored in the password column of …

The MySQL Document Store is an quick and easy to use NoSQL JSON Document Database that was designed to be easy to use.  But there is one thing that may not be obvious if you are working to modify multiple documents. 

So let us start with a very simple example collection of three documents.

>db.xyz.find()
{
    "a": 1,
    "_id": "00005eb2ba3c0000000000000004"
}
{
    "b": 2,
    "_id": "00005eb2ba3c0000000000000005"
}
{
    "c": 3,
    "_id": "00005eb2ba3c0000000000000006"
}
3 documents in set (0.0008 sec)

So we have three different documents and later determine we need to add the same key/value pair for all of them.

But the following will not work:

>db.xyz.modify().set("fizz","buzz")
…

As a Solutions Engineer at Percona, one of my responsibilities is to support our customers as they investigate new and emerging technologies. This affords me the opportunity to speak to many current and new customers who partner with Percona.

The topic of Kubernetes is becoming more popular as companies are investigating and adopting this technology. The issue most companies are encountering is having a stateful database that doesn’t fall victim to an environment tuned for ephemeral workloads. This obviously introduces a level of complexity as to how to run a stateful database in an inherently stateless world, as databases are not natively designed for that.

To make your life easier, as a part of the Percona Cloud-Native Autonomous Database Initiative, our engineering teams have built a …

This tutorial shows how you can install Nginx on an Ubuntu 20.04 LTS server with PHP 7.4 support (through PHP-FPM) and MySQL support (LEMP = Linux + Nginx (pronounced "engine x") + MySQL + PHP).

In Percona’s Managed Services, we start up and shut down MySQL servers all the time.  Over the years, we’ve seen various issues occur due to the way servers are shut down.  No matter if it is a situation where unexpected errors appear in the log or you’re stuck waiting for hours for a server to finishing shut down… Here are some steps we can take to minimize any trouble and prepare MySQL for shutdown.

This list of “super safe practices” was born from lessons we’ve learned in a wide variety of MySQL environments.

1. Stop Replication.

Under some (rare) circumstances, a slave may try to startup in the incorrect position. To help minimize this risk, stop the IO thread first so it’s not receiving new events.

STOP SLAVE IO_THREAD;

Wait for the SQL thread to apply all events, then stop it too.

SHOW SLAVE STATUS\G
STOP SLAVE SQL_THREAD;

This puts both of the …

On a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post.

Configuration

The binary log compression feature is controlled by two variables, one for enabling the feature and another to specify the compression level. These are summarized in the below table.

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:

SELECT ord, …