Recently, somebody asked me how he can find the long running transactions in MySQL.

I already have one MySQL Shell plugin that allows you to find the current transactions sorted by time. The plugin allows you to also get the details about the desired transaction. See check.getRunningStatements().

Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).

SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       Command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM …

Since 8.0.30, you have the possibility to modify the InnoDB Redo Log Capacity online. An undersized Redo Log Capacity is problematic and lead to performance issues.

However, it’s not recommended to oversize the Redo Log either. Redo Log files consume disk space and increases the recovery time in case of a restart (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when innodb_fast_shutdown=0.

This means that now, you don’t need to restart MySQL if you want to increase or decrease the size of the InnoDB Redo Logs files. In fact, we don’t talk anymore about file size but about capacity ! The DBA doesn’t need to specify any file size and/or amount of files for Redo Logs anymore …

My last blog, Percona Utilities That Make Major MySQL Version Upgrades Easier, detailed the tools available from the Percona toolkit that assists us with major MySQL version upgrades. The pt-upgrade tool aids in testing application queries and generates reports on how each question performs on servers running various versions of MySQL.

MySQL Shell Upgrade Checker is a utility that helps in compatibility tests between MySQL 5.7 instances and MySQL 8.0 upgrades, which is part of the mysql-shell-utilities. The util.checkForServerUpgrade() function checks whether the MySQL 5.7 instance is ready for the MySQL 8.0 upgrade and generates a report with warnings, errors, and notices for preparing the current MySQL 5.7 setup for upgrading to MySQL 8.0.

We can run this Upgrade Checker Utility in the current MySQL 5.7 …

 中国加密标准

中国加密标准的SM1、SM2、SM3、SM4、SM7、SM9等。 借助国际加密标准，我们可以利用来自开源的加密库, 例如，最常用和最流行的加密库之一是 OpenSSL。

本文旨在使用有 SMx（中国加密库）的“OpenSSL”库 的 BabaSSL加上 MySQL的TLS设置 来提供一个方法实现 国密的 MySQL。  BabaSSL不是唯一采用 MySQL 的中国加密标准,  来自其他的中国加密供应商/开源的/兼容的/最新的 OpenSSL 库， 也可能会会支持类似的方式来实现MySQL国密TLS加密。

测试环境（用于分享）

1.计算机资源（VM）

2. 操作系统 - 计算 机VM 配备 Oracle Linux 8

3. 通过公用 yum 的存储库安装MySQL 社区版本8.0

4. 使用 BabaSSL 8.3 [ BabaSSL 8.3.2-dev ]。 它基于 2020 年 9 月 22 日的 OpenSSL 1.1.1h 版本

介绍 …

A work problem: A commercial application, Artifactory, where we do not control the source or the schema has performance problems involving a certain long running query.

The data size and row counts are not outrageous, and the query itself and the schema are not broken. But the data is very skewed and for certain values the query is very slow, as almost the entire table is selected.

We introduce an experimental covering index, and show a 16x improvement, going from 143s to 9s execution time. We advise the customer to ask for this covering index to be added officially using the normal way through MySQL portal.

Here we go

A call to DBA:

We’ve been investigating performance issues on the Artifactory side and found that our current MySQL primary is sitting at 80-90% I/O utilization. Our next steps would be to see if there …

in this tutorial, we’ll learn how to fix “UNKNOWN COLUMN IN ‘FIELD LIST'”. Sometimes, We’re are getting errors after each insert or update to the MySQL table. The message is “Unknown column ‘column-name’ in ‘field list'” while this column was existing in this table. The most common causes of the error “MySQL: Unknown column in […]

The post MySQL ERROR: UNKNOWN COLUMN IN ‘FIELD LIST’ appeared first on Phpflow.com.

China Encryption Standards There are SM1,SM2, SM3, SM4, SM7, SM9, ...etc with China encryption standards.  With international encryption standards, we can leverage encryption libraries from publicly available source(s). For example, one of the most common and popular use encryption library is OpenSSL. 
This tutorial is written to provide steps with one of the "SSL" library having SMx (China Encryption Library) namely BabaSSL with MySQL.  It does not serve as a purpose of the only way adopting China Encryption Standards with MySQL. Applying compatible / Latest OpenSSL library from China Encryption vendor(s)/source(s) may possibly work in similar way as documented in this tutorial.
Environment (for the tutorial sharing) 1. Compute Resource (VM) 2. Operating System - Compute VM is provisioned with Oracle Linux 8 3. MySQL community with 8.0.xis installed via public yum repository 4. …

The MySQL InnoDB storage engine lives off primary keys: Records are stored physically in a B+-Tree, a balanced tree where the data pages are the leaves of the primary key B-Tree.

That implies that the data is stored physically in primary key order – records with similar primary keys are usually physically closer together than records with larger differences in primary key value. In literature, such physically ordered indexes are often called clustered indexes.

InnoDB depends on primary keys

Secondary indexes in databases need to store the indexed data together with a row address, a pointer to the full row (in InnoDB, into the primary key).

If that row address was a physical address (a disk block number or a similar pointer tied to the position of the data), growing data pages and moving them around would …

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.7, consisting of MySQL-wsrep 5.7.38 (release notes, download). This release incorporates all changes to MySQL 5.7.38, adding a synchronous option for your MySQL High Availability solutions.

In MySQL 5.7.38, we now distinguish between unset ssl_mode and ssl_mode explicitly set to “DISABLED”, because previously ssl_mode=DISABLED was ignored in the xtrabackup-v2 script.

This is a rare release where you are also meant to continue using the Galera replication library 3.37 implementing the wsrep API version 25, from the previous release, as there was no changes made to the Galera replication …

Meet the long-awaited release of dbForge tools for MySQL. We have enhanced them with a few new features that will surely come in handy.  However, the foremost thing in this update is that our tools now can run on Linux and macOS through CrossOver, a solution that helps launch Windows applications on different operating systems. […]

The post The Enhanced dbForge Tools for MySQL v9.1 Are Out! appeared first on Devart Blog.