Showing entries 1 to 10 of 1243
10 Older Entries »
Displaying posts with tag: Databases (reset)
MySQL SHOW USERS: List All Users in a MySQL Database

Today we are going to guide you on how to show all users in the MySQL users Database. A common ...

Read more

The post MySQL SHOW USERS: List All Users in a MySQL Database appeared first on RoseHosting.

How to Fix WordPress Error Missing MySQL Extension Problem

If you have received the “Your PHP installation appears to be missing the MySQL extension which Is required by WordPress” ...

Read more

The post How to Fix WordPress Error Missing MySQL Extension Problem appeared first on RoseHosting.

#WDILTW – What can I run from my AWS Aurora database

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]
#WDILTW – To use a RDBMS is to use a transaction

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]
Defensive Data Techniques

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.

        YYYYMMDDXX.sql     where XX,ZZ are sequential 2 digit numbers, e.g. 01,02
       YYYYMMDDXX.sql   This is the same file name in the revert sub-directory.

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 …

[Read more]
How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04

In this tutorial, we will show you how to create a backup of MySQL databases on an Ubuntu 20.04 VPS, ...

Read more

The post How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04 appeared first on RoseHosting.

Modern approaches to replacing accumulation user-defined variable hacks, via MySQL 8.0 Window functions and CTEs

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]
Storage and Indexed access of denormalized columns (arrays) on MySQL 8.0, via multi-valued indexes

Another “missing and missed” functionality in MySQL is a data type for arrays.

While MySQL is not there yet, it’s now possible to cover a significant use case: storing denormalized columns (or arrays in general), and accessing them via index.

In this article I’ll give some context about denormalized data and indexes, including the workaround for such functionality on MySQL 5.7, and describe how this is (rather) cleanly accomplished on MySQL 8.0.

[Read more]
An introduction to Functional indexes in MySQL 8.0, and their gotchas

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.


[Read more]
Generating sequences/ranges, via MySQL 8.0’s Common Table Expressions (CTEs)

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


[Read more]
Showing entries 1 to 10 of 1243
10 Older Entries »