Using Aegir 7.x-3.x, cloning/migrating a site with views/triggers (CiviCRM) fails because of the regexes that are run on the sql dump. This seems linked to #1324466: provision-migrate fails because provision-backup creates a useless dump and commit a49295c0d32837443c07e6262e74094a61d222f2.

The error shown by the front-end is: Database import failed: ERROR at line 15316: Unknown command '\*'.

Looking at the database.sql, line 15316 has:

/*!50001 SET collation_connection      = utf8_general_ci */;
/\*!50001 CREATE \*/
/*!50001 VIEW `civicrm_batch_en_US` AS select `civicrm [...]

Comments

bgm’s picture

bgm CreditAttribution: bgm commented

To be more precise, reverting patch 764fcb8cd6e198ac8b0b7cdf2dd14afb5782092e solves the problem.

omega8cc’s picture

omega8cc CreditAttribution: omega8cc commented

The patch you have referenced did two things: it removed anchors because they have caused problems reported previously *and* modified escapes in the regexes.

This change didn't cause any problems in our tests with blank CiviCRM sites on BOA, so it seems that some extra CiviCRM features, when enabled, may trigger this problem.

Reverting it may fix something and break (again) something else, so we need to be careful and test what exactly (and why) breaks things here: removed double escaping or removed anchors?

ergonlogic’s picture

ergonlogic
he/him
Primary language English
Location Montréal, Québec 🇨🇦
CreditAttribution: ergonlogic as a volunteer commented

I'd suggest using our new hook, hook_provision_mysql_regex_alter(&$regexes) to test/fix this. I've just clarifies the API docs to read:

/**
 * Alter the array of regexes used to filter mysqldumps.
 *
 * @param $regexes
 *   An array of patterns to match (keys) and replacement patterns (values).
 *   Setting a value to FALSE will omit the line entirely from the database
 *   dump. Defaults are set in Provision_Service_db_mysql::get_regexes().
 */
function hook_provision_mysql_regex_alter(&$regexes) {
  $regexes = array(
    // remove these lines entirely.
    '#/\*!50013 DEFINER=.*/#' => FALSE,
    // just remove the matched content.
    '#/\*!50017 DEFINER=`[^`]*`@`[^`]*`\s*\*/#' => '',
    // replace matched content as needed
    '#/\*!50001 CREATE ALGORITHM=UNDEFINED \*/#' => '/\*!50001 CREATE \*/',
  );
}

That said, I'm happy to fix the defaults to work with CiviCRM. So let me know what's causing the issue, and you're suggested fix.

bgm’s picture

bgm CreditAttribution: bgm commented

Thanks for the responses. I will do more testing. For reference, I am using multi-lingual CiviCRM, so there are mysql views in the sql dump (by default, CiviCRM does not use mysql views).

omega8cc’s picture

omega8cc CreditAttribution: omega8cc commented

We have identified the problem. The replacement string in the third line needs to be literal (replacement in preg_replace() is not regex!), thus it should *not* use any escapes -- then it works:

      $regexes = array(
        // remove DEFINER entries
        '#/\*!50013 DEFINER=.*/#' => FALSE,
        // remove another kind of DEFINER line
        '#/\*!50017 DEFINER=`[^`]*`@`[^`]*`\s*\*/#' => '',
        // remove broken CREATE ALGORITHM entries
        '#/\*!50001 CREATE ALGORITHM=UNDEFINED \*/#' => "/*!50001 CREATE */",
      );

  • omega8cc committed e1502d7 on 7.x-3.x 
    Issue #2497091 - The replacement string in the third line needs to be...
omega8cc’s picture

omega8cc CreditAttribution: omega8cc commented
Version: 7.x-3.0-beta2 » 7.x-3.x-dev
Status: Active » Fixed

This has been fixed in e1502d7aafcf5d1d15e4395beac5416d4ff31ada

bgm’s picture

bgm CreditAttribution: bgm commented

Thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.