We know that SQL statement compatibility can change with major database version upgrades and that you should adequately test for them. But what about minor version upgrades?

It is dangerous to assume that your existing SQL statements work with a minor update, especially when using an augmented version of an open-source database such as a cloud provider that may not be as transparent about all changes.

While I have always found reading the release notes an important step in architectural principles over the decades, many organizations skip over this principle and get caught off guard when there are no dedicated DBAs and architects in the engineering workforce.

Real-world examples of minor version upgrade issues

Here are two real-world situations common in the AWS RDS ecosystem using MySQL.

1. You are an organization that uses RDS Aurora MySQL for its production systems, and you upgrade one minor version …

MySQL warnings are an anti-pattern when it comes to maintaining data integrity. When the information retrieved from a database does not match what was entered, and this is not identified immediately, this can be permanently lost.

MySQL by default for several decades until the most recent versions enabled you to insert incorrect data, or insert data that was then truncated, or other patterns that resulted in failed data integrity. Very few applications considered handling warnings as errors, and there is a generation of software products that have never informed the developers that warnings were occurring.

The most simplest example is:

CREATE SCHEMA IF NOT EXISTS warnings;
USE warnings;

CREATE TABLE short_name(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  PRIMARY KEY(id)
);

INSERT INTO short_name (name) VALUES ('This name is too long and will get truncated');
ERROR 1406 (22001): Data too long for …

I continue to dig up and share this simple approach for production data masking via SQL to create testing data sets. Time to codify it into a post.

Rather than generating a set of names and data from tools such as Mockaroo, it is more practical to use actual data for a variety of testing reasons.

The SQL below is a self-explanatory approach of removing Personal Identifiable Information (PII), but keeping data relevant. I use this approach for a number of reasons.

• We are using production data rather than synthetic data. Data volume, distribution, and additional column values are realistic. This is a subset of an example, but dates and locations are therefore realistic
• Indexes (and unique indexes) still work, and distribution across the index is adequate for searching. Technically the index …

CSV or SQL? SQL or CSV? How about together? Without a doubt, CSV’s are one of the most common and familiar data interchange formats. Importing and exporting CSV data into or out of an SQL database is a staple process in most every data workflow. I’ve written numerous blog posts on both importing and exporting CSV data in an SQL environment. In this post, I am including all of these specific posts (as of the time of writing) in one easy-to-read and centralized location…

Image by xresch from  …

In the last post I covered some of the many reasons to migrate data from a spreadsheet to a database and now it is time to show how to do just that.  Moving data from a spreadsheet into MySQL can actually be pretty simple in many cases. Now I will not mislead you by telling you that all cases are easy but there is an easy way to make this migration.  And I highly recommend the following process when possible as it can save lots of time. More painful migrations will be covered in the future but this post is my favorite 'fast and easy' process.

This is a guide to taking data from a spreadsheet and easily moving that data into a MySQL database instance. The trick is the Import Wizard in MySQL Workbench that does a lot of the detail work for you. In a future post we will go into what you have to do when you are not …

CSV imports with MySQL Workbench, is super simple. Since CSV’s are probably the most common data interchange format, it goes without saying that importing CSV data into MySQL is a staple task for all DBA’s and Developers. Continue reading to learn how easy it is using MySQL Workbench…

Image by OpenClipart-Vectors from Pixabay

Self-Promotion:

If you enjoy the content written here, by all means, …

If you use SQL on a regular basis, then you are well aware that Window Functions are powerful. They allow us to simplify queries that would otherwise be quite the mess. We can provide meaningful insight across rows of data without collapsing the results into a single value. I have written numerous blog posts on Window Functions, many here recently. I decided to make this blog post a compilation of all the Window Function posts I have written, providing a one-stop source for any readers interested in learning more about Window Functions…

Image by Free-Photos from …

Top n Window Function queries over a specific subset of data are common in analysis and reporting requirements. Luckily, in MySQL, there are Window functions we can use for this type of query. To be quite honest, you don’t necessarily need Window Functions. You can retrieve those top 3 (or whatever) types of results with a regular SQL query. But, since we have those powerful Window Functions, why not use them? My thoughts exactly! Besides, no one wants a spaghetti code mess of SQL to try and understand. Not to mention, Window functions are often better optimized for querying larger data sets. Continue reading and see example queries for more understanding…

Image by …

Rolling sum and average query results are possible by combining the aggregate functions SUM() or AVG() with the OVER() clause, making for powerful analytic queries. I recently learned how to compute a rolling average or sum of values by using the Windowing option of the OVER() clause, applying the concepts to a data set I am familiar with. I wanted to share my learning with any readers who might be interested…

Image by Steve Buissinne from …

The Window Ranking functions: ROW_NUMBER(), RANK(), and DENSE_RANK() each rank rows with an increasing integer value. I wrote a previous blog post, ROW_NUMBER() Window Function – find duplicate values, where I covered how the ROW_NUMBER() window function can be used to target any duplicate rows, with the use of the PARTITION BY clause in the OVER() clause. In this post, I cover the differences between RANK(), and DENSE_RANK() in handling any ties according to the sorting performed by the ORDER BY clause with regards to the assigned increasing integer. Continue reading and see examples…

…