Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Matomo Setup Doesn't Work with Planet Scale #19501

Open
njfdev opened this issue Jul 9, 2022 · 13 comments
Open

Matomo Setup Doesn't Work with Planet Scale #19501

njfdev opened this issue Jul 9, 2022 · 13 comments
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.

Comments

@njfdev
Copy link

njfdev commented Jul 9, 2022

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
@njfdev njfdev added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label Jul 9, 2022
@bx80
Copy link
Contributor

bx80 commented Jul 10, 2022

@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.

@bx80 bx80 added Bug For errors / faults / flaws / inconsistencies etc. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Jul 10, 2022
@bx80 bx80 added this to the For Prioritization milestone Jul 10, 2022
@njfdev
Copy link
Author

njfdev commented Jul 11, 2022

@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
Copy link

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
Copy link
Contributor

bx80 commented Jul 19, 2022

@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
Copy link

@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
Copy link
Contributor

bx80 commented Jul 25, 2022

@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
Copy link

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

@justinvelluppillai justinvelluppillai added Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. and removed Bug For errors / faults / flaws / inconsistencies etc. labels Jul 27, 2022
@justinvelluppillai
Copy link
Contributor

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

@justinvelluppillai
Copy link
Contributor

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

@stephtr
Copy link

stephtr commented Mar 14, 2023

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
Copy link
Member

sgiehl commented Mar 24, 2023

@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
Copy link

stephtr commented Mar 24, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Development

No branches or pull requests

7 participants