Reports > System Information > Database Tables Grid not loading on MySQL 8.0.18

[muzzwood]

Bug report

Summary

Just upgraded a site to MySQL 8.0.18 - now Database Tables grid in manager not loading. Everything else seems to work.

Step to reproduce

Install MySQL 8.x and view the Database Tables grid. I switched from MySQL 5.7 to 8.0.18. The grid was loading on 5.7.

Observed behavior

Database tables grid doesn't load any data. There are no log errors and browser console shows the response payload as

{"success":true,"total":0,"results":[]}

Expected behavior

The Database tables grid should show the database tables like normal.

Environment

MODX version 2.7.2, Nginx, MySQL 8.0.18

[Jako]

Could be a ONLY_FULL_GROUP_BY issue. Could you replay the mySQL query directly on the host?

[muzzwood]

Hmmm it's the $this->modx->getOption('dbname'); call that's failing and returning nothing.

https://github.com/modxcms/revolution/blob/2.x/core/model/modx/processors/system/databasetable/getlist.class.php#L53

So the query ends up being SHOW TABLE STATUS FROM ``

I'm unsure where MODX/xPDO gets the dbname from when it's meant to be added to the config array. Is it the dsn string in the config.inc.php file?

I'm using Digital Ocean's managed database service. So it's not localhost and I've added a custom port (i.e. port=xxxxxx;) to the dsn string just after the host address in the config file but that's the only difference from the default setup.

Edit: Looking at the MODX config array at the location linked above, I can see the dbname value is empty. However under connections I can see the dbname within the dsn string.

[muzzwood]

So the crux of the matter is MODX is unable to get the database name. Does anyone have an idea of what might be causing this?

With a cursory IDE search through the core, it seems like the Database Tables grid is the only place the value is used, but I know some extras use it.

For now I've just added $this->config['dbname'] = 'mydbname'; to the end of the initialize() function in modx.class.php to force the database name which works.

[Jako]

Look for the result of of the system/info XHR request in the browser in the system info manager page. Maybe the JSON has an issue there.

You could also look into the parseDSN method of xPDO which prepares the 'dbname' MODX config. But MODX won't run without a database connection.

[muzzwood]

So the problem appears (I think) to be with the parseDSN function.

I took the logic out of the parseDSN function and made a simple snippet with it as you can't log from the that function, feeding it the DSN string I'm using (changed the personal info of course):

<?php

$string = 'mysql:host=db-mysql-xxxx-xxxxxxxxx-xx-do-user-0000000-0.db.ondigitalocean.com;port=00000;dbname=mydbname;charset=utf8mb4;';

$result= array ();
$pos= strpos($string, ':');
$result['dbtype']= strtolower(substr($string, 0, $pos));
$parameters= explode(';', substr($string, ($pos +1)));
for ($a= 0, $b= count($parameters); $a < $b; $a++) {
    $tmp= explode('=', $parameters[$a]);
    if (count($tmp) == 2) {
        $result[strtolower(trim($tmp[0]))]= trim($tmp[1]);
    } else {
        $result['dbname']= trim($parameters[$a]);
    }
}
if (!isset($result['dbname']) && isset($result['database'])) {
    $result['dbname'] = $result['database'];
}
if (!isset($result['host']) && isset($result['server'])) {
    $result['host'] = $result['server'];
}
var_dump($result);

The output shows as:

array (size=5)
  'dbtype' => string 'mysql' (length=5)
  'host' => string 'db-mysql-xxxx-xxxxxxxxx-xx-do-user-0000000-0.db.ondigitalocean.com' (length=66)
  'port' => string '00000' (length=5)
  'dbname' => string '' (length=0)
  'charset' => string 'utf8mb4' (length=7)

So the dbname isn't getting set for some reason.

[Jako]

Removing the last semicolon from the dsn string should solve this.

This line is the culprit:

else {
        $result['dbname']= trim($parameters[$a]);
}

[muzzwood]

Holy moly, that was it!

I appreciate you working through it with me @Jako , thank you!

[Jako]

Glad that it is solved, but why the heck does that culprit exist. It should at least not overwrite the dbname property or if it does so, the whole installation should use that wrong option.

[muzzwood]

Good question. The parts below referencing $result['database'] and $result['server'] I assume must be for backwards compatibility with an older version of config file, but I'm not sure what that condition you specified is for. Perhaps I'll reopen this for now so it can be discussed.