The latest from the MySQL community

Many of our users, developers and database administrators, keep asking our team about EverSQL's indexing recommendations algorithm. So, we decided to write about it.

The first option is to use EverSQL to automatically find indexes that are best for your database.

The second option is to read our detailed tutorial below and learn more about indexing best practices.

This tutorial won't detail all the internals of the algorithm, but rather try to lay out the basic and important aspects of indexing, in simple terms.
Also, and most importantly, we'll present practical examples for properly indexing your tables and queries by relying on a set of …

How to build a multi-region, multi-master MySQL cloud database back-end capable of serving a global high volume cloud contact center

This global SaaS provider is a Cloud Contact Center solution provider (the leading Salesforce telephony solution), who needed to deliver up-to-date data to clients as quickly as possible. Its cloud-based call center software frees users to make every conversation personal; it supports the creation of exceptional customer experiences that help serve better and sell more. Its technology is 100% cloud-based and device-agnostic for telephony infrastructure.

What is the Challenge?

Active/Active (multi-master) MySQL clustering is needed when there is significant update load on geographically distributed applications. These require fast response times both for updates and reads, and they also need to share data on a global scale.

• Regional distribution of data
• Deliver …

Oups, my previous post had a forbidden word and did not show-up on Planet MySQL, so retry...

As written in a previous post, the state of Planet MySQL is unhealthy !  I am still aggregated there for now as, before leaving what was the best news-feed for the MySQL Community, we need a replacement.  This post aims at starting a discussion on this replacement.

Update

So, I admit the title for this post is a bit ambiguous. Not only is it the second post I’ve written in a short period of time, but it’s also focused on ProxySQL 2.0. As promised in the previous post, I’ve upgraded my testing environment to ProxySQL 2.0 and was interested to see if there are any differences in behaviour from the previous version. As it turns out, there are.

In the comments section of the previous post, you’ll find a comment by ProxySQL Founder and CEO, René Cannaò:

“Indeed ProxySQL doesn’t support caching_sha2_password authentication plugin, but since ProxySQL 2.0.3 (March 2019) a client connection using caching_sha2_password will be automatically switched to mysql_native_password.

With regards to “OPT_CHARSET_NAME=utf8”, the problem is that your backend it is not MySQL 8.0 …

In this two-part blog series, I wanted to cover some failover scenarios with Group Replication. In part one, I will discuss an interesting behavior and performance degradation I discovered while writing these posts. In part two, I will show several failover scenarios and demonstrate how Group Replication handles each situation.

The test environment is very basic, a three-node Group Replication (mysql1,mysql2,myslq3) on MySQL 8.0.19 with default settings. mysql2 is the Primary node.

In this scenario, I was testing a partial network failure when a node gets separated from the primary, but other nodes still can see it.

You would think mysql3 is going to lose quorum and exit the cluster, but no. Inside the cluster, all the nodes are in constant communication with each other, not just …

Databases are complicated software made to handle the concurrent load while making specific guarantees about data consistency and availability. There are many scenarios which should be tested that can only happen under concurrent conditions.

Pstress is a probability-based open-source database testing tool designed to run in concurrency and to test if the database can recover when something goes wrong. It generates random transactions based on options provided by the user. With the right set of options, users can test features, regression, and crash recovery. It can create the concurrent load on a cluster or on a single server.

The tool is currently in beta, but it has already become very important within the testing pipeline for Percona. Pstress is widely used by Percona’s QA team during each phase of testing. It has …

MySQL Shell is a powerful tool.  It allows Javascript, Python and SQL access to MySQL.

Usage of MySQL Shell can be
1. As admin tool to MySQL Server
2. As Scripting Engine to run Javascript or Python or SQL to access MySQL Server
3. As a tools to allow Document Store access (like collection API) with MySQL Server
4. As MySQL InnoDB Cluster admin Tooling

Customization and extensible framework is essential to a tool as such DB administrators can create customized report and extension to do the daily job.

This tutorial is written to provide a short summary how we can create a MySQL Shell Report in Python.

What is MySQL Shell Report
MySQL Shell report can be executed thru "\show"


 There are 3 'report's coming with MySQL Shell - namely 'query', 'thread', 'threads'.

Query Report :
Getting the "help" …

It’s the in-between term time and we’re all stuck at home. I decided to update the image for my Fedora 30 virtual machine. I had a work around to the update issue that I had encountered last October in Bug #96969 but it was not required with the current version. However, after updating from MySQL 8.0.17 to MySQL 8.0.19, I found that my Java connection example failed.

The $CLASSPATH value was correct:

/usr/share/java/mysql-connector-java.jar:.

The first error that I got was the my reference to MySQL JDBC driver was incorrect. The error message is quite clear:

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Cannot connect to database server:
The server time zone …

Recently, we were affected by an ignored configuration option introduced in MySQL 5.6. This incident caused us to perform extended planning of downtime and the rebuilding of the slave. In this post, we’ll discuss our encounter with binlog_error_action and likely bad default.

The incident started with an alert from our monitoring platform that a check failed to verify a MySQL master’s binary log coordinates. My colleague working from the other side of the globe observed the following:

mysql> show master status;
Empty set (0.00 sec)

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

Interestingly, the MySQL slave was still reporting that everything was okay!

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: …

March is not only the month when a pandemic closed all borders and people had to stay home on the quarantine; it’s also the month when daylight saving time change happens. For some regions, this is not only a time change but also a switch to a different timezone. For example, New York uses EST during winter and EDT during summer. If you use the system timezone and do not restart the MySQL server or a PXC node after the switch, you may notice that the change was not implemented.

You may end up with a situation where some of your cluster nodes still use the time zone before the switch (e.g. EST) and others use the timezone after the change (e.g. EDT).

$ date
Sun Mar  8 03:03:28 EDT 2020

$ ./bin/mysql  -h127.0.0.1 -P3373 -uroot test
...
EDT node> show variables like '%zone%'; …