Trouble Shooting MySQL/MariaDB problems

Errors

On modern (bookworm and above) versions of MariaDB, errors are logged to journald and should be exported to elastic search. On earlier versions, look in /var/log/mysql/.

Slow Queries

You can temporarily enable slow query logs (will be unset when MariaDB restarts) with:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 10;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';

This may adversely affect performance. You can turn it off with simply:

SET GLOBAL slow_query_log = 'OFF';

Deadlocks

You can see the most recent deadlock with the SQL command:

SHOW ENGINE INNODB STATUS;

To ensure all deadlocks are recorded in the error log (see above):

SET GLOBAL innodb_print_all_deadlocks = ON;

Resource hogs

On servers running Red, you can use the resourcehog command to find the users consuming the most CPU.

On other servers, run the sql command by hand:

SELECT USER, ROUND(SUM(BUSY_TIME), 4) as cpu_usage FROM information_schema.user_statistics GROUP BY USER ORDER BY cpu_usage;

If you don’t get any stats, enable stats with:

SET GLOBAL userstat = 1;

(It should be configured in /etc/mysql/conf.d/userstat.cnf and enabled on reboot.)

Incorrect definer statements

The backup log might include a line like this:

Warning: mysqldump: Got error: 1449: “The user specified as a definer (‘xxxxx’@’localhost’) does not exist” when using LOCK TABLES

A DEFINER statement specifies the security context in which a stored procedure, function, trigger, event or view is executed. It determines which user’s privileges should be used when running the stored routine or trigger.

This error might happen if we move a database from a host in which the user is local to a network database host - because the user@localhost no longer exists, now it’s user@ip.add.re.ss.

The safest way to fix it, is to re-build the function, trigger, etc. via sql as the new user, e.g. from their web site CLI while logged in as their MySQL user (see last option to run as root if all else fails).

Fixing Views

The following steps can be used to fix views.

Drupal 7 (untested)

  1. Create the file ~/includes/fix-definer.php
<?php

$sql = "SELECT TABLE_NAME as viewname from information_schema.VIEWS";
$result = db_query($sql);
echo "Running $sql\n";
while ($row = $result->fetchAssoc()) {
    echo $row->viewname . "\n";
    $sql = "SHOW CREATE VIEW {$row->viewname}";
    $viewResult = db_query($sql);
    $viewRow = $viewResult->fetchAssoc();
    $sql = $viewRow['Create View'];
    $sql = preg_replace('/^CREATE/', 'CREATE OR REPLACE', $sql);
    $sql = preg_replace('/@`localhost`/', '@`10.9.67.%`', $sql);
    db_query($sql);
    //echo "$sql\n";
}
  1. Run: drush php:script ~/include/fix-definer.php

Drupal 8+

  1. Create the file ~/include/fix-definer.php
<?php

$database = \Drupal::database();
$sql = "SELECT TABLE_NAME as viewname from information_schema.VIEWS";
$result = $database->query($sql);
while ($row = $result->fetchAssoc()) {
        echo $row['viewname'] . "\n";
        $sql = "SHOW CREATE VIEW {$row['viewname']}";
        $viewResult = $database->query($sql);
        $viewRow = $viewResult->fetchAssoc();
        $sql = $viewRow['Create View'];
        $sql = preg_replace('/^CREATE/', 'CREATE OR REPLACE', $sql);
        $sql = preg_replace('/@`localhost`/', '@`10.9.67.%`', $sql);
        $database->query($sql);
}
  1. Run: drush php:script ~/include/fix-definer.php

WordPress

  1. Create the file ~/include/fix-definer.php
<?php

global $wpdb;

$sql = "SELECT TABLE_NAME as viewname FROM information_schema.VIEWS WHERE TABLE_SCHEMA = DATABASE()";
$views = $wpdb->get_results($sql);

foreach ($views as $view) {
    echo $view->viewname . "\n";
    $sql = "SHOW CREATE VIEW {$view->viewname}";
    $createViewResult = $wpdb->get_row($sql);
    $createViewSql = $createViewResult->{'Create View'};
    $createViewSql = preg_replace('/^CREATE/', 'CREATE OR REPLACE', $createViewSql);
    $createViewSql = preg_replace('/@`localhost`/', '@`10.9.67.%`', $createViewSql);
    $wpdb->query($createViewSql);
}
  1. Run: wp eval-file ~/include/fix-definer.php

Fixing CiviCRM

To ensure you use the CiviCRM credentials and not the CMS credentials, you will need to download the cv tool from: https://github.com/civicrm/cv.

  1. Create the file ~/include/fix-definer.php
<?php

// This snippet fixes CiviCRM sites with multiple languages installed.
// You may or may not need it.
$sql = "SELECT locales FROM civicrm_domain WHERE locales IS NOT NULL";
$dao = CRM_Core_DAO::executeQuery($sql);
if ($dao->N != 0) {
  while ($dao->fetch()) {
    $langs = explode(CRM_Core_DAO::VALUE_SEPARATOR, $dao->locales);
    CRM_Core_I18n_Schema::rebuildMultilingualSchema($langs);
  }
}

// This snipped fixes all triggers.
Civi::service('sql_triggers')->rebuild(null, false);
\CRM_Core_DAO::triggerRebuild();
  1. Run: cv php:script ~/include/fix-definer.php

If all else fails

On each database server is the script mf-fix-definer which takes the arguments --dry-run and $USER to fix. You can fix with:

# See what will be fixed.
mf-fix-definer --dry-run mysql_user_name
# Fix it.
mf-fix-definer mysql_user_name