I recently needed to add multiple columns to an existing table to store summary data calculations and wondered if I could do it in one MySQL ALTER TABLE statement. Turns out you can. And, it’s super simple. Convenient too. Continue reading and learn what I learned…
Image by Gerd Altmann from Pixabay
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you …
[Read more]The utf8mb4 character set is the new default as of MySQL 8.0, and this change neither affects existing data nor forces any upgrades.
Migration to utf8mb4 has many advantages including:
Still, you may wonder how migration affects your existing data. This blog covers multiple aspects of it.
Storage Requirements
As the name suggests, the maximum number of bytes that one character can take with character set utf8mb4 is four bytes. This is larger than the requirements for utf8mb3 which takes three bytes and many other MySQL character sets.
Fortunately, utf8mb3 is a subset of …
[Read more]I’ve once again published a massive issue of the OpenLampTech newsletter, the newsletter for PHP/MySQL developers. If you’re looking to learn more about PHP and MySQL, you’ve come to the right place…
Image by Clker-Free-Vector-Images from Pixabay
The Newsletter for PHP and MySQL Developers
Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the …
[Read more]On 22nd March 2022 08:43 UTC, we became aware of the issue affecting Okta, a third-party identity provider that Percona uses for https://id.percona.com. Initially, there was no statement from Okta, so our Security Operations team reviewed the information available from LAPSUS$ and other public sources.
Based on the public information available about the issue, we evaluated the potential exposure to Percona and determined that the impact was minimal. Percona uses Okta integrations so https://id.percona.com can be used to authenticate against Percona’s deployments of:
Integrations of …
[Read more]For a very long part of its history, MySQL replication has been limited in terms of performance. Because there was no way of knowing if transactions or updates were independent, the updates had to be executed on a replica following the exact same sequence of operations as on the primary server. The only way to guarantee the same sequence of operations on the replica was to use a single thread. In this post, we’ll do a dive into the MySQL multi-threaded replication (MTR) implementation and explore the available tuning options.
MTR is the culmination of the evolution in the development of parallel replication which followed the path:
We’ll leave aside, for now, the recent dependency tracking feature.
Context
Before we discuss the multi-threaded implementation, let’s review in …
[Read more]In MySQL, the @@sql_mode parameter should generally use ONLY_FULL_GROUP_BY. If it doesn’t include it and you don’t have the ability to change the database parameters, you can use a MySQL PSM (Persistent Stored Module), like:
Create the set_full_group_by procedure:
-- Drop procedure conditionally on whether it exists already.
DROP PROCEDURE IF EXISTS set_full_group_by;
-- Reset delimter to allow semicolons to terminate statements.
DELIMITER $$
-- Create a procedure to verify and set connection parameter.
CREATE PROCEDURE set_full_group_by()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'Set connection parameter when not set.'
BEGIN
/* Check whether full group by is set in the connection and
if unset, set it in the scope of the connection. */
IF NOT EXISTS
(SELECT NULL
WHERE REGEXP_LIKE(@@SQL_MODE,'ONLY_FULL_GROUP_BY'))
THEN
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
END IF; …[Read more]
As part of my ongoing series around MySQL 8 user administration, I’d like to cover one of the new features introduced in MySQL 8.0.27 – multi-factor authentication. In order to establish identity, multi-factor authentication (MFA) is the use of multiple authentication values (factors) during the MySQL authentication process.
Introduction
MFA provides greater security compared to a single-factor authentication method, which has historically been based on simple methods such as password authentication. With MFA, additional authentication methods are enabled, such as requiring multiple passwords, or with devices such as smart cards, security keys, or biometric readers.
As of MySQL 8.0.27, it is now possible to require up to three authentication values to establish identity. In addition to the more common 2FA (two-factor authentication), MySQL can now also support 3FA (three-factor authentication) to complement the …
[Read more]This blog post is an excerpt from premium MySQL Beginner content I am creating to help anyone learn how to use MySQL. Thank you for reading and please do provide feedback.
Image by Willi Heidelbach from Pixabay
You may also be interested in the blog post, MySQL Beginners Series — The SELECT statement, which is a primer for this article.
Limit …
[Read more]In keeping with my MySQL 8 user administration and security theme, I’d like to discuss the password verification-required policy introduced in MySQL 8.0.13. With this feature, it is possible to require that attempts to change an account password be verified by specifying the existing current password to be replaced.
Introduction
The password verification-required policy enables DBAs to prevent users from changing a password without proving that they know the current password. When would this happen? Such changes could occur if a user walked away from a terminal session without logging out, and a malicious user uses the open session to change the original user’s MySQL password. As you can imagine, this could have disastrous consequences:
When MySQL client connects to the server it uses one of the authentication plugins. On the server-side, plugins are located in the directory, specified by the option plugin-dir that defaults to
BASEDIR/lib/plugin
where
BASEDIR
is the base directory of your MySQL installation. This perfectly works whenever you install MySQL using package manager, or from the Linux tarball.
However, authentication plugins should be also loaded on the client-side. If you installed MySQL into the custom location, you may end up with an error when trying to connect using any plugin requiring a separate client library.
For example, let’s set the …
[Read more]