The latest from the MySQL community

In this blog, we will explore databases on containers. We will use Docker as a container engine tool and Percona Server for MySQL as a database administration tool. Both are open source tools.

MySQL is a relational database management system that stores data on disk. Percona Server for MySQL is a fork of MYSQL, providing much more advanced features. To run it correctly, we need to know volumes because we want to “persist” the data, the most important thing in databases.

Running a single Percona Server for MySQL container

First, let’s create a container without volumes:

Figure 1: From Percona Server for MySQL image to a running container in Docker

The following command will create a container called percona-server-1, where we can create databases and add some data.

Mobile World Congress (MWC), the world’s leading event is all set to unite the global mobile and telecoms industry in Barcelona, Spain from 27 February – 2 March 2023. In this framework, Codership is glad to announce that it has been selected by the European Innovation Council to join the EIC Pavilion. The 20 most promising EIC-funded SMEs, startups and scale ups have an unprecedented opportunity to impress the international audience with their game-changing innovations and establish business partnerships with like-minded counterparts.

The EIC Pavilion will feature a wide variety of extraordinary innovations in crucial sectors, such as Artificial Intelligence, Robotics, Machine Learning and IoT, among others. From interactive sessions and expert panel …

We are seeing increasing requests for migrations from MariaDB to MySQL. MariaDB has significantly diverged, and is no longer drop-in compatible with MySQL. To migrate from MariaDB to MySQL, an in-place upgrade is not possible. A logical dump and load will be necessary. In this post I explain the process of such migration and the […]

When it comes to writing database integration tests with Spring Boot, there are two options: an in-memory database or Testcontaienrs. As we already covered Testing Spring Data Repositories with H2 [...]

The post Spring Boot MySQL integration tests with Testcontainers appeared first on Geeky Hacker.

MySQL Database Service supports migrations from a live on-premises MySQL 8.0 database to a MySQL DB system on Oracle Cloud Infrastructure (OCI) in almost real-time using replication. Migrating the data in real time helps you achieve minimal downtime and verify that your live production data is all good.  MySQL Database Service is a fully-managed service, powered by the integrated […]

MySQL Database Service supports migrations from a live on-premises MySQL 8.0 database to a MySQL DB system on Oracle Cloud Infrastructure (OCI) in almost real-time using replication.

Introduction/TLDR:

We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools. We would like to get feedback from the Community about the different approaches for achieving this.

The problem:

Running EXPLAIN is a great way to understand how complex SQL statements are executed. So it is natural that monitoring and query analysis tools utilize EXPLAIN for these purposes.

However, there is a problem for cases when INSERT/UPDATE/DELETE statements need to be explained. Running EXPLAIN for these statements, a read-only operation, requires the same privileges as running the original statements …

Given a table named tbl with one million entries, we want to select a random row from this table, fast. Our table definition looks like this:

create table tbl (
    id INTEGER NOT NULL,
    d VARCHAR(200) NOT NULL,
    INDEX(id)
);

Dense id space

We can generate some test data using a recursive CTE:

mysql> set cte_max_recursion_depth = 100000;
mysql> insert into tbl 
    -> with recursive c(n, u) as (
    ->   select 1, uuid() 
    -> union all
    ->   select n+1, uuid() from c where n < 100000
    -> ) select * from c ;

The Recursive CTE will generate 100k pairs of (number, uuid()). The initial row is defined in the upper row of the UNION, each subsequent row builds recursively on top of that, by simply counting …

Given a table named tbl with one million entries, we want to select a random row from this table, fast. Our table definition looks like this:

create table tbl (
 id INTEGER NOT NULL,
 d VARCHAR(200) NOT NULL,
 INDEX(id)
);

Dense id space

We can generate some test data using a recursive CTE:

mysql> set cte_max_recursion_depth = 100000;
mysql> insert into tbl
 -> with recursive c(n, u) as (
 -> select 1, uuid()
 -> union all
 -> select n+1, uuid() from c where n < 100000
 -> ) select * from c ;

The Recursive CTE will generate 100k pairs of (number, uuid()). The initial row is defined in the upper row of the UNION, each subsequent row builds recursively on top of that, by simply counting up.

Since we generate …

2023 is flying by and so are the weekly OpenLampTech newsletter issues. I cannot thank you enough for reading the weekly newsletter publication. But, I’ll try anyway: thanks so much! Now to this week’s issue…

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 #66, we have content on:

• SQL JSON columns
• Apache mod_rewrite
• MySQL Document Store
• WordPress local development cheatsheet
• And a whole lot more

If you’re not already, be sure and …