Hello friends, at Percona Managed Services, we usually do different types of things every day, including routine tasks, monitoring, and, very frequently, answering questions that are not always easy to answer.

A few days ago, a client asked us the following question: “Hey Percona, I have a question and a problem simultaneously: I want to delete rows from a table from a specific date back because the data is not necessary. I tried to run the DELETE command, which gave me this error: Cannot delete or update a parent row: a foreign key constraint fails. Could you please help me?”

At first glance, the error message was obviously clear: the table from which rows were to be deleted had a child table, which prevented the execution of the DELETE directly.

“Don’t worry, we’ll take a look at the issue, and …

Introduction/TLDR:

We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools. We would like to get feedback from the Community about the different approaches for achieving this.

The problem:

Running EXPLAIN is a great way to understand how complex SQL statements are executed. So it is natural that monitoring and query analysis tools utilize EXPLAIN for these purposes.

However, there is a problem for cases when INSERT/UPDATE/DELETE statements need to be explained. Running EXPLAIN for these statements, a read-only operation, requires the same privileges as running the original statements …

Given a table named tbl with one million entries, we want to select a random row from this table, fast. Our table definition looks like this:

create table tbl (
    id INTEGER NOT NULL,
    d VARCHAR(200) NOT NULL,
    INDEX(id)
);

Dense id space

We can generate some test data using a recursive CTE:

mysql> set cte_max_recursion_depth = 100000;
mysql> insert into tbl 
    -> with recursive c(n, u) as (
    ->   select 1, uuid() 
    -> union all
    ->   select n+1, uuid() from c where n < 100000
    -> ) select * from c ;

The Recursive CTE will generate 100k pairs of (number, uuid()). The initial row is defined in the upper row of the UNION, each subsequent row builds recursively on top of that, by simply counting …

Given a table named tbl with one million entries, we want to select a random row from this table, fast. Our table definition looks like this:

create table tbl (
 id INTEGER NOT NULL,
 d VARCHAR(200) NOT NULL,
 INDEX(id)
);

Dense id space

We can generate some test data using a recursive CTE:

mysql> set cte_max_recursion_depth = 100000;
mysql> insert into tbl
 -> with recursive c(n, u) as (
 -> select 1, uuid()
 -> union all
 -> select n+1, uuid() from c where n < 100000
 -> ) select * from c ;

The Recursive CTE will generate 100k pairs of (number, uuid()). The initial row is defined in the upper row of the UNION, each subsequent row builds recursively on top of that, by simply counting up.

Since we generate …

Thank you to everyone who joined us for the 19th edition of MyWebinar. We hope that our time together helped to elevate and seam your work to perfection. We’re committed to giving back to the opensource database community in the coming days by presenting more LIVE events.

Recently, Mydbops conducted a webinar on the top 10 features of MySQL 8. MySQL is one of the most popular and widely used open-source relational database management systems. With every new version, MySQL brings new features and enhancements that make it more reliable, secure, and efficient. In this blog, we will wrap up the top 10 features of MySQL 8 discussed in the webinar by Mydbops.

Vinoth Kanna, co-Founder from  …

Percona XtraBackup is an open source hot backup utility for MySQL-based servers. To take consistent and hot backup, it uses various locking methods, especially for non-transactional storage engine tables. This blog post discusses the cause and possible solution for queries with ​Waiting for table flush state in processlist when taking backups using Percona XtraBackup. Only MySQL 5.7 version is affected by this, as per my tests.

Type of locks taken by Percona XtraBackup

Before discussing the main issue, let’s learn about the type of locks used by Percona XtraBackup to take consistent backups. Percona XtraBackup uses backup locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is …

I discovered a dependency for MySQL Workbench on AlmaLinux 8 installation. I neglected to fully cover it when I documented the installation in a VM of AlmaLinux 9. I go back later and update that entry but for now you need the following dependencies:

proj-6.3.2-4.el8.x86_64.rpm
proj-datumgrid-1.8-6.3.2.4.el8.noarch.rpm
proj-devel-6.3.2-4.el8.x86_64.rpm

Install like this:

sudo dnf install -y *.rpm

Log file:

Last metadata expiration check: 3:01:53 ago on Fri 10 Feb 2023 03:37:49 AM UTC.
Dependencies resolved.
==========================================================================================
 Package                Architecture   Version                 Repository            Size
==========================================================================================
Installing:
 proj                   x86_64         6.3.2-4.el8             @commandline         2.0 M
 proj-datumgrid         noarch …

Note

PlanetScale now supports PostgreSQL.

Choosing a data store is one of the most important decisions you'll make when building software applications. The good and bad news though, is that there is an abundance of options. Depending on the type of application you're building, you may opt for a relational database like MySQL or Postgres, a non-relational database like MongoDB or CouchDB, a graph database like Neo4j, or one of the many other alternatives that each bring their own benefits and drawbacks.

It is a difficult choice and sometimes you realize that the database you initially went with no longer serves the needs of your application and you have to make the choice to migrate.

In this blog post, we'll take a look at how you can migrate your database schema from PostgreSQL to MySQL. MySQL and PostgreSQL …

While MySQL error codes are useful, understanding what they mean can be difficult. This article looks at common MySQL error codes, non-coded errors, and how to fix them.

MySQL error code format

Each MySQL error includes:

• An error number: A MySQL-specific number corresponding to a particular error.
• An SQLSTATE value: A five-character string that indicates the error condition.
• An error message: A textual description of the error.

MySQL error example

Here’s an example of what a MySQL error code looks like:

ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

In the error above:

The latest MySQL release has been published on January 17th, 2023. MySQL 8.0.32 contains some new features and bug fixes. As usual, it also contains contributions from our great MySQL Community.

I would like to thank all contributors on behalf of the entire Oracle MySQL team !

MySQL 8.0.32 contains patches from Facebook/Meta, Alexander Reinert, Luke Weber, Vilnis Termanis, Naoki Someya, Maxim Masiutin, Casa Zhang from Tencent, Jared Lundell, Zhe Huang, Rahul Malik from Percona, Andrey Turbanov, Dimitry Kudryavtsev, Marcelo Altmann from Percona, Sander van de Graaf, Kamil Holubicki from Percona, Laurynas Biveinis, Seongman Yang, Yamasaki Tadashi, Octavio Valle, Zhao Rong, Henning Pöttker, Gabrielle Gervasi and Nico Pay.

Here is the …