For testing purposes, especially if you’re working on a project that uses any database technology to store information, you may need data to try out your project. In that case, you have two options:

• Find a good dataset (Kaggle) or,
• Use a library like Faker

Through this blog post, you will learn how to generate test data for MySQL using Faker.

Requirements Dependencies

Make sure all the dependencies are installed before creating the Python script that will generate the data for your project.

You can create a requirements.txt file with the following content:

pandas
sqlalchemy
PyMySQL
tqdm
faker

Once you have created this file, run the following command:

pip install -r requirements.txt

Or if you’re using Anaconda, create an environment.yml file:

name: percona
dependencies:
  - python=3.10
  - pandas
  - sqlalchemy …

To follow up on my previous Kubernetes articles:

• Installed in part one (see Using Percona Kubernetes Operators With K3s Part 1: Installation) and
• Have Percona Server for MySQL running (Using Percona Kubernetes Operators With K3s Part 2: Percona Server for MySQL Operator)
• Monitoring with Percona Monitoring and Management (PMM): Using Percona Kubernetes Operators With K3s Part 3: Monitoring and PMM

I would like to introduce a project which brings Kubernetes observability to the next …

Not long ago, we found a long-running query in one of our clients on Managed Services. While reviewing this long-running query, we found it was generated by a pt-archiver operation. That doesn’t sound good, right?

I was able to reproduce the issue on my test environment. In this article, I will show you how to use pt-archiver to purge data from a table using a secondary index. First, we need to have the full picture before going any deeper. Here is a representation of the table structure:

Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4000001 DEFAULT …

This week is KubeCon in Detroit, and in preparation for attending I have been polishing up my Kubernetes skills. This big rush to put software in containers and have Kubernetes run everything is getting a lot of push in the industry. Many software applications run perfectly well in ready-made packages from a container made for ephemeral consumption much like a can of Campell’s tomato soup. But generally, relational databases like permanence, stability, and a consistent presence. Databases like to run to stock caches and build statistics, so they are not great at running well after being started. But this article is the first in a series for ‘us’ database folks to learn how to keep our databases happy in a containerized world.

With the trip to the Motor City on my calendar, it was time to answer the question “What is the minimalist …

MySQL is an outstanding database for online transaction processing. With suitable hardware, it is easy to execute more than 1M queries per second and handle tens of thousands of simultaneous connections. Many of the most demanding web applications on the planet are built on MySQL. With capabilities like that, why would MySQL users need anything else?  

Well, analytic queries for starters. Analytic queries answer important business questions like finding the number of unique visitors to a website over time or figuring out how to increase online purchases. They scan large volumes of data and compute aggregates, including sums, averages, and much more complex …

Oracle releases updates to MySQL on a quarterly basis and the Release Notes for 8.0.31 arrived just before the software.  This time around there are some very interesting new features that will be handy including SQL standards support, as well as over 130 bug fixes.

The TL;DR:  Some nice new features but nothing spectacular.

• FULL is now a reserved word.
• InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded.
• The OpenSSL library for MySQL Server has been updated to version 1.1.1q.
• The optimizer has been improved so that the old ER_NOT_SUPORTED_YET is not thrown when you try something like this:
  

      ((SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2) ORDER …

As we have Kubernetes installed in part one (see Using Percona Kubernetes Operators With K3s Part 1: Installation), now we will install Percona Server for MySQL Operator into the running cluster.

I will copy some ideas from Peter’s Minukube tutorial (see Exploring MySQL on Kubernetes with Minkube).

In this case, I will use not Percona XtraDB Cluster Operator but a regular Percona Server for MySQL with Asynchronous replication.

We have recently released version 0.3.0 and it is still in the technical preview state, so we are actively looking for more feedback!

If we go with all …

A few weeks back, we faced an issue in a replication environment for a Managed Services client:

LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”

After some initial investigation, we noticed that the user in the replica didn’t exist! Was MySQL going crazy? But then the customer mentioned they had the following error in the primary before being able to execute the query successfully:

root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY …

If you watched Finding Poorly Designed Schemas and How to Fix Them you witnessed Marcos Albe use some very interesting queries. These queries let you find tables without primary keys, tables with non-integer primary keys, tables that do not use InnoDB, tables and indexes with the most latency, indexes that are 50% larger than the table, find duplicate indexes, and find unused indexes. As promised, they are below.

— Find tables without PK
SELECT t.table_schema,t.table_name,t.engine
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema=c.table_schema
AND t.table_name=c.table_name
WHERE t.table_schema NOT IN (‘mysql’, ‘information_schema’, ‘sys’, ‘performance_schema’)
AND t.table_type = ‘BASE TABLE’
GROUP BY t.table_schema,t.table_name, t.engine
HAVING …

Geo locations are a cornerstone of modern applications. Whether you’re a food delivery business or a family photographer, knowing the closest “something” to you or your clients can be a great feature.

In our ‘Scaling and Optimization’ training class for MySQL, one of the things we discuss is column types. The spatial types are only mentioned in passing, as less than 0.5% of MySQL users know of their existence (that’s a wild guess, with no factual basis). In this post, we briefly discuss the POINT type and how it can be used to calculate distances to the closest public park.

Import the data

To start off, we need a few tables and some data. The first table will hold the mapping between the zip code and its associated latitude/longitude. GeoNames has this data under the Creative Commons v3 license, …