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. …

We know that when filtering text data in a MySQL WHERE clause conditional, strings are placed in single quotes. In this blog post, learn how to filter numeric data in a MySQL WHERE clause conditional…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by Gerd Altmann from Pixabay 

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 …

Azure Database for MySQL

Introduction

This blog is the third chapter related to deploying a MySQL infrastructure on the Azure cloud. In addition to performance, we should indeed also consider backup and restore capabilities. The objective of this blog is to present the main backup and restore possibilities offered by Azure through a simple example and show a second backup/restore possibility using MySQL Shell dump utilities.

Backup window and mechanism

Flexible Azure for MySQL generates by default a 7 days server backup retention period. This retention period can be extended up to 35 days or shorten to 1 day. In addition we can decide if you want to have a Geo Redundant backup storage. By default the backups are locally redundant.

It’s important to understand that Azure makes …

The OpenLampTech publication is entering its 9th month of publishing and I have you to thank. OpenLampTech continues to grow. Thank you! We have several great articles this week. Enjoy and share with others.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #40 we have articles covering:

• The SQL NULL
• Custom helper functions in CodeIgniter 4
• PHP built-in web server
• MySQL command line
• And much much more

Be sure and head over to the …

We recently migrated some of EverSQL's workload from AWS RDS MySQL to Aurora MySQL. In this post I'll share several options I explored, and the issues I ran into when using each of them, and specifically AWS DMS, so hopefully you could avoid similar issues when going through the migration process.

Potential solutions for migrating RDS MySQL to Aurora MySQL

Naturally, I first started with AWS's documentation, which states you can migrate RDS MySQL to Aurora MySQL by creating an Aurora read replicate and promoting it after replication is done. Unfortunately, that solution wasn't viable in our case, as the RDS instance was already upgraded to MySQL 8.0.28, while Aurora MySQL only supports …

Question on the Libera/#mysql IRC channel:

Is there a way to split a simple select into multiple returned rows? For example, select 1, 2, 3 to be returned as rows?

This is actually asking for a table literal notation. I know of four ways to construct a table literal in MySQL:

UNION ALL

The oldest way to construct a table literal in any SQL that supports UNION is the UNION ALL construct. Write SELECT statements to return literal rows, and add them together to a table using UNION ALL:

mysql> select i from (
    ->   select 1 as i union all
    ->   select 2 as i union all
    ->   select 3 as i
    -> ) as t;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

This has always worked, even on the oldest versions …