search

oddnoc / silverstripe-artefactcleaner

Find and optionally delete disused tables and fields in a SilverStripe database.
Other
12 stars 5 forks source link

Does not work on CWP/SilverStripe cloud or in local dev env #13

Open torleif opened 8 months ago

torleif commented 8 months ago

Attempting to run this module in SilverStripe cloud results in the following error:

error-log.ERROR: Uncaught Exception mysqli_sql_exception: "Access denied for user 'silverstripe'@'%' to database 'ss_tmpdb_1710211082_5775942'" at /sites/op-uat/releases/20240312023520/vendor/silverstripe/framework/src/ORM/Connect/MySQLiConnector.php line 184 {"exception":"[object] (mysqli_sql_exception(code: 1044): Access denied for user 'silverstripe'@'%' to database 'ss_tmpdb_1710211082_5775942' at /sites/op-uat/releases/20240312023520/vendor/silverstripe/framework/src/ORM/Connect/MySQLiConnector.php:184)"} []

Running it locally (full permissions with MariaDB) results in a different SQL error:

Running Task Display [remove] Database Artefacts

ERROR [UNKNOWN TYPE, ERRNO 1064]: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') character set utf8mb4 collate utf8mb4_unicode_ci,
"HideAskAQuestion" tinyint(1' at line 9
IN GET dev/tasks/ArtefactCleanTask
Line 184 in C:\Wnmp\op\vendor\silverstripe\framework\src\ORM\Connect\MySQLiConnector.php

Source
======
  175:         // Clear the last statement
  176:         $this->setLastStatement(null);
  177:     }
  178:
  179:     public function query($sql, $errorLevel = E_USER_ERROR)
  180:     {
  181:         $this->beforeQuery($sql);
  182:
  183:         // Benchmark query
* 184:         $handle = $this->dbConn->query($sql ?? '', MYSQLI_STORE_RESULT);
  185:
  186:         if (!$handle || $this->dbConn->error) {
  187:             $this->databaseError($this->getLastError(), $errorLevel, $sql);
  188:             return null;
  189:         }
  190:

Trace
=====
mysqli->query(CREATE  TABLE "Page" (
                                "ID" int(11) not null auto_increment,
"MaoriName" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"ShowOnOpen" tinyint(1) unsigned not null default '0',
"SideBar" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"Filter" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"ClassOverride" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"HideHero" tinyint(1) unsigned not null default '0',
"SubNavigation" enum() character set utf8mb4 collate utf8mb4_unicode_ci,
"HideAskAQuestion" tinyint(1) unsigned not null default '0',
"ShowWelcomeMessage" tinyint(1) unsigned not null default '0',
"HeroID" int(11) not null default '0',
"ElementalAreaID" int(11) not null default '0',
"HeaderID" int(11) not null default '0',

                                index "HeroID" ("HeroID"),
index "ElementalAreaID" ("ElementalAreaID"),
index "HeaderID" ("HeaderID"),

                                primary key (ID)
                        ) ENGINE=InnoDB, 0)
MySQLiConnector.php:184

SilverStripe\ORM\Connect\MySQLiConnector->query(CREATE  TABLE "Page" (
                                "ID" int(11) not null auto_increment,
"MaoriName" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"ShowOnOpen" tinyint(1) unsigned not null default '0',
"SideBar" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"Filter" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"ClassOverride" mediumtext character set utf8mb4 collate utf8mb4_unicode_ci,
"HideHero" tinyint(1) unsigned not null default '0',
"SubNavigation" enum() character set utf8mb4 collate utf8mb4_unicode_ci,
"HideAskAQuestion" tinyint(1) unsigned not null default '0',
"ShowWelcomeMessage" tinyint(1) unsigned not null default '0',
"HeroID" int(11) not null default '0',
"ElementalAreaID" int(11) not null default '0',
"HeaderID" int(11) not null default '0',

                                index "HeroID" ("HeroID"),
index "ElementalAreaID" ("ElementalAreaID"),
index "HeaderID" ("HeaderID"),

                                primary key (ID)
                        ) ENGINE=InnoDB, 256)
Database.php:159

note /dev/build works as expected

oddnoc commented 8 months ago

Thanks for this report. Can you provide more details of your local environment (I have no access to Silverstripe Cloud)?

Thanks!

torleif commented 8 months ago

locally the settings are: 10.3.12-MariaDB

SS_DATABASE_SERVER="localhost"
SS_DATABASE_USERNAME=""
SS_DATABASE_PASSWORD=""
SS_DATABASE_NAME=""
SS_DATABASE_CLASS="MySQLDatabase"
WKHTMLTOPDF_BINARY="C:\Program Files (x86)\wkhtmltopdf14\bin\wkhtmltopdf.exe"
SS_DEPRECATION_ENABLED="1"
{
    "name": "cwp/cwp-installer",
    "description": "Otago Polytechnic website",
    "type": "silverstripe-project",
    "require": {
        "php": ">=8.1",
        "ext-SimpleXML": "*",
        "ext-curl": "*",
        "ext-json": "*",
        "algolia/algoliasearch-client-php": "3.3.2",
        "axllent/silverstripe-version-truncator": "3.0.0",
        "cwp/cwp-pdfexport": "1.4.0",
        "dnadesign/silverstripe-elemental": "4.9.4",
        "dnadesign/silverstripe-elemental-list": "^1.2",
        "ezyang/htmlpurifier": "v4.16.0",
        "gorriecoe/silverstripe-link": "1.4.0",
        "gorriecoe/silverstripe-linkfield": "^1.0",
        "guzzlehttp/guzzle": "^7.5",
        "hybridauth/hybridauth": "v3.8.2",
        "otago/autocomplete-suggest-field": "4.3.0",
        "otago/crm": "4.7",
        "otago/ebs": "v4.10.1",
        "otago/moodle": "4.0.5",
        "otago/opcolor": "4.0.5",
        "otago/silverstripe-retinaimages": "^4.10",
        "otago/subsites-domains": "^4.0",
        "otago/tiles": "4.12",
        "phpxmlrpc/phpxmlrpc": "^4.6",
        "ryanpotter/silverstripe-cms-theme": "^3.4",
        "silvershop/silverstripe-hasonefield": "^3.1",
        "silverstripe/asset-admin": "1.11.2",
        "silverstripe/cms": "4.11.3",
        "silverstripe/comments": "3.8.0",
        "silverstripe/elemental-bannerblock": "2.5.0",
        "silverstripe/framework": "4.11.14",
        "silverstripe/googlesitemaps": "2.2.1",
        "silverstripe/graphql": "3.8.0",
        "silverstripe/realme": "4.3.0",
        "silverstripe/recipe-ccl": "2.11.0",
        "silverstripe/reports": "4.11.0",
        "silverstripe/restfulserver": "2.5.0",
        "silverstripe/saml": "2.1.0",
        "silverstripe/siteconfig": "4.11.0",
        "silverstripe/subsites": "2.6.0",
        "silverstripe/tagfield": "^2.10",
        "silverstripe/userforms": "5.13.1",
        "silverstripe/vendor-plugin": "1.6.0",
        "silverstripe/versioned": "1.11.1",
        "silverstripe/versioned-admin": "1.11.1",
        "symbiote/silverstripe-gridfieldextensions": "3.5.0",
        "symbiote/silverstripe-queuedjobs": "4.10.1",
        "undefinedoffset/silverstripe-nocaptcha": "2.3.0"
    },
    "config": {
        "process-timeout": 900,
        "sort-packages": true,
        "allow-plugins": {
            "composer/installers": true,
            "silverstripe/vendor-plugin": true,
            "silverstripe/recipe-plugin": true
        }
    },
    "minimum-stability": "RC",
    "autoload": {
        "files": [
            "app/src/bootstrap.php"
        ]
    },
    "extra": {
        "expose": [
            "app/img",
            "app/javascript",
            "app/css"
        ],
        "project-files-installed": [
            "app/.htaccess",
            "app/_config.php",
            "app/_config/*.yml",
            "app/_config/environmentcheck.yml",
            "app/_config/mimevalidator.yml",
            "app/_config/mysite.yml"
        ],
        "public-files-installed": [
            ".htaccess",
            "favicon.ico",
            "index.php",
            "web.config"
        ]
    },
    "require-dev": {
        "phpunit/phpunit": "^9.5",
        "squizlabs/php_codesniffer": "^3.6.0",
        "silverstripe/graphql-devtools": "dev-master"
    }
}

In CWP, the MySQL database settings currently restrict the creation of additional temporary databases due to security concerns. While this limitation is acceptable to us, we're considering exporting the SQL to a development database and then executing it on the server.

torleif commented 8 months ago

I've found that removing all $db fields in Page.php resolved the issue. The only unique thing i can see from this file is that it's in the global namespace

oddnoc commented 8 months ago

Thanks. I'll try to reproduce the issue as time allows. The lack of namespace for Page should not have been a problem, as this is the typical pattern for CMS implementations.