Matomo Setup Doesn't Work with Planet Scale

[njfdev]

During setup, the connection to a Planet Scale MySQL database fails.

Expected Behavior

A user should be able to input their Planet Scale credentials and go to the next step in the setup.

Current Behavior

Matomo throws an error on the web interface:

Could not connect to the database server: SQLSTATE[HY000] [1105] unknown error: Code: UNAVAILABLEserver does not allow insecure connections, client must use SSL/TLS

Possible Solution

Matomo should have all connections to the database use SSL/TLS. This would allow databases that only support SSL (such as Planet Scale) to be used.

Steps to Reproduce (for Bugs)

1. Make an account on Planet Scale
2. Create a database on Planet Scale (save credentials for later)
3. Create a brand new Matomo instance (I am self hosting on Heroku)
4. Go through setup steps 1-2
5. On setup step 3, supply the Planet Scale database credentials

Context

I am trying to self-host Matomo for the first time using Heroku and Planet Scale which both have a free tier. This is useful because I can use my own domain to bypass ad blockers.

Your Environment

• Matomo Version: 4.12.0-b1
• PHP Version: 7.4.30
• Server Operating System: Linux
• Additionally installed plugins: N/A

[bx80]

@njfdev, thanks for raising this issue.

Currently Matomo doesn't support SSL/TLS during initial configuration as there is no option to specify the various SSL settings in the wizard. The SSL/TLS can only be configured after installation

A workaround could be to install Matomo to a local MySQL server, then dump the database and import it into Planet Scale, then edit the config/config.ini.php setting to point at the database on Planet Scale using SSL. This is obviously not ideal, but should work without issues once the initial database is on Planet Scale and the SSL connection is setup.

A proper fix could be to have the installation wizard detect any server does not allow insecure connections, client must use SSL/TLS errors. Then an SSL connection could be attempted with the same credentials using the default OS cert bundle if present, which should work for providers like Planet Scale who sign using a common CA. If the SSL connection attempt succeeds then SSL could be enabled automatically, if it fails then the wizard could prompt to upload a cert bundle (for providers who don't use a common CA, eg. AWS RDS). Additionally a 'Use SSL' option could be provided on the database setup page to allow SSL to be forced even if the server supports non-SSL connections.

[njfdev]

@bx80 Thank you for your detailed response. I currently using MySQL through Railway which works fine for now (might not scale well in the future). Railway doesn't allow me to access the filesystem of the deployment, therefore I can't edit the config/config.ini.php file.

I think an SSL option during setup would be very useful because it allows SSL only databases (such as Planet Scale), but also increases security. Maybe SSL should even be the default.

Thanks again for your help.

[MatomoForumNotifications]

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/planetscale-db-compatibility-on-prem/46210/2

[llibdude]

I was able to get started with PlanetScale & Matomo using the above steps. Sure, it was inconvenient, but considering it's a one time cost I found it an acceptable cost.

The much larger blocker to the integration of PlanetScale & Matomo is how upgrading Matomo would work. PlanetScale follows a similar branching strategy to that of GitHub's, except instead of code, it's databases. To make a schema change to a PlanetScale database, you must branch, make the change, and merge back in (you cant make schema changes to main branch). Docs here https://docs.planetscale.com/concepts/branching. However any data changes you make in a branch will not be copied back to main.

The problem here is that Matomo is not built to handle this kind of workflow (to no fault of Matomo, this is a somewhat novel concept in the db world).

I suppose a solution to this other than an intentional integration on Matomo's side would be to somehow apply schema changes in a branch, merge them to main, then apply any data changes in the main branch. This would be required for the base matomo product as well as all plugins. This could be tricky to manage.

[bx80]

@llibdude Good to hear you were able to work around the installation issue with some success.

A possible workaround for applying Matomo upgrades could be to initiate the upgrade using the console command ./console core:update this will show the proposed schema changes along with a confirmation prompt. It could be possible to use this method to copy the schema change SQL, abort at the confirmation prompt, then use PlanetScale to create a new branch, apply the copied changes and finally merge the branch back to main. At this point the normal Matomo upgrade process should be able to complete since the schema changes would already have been applied.

[llibdude]

@bx80 I attempted this strategy. I think however, that Matomo will still attempt to run the migration even after the branch has been merged in. PlanetScale will still error out on any Alter query applied to main, even if the underlying schema change has already been applied. For this strategy to work Matomo would need to check if the schema is already updated before applying the schema change, which obviously is not how normal upgrades are done with idempotent migrations.

[bx80]

@llibdude Unfortunately at the moment there is no way to just skip the Alter database parts of an update as database changes are not broken out from other necessary parts of the update, such as data migration, adding new records, etc.

Checking the schema first before applying database update statements would be a big change to the way Matomo updates curenntly work, as would adding explicit support for Planet Scale's database branching paradigm.

Something that might be easier to implement would be to get the updater to ignore the Planet Scale ERROR 1105 (HY000): direct DDL is disabled error code, then the schema could be updated manually and ./console core:update would be able to complete without errors.

In the core/Updater/Migration/Db/Sql::addErrorCodeToIgnore class we already have the option for individual updates to specify database error codes that will be ignored during updates. So a config option to globally add the 1105 error code to this list might be all that is needed.

Something like:

diff --git a/core/Updater/Migration/Db/Sql.php b/core/Updater/Migration/Db/Sql.php
--- a/core/Updater/Migration/Db/Sql.php (revision b031b18affe84914907c3b1a05cc9cf1c86086a6)
+++ b/core/Updater/Migration/Db/Sql.php (date 1658791515567)
@@ -41,7 +41,7 @@
         }

         $this->sql = $sql;
-        $this->errorCodesToIgnore = $errorCodesToIgnore;
+        $this->errorCodesToIgnore = array_merge($errorCodesToIgnore, [1105]);
     }

     public function shouldIgnoreError($exception)

(but with code(s) loaded from a proper config.ini.php setting, not hardcoded!)

If you think this might be an effective approach then I can create a new enhancement request to add this option, as it should be fairly trivial to implement and may be helpful as a workaround for other database services with similar quirks.

[llibdude]

Thanks. I'd be willing to give it a test

[justinvelluppillai]

Adding to current milestone as there's an easy solution already implemented that will allow further testing 👍🏽

[justinvelluppillai]

Wrong one, linking the related issue instead of this one now! Thanks for the headsup @bx80

[stephtr]

There is actually a (obviously unsupported) workaround for installation: monkey-patching the core/Db/Adapter/Pdo/Mysql.php file to:

        if (true || $config['enable_ssl']) {
            if (!empty($config['ssl_key'])) {
                $config['driver_options'][PDO::MYSQL_ATTR_SSL_KEY] = $config['ssl_key'];
            }
            if (!empty($config['ssl_cert'])) {
                $config['driver_options'][PDO::MYSQL_ATTR_SSL_CERT] = $config['ssl_cert'];
            }
            if (true || !empty($config['ssl_ca'])) {
                $config['driver_options'][PDO::MYSQL_ATTR_SSL_CA] = ''; //$config['ssl_ca'];
            }
            if (!empty($config['ssl_ca_path'])) {
                $config['driver_options'][PDO::MYSQL_ATTR_SSL_CAPATH] = $config['ssl_ca_path'];
            }
            if (!empty($config['ssl_cipher'])) {
                $config['driver_options'][PDO::MYSQL_ATTR_SSL_CIPHER] = $config['ssl_cipher'];
            }
            if (true || !empty($config['ssl_no_verify'])
                && defined('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT')
            ) {
                $config['driver_options'][PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false;
            }
        }

[sgiehl]

@stephtr As a workaround you could also adjust config/global.ini.php with the database ssl settings you need before starting the installation. Once the installation was done you can then copy those settings to the newly created config/config.ini.php and revert the changes in the global config (as they would be overwritten on update).

[stephtr]

A workaround could be to install Matomo to a local MySQL server, then dump the database and import it into Planet Scale, then edit the config/config.ini.php setting to point at the database on Planet Scale using SSL.

As far as I have understood, that only works once the database is seeded. At least in my case I only managed it to run it after seeding the DB using my workaround. But it can also be that I made a mistake in the first place.