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

Read More

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:

• Importing while you are exporting
• Remote backups
…

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 …

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:

• For obvious reasons, this year the event will not take place …

A recommendation we often give to our customers is along the lines of “archive old data” to reduce your database size. There is a tradeoff between keeping all our data online and archiving part of it to cold storage.

There could also be legal requirements to keep certain data online, or you might want to query old data occasionally without having to go through the hassle of restoring an old backup.

In this post, we will explore a very useful feature of AWS RDS/Aurora that allows us to export data to an S3 bucket and run SQL queries directly against it.

Archiving Data to S3

Let’s start by describing the steps we need to take to put our data into an S3 bucket in the required format, which is called Apache Parquet.

Amazon states the Parquet format is up to 2x faster to export and consumes up to 6x less storage in S3, compared to other text formats.

1. Create a snapshot of the database (or …

I recently learned how to use MySQL to generate MySQL statements for several admin-type Data Definition Language (DDL) SQL queries I needed to execute on the server. For many DBA’s this is likely nothing new and in fact, is a well-used tool in their kit. However, I mostly focus on developer-related tasks, so it is a fresh-in-my-mind learning experience. Continue reading for more information…

Some backstory for context

I’ve recently been porting over an existing vanilla PHP application to CodeIgniter 4. One of the new features is bootstrapping all of the necessary MySQL CREATE TABLE statements dynamically based on information parsed from a .fxl file (which is close kin to a .xml file structure-wise).

Each CREATE TABLE statement is complete with FOREIGN KEY constraint clauses to ensure data integrity between linking …

Introduction In this article, I’m going to show you the RevoGain software architecture. RevoGain is a web application that helps Revolut users calculate their stocks and crypto trading gains, and I started building it at the beginning of September 2021. Hype vs. Reality Have you ever been to a software conference and felt disconnected from all those hyped technologies that were presented so beautifully on stage? I certainly had. Whenever I came back from a software conference and looked at my project codebase, I could see a large gap between what’s being... Read More

The post RevoGain Software Architecture appeared first on Vlad Mihalcea.

If you ever had to deal with performance and/or disk space issues related to temporary tables, I bet you eventually found yourself puzzled. There are many possible scenarios depending on the type of temporary table, settings, and MySQL version used. We have observed a pretty long evolution in that matter due to a couple of reasons. One of them was the need to completely eliminate the need to use the deprecated MyISAM engine, and at the same time introduce more performant and reliable alternatives. Another set of improvements was required related to InnoDB, where it was necessary to lower the overhead of temporary tables using that engine.

For that reason, I decided to gather them in a sort of summary which may help to troubleshoot their usage. Due to vast changes between major MySQL releases, I divided the article by them.

…