Category: Article

Gabi’s Office Hours a.k.a. the Hallway Track

Office Hours featuring gabidavila
Book your time on gabi.tips/slots

Since travel to deliver content and awareness is being held for a while (COVID-19), I am missing the interaction I would have with people at random times during a conference, the exchange of ideas and impromptu problem solving.

The hallway track of a conference has always been my favorite part: the networking, exchanging ideas, getting feedback about products, all of that is harder to replicate without talks and social the setting of a conference.

I decided to experiment and try to find new ways to interact with “my” audience, the idea of Office Hours came, which originally is not mine, however I like the fact that I can have a technical conversation with someone that has questions about databases (not just about Cloud SQL) and also provide a window of opportunity for you to give me feedback on our products.

Examples of things we can talk about:

  • Query Performance
  • Best Practices for Migrations
  • Where to store your data
  • Should I put my Server on Kubernetes?
  • How do I migrate my data to the cloud?

The pilot started with 30 min sessions with the availability to talk to up to 10 people weekly (5h/week). However I think it is more productive a change to up to 3h/week and talking to 9 people in 20 min slots in more timezones.

Click here to book your time: gabi.tips/slots.

Tips for a good appointment:

  • Write down your questions and add it to the booking tool.
  • If a lengthy context is needed to understand your problem, please add the information in the booking tool, however do not send to me:
    • PII – Personal Identifiable Information
    • Your SQL Dump
    • Your intellectual property
    • No database credentials
  • Have defined scope of what you want to talk about, I can’t solve everything in 20 minutes.
  • Do not double book, other people should also take advantage of this

Disclaimers

This is not a guaranteed consultancy agreement, this is just people talking informally about tech problems and possible solutions, information shared and explained are guides, you are responsible for weighing your options and if any advice is executed, the outcome is your responsibility.

Your SQL IS NOT JavaScript (neither PHP)

IS NULL, IS NOT NULL, truthiness among other assertions on MySQL and PostgreSQL

People like to complain about JavaScript, how can one thing be equal to the other, i.e. null == undefined evaluates to true unless you use the triple equals ===. Other dynamic typed languages have its peculiarities, such as Ruby where 0 == true evaluates to true, the reason for Ruby is that considers 0 as a value and any value evaluates to true.

How about SQL? The answer is… it depends.

Which database are you using?

Some may be more forgiving, like MySQL doing casts for you all over the place, or more strict like PostgreSQL where you can only compare the truthiness of something of the same type.

Assertion MySQL PostgreSQL
('A' = TRUE) IS TRUE 0 invalid input syntax for type boolean: “A”
('A' IS TRUE) IS TRUE 0 invalid input syntax for type boolean: “A”
(1 = TRUE) IS TRUE 1 operator does not exist: integer = boolean
(1 IS TRUE) IS TRUE 1 argument of IS TRUE must be type boolean, not type integer
('1' IS TRUE) IS TRUE 1 1
('0' IS FALSE) IS TRUE 1 1
(1 = '1') IS TRUE 1 1
(0 = '0') IS TRUE 1 1

Beyond the implications of wrong type comparison, as you can see on lines 1 through 4 where MySQL evaluates values as true or false, you should also worry about what the fact of a column being NULL may imply when comparing two columns.

Suddenly knowing if it is true or false doesn’t matter because you cornered yourself with a third possible value: NULL. By definition NULL is not a value neither a state, it should be considered “garbage”, and no column where you know its value and type would be garbage.

In this front both databases operate the same way:

Assertion SQL
(NULL = NULL) IS TRUE 0
(NULL = NULL) IS FALSE 0
(NULL IS NULL) IS TRUE 1
(0 = NULL) IS NULL 1
(1 = NULL) IS NULL 1
('A' = NULL) IS NULL 1
(TRUE = NULL) IS NULL 1
(FALSE = NULL) IS NULL 1
(0 IS NULL) IS TRUE 0
(0 IS NOT NULL) IS TRUE 0
(0 = NULL) IS TRUE 0
(0 = NULL) IS FALSE 0
(0 = NULL) IS NOT TRUE 1
(0 = NULL) IS NOT FALSE 1
('NULL' = NULL) IS TRUE 0
('NULL' IS NULL) IS TRUE 0

The highlighted parts are usually assumptions that people expect to behave differently, for example that zero is not equal to NULL. That catches many people by surprise. It shouldn’t catch you by surprise though, because 0 is a value. The default behavior of MySQL of casting NULL to 0, on a INTEGER NOT NULL column without a DEFAULT value taught a whole generation of developers that this assumption is true. The same applies when casting a string-based column that has no default value on a NOT NULL column to empty string.

Is that a spaceship operator?

While writing this post and taking a peek into MySQL documentation, I never noticed that a NULL-safe equal operator, <=>, existed, unfortunately the MySQL website only shows the documentation from 5.5 to 8.0, so I can’t be certain if this existed in prior releases.

SELECT
1 IS NOT NULL,
1 = NULL,
1 <=> NULL;
Assertion MySQL
1 IS NOT NULL 1
1 = NULL NULL
1 <=> NULL 0

The case where MySQL thinks it is PHP

Another behavior I discovered when doing some JOINs were the string and integer comparison. Both databases get SELECT 1 = '1'; as true, however, MySQL takes a step further:

SELECT 1 = '1a',
'1' * 3,
'1abc' + 4;
Assertion MySQL PostgreSQL
1 = '1a' 1 invalid input syntax for integer: “1a”
'1' * 3 3 3
'1abc' + 4 5 invalid input syntax for integer: “1abc”

Thus behaving like PHP. PHP is expected to change this behavior soon with this RFC. PostgreSQL, in this case, is forgiving in casting a string as an integer, only if there is an integer inside of the quotes. If you mix the integer with other characters, it throws an error as you can see above.

I Know!

No, I don’t. I thought I knew enough SQL. Apparently, I was wrong.

Independent of the language you are using, you should be aware that assertions that are true in your language may not be true in SQL and the other way as well.

Extending WordPress Dockerfile to use MySQL 5.7 (or 8.0)

Extending WordPress Dockerfile to use MySQL 5.7 (or 8.0)

Oracle’s website shows End of life for MySQL 5.5 as of Jan 20th of 2019, so hurry up and upgrade!

I am working building some demos for Cloud SQL and one of the requirements I had was to run MySQL 5.7 and WordPress as my sample application. The demo consisted on migrating from a single VM environment with WordPress and MySQL running alongside. The narrative: the site got popular and the database became the bottle neck because of all the shared resources between them and the application. The proposed solution? A minimal downtime migration to Cloud SQL, moving the data layer to a dedicated server.

I am going to be doing this demo a lot of times, so I needed some way to automate it. I thought of doing through Docker. I am not Docker proficient, and to begin with I asked Anthony for help to get me to what I wanted, but there are so many nuances! Maybe someone will find a better solution to it than this one, but I decided to share what I got.

Let’s examine the two scenarios I faced. All examples assume Debian/Ubuntu.

I don’t run Docker, just have a VM and want to have MySQL 5.7

In this case it’s straightforward: you need to use the MySQL official APT repository available in https://dev.mysql.com/downloads/repo/apt/.

At this time the most recent version is mysql-apt-config_0.8.12-1_all.deb, keep an eye before continuing this because it may change the version until you use this tutorial.

In line 2 you can change from mysql-5.7 to mysql-8.0, if unspecified the command, version 8.0 will be installed.

I run Docker and want to have 5.7 or 8.0 installed on it

It’s a bit similar to the previous situation, you still need to go to the APT repository page to know which file to download and add this on your Dockerfile:

Notice, you can also change the version of MySQL here. Don’t forget to pass DB_ROOT_PASSWORD​ when doing your docker build using the --build-arg argument. More details here.

It works!

These are the workarounds to avoid using MySQL 5.5. After that I was able to finally automate my demo. Feel free here to share better examples of what I did, as I said, I don’t have proficiency in the subject.