The latest from the MySQL community

Your Amazon Aurora MySQL cluster looks healthy. Reads and writes succeed and every metric is green. Then a routine CREATE INDEX fails with error 63955, and from then on no index that includes a virtual column can be created on the cluster until you rebuild its databases.

Error Code: 63955. 
Index cannot be created because virtual index ID limit (4294967295) was reached.

• What it means: the cluster has used up the internal IDs Aurora MySQL uses when it evaluates indexes that contain a virtual column. This error does not affect existing data or ordinary indexes, but new indexes on VIRTUAL generated columns, functional indexes, and multi-valued indexes fail.
• What helps short term: converting the generated column in the failing index from VIRTUAL to STORED, so that the index no longer contains a …

Welcome back to Village News, our curated roundup of MySQL and database news. This week, PostgreSQL shipped updates fixing 52 security vulnerabilities and retired PostgreSQL 14, and the FOSDEM M-Ecosystem devroom opened its call for participation.

If you want to get these updates, just subscribe to the blog.

Enjoy!

MySQL News

Note: Aggregated MySQL news can be found at Planet for MySQL Community and Planet MySQL (Oracle curated)

Just Because You Can, Doesn’t Mean You Should: Re-evaluating Galera Node Weights
…

We all know that when using a cluster based on Galera (Codership) such as Percona XtraDB Cluster (PXC), it is recommended to deploy an odd number of nodes.

The generic answer to this recommendation is that otherwise the risk of having an inconsistent cluster (like split-brain) is higher.  (see Get started with Percona XtraDB Cluster – Percona XtraDB Cluster ) 

As a reminder, when split-brain occurs, a cluster will stop accepting queries, which is what a safe solution should do while waiting for the DBA to review and decide which of the two nodes is the leader. I do not even want to describe the infamous wsrep_provider_options=”pc.ignore_sb = yes”, which has been the cause of many, many nights spent trying to …

MySQL's LAG() window function helps you compare sales totals across rows and calculate changes between consecutive records. See how to retrieve previous row values, order sales data by date, and calculate sales differences using the LAG() function in MySQL: https://dev.mysql.com/doc/refman/26.10/en/window-function-descriptions.html#function_lag

It’s Thursday afternoon, and a developer has an idea.

Nothing complicated. They are working on a new application feature and want to test an idea before committing to a larger implementation. A quick prototype is already running locally. So far, so good. Next, they want to deploy the application to a shared environment so their colleagues can try it out. The application needs a database too, but it needs to be accessible from the shared environment, not just their laptop. And that’s where things get more complicated.

Now they need to submit a request to the infrastructure team, wait for an environment to be provisioned, or spend the afternoon working through Kubernetes configurations. What should have been a quick experiment becomes another task on somebody else’s backlog.

This is a common problem for engineering teams. Getting a database running in a shared environment shouldn’t require extensive coordination or …

Shopify, GitHub, and Uber run on MySQL, yet, as Dominic Preuss puts it, the hype, innovation, and investment historically went to PostgreSQL. Inside the Silicon Mind published Firas Sozan's interview with Dominic, co-founder and CEO of VillageSQL, about why that happened, what Oracle is doing to address it, and how VillageSQL plans to bring innovation back to MySQL.

Dominic covers how MySQL went from a Swedish startup to Oracle, why Oracle is now opening MySQL development to the community, including AWS and Google, and why he started a company on MySQL when everyone was running in the direction of PostgreSQL.

Watch more VillageSQL talks on the Talks page, or get started at villagesql.com.

When an Amazon Aurora MySQL-Compatible Edition instance runs critically low on memory, the operating system terminates the mysql process. This drops every connection, rolls back in-flight transactions, and triggers a cluster restart. The instinctive fixes (move to a larger instance, or lower max_connections) treat the symptom, not the underlying memory pressure your workload generates.

Aurora has long included out-of-memory (OOM) avoidance that watches system memory and takes recovery actions before the OS steps in. On Aurora MySQL version 3, use the aurora_oom_response parameter to configure these actions; Aurora provides sensible defaults that you can customize. Aurora MySQL version 8.4 adds a new instance-level …

When an Amazon Aurora MySQL-Compatible Edition or Amazon Relational Database Service (Amazon RDS) for MySQL database slows down, it can be hard to tell why. CPU graphs and slow-query logs rarely tell the whole story. The more direct answer usually lies in wait events. A wait event tells you what your sessions are waiting for: reading from storage, redo log flushes, acquiring an internal lock, or waiting for CPU time. Amazon CloudWatch Database Insights surfaces this data as DB load, so you can quickly see whether a database is constrained and, if so, on what.

Earlier MySQL wait-event guidance relied on the Performance Insights dashboard. Our 2018 post …

Omar shares how he got started with MySQL, his experience at the contributor summit, and his enthusiasm for replication performance, extensibility, and data interoperability.