Typical issues that only happens to me - #1

At this point I have the idea that I'm the guy that usually runs into the issues that no one had before when starting a project, so I'm going to start publishing every one of them to see how the count goes and confirm if that's true.

This is obviously not the first time I face a case like this, but it is the first time I'm publishing it, so starting with number #1 here.

The first part of this post is related to my local environment.

While working locally in a Linux (Ubuntu 20.04) with MySQL 8 (locally installed), I've got some issues while trying to install the application with the database dump I had, after the installation an index process was throwing an error (bad on me didn't pay much attention on the error the first time).

So the first recommendation I've got was to use MySQL 5.7 which was the version running the app was running on the server.

Though at the end the MySQL version wasn't causing the issue I'm mentioning here because I run into a problem I didn't had before: all my environments until this point were in docker so never had to use my local database.

My first question was: I'm supposed to downgrade MySQL?

My second question was: really???

Not under my watch! I've started to look into options for multiple MySQL instances.... PLOP! What I was thinking? That was even worst idea than not liking the idea of downgrade....

Anyway, that quick research brought me to MySQL-Sandbox: https://metacpan.org/pod/MySQL::Sandbox#Multiple-sandboxes which obviously didn't worked on my local environment!

So I give up quickly on that and went for the most easy path.... THE OBVIOUS ONE! KEEP USING DOCKER!!!

Not going to explain how to install Docker, docker-compose, I will assume you have all that already, so just created a docker-compose.yml file with the following content:

version: '3.3'
services:
 db:
 image: mysql:5.7
 restart: always
 command: mysqld --sql_mode=""
 environment:
 MYSQL_DATABASE: 'db'
 # So you don't have to use root, but you can if you like
 MYSQL_USER: 'youruser'
 # You can use whatever password you like
 MYSQL_PASSWORD: 'yourpass'
 # Password for root access
 MYSQL_ROOT_PASSWORD: 'yourpass'
 ports:
 # <Port exposed> : < MySQL Port running inside container>
 - '3308:3306'
 expose:
 - '3306'
 # Where our data will be persisted
 volumes:
 - my-db:/var/lib/mysql
# Names our volume
volumes:
 my-db:

You can note some details in this file:

- You could change your image to use any MySQL version you like.

- It has a volume to save your databases.

- You could have multiple instances for different databases by changing the port (instead 3308, 3309, whatever you like).

Just save the file and run:

$ docker compose up -d

That's it! Your database is ready! Give it a try:

$ mysql -u root -p --port=3308 --host=127.0.0.1
> yourpass

Side note: on my local environment it only worked specifying the 127.0.0.1 host.

So where is the "Typical issues that only happens to me"?????????? You may already see it....

After deploy the database and run the reindex process for about an hour, I've got the following error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains non aggregated column 'XXXXXXXXXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The fix? Indeed you already see it, just included in the docker file:

command: mysqld --sql_mode=""

Restarted the docker container and be happy :)