If you have received the “Your PHP installation appears to be missing the MySQL extension which Is required by WordPress” ...
The post Your PHP installation appears to be missing the MySQL extension which Is required by WordPress appeared first on RoseHosting.
When you work with AWS Aurora you have limited admin privileges. There are some different grants for MySQL including SELECT INTO S3 and LOAD FROM S3 that replace the loss of functionality to SELECT INTO OUTFILE and mysqldump/mysqlimport using a delimited format. While I know and use lambda capabilities, I have never executed anything with INVOKE LAMDBA directly from the database.
This week I found out about INVOKE COMPREHEND (had to look that product up), and …
[Read more]I learned this week that 30+ years of Relational Database Management System (RDBMS) experience still does not prepare yourself for the disappointment of working with organizations that use a RDBMS; MySQL specifically; have a released production product, have dozens to hundreds of developers, team leaders and architects, but do not know the importance of, nor use transactions. If I was to ask this when interviewing somebody that would work with a database and the response was it is not important, or not used these days it would be a hard fail.
To use a RDBMS is to understand a very simple principle, a foundation of a transactional system. It is called ACID. Atomicity, Consistency, Isolation, Durability.
In a …
[Read more]
As a data architect I always ensure that for any database schema
change there a fully recoverable execution path.
I have generally advised to create a patch/revert process for
every change. For example, if a change adds a new column or
index to a table, a revert script would remove the respective
column or index.
The goal is to always have a defensive position for any changes.
The concept is that simple, it is not complex.
In its simplest form I use the following directory and file structure.
/schema
schema.sql
/patch
YYYYMMDDXX.sql where XX,ZZ are sequential 2 digit numbers, e.g. 01,02
YYYYMMDDZZ.sql
/revert
YYYYMMDDXX.sql This is the same file name in the revert sub-directory.
YYYYMMDDZZ.sql
At any commit or tag in configuration management it is possible
to create a current copy of the schema, i.e. use
schema.sql.
It is also possible to take the first …
In this tutorial, we will show you how to create a backup of MySQL databases on an Ubuntu 20.04 VPS, ...
The post How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04 appeared first on RoseHosting.
A common MySQL strategy to perform updates with accumulating
functions is to employ user-defined variables, using the
UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar,
mycol)) pattern.
This pattern though doesn’t play well with the optimizer (leading to non-deterministic behavior), so it has been deprecated. This left a sort of void, since the (relatively) sophisticated logic is now harder to reproduce, at least with the same simplicity.
In this article, I’ll have a look at two ways to apply such logic: using, canonically, window functions, and, a bit more creatively, using recursive CTEs.
[Read more]Another interesting feature released with MySQL 8.0 is full support for functional indexes.
Although this is not a strictly new concept in the MySQL world (indexed generated columns provided the same functionality), I find it worth reviewing, through some applications, notes and considerations.
All in all, I’m not 100% bought into functional indexes (as opposed to indexed generated columns); I’ll elaborate on this over the course of the article.
As a natural fit, generated columns are included in the article; additionally, some constructs build on my previous article, in relation to the subject of CTEs.
Updated on 12/Mar/2020: Found another bug.
Contents:
A long-time missing (and missed) functionality in MySQL, is sequences/ranges.
As of MySQL 8.0, this functionality is still not supported in a general sense, however, it’s now possible to generate a sequence to be used within a single query.
In this article, I’ll give a brief introduction to CTEs, and
explain how to build different sequence generators; additionally,
I’ll introduce the new (cool) MySQL 8.0 query hint
SET_VAR, and a pinch of virtual columns and
functional indexes (“functional key parts”, another MySQL 8.0
feature).
Contents:
[Read more]In this post I’ll expand on the subject of my MySQL pre-FOSDEM talk: what dbadmins need to know and do, when upgrading from MySQL 5.7 to 8.0.
I’ve already published two posts on two specific issues; in this article, I’ll give the complete picture.
As usual, I’ll use this post to introduce tooling concepts that may be useful in generic system administration.
The presentation code is hosted on a GitHub repository (including the …
[Read more]I posted a few days ago some initial thoughts on the the MySQL native cloning functionality. Overall this looks good and I need to spend time to test further. I’m here in San Francisco ahead of Oracle Open World which starts today. As is usual with trips like this jet lag wakes you up rather … Continue reading MySQL Cloning: more thoughts
The post MySQL Cloning: more thoughts first appeared on Simon J Mudd's Blog.