AWS has just announced the general availability of Aurora MySQL 8 compatibility (known as Aurora Version 3). This is long awaited addition to RDS MySQL 8 and provides many of the new features that can be found in the open-source MySQL 8 community version.
For those unfamiliar with Amazon Aurora my Understanding AWS RDS Aurora Capabilities presentation from Percona Live 2021 provides a great introduction of the benefits of this managed service.
There is a lot to digest and the …
[Read more]
MySQL 8.0.27 introduced a new variable to
control the maximum of parallel threads InnoDB can use for
creating (sorting and building) secondary indexes: innodb_ddl_threads.
This new variable is coupled with another new variable:
innodb_ddl_buffer_size.
If you have fast storage and multiple CPU cores, tuning these variables can speed up secondary index creation.
For this example, I used the airportdb database, and I added a
secondary index to the largest table, booking.
Let’s start with the default settings:
SQL alter table booking …[Read more]
Working on a real case scenario in a five node Percona XtraDB Cluster (PXC), we were forced to use wsrep_sync_wait = 1, because the app does reads-after-write and we send reads to all the nodes. We had the idea to leave some nodes in DESYNC mode to reduce the flow control messages during peak load and expected to have a steadier write throughput keeping the read consistency.
We decided to test Perconas’s new PXC Scheduler Handler which is an application that manages integration between ProxySQL and Galera/PXC (the scope is to maintain the ProxySQL mysql_server table, if a negative scenario occurs, like: failures, service degradation, and maintenance). However, we realized that when a node is in DESYNC mode, it is kicked out of the read hostgroup. That is …
[Read more]In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.
Below is my dropIndexIfExists stored procedure:
-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;
-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$
-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name VARCHAR(64)
, pv_index_name VARCHAR(64))
BEGIN
/* Declare a local variable for the SQL statement. */
DECLARE stmt VARCHAR(1024);
/* Set a session variable with two parameter markers. */
SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);
/* Check if the constraint exists. …[Read more]
I was working through some tutorials for my students and noticed that there was a change in how a WHERE clause must be written against the information_schema.table_constraints table. It might have been made in an earlier release, I actually hadn’t checked it since 2014 when I wrote this early post on capturing MySQL Foreign Keys.
You could use the following WHERE case insensitive clause:
WHERE tc.constraint_type = 'foreign key'
Now, you must use a case sensitive WHERE clause:
WHERE tc.constraint_type = 'FOREIGN KEY'
I’d love to know why but I can’t seem to find a note on the change. As always, I hope this helps those looking for an answer.
A year ago, I blogged about An Unprivileged User can Crash your MySQL Server. At the time, I presented how to protect yourself against this problem without explaining how to generate a crash. In this post, I am revisiting this vulnerability, not giving the exploit yet, but presenting the fix. Also, because the default configuration of Group Replication in 5.7 is still vulnerable
MySQL is an open-source relational database management system (RDBMS), it’s widely used and part of the popular LAMP/LEMP stacks. The ...
The post How to Install MySQL Database on Ubuntu 20.04 appeared first on RoseHosting.
The new MyDumper 0.11.3 version, which includes many new features and bug fixes, is now available. You can download the code from here.
We are very proud to announce that we were able to achieve the two main objectives for the milestone ZSTD and Stream support! We added four packages with ZSTD support because not all the distributions have support for v1.4 or higher. Package libzstd is required to use ZSTD compression. ZSTD Bullseye package is only available with libraries for Percona Server for MySQL 8.0. There are two main use cases for the Stream functionality:
Issue #3 of OpenLamp.tech, the newsletter for PHP/MySQL developers, is now available. This is the deep-dive/featured piece edition, 5 MySQL String Functions You Should Know, and you don’t want to miss it…
Image by Harish Sharma from Pixabay
Deep Dive – Feature Edition
On a monthly basis, in addition to the weekly curated content, OpenLamp.tech …
[Read more]We received today the notification for FOSDEM’s organization that the MySQL DevRoom is accepted for the 2022’s edition !
The MySQL DevRoom will take place on February 6th, 2022. It’s a Sunday. Like previous edition, FOSDEM will be an online event.
Due to the very short deadlines, this year an external committee cannot be created to validate, sort and vote for the proposed talks.
We will propose the best agenda respecting the ideas and precepts that have made our MySQL DevRoom one of the most popular room of FOSDEM for so many years.
The MySQL Developer room focuses on MySQL and its Ecosystem.
The call for papers for the MySQL DevRoom is now officially open !
Same rules as last year:
