The latest from the MySQL community

As a best practice before dropping a large table in MySQL, pt-archiver can be used to delete all the records in batches. This helps to avoid a situation where your server may get stalled under certain circumstances.

I recently read a comment from a user saying “The pt-archiver is not working as expected! It is skipping the last record, which seems like a bug.”. Let’s examine pt-archiver’s default behavior and understand why the author of this comment believes that pt-archiver is bugged (Spoiler: It’s not!).

But wait, before continuing on busting the blame, let me clarify why to use pt-archiver before dropping large tables.

When we drop a table in MySQL:

• Table data/index (ibd) and definition (frm) files are removed.
• Triggers are removed.
• Table definition cache is updated by removing the table being dropped.
• InnoDB buffer pool is scanned for associated pages to …

Recently we saw how we can catch audit information using invisible column, JSON data type and triggers in MySQL 8.0:

• Poorman’s MySQL table audit information
• Poorman’s MySQL table audit information – part 2

Of course, the creation of these triggers can quickly become a tedious operation. Once again, MySQL has all what we need to make it simple.

As you may know, it’s impossible to create triggers from store procedure, so using a sys schema function would not be something possible. However, MySQL Shell is again the answer ! It’s very easy to use a python plugin to perform the necessary operations.

I’ve written such example, available on …

I really enjoyed to dig into the solution I described yesterday in this post, to generate table audit information using invisible columns and triggers.

In this post, I will focus only on the solution using a JSON column to store the audit information.

Yesterday, I wrote that it’s also possible to track all changes an not only the last one but also keep information about what changed.

I wanted to illustrate that with an example, let’s start with the output:

This is exactly what I was looking for !

And of course we can search in that audit information. For example let’s search for all records that have been modified and where the old or new name was/is frederic:

We can verify that indeed that record had frederic as initial value.

Triggers

Compare …

Abstract:

By diving into the details of our case study, we will explain how incorrect table statistics may lead the optimizer to choose a suboptimal execution plan. We will also go into how MySQL calculates the table statistics and the ways to correct the table statistics to prevent it from happening again.

Case study: Incorrect table statistics lead the optimizer to choose a poor execution plan.

A customer reported a drastic performance degradation of a query while there were no code changes and no configuration changes made. The data in this article has been edited for brevity and modified to mitigate the exposure of confidential information. The case has also been approved for publication by the customer.

We obtained the query execution plan, and got the results as shown below (execution plan #1):

mysql> explain 
-> SELECT count(con.id)          ,
->        MAX(DAYNAME(con.date)) ,
-> …

Today, somebody asked me how he could track the creation of a record (who created it and when) and who and when it was last modified.

Usually, this is performed in a different table, called audit table. In MySQL you can create an audit table and populate it using triggers.

But of course, this person had some constraints, otherwise it would have been too easy:

1. no other table could be used/created
2. the application should keep working without any changes

He was desperate for help…

MySQL 8.0 Invisible Columns

The answer is : Invisible Columns.

Let’s have a look at a simple table, containing an ID as primary key, a first name and a last name. And of course the application must work as expected… and unfortunately, it seems the application is …

In this article, we are going to talk about the WHERE clause and how to use it in MySQL. Besides SELECT, the scope of the WHERE clause includes the UPDATE and DELETE statements. The WHERE clause is indispensable for quality work with MySQL databases. That’s why the current article will deal with this clause and […]

The post WHERE Condition in MySQL with 16 Different Query Examples appeared first on Devart Blog.

Let’s start with some info about how MySQL Document Store handles JSON documents.

Document Store and CRUD

We know that MySQL 8.0 Document Store handles JSON documents with CRUD operations. We can add, delete and modify those documents very easily:

 JS >db.mycollection.find()
{
    "_id": "0000624d3e890000000000000001",
    "name": "my_iot1",
    "type": "sensor",
    "capabilities": "{'temperature':'true','humidity':'true'}"
}
{
    "_id": "0000624d3e890000000000000002",
    "name": "my_iot2",
    "type": "sensor",
    "capabilities": "{'temperature':'true'}"
}
2 documents in set (0.0007 sec)

To modify a document, the modify method can be used in different ways:

As illustrate above, we have:

• set()
• …

Do you wonder if MySQL tells you the truth about writes to tables that have foreign key constraints? The situation is complex, and getting visibility on what is really happening can be a problem.

I found this issue intriguing and decided to share and highlight some examples.

Query Plan

Let us take this example table:

CREATE TABLE `product` (
  `category` int NOT NULL,
  `id` int NOT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`category`,`id`)
) ENGINE=InnoDB;

We want to know how costly an example UPDATE against this table will be:

mysql > EXPLAIN update product set id=id+1 where id=65032158 and category=3741760\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: product
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 1 …

I love the Raspberry Pi, and I love Percona server. The combination of the two can provide a nice home database. I have been running a Percona Server database since 2019 to hold all the weather information, that I collect from several of my Weather Stations.

I did a my first blog post on installing Percona Server 5.7 on the Raspberry Pi 3+.

You can read that blog post here: How to Build a Percona Server “Stack” on a Raspberry Pi 3+

Fast forward to 2022 and we now have the resources to build Percona Server 8.0 64-bit on the Raspberry Pi. In this post I will cover building and installing Percona Server 8.0.29 and Percona XtraBackup 8.0.29.

Prereqs:

1. Raspberry Pi 3B+, 4 or 400 (any memory size will work).
2. 128GB or 256GB microSD card. Of course you can go …

The utf8mb4 character set is the new default as of MySQL 8.0, and this change neither affects existing data nor forces any upgrades.

Migration to utf8mb4 has many advantages including:

• It can store more symbols, including emojis
• It has new collations for Asian languages
• It is faster than utf8mb3

Still, you may wonder how migration affects your existing data. This blog covers multiple aspects of it.

Storage Requirements

As the name suggests, the maximum number of bytes that one character can take with character set utf8mb4 is four bytes. This is larger than the requirements for utf8mb3 which takes three bytes and many other MySQL character sets.

Fortunately, utf8mb3 is a subset of …