MySQL InnoDB Cluster is the most easy and integrated High Availability solution for MySQL.

The solution is composed of:

• MySQL Server
• MySQL Group Replication Plugin
• MySQL Clone Plugin
• MySQL Router
• MySQL Shell

All those components are developed and tested together to provide the easiest and best experience to the MySQL users.

As the MySQL Router is a TCP Level 4 router (like HA Proxy), some users requiring a more “intelligent” proxy having other extra features like caching, read/write splitting in relation with the user or SQL, firewall, … may be interested in using ProxySQL… and this is a good choice !

ProxySQL / MySQL Router in OSI Model

However, even is ProxySQL supports MysQL Group Replication, for …

I have to celebrate the anniversary of my last day in Oracle (that was 7 years ago!) somehow, and I think writing yet another blog post about Oracle MySQL bugs is a good way to do this. I am actually surprised (and happy) that public bugs database is still alive, maintained and considered important in Oracle, and I know who in Oracle was working hard all these years for this to happen!

In my previous post in this series I've stopped on Bug #95954 and had not completed review of interesting MySQL bug reports that I've subscribed to in June 2019. So, below I start with the next bug in my list, complete review for June and cover some bugs reported in July. There were many.

• …

Mid September, MySQL Community, MySQL Customers and MySQL Engineers will be in San Francisco to share their experience and present the new features of your favorite database !

The event will be held in Moscone South (just Mirko Ortensi‘s Hands-on Lab will be delivered in Moscone West).

During the week, the MySQL Community Team will host the traditional MySQL Reception. We got so great feedback from last year that we decided to renew the experience in the same awesome location, the Samovar Tea Lounge at Yerba Buena Gardens. Don’t forget that you need to register for this reception but no OOW pass is required. Please register here !

Back to the conference, you can find the full schedule for the session in the …

Answering this question is not easy. Like always, the best response is “it depends” !

But let’s try to give you all the necessary info the provide the most accurate answer. Also, may be fixing one single query is not enough and looking for that specific statement will lead in finding multiple problematic statements.

The most consuming one

The first candidate to be fixed is the query that consumes most of the execution time (latency). To identify it, we will use the sys schema and join it with events_statements_summary_by_digest from performance_schemato retrieve a real example of the query (see this post for more details).

Let’s take a look at what sys schema has to offer us related to our mission:

> show tables like …

Recently, PeterZ pointed a huge difference in memory usage of MySQL 8.0 compare to MySQL 5.7. This can be an issue for small instances if the same configuration for buffers like the buffer pool are not changed.

As explained in Peter’s article, this can lead to the awakening of the so feared OOM Killer !

MorganT, pointed accurately in his comment what is the source of such difference and how this was then caused by the new instrumentation added in MySQL 8.0.

Nothing is free, even as a …

It's Saturday night. I have a week of vacation ahead that I am going to spend at home, working on things I usually do not have time for. I already did something useful (created a couple of test cases for MariaDB bugs here and there, among other things), so why not to get some fun and continue my previous review of recent interesting MySQL bug report...

So, here is the list of 15 more MySQL community bug reports that I've subscribed to back in May and June 2019:

• Bug #95491 - "The unused wake up in simulator AIO of function reserve_slot". I am impressed by …

When somebody wants to migrate from a single MySQL instance to a full HA solution using MySQL InnoDB Cluster, the best solution to reduce the downtime is to use asynchronous replication and switch database only once at a certain point in time when everything is ready. This is almost what I explained already in this post.

The most difficult part was related to the provisioning of the existing data to the new cluster members. A backup (physical or logical) was required. It should have been restored on every nodes and we had to be sure to not mess up with the GTIDs.

This is not more the case since MySQL 8.0.17 ! Now we can use the CLONE plugin to start the cluster provisioning too.

The current situation

…

After a 3 months long break I'd like to continue reviewing MySQL bug reports that I am subscribed to. This issue is devoted to bug reports I've considered interesting to follow in May, 2019:

• Bug #95215 - "Memory lifetime of variables between check and update incorrectly managed". As demonstrated by Manuel Ung, there is a problem with all InnoDB MYSQL_SYSVAR_STR variables that can be dynamically updated. Valgrind allows to highlight it.
• Bug #95218 - "Virtual generated column altered …

In MySQL 8.0, when using invalid values for date data types, an error is returned. This was not the case in 5.x versions.

Let’s have a look using the table definition of bug 96361:

CREATE TABLE `new_table` (
  `id_table` int(11) NOT NULL AUTO_INCREMENT,
  `text_table` varchar(45) DEFAULT NULL,
  `date_table` date DEFAULT NULL,
  PRIMARY KEY (`id_table`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now we can try the following statement in MySQL 5.7 and MySQL 8.0:

MySQL 5.7.26> SELECT id_table, text_table 
              FROM new_table WHERE date_table = '' OR date_table IS NULL;
Empty set, 1 warning (0.01 sec)

MySQL 5.7.26> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   | …

You may have noticed that in many of my demos, I disable firewall and SELinux (I even use --initialize-insecure sometimes ). This is just to make things easier… But in fact enabling iptables and SELinux are not complicated.

Firewall

These examples are compatible with Oracle Linux, RedHat and CentOS. If you use another distro, the principle is the same.

For the firewall, we need first to allow incoming traffic to MySQL and MySQL X ports: 3306 and 33060:

# firewall-cmd --zone=public --add-port=3306/tcp --permanent
# firewall-cmd --zone=public --add-port=33060/tcp --permanent

If you don’t plan to restart the firewall, you just need to run the same commands without --permanent to make then immediately active.

Then we need to allow the Group Replication’s communication port. This is usually 33061 but it can be configured in …