Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Anything special in that database? Views? triggers? Functions?

It's very possible that the refactoring of how we filter out unwanted SQL statements in #1324466: provision-migrate fails because provision-backup creates a useless dump needs further work. The relevant regexes now live in Provision_Service_db_mysql::get_regexes(), and can be extended or altered by implementing hook_provision_mysql_regex_alter(&$regexes). I suspect the regexes simply need some work, as per this comment.

This affects CiviCRM sites as well:

Database import failed: ERROR 1227 (42000) at line 1283: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The regex anchors were misplaced. Re-open this issue if this bug persists for you on commit 880a57e, or later.

The patch didn't fix the problem for CiviCRM sites.

Perhaps we shouldn't be anchoring these regexes at all... Can you post the relevant line of the dump? This'll make replicating this issue much easier.

For CiviCRM specifically, it seems like implementing hook_provision_mysql_regex_alter() in provision_civicrm would be the way to go.

It worked just fine before this refactoring so it should work without the need for any extra hooks. I don't have relevant lines, just a failed task logs on sites working perfectly before this change/refactoring. It should be easy to reproduce on any CiviCRM site, I suppose, because we have received many reports from many users actively working with CiviCRM sites.

i believe we may have too many escapes, like mentionned in #1324466: provision-migrate fails because provision-backup creates a useless dump.

so the attempt with anchors was to avoid corrupting nodes (e.g. this one!!!) that would have snippets of database dumps in them. without the anchors, we could be messing with that data, which is really bad!

but since it broke other stuff, let's remove the anchors and split that in another issue. here's the patch that i pulled out of my ass while i was workign on this, incomplete:

diff --git a/db/Provision/Service/db/mysql.php b/db/Provision/Service/db/mysql.php
index 404d3b3..fd892fa 100644
--- a/db/Provision/Service/db/mysql.php
+++ b/db/Provision/Service/db/mysql.php
@@ -176,11 +176,11 @@ port=%s
     if (is_null($regexes)) {
       $regexes = array(
         // remove DEFINER entries
-        '#^/\\*!50013 DEFINER=.*/#' => FALSE,
+        '#^/\*!\d+ CREATE\*/ */\*!50013 DEFINER=.*/;#' => FALSE,
         // remove another kind of DEFINER line
-        '#^/\\*!50017 DEFINER=`[^`]*`@`[^`]*`\s*\\*/#' => '',
+        '#^(/\*!\d+ CREATE\*/ *)/\*!50017 DEFINER=`[^`]*`@`[^`]*`\s*\*/;#' => '\1',
         // remove broken CREATE ALGORITHM entries
-        '#^/\\*!50001 CREATE ALGORITHM=UNDEFINED \\*/#' => '/\\*!50001 CREATE \\*/',
+        '#^/\*!50001 CREATE ALGORITHM=UNDEFINED \*/;#' => '/\*!50001 CREATE \*/;',
       );

       // Allow regexes to be altered or appended to.

I've removed the anchors and double-escaping, which should get us back to a working solution here. Please confirm, for those of you seeing this. If it's still an issue, please post the relevant line from the db dump, to make testing easier.

I'm going to close this issue and open a new one to address the possibility of removing lines we wouldn't want in body fields, etc.

follow-up issue: #2485017: SQL filtering (slightly) too agressive

From my earlier testing with plain sed, just removing the double-escaped backslashes should be sufficient, the anchors weren't a problem.

Actually, strike that, while I did not touch any anchors, that's because they weren't there in the script I tested (https://github.com/omega8cc/boa/pull/672).

Indeed, 764fcb8 fixed the problem for CiviCRM sites. Thanks!