Pipelines with MySQL DB issues

[Lee Richmond]

So I've recently set up a project on pipelines and seem to have come across a strange issue in that in our local test and dev environment everything works as expected and tests pass etc. However, as soon as we push to bitbucket pipelines is failing because for some reason unknown to me, `only_full_group_by` seems to be set automatically on pipelines but isn't in our local environment I have checked the versions of the docker images we're using for both and they're the same. 

My question is this, is there any way I can set up the sql-mode for our pipelines env in the YAML file so that it matches our development/production env?

I've tried looking over the documentation for this and aside from finding several differently named links that all go to the same page, I have found nothing.

Thanks in advance

[Philip Hodder]

Hi Lee,

I'm not 100% sure on what the problem specifically is. Perhaps you can add a more detailed error message you're getting from your pipeline, if the following doesn't help.

I think these docs may be relevant for your specific issue, which describe how to enable/disable these types of settings: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

Looks like you can either do this on startup or runtime. It's probably going to be easier to disable 'only_full_group_by' during runtime in this case.

According to this: https://stackoverflow.com/questions/23921117/disable-only-full-group-by

You can run the following to disable ONLY_FULL_GROUP_BY:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

So you should be able to add that command to your pipeline script (do this for each step that needs MySQL) before any other interactions with MySQL.

---

As an aside (to try figure out why you're getting different behaviour locally with the same image), are you starting MySQL locally with any environment variables that configure it? Pipelines allows you to add environment variables to a service container. So you could try something like:

definitions:
  services:
    mysql:
      image: mysql:5.7
      environment:
        MYSQL_ROOT_PASSWORD: password

Support environment variables for MySQL are listed here: https://hub.docker.com/_/mysql/

---

Let me know if that helps.

Thanks,

Phil

[Bart Nagel]

Fixing mysql to 5.7 helped with the similarly bizarre problem I was having. So thanks, Philip!

In my case one migration added a composite unique index, and a later migration removed it again. This was failing in the pipeline environment but succeeding everywhere else.