Adds additional system check for CREATE TEMPORARY TABLES permission (#15139)

* Adds additional system check for CREATE TEMPORARY TABLES permission

* Hide LOAD DATA IN FILE check if disabled in config

* Adds link to FAQ if temporary table grant is not given

* check that writing and reading from temporary table works

* check transaction isolation level

* Adds UI tests for diagnostics page

6 files changed, 177 insertions, 88 deletions

diff --git a/plugins/Diagnostics/Diagnostic/DatabaseAbilitiesCheck.php b/plugins/Diagnostics/Diagnostic/DatabaseAbilitiesCheck.php
new file mode 100644
index 0000000000..2efa9ff494
--- /dev/null
+++ b/plugins/Diagnostics/Diagnostic/DatabaseAbilitiesCheck.php
@@ -0,0 +1,150 @@
+<?php
+/**
+ * Piwik - free/libre analytics platform
+ *
+ * @link https://matomo.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ */
+namespace Piwik\Plugins\Diagnostics\Diagnostic;
+
+use Piwik\Common;
+use Piwik\Config;
+use Piwik\Db;
+use Piwik\Translation\Translator;
+
+/**
+ * Check if Piwik can use LOAD DATA INFILE.
+ */
+class DatabaseAbilitiesCheck implements Diagnostic
+{
+    /**
+     * @var Translator
+     */
+    private $translator;
+
+    public function __construct(Translator $translator)
+    {
+        $this->translator = $translator;
+    }
+
+    public function execute()
+    {
+        $isPiwikInstalling = !Config::getInstance()->existsLocalConfig();
+        if ($isPiwikInstalling) {
+            // Skip the diagnostic if Piwik is being installed
+            return array();
+        }
+
+        $result = new DiagnosticResult($this->translator->translate('Installation_DatabaseAbilities'));
+
+        if (Config::getInstance()->General['enable_load_data_infile']) {
+            $result->addItem($this->checkLoadDataInfile());
+        }
+
+        $result->addItem($this->checkTemporaryTables());
+        $result->addItem($this->checkTransactionLevel());
+
+        return [$result];
+    }
+
+    protected function checkLoadDataInfile()
+    {
+        $optionTable = Common::prefixTable('option');
+        $testOptionNames = array('test_system_check1', 'test_system_check2');
+
+        $loadDataInfile = false;
+        $errorMessage = null;
+        try {
+            $loadDataInfile = Db\BatchInsert::tableInsertBatch(
+                $optionTable,
+                array('option_name', 'option_value'),
+                array(
+                    array($testOptionNames[0], '1'),
+                    array($testOptionNames[1], '2'),
+                ),
+                $throwException = true,
+                $charset = 'latin1'
+            );
+        } catch (\Exception $ex) {
+            $errorMessage = str_replace("\n", "<br/>", $ex->getMessage());
+        }
+
+        // delete the temporary rows that were created
+        Db::exec("DELETE FROM `$optionTable` WHERE option_name IN ('" . implode("','", $testOptionNames) . "')");
+
+        if ($loadDataInfile) {
+            return new DiagnosticResultItem(DiagnosticResult::STATUS_OK, 'LOAD DATA INFILE');
+        }
+
+        $comment = sprintf(
+            'LOAD DATA INFILE<br/>%s<br/>%s',
+            $this->translator->translate('Installation_LoadDataInfileUnavailableHelp', array(
+                'LOAD DATA INFILE',
+                'FILE',
+            )),
+            $this->translator->translate('Installation_LoadDataInfileRecommended')
+        );
+
+        if ($errorMessage) {
+            $comment .= sprintf(
+                '<br/><strong>%s:</strong> %s<br/>%s',
+                $this->translator->translate('General_Error'),
+                $errorMessage,
+                'Troubleshooting: <a target="_blank" rel="noreferrer noopener" href="https://matomo.org/faq/troubleshooting/#faq_194">FAQ on matomo.org</a>'
+            );
+        }
+
+        return new DiagnosticResultItem(DiagnosticResult::STATUS_WARNING, $comment);
+    }
+
+    protected function checkTemporaryTables()
+    {
+        $status = DiagnosticResult::STATUS_OK;
+        $comment = 'CREATE TEMPORARY TABLES';
+
+        try {
+            // create a temporary table
+            Db::exec("CREATE TEMPORARY TABLE `piwik_test_table_temp` (
+                                        id INT,
+                                        val VARCHAR(5) NULL,
+                                        PRIMARY KEY (id)
+                                     )");
+
+            // insert an entry into the new temporary table
+            Db::exec('INSERT INTO `piwik_test_table_temp` (`id`, `val`) VALUES ("1", "val1");');
+
+            for ($i=0; $i < 5; $i++) {
+                // try reading the entry a few times to ensure it doesn't fail, which might be possible when using load balanced databases
+                $result = Db::fetchRow('SELECT * FROM `piwik_test_table_temp` WHERE `id` = 1');
+
+                if (empty($result)) {
+                    throw new \Exception('read failed');
+                }
+            }
+        } catch (\Exception $e) {
+            $status = DiagnosticResult::STATUS_ERROR;
+            $comment .= '<br/>' . $this->translator->translate('Diagnostics_MysqlTemporaryTablesWarning');
+            $comment .= '<br/>Troubleshooting: <a target="_blank" rel="noreferrer noopener" href="https://matomo.org/faq/how-to-install/faq_23484/">FAQ on matomo.org</a>';
+        }
+
+        return new DiagnosticResultItem($status, $comment);
+
+    }
+
+    protected function checkTransactionLevel()
+    {
+        $status = DiagnosticResult::STATUS_OK;
+        $comment = 'Changing transaction isolation level';
+
+        $level = new Db\TransactionLevel(Db::getReader());
+        if (!$level->setUncommitted()) {
+            $status = DiagnosticResult::STATUS_WARNING;
+            $comment .= '<br/>' . $this->translator->translate('Diagnostics_MysqlTransactionLevel');
+        } else {
+            $level->restorePreviousStatus();
+        }
+
+        return new DiagnosticResultItem($status, $comment);
+
+    }
+}
diff --git a/plugins/Diagnostics/Diagnostic/LoadDataInfileCheck.php b/plugins/Diagnostics/Diagnostic/LoadDataInfileCheck.php
deleted file mode 100644
index 50a8674f8c..0000000000
--- a/plugins/Diagnostics/Diagnostic/LoadDataInfileCheck.php
+++ /dev/null
@@ -1,87 +0,0 @@
-<?php
-/**
- * Piwik - free/libre analytics platform
- *
- * @link https://matomo.org
- * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
- */
-namespace Piwik\Plugins\Diagnostics\Diagnostic;
-
-use Piwik\Common;
-use Piwik\Config;
-use Piwik\Db;
-use Piwik\Translation\Translator;
-
-/**
- * Check if Piwik can use LOAD DATA INFILE.
- */
-class LoadDataInfileCheck implements Diagnostic
-{
-    /**
-     * @var Translator
-     */
-    private $translator;
-
-    public function __construct(Translator $translator)
-    {
-        $this->translator = $translator;
-    }
-
-    public function execute()
-    {
-        $isPiwikInstalling = !Config::getInstance()->existsLocalConfig();
-        if ($isPiwikInstalling) {
-            // Skip the diagnostic if Piwik is being installed
-            return array();
-        }
-
-        $label = $this->translator->translate('Installation_DatabaseAbilities');
-
-        $optionTable = Common::prefixTable('option');
-        $testOptionNames = array('test_system_check1', 'test_system_check2');
-
-        $loadDataInfile = false;
-        $errorMessage = null;
-        try {
-            $loadDataInfile = Db\BatchInsert::tableInsertBatch(
-                $optionTable,
-                array('option_name', 'option_value'),
-                array(
-                    array($testOptionNames[0], '1'),
-                    array($testOptionNames[1], '2'),
-                ),
-                $throwException = true,
-                $charset = 'latin1'
-            );
-        } catch (\Exception $ex) {
-            $errorMessage = str_replace("\n", "<br/>", $ex->getMessage());
-        }
-
-        // delete the temporary rows that were created
-        Db::exec("DELETE FROM `$optionTable` WHERE option_name IN ('" . implode("','", $testOptionNames) . "')");
-
-        if ($loadDataInfile) {
-            return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_OK, 'LOAD DATA INFILE'));
-        }
-
-        $comment = sprintf(
-            'LOAD DATA INFILE<br/>%s<br/>%s',
-            $this->translator->translate('Installation_LoadDataInfileUnavailableHelp', array(
-                'LOAD DATA INFILE',
-                'FILE',
-            )),
-            $this->translator->translate('Installation_LoadDataInfileRecommended')
-        );
-
-        if ($errorMessage) {
-            $comment .= sprintf(
-                '<br/><strong>%s:</strong> %s<br/>%s',
-                $this->translator->translate('General_Error'),
-                $errorMessage,
-                'Troubleshooting: <a target="_blank" rel="noreferrer noopener" href="https://matomo.org/faq/troubleshooting/#faq_194">FAQ on matomo.org</a>'
-            );
-        }
-
-        return array(DiagnosticResult::singleResult($label, DiagnosticResult::STATUS_WARNING, $comment));
-    }
-}
diff --git a/plugins/Diagnostics/config/config.php b/plugins/Diagnostics/config/config.php
index 3bf076e014..3861c5c5a1 100644
--- a/plugins/Diagnostics/config/config.php
+++ b/plugins/Diagnostics/config/config.php
@@ -27,7 +27,7 @@ return array(
         DI\get('Piwik\Plugins\Diagnostics\Diagnostic\NfsDiskCheck'),
         DI\get('Piwik\Plugins\Diagnostics\Diagnostic\CronArchivingCheck'),
         DI\get(CronArchivingLastRunCheck::class),
-        DI\get('Piwik\Plugins\Diagnostics\Diagnostic\LoadDataInfileCheck'),
+        DI\get('Piwik\Plugins\Diagnostics\Diagnostic\DatabaseAbilitiesCheck'),
         Di\get('Piwik\Plugins\Diagnostics\Diagnostic\DbOverSSLCheck'),
         Di\get('Piwik\Plugins\Diagnostics\Diagnostic\DbMaxPacket'),
         Di\get('Piwik\Plugins\Diagnostics\Diagnostic\ForceSSLCheck'),
diff --git a/plugins/Diagnostics/lang/en.json b/plugins/Diagnostics/lang/en.json
index c3638dfcb7..741126d70b 100644
--- a/plugins/Diagnostics/lang/en.json
+++ b/plugins/Diagnostics/lang/en.json
@@ -1,6 +1,8 @@
 {
     "Diagnostics": {
         "ConfigFileTitle": "Config file",
+        "MysqlTemporaryTablesWarning": "MySQL Permission CREATE TEMPORARY TABLES is required for Matomo to work properly.",
+        "MysqlTransactionLevel": "Changing transaction isolation level not supported. Archiving will still work but it may be slower and it is recommended to change for example the `binlog_format` to `row` if possible.",
         "MysqlMaxPacketSize": "Max Packet Size",
         "MysqlMaxPacketSizeWarning": "It is recommended to configure a 'max_allowed_packet' size in your MySQL database of at least %1$s. Configured is currently %2$s.",
         "ConfigFileIntroduction": "Here you can view the Matomo configuration. If you are running Matomo in a load balanced environment, the page might be different depending on from which server this page is loaded. Rows with a different background color are changed config values that are specified for example in the %1$s file.",
diff --git a/plugins/Diagnostics/tests/UI/Diagnostics_spec.js b/plugins/Diagnostics/tests/UI/Diagnostics_spec.js
new file mode 100644
index 0000000000..ce6d4b9ccd
--- /dev/null
+++ b/plugins/Diagnostics/tests/UI/Diagnostics_spec.js
@@ -0,0 +1,21 @@
+/*!
+ * Matomo - free/libre analytics platform
+ *
+ * Screenshot tests for the DBStats plugin.
+ *
+ * @link https://matomo.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ */
+
+describe("Diagnostics", function () {
+    this.timeout(0);
+
+    const url = "?module=Installation&action=systemCheckPage&idSite=1&period=day&date=yesterday";
+
+    it("should load correctly", async function() {
+        await page.goto(url);
+
+        const content = await page.$('#content');
+        expect(await content.screenshot()).to.matchImage('page');
+    });
+});
\ No newline at end of file
diff --git a/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png b/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png
new file mode 100644
index 0000000000..38c620ea39
--- /dev/null
+++ b/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png
@@ -0,0 +1,3 @@
+version https://git-lfs.github.com/spec/v1
+oid sha256:beac9c4490143385645ba9176a3cb52dc32ce1c46cffd34d4dee642449dd36b2
+size 204429