SLEEP thread causing "Waiting for table metadata lock"
We might have faced some scenarios once our db connection threads are getting locked and we try to find the culprit thread id which is locking the thread, to kill and release the connections. 
Here I am going to focus on one of the scenarios when db connection threads are locked at state "waiting for table metadata lock" and when we see the processlist, we can't find any thread which is running and using the same table, i.e., all threads are in SLEEP state except the locked threads.

Such as : 
mysql [localhost:5726] {msandbox} (world) > show …

During the last conferences, I had the chance to discuss a lot with many colleagues. Some of them, like me, feel we have lost ourselves, not totally but a bit. 

I had start to think why, and this is my reasoning, not saying is right, but I think is not far from the truth.

Let us start just mentioning few events in the 2019 schedule, February Fosdem, end of May Percona live US, mid-June DataOps Barcelona, September Oracle open World, end of September-October Percona Live Europe, plus an undefined number of secondary events or MariaDB roadshows.

Where is the MySQL (and related) conference?

Do not get me wrong, we talk about MySQL a lot during each one of the above (or MariaDB, tometo/tomato). But it seems to me, that in a subtle way, the community had lost his conference.

There is nothing wrong if each company is trying to get the most out of their investment, so implicitly driving the show to get …

Two important MySQL 8.0 Shell Utilities we use regularly are the upgrade checker utility and  JSON import utility . The upgrade checker utility simplifies the pre-upgrade compatibility audit (whether MySQL server instances are ready for upgrade), We have blogged about MySQL upgrade checker utility here . The upgrade checker utility does not support checking MySQL Server instances at a version earlier than MySQL 5.7. From MySQL Shell 8.0.16, the upgrade checker utility can check the configuration file (my.cnf or my.ini) for the server instance. The utility checks for any system variables that are defined in the configuration file but have been removed in the target MySQL Server release, and also for any system variables that are …

(Image credit: Pixabay).

In my earlier post, I showed how MySQL, since version 8.0.14, has support for LATERAL derived tables. With LATERAL, a JOIN can have a second table – a subquery-based derived table – be defined based on values from columns of the first table, and thus be re-calculated for each row of the first table.…

Facebook Twitter LinkedIn

What is new with MySQL 8.0 Delayed Replication ?

Delayed Replication – You can deliberately execute transactions later than the master by a specific duration of time , Why you do that and for what ? Consider this, Accidentally someone did a wrong UPDATE / DELETE in the master and the transaction is committed, Now how can DBA rollback the database system to the last known good condition ? This is when we benefit from MySQL delayed slave replication investment. The default replication delay in MySQL is “0” seconds, To delay the slave by N seconds use the CHANGE MASTER TO MASTER_DELAY = N, The transactions received from the master is not executed until N seconds later than it’s commit on the immediate master. We have blogged here how to setup …

MySQL Roles are becoming more and more popular. Therefor, we receive more and more questions related to them.

First I encourage you to read this previous 2 posts:

• MySQL 8.0 Roles and GraphML
• MySQL 8.0: Listing Roles

In this post, I will share you some queries I find useful when using MySQL Roles.

Listing the Roles

The first query allows you to list the Roles created on your MySQL Server and if they are assigned to users, and how many:

SELECT any_value(User) 'Role Name', 
       IF(any_value(from_user) is NULL,'No', 'Yes') Active,
       count(to_user) 'Assigned  …

Like I stated in my previous article - MySQL InnoDB Cluster - Recovering and provisioning with mysqldump :
"As the administrator of a cluster, among others tasks, you should be able to restore failed nodes and to add (or remove) new nodes".
Well, I still agree with myself :)
MySQL customers using a Commercial Edition have access to MySQL Enterprise Backup (MEB) which provide enterprise-grade physical backup and recovery for MySQL.
MEB delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.

The post MySQL InnoDB Cluster - Recovering and provisioning with MySQL Enterprise Backup first appeared on dasini.net - Diary of a MySQL expert.

Percona is glad to announce the release of Percona XtraBackup 2.4.15 on July 10, 2019. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it …

The Question Recently, a customer asked us:

Where are the logs for a Tungsten Cluster and which are the proper log files to monitor if I do a master role switch to another node?

The Answer: Part I Both basic and advanced logs are available!

The basic logs are symbolic links into the log subdirectory for each layer of the cluster.

For example, this is the listing of the default log directory, /opt/continuent/service_logs:

connector-user.log -> /opt/continuent/tungsten/tungsten-connector/log/connector-user.log
manager-user.log -> /opt/continuent/tungsten/tungsten-manager/log/manager-user.log
mysqldump.log -> /opt/continuent/tungsten/tungsten-replicator/log/mysqldump.log
replicator-user.log -> /opt/continuent/tungsten/tungsten-replicator/log/replicator-user.log
xtrabackup.log -> /opt/continuent/tungsten/tungsten-replicator/log/xtrabackup.log

As you can see, each log file is a …

When having only several databases to manage, regular backup operations can be done quite easily and seamlessly either with the help of a few simple scripts or by configuring an SQL Server agent that will perform the backup automatically. But sometimes the situation is more complicated. When, for instance, there are hundreds of databases, backing […]