Problem migrating sites with mysql views

The issue seems to be with the definer bit -

http://stackoverflow.com/questions/154332/how-can-i-use-mysqldump-to-rep...

it seems the only option is for us to modify the dump after it has been created, or even more painfully as we import it

It may also help if the modules use the full definition for views.

ie: http://drupalcode.org/viewvc/drupal/contributions/modules/site_user_list...

Should be something like 'CREATE OR REPLACE DEFINER CURRENT_USER SQL SECURITY DEFINER VIEW '.
or something.

There is a dirty workaround I used with 100% success, see http://groups.drupal.org/node/65128#comment-194963

suscribe

I have half of workaround here, but I haven't tried adrian suggestion yet (instead of blablablah should be drop view sentences)
http://drupal.org/node/855528

subscribe.

subscribe

Included a patch that uses sed to change/remove the problematic lines, it works here, but I'm not sure if the version numbers are the same for all mysql's installs.

I don't like the sed into tmpfiles. Isn't there an option to mysqldump that does this better?

Also keep in mind you can chain multiple sed commands in one shot, no need to call sed multiple times.

AFAIK there's no option in mysqldump, i looked around but couldn't find anything. I'm not even sure if all versions of mysql (> 5) use the same syntax for the commands

What do you suggest to use if not a tmp file?

subscribe

(this is a major issue if managing multi-lingual CiviCRM sites in Aegir)

Instead of using a tempfile, the output of mysqldump should be sent straight into sed and then redirected to the file. Pseudocode:

mysqldump -r- | sed > database.sql

Also, instead of replacing the DEFINER line with emptiness, we should comment it out or delete it (//d).

So, I had a go at combining the sed commands from #9 and anarcat's all in a single line approach.

Patches attached.

Patch in #15 is working for me (6.x-1.x)

steven, i'll let you do the honors of pushing your patch. :)

Assigning to me then.

patch in #15 on 6 worked for me.

#15 works for me as well (tested on a site with CiviCRM). thanks!

Hmm, in fact, it seems that I am having issues on a server with MySQL 5.1. My database dump has the following:

/*!50003 CREATE */ /*!50017 DEFINER=`example`@`192.168.0.140` */ /*!50003 TRIGGER `civicrm_address_before_insert`[...]

I added a sed expression to remove that specific part, based on the patch from #15.

#15 worked for me too

Maybe we need to research to see if we need to remove any other lines too.

@SocialNicheGuru but can you test the patch in #21?

@Steven Jones: CiviCRM uses MySQL InnoDB extensively (especially for multi-lingual, relies on Views/Triggers), so I think it makes a good test-case. I checked the resulting SQL and it is pretty clean.

Patch at #21 worked for me. +1 for committing.

this looks like rtbc now

fix committed to head and 1.x

Sorry to reopen this really old issue, but I need feedback from the people that actually use MySQL views (which is not often my case).

After reading on this issue after getting in a pretty good SNAFU on a CiviCRM site here, I have discovered that people only remove the DEFINER bit, not the ALGO parts. Doing this could simplify the pipeline. Take this for example:

https://gist.github.com/1228701

A single regex:

        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;

Why do we also do this?

s|/\\*!50001 CREATE ALGORITHM=UNDEFINED \\*/|/\\*!50001 CREATE \\*/|g;

Worst, why isn't the sed pipeline combined into one? Again, here's the current pipeline, whitespace added for clarity:

    $cmd = sprintf("mysqldump --defaults-file=/dev/fd/3 --single-transaction --quick %s
 | sed 's|/\\*!50001 CREATE ALGORITHM=UNDEFINED \\*/|/\\*!50001 CREATE \\*/|g; 
        s|/\\*!50017 DEFINER=`[^`]*`@`[^`]*`\s*\\*/||g'
 | sed '/\\*!50013 DEFINER=.*/ d' 
> %s/database.sql", escapeshellcmd(drush_get_option('db_name')), escapeshellcmd(d()->site_path));

Doesn't that seem redundant to people here?

Thanks for the feedback.

PS: note that I have already removed the \s* statement in 2.x from the above because (1) it was not properly escaped and (2) it is a perlism, and won't work in sed...

PPS: for reference, the upstream MySQL and MariaDB bugs: http://bugs.mysql.com/bug.php?id=24680 https://mariadb.atlassian.net/browse/MDEV-3836

I think that the current 'multi-sed' approach was taken because that was the way that the command came about, i.e. built up over time and added too, not for any other reason really. I suspect that there's too much magic in that single command to understand fully, easily, so people only tinker with parts of it.

The patch from #21, committed in #29 caused a regression: #2098289: Backup task does not detect failed database dump

Is this still a problem? Please re-open with more information...

well the reason i marked this as needs work is because the pipeline seems sick in the head and should be simplified. i think this is still valid, unless i missed a commit.

This should be fixed by recent refactoring of this code. We now do the filtering line by line via proc_open() and preg_replace(), rather than piping to sed which was suppressing errors in mysqldumps. We also now have a hook to alter or add to the reflexes we run.

In fact the code refactoring introduces regression which breaks things completely.

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