MySQL 8.0 support

[vladimirdulov]

Installation Method

Docker Installation

AzuraCast Release Channel

Stable Channel

Current AzuraCast Version

0.19.7

What happened?

Hello,

I'm packaging AzuraCast application for Cloudron.io. Technically I've managed to build and get AzuraCast v0.19.7 app working successfully.
Cloudron is based on docker and has MySQL 8.0 support out of the box (but no MariaDB support at the moment) so there are differences and difficulties to make it working in the long term.
I'd like to propose some suggestions/changes to AzuraCast upstream so it could work with MySQL 8.0.

1. refactor DB migration SQL queries to get rid of IF NOT EXISTS/IF EXISTS clauses for COLUMN, INDEX, KEY from respective queries which not supported by MySQL server.

Basically I patched them as following:

$ find ./src/Entity/Migration -type f -name "*.php" -exec sed -i -E 's/(COLUMN|INDEX|KEY|ADD) IF NOT EXISTS/\1/g' {} \;
$ find ./src/Entity/Migration -type f -name "*.php" -exec sed -i -E 's/(COLUMN|INDEX|KEY|ADD|DROP) IF EXISTS/\1/g' {} \;

then commented the query out

AzuraCast/src/Entity/Migration/Version20201003021913.php

Line 22 in adef71b

$this->addSql('ALTER TABLE song_history DROP COLUMN IF EXISTS `text`, DROP COLUMN IF EXISTS `artist`, DROP COLUMN IF EXISTS `title`');

$ sed -E "s/([$]this->addSql\('ALTER TABLE song_history DROP COLUMN \`text\`, DROP COLUMN \`artist\`, DROP COLUMN \`title\`'\);)/\/\/\1/g" -i ./src/Entity/Migration/Version20201003021913.php

2. add the ability to change MySQL init command in ./config/services.php
   https://github.com/AzuraCast/AzuraCast/blob/0.19.7/config/services.php#L81

Namely I need to remove ONLY_FULL_GROUP_BY option from sql-mode.

$ sed -e "s/PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_general_ci',/PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; SET sql_mode=(SELECT REPLACE(@@sql_mode, \"ONLY_FULL_GROUP_BY\", \"\"))',/" \
        -i ./config/services.php

This is a workaround for errors like:

3. add the ability to override or suppress/remove serverVersion in the connection options in ./config/services.php

https://github.com/AzuraCast/AzuraCast/blob/0.19.7/config/services.php#L84

Basically I commented it out so Doctrine could guess the database server version automatically.

sed -E "s/(.*'serverVersion' => '11.2.3-MariaDB-1',)/\/\/\1/" \
        -i /app/code/www/config/services.php

Considering that Cloudron community is happy to see AzuraApp app being published in Cloudron App Store for one-click installation, we'd like to see MySQL 8.0 support in the upstream.
Obviously the app update should be robust in the long term and those cannot be kept as patches.

Relevant log output

No response

[gramakri]

I am part of the Cloudron team. We would be appy to submit PRs/patches to make AzuraCast work with MySQL. Please let us know if this is of interest, thanks!

[OrcVole]

If you understand how useful Cloudron is to make Azuracast deployment easy, you would want to help here. Please make it happen.

[BusterNeece]

@vladimirdulov @gramakri Thank you all for your work in getting AzuraCast to work on Cloudron! It shouldn't be a terribly difficult thing to get running with the exception of the database issues we're having here.

I'm happy to remove the auto-version detection (in fact, we can set that so it only happens on localhost connections, so unmodified Docker installs still get the perf boost but custom ones are supported).

As for removing the IF NOT EXISTS stuff...eh. I kinda loathe that MySQL doesn't have that functionality. It has helped us out of a lot of tricky spots with regard to those migrations being "half-completed" for users.

We may have to think of a way to specify that we want those specifiers for MariaDB-based DBs but can remove them for MySQL ones (with the understanding that, without them, some migrations may be less robust for failing midway through and re-running).

[BusterNeece]

@vladimirdulov @gramakri Upon second examination, it appears the only time we've used the IF EXISTS/IF NOT EXISTS are from migrations from several years ago. We haven't had to use them at all in recent migrations, so I feel reasonably comfortable removing them where they do exist.

That should put us at full compliance with MySQL 8.0. I'll leave this issue open in case there are any other issues discovered, but the latest Rolling Release version (which is currently deploying) should be working.

[vladimirdulov]

@BusterNeece I've just noticed a typo in #7114
bc07fce#r142136210

A new PR has been created to fix it
#7120

[BusterNeece]

@vladimirdulov Should be fixed now.

[vladimirdulov]

Thank you, seems to work well now.
Look forward for a new AzuraCast stable version released to publish/release a Cloudron app.