During many decision-making phases in programming code
(conditional logic), there are times execution depends on
several different factors. Multiple conditional tests
are powerful and constraining, oftentimes requiring more than one
test to be passed in order for program flow to proceed. For MySQL
(and standard SQL in general) the CASE
expression is
used for IF
/THEN
/ELSE
conditional logic. The post, MySQL Simple CASE Expression – with
examples, covered Simple CASE
queries which
are essentially equality tests. MySQL Simple CASE
is
but one variant of 2, with the other being a MySQL
Searched CASE
Expression. A MySQL Searched
CASE
Expression can have multiple
conditional tests in each WHEN
…
Just last week we had a Galera Cluster for MySQL streaming replication feature deep dive webinar, by the person that implemented it – Seppo Jaakola. In fact, if you haven’t already read the entire story, we encourage you to read: Galera Streaming Replication: Too Big A Bite Can Choke Your Cluster. We also have the 60 minute video recording for reference.
A huge number of Galera Cluster users have always complained about having issues with large transactions (where writes hang but reads are OK), and we are certain that the correct usage of streaming replication is what will ease that burden. What constitutes a large transaction? Typically when you make big number of …
[Read more]
Programming logic is foundational in any application or piece of
software. Without it, software wouldn’t really do much of
anything. Everything happens off of choice. In the end, some
truthy or falsy value is what makes stuff work.
For IF
/THEN
/ELSE
logic in
standard SQL, there is the CASE
expression. There
are 2 variations of the CASE
Expression: Simple and
Searched. In this post, I cover the Simple MySQL
CASE
expression with example queries…
Image by Nika Akin from …
[Read more]
Be it running reports or displaying data in some other
visualization, SQL SELECT
column expressions should
be meaningful and understandable. To provide those valuable query
results, SQL Developers, use a multitude of available functions,
adjacent columns, or other means not readily apparent to
end-users. All that being said, the column names often suffer the
most as far as readability is concerned, taking on long function
call names or other combined expressions. But, as luck would be
on our side, there is an easy fix and that is aliasing columns
using the AS
keyword. Although AS
is
optional – in this particular context – I err on the side of
readability and use it when aliasing SELECT
column
expressions.
A significant improvement has come to Galera Cluster 4 for MySQL, in the form of streaming replication. When you are dealing with large (greater than 2GB in size) or long running transactions, previous releases of Galera Cluster would reject the transaction; in the current release transactions can be split into fragmented chunks and those rows can be replicated in chunks to the other nodes even before commit. This is the feature that will remove you from all cluster stall issues due to large transaction processing!
While disabled by default, you can use it and configure it dynamically, and there is also the ability to monitor streaming replication progress. Come to this webinar to learn from the primary author, Seppo Jaakola, on why he implemented the feature, how you can use it, finding the optimal fragment size based on workload, and the design trade-offs for the initial release in where the focus was more on functionality over …
[Read more]Any of you who read my blog posts here regularly know I am passionate about SQL, PHP, and Back-end Development. I consider writing an integral part of my brand and growth. Continue reading and learn more about what’s on my mind lately with writing and code…
Image by Arek Socha from Pixabay
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. …
[Read more]Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.6, 5.7 and 8.0, consisting of MySQL-wsrep 5.6.51 (release notes, download), 5.7.33 (release notes, download) and 8.0.23 (release notes, download) with Galera replication library 3.33 ( …
[Read more]
Each SQL dialect is different in some way, shape, form, or
fashion from the next flavor. Some dialects have this
function, while others have that function. In this post, I cover
porting over Oracle SQL to MySQL in order to count the
number of occurrences of a specific weekday found in the current
given month (at the time of writing) purely as a learning
exercise focused on MySQL DATE
functions and the
WITH
clause…
Image by tigerlily713 from Pixabay
…
[Read more]
I recently published a blog post over on Medium
about the differences in 2 versions of the MySQL
COUNT()
aggregate function: COUNT(*)
and COUNT(column_name or expression)
. I wanted to
share the post here with any readers who may be interested so
continue reading for more on this post…
Image by anncapictures from Pixabay
Self-Promotion:
If you enjoy the content written here, by …
[Read more]A question has cropped up recently: how does one install Galera Cluster 4 with MySQL 8 on Ubuntu 20.04 LTS (Focal Fossa)? This quick guide will get you started.
Install 3 Galera Cluster nodes with the base OS being Ubuntu 20.04. Remember to run apt update and apt upgrade.
Once this is done, edit: /etc/apt/sources.list.d/galera.list and add the following lines:
deb https://releases.galeracluster.com/galera-4/ubuntu focal main
deb https://releases.galeracluster.com/mysql-wsrep-8.0/ubuntu focal main
Once that is done, don’t forget to also pin the preference to the Galera Cluster repository. To do this, edit: /etc/apt/preferences.d/galera.pref and have it read:
# Prefer the Codership repository
Package: *
Pin: origin releases.galeracluster.com
Pin-Priority: 1001
Once you are done, run apt update yet again. Now you are ready to install Galera Cluster 4 with MySQL 8, just by …
[Read more]