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 …

This tutorial explains the use of MySQL aggregate functions like AVG, COUNT, SUM, MAX, MIN with the help of simple examples. Aggregate functions are a bunch of methods that operate on a set of values. They can do calculations for us and then returns one final value. For example, you may like to compute the sum of the data values in a given field. The following are aggregate functions that we are covering in this tutorial. 1. COUNT function 2. MIN function 3. MAX function 4. SUM function 5. AVG function MySQL Aggregate Functions with Examples Before we go through

The post MySQL Aggregate Functions appeared first on Learn Programming and Software Testing.

This tutorial explains MySQL OPTIMIZE TABLE statement which defrags tables and recovers unused space. We’ll describe the complete usage of this method with the help of simple examples. If your database is receiving a lot of deletes and updates calls, then it might lead to fragmentation in your MySQL data files. Therefore, a lot of unused space would go in vain, and also put a high impact on the performance. So, experts recommend that you must defrag your MySQL tables regularly. Hence, we’ll today explain how to use the MySQL OPTIMIZE TABLE to defrag tables and free up space. MySQL OPTIMIZE

The post MySQL OPTIMIZE TABLE Statement appeared first on Learn Programming and Software Testing.

This tutorial explains MySQL CONCAT() which is a built-in String function. It takes variable no. of strings as input and concatenates them together. We’ll describe the usages of this method with the help of simple examples. MySQL string concatenation is more user-friendly than other databases such as PostgreSQL or Oracle. They provide a string concatenation operator “||” instead of a proper function. However, MS SQL server does the same job using the addition arithmetic operator (+). 1. CONCAT() Syntax 2. CONCAT() Simple Examples 4. CONCAT() with Tables Let’s now go through each of the section one by one. MySQL CONCAT()

The post MySQL CONCAT to Concatenate Strings appeared first on Learn Programming and Software Testing.

This tutorial explains MySQL DROP TABLE statement which deletes the specified tables from the in-use database. We’ll describe several usages of this method with the help of simple examples. 1. DROP TABLE Syntax 2. DROP TABLE If Exist 3. DROP TABLE Matching Pattern 4. DROP TABLE Full Example Let’s now read and understand each of the section one by one. MySQL DROP TABLE statement As stated initially, the DROP TABLE is a built-in MySQL statement which removes a specified table from the database. So, let’s first check the details and see how to use the DROP command. But first, create

The post MySQL DROP TABLE with Simple Examples appeared first on Learn Programming and Software Testing.

This tutorial explains MySQL ABS() which is a Math/Trig function. It takes a number as input and determines its absolute (positive) value. We’ll describe the usages of this method with the help of simple examples. 1. ABS() Syntax 2. ABS() Simple Examples 3. ABS() for Expression 4. ABS() with Tables Let’s now go through each of the section one by one. MySQL ABS() Function As stated initially, ABS() is a built-in MySQL function which does some math calculation to derive the absolute value of a number. In Maths term, an absolute value represents the length of a number on the

The post MySQL ABS Function with Simple Examples appeared first on Learn Programming and Software Testing.

This tutorial explains MySQL DATE_ADD function which adds up a slice of time (in days/hours, e.g., 1 day or 10 days) to the given date. We’ll describe the complete date arithmetic of this method with the help of simple examples. 1. DATE_ADD() Syntax 2. DATE_ADD() with -ve Interval 3. DATE_ADD() for Invalid Date 4. DATE_ADD() for Auto Adjustment Let’s now go through each of the section one by one. MySQL DATE_ADD() Function As stated initially, DATE_ADD() is a built-in MySQL function which adds the specified no. of days to a given date. So, let’s now see the details and check

The post MySQL DATE_ADD Function with Simple Examples appeared first on Learn Programming and Software Testing.

MySQL How do you restore tablespace?

This is not new information but I have not covered it much so addressing it now for those that need it.

If you lose your ibd files... you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it.  What/how do you lose tablespace?

Here is a simple example to recover tablespace.



mysql> Create database demo;

mysql> use demo;

mysql> CREATE TABLE `demotable` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;


Now we store some data...

…