search

welaika / wordmove

Multi-stage command line deploy/mirroring and task runner for Wordpress
https://wptools.it/wordmove
MIT License
1.87k stars 167 forks source link

Local backup of database with views fails #583

Closed korkey128k closed 4 years ago

korkey128k commented 4 years ago

Describe the bug When pulling a database with a view, the local backup fails to run because the definer (remote user) does not exist;
mysqldump: Got error: 1449: "The user specified as a definer ('{remote_user}'@'{remote_host}') does not exist" when using LOCK TABLES

Wordmove command wordmove pull -d -e staging

Expected behavior The local database successfully backs up.

movefile.yml

global:
  sql_adapter: wpcli

local:
  vhost: #######
  wordpress_path: #######

  database:
    name: #######
    user: #######
    password: #######
    host: localhost

staging:
  vhost: #######
  wordpress_path: #######

  database:
    name: #######
    user: #######
    password: #######
    host: localhost

  exclude:
    - '.git/'
    - '.gitignore'
    - 'node_modules/'
    - 'bin/'
    - 'tmp/*'
    - 'Gemfile*'
    - 'Movefile'
    - 'movefile'
    - 'movefile.yml'
    - 'movefile.yaml'
    - 'wp-config.php'
    - 'wp-content/*.sql.gz'
    - '*.orig'
    - 'dev.log'

  ssh:
    host: #######
    user: #######

Exception/trace

local | mysqldump --host=[secret] --user=[secret] --password=[secret] --result-file="[secret]/wp-content/local-backup-1580911741.sql" #########
mysqldump: Got error: 1449: "The user specified as a definer ('{remote_user}'@'{remote_host}') does not exist" when using LOCK TABLES
Traceback (most recent call last):
  17: from #####/gems/ruby-2.6.3/bin/ruby_executable_hooks:24:in `<main>'
  16: from #####/gems/ruby-2.6.3/bin/ruby_executable_hooks:24:in `eval'
  15: from #####/gems/ruby-2.6.3/bin/wordmove:23:in `<main>'
  14: from #####/gems/ruby-2.6.3/bin/wordmove:23:in `load'
  13: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/exe/wordmove:6:in `<top (required)>'
  12: from #####/gems/ruby-2.6.3/gems/thor-0.20.3/lib/thor/base.rb:466:in `start'
  11: from #####/gems/ruby-2.6.3/gems/thor-0.20.3/lib/thor.rb:387:in `dispatch'
  10: from #####/gems/ruby-2.6.3/gems/thor-0.20.3/lib/thor/invocation.rb:126:in `invoke_command'
   9: from #####/gems/ruby-2.6.3/gems/thor-0.20.3/lib/thor/command.rb:27:in `run'
   8: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/cli.rb:91:in `pull'
   7: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/cli.rb:39:in `handle_options'
   6: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/cli.rb:39:in `each'
   5: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/cli.rb:40:in `block in handle_options'
   4: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/cli.rb:92:in `block in pull'
   3: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/deployer/ssh.rb:47:in `pull_db'
   2: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/deployer/ssh/wpcli_sql_adapter.rb:29:in `backup_local_db!'
   1: from #####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/deployer/base.rb:183:in `save_local_db'
#####/gems/ruby-2.6.3/gems/wordmove-5.0.2/lib/wordmove/deployer/base.rb:95:in `run': Return code reports an error (Wordmove::ShellCommandError)

Environment (please complete the following information):

Doctor

alessandro-fazzi commented 4 years ago

Hello,

how do you manually backup you database?

Said that I don't know much about operations on views, this seems a configuration problem to me more than a bug. Or, depending on the answer to the previous question, an unsupported task.

Wordmove is throwing Wordmove::ShellCommandError just because mysqldump errored.

If you can share your manual backup command, I'll try to figure out something more. TIA

korkey128k commented 4 years ago

The backup command is what wordmove is running: mysqldump --host=[secret] --user=[secret] --password=[secret] --result-file="[secret]/wp-content/dump.sql" [secret]. We manually backup the database the same, no fancy dump flags.

If I remove the view locally (DROP VIEW *****;) before every pull, I can use wordmove pull -d -e staging and everything works, but if I don't, it throws the above error.

I understand if this isn't supported, database views are pretty "close to the metal" imo. Found this stack overflow article on how to remove the definer during a dump, we could just implement that client side for a fix.

alessandro-fazzi commented 4 years ago

There is not an explicit support actually. This is the first time I come across such a question. And moreover I thought it was all up to mysqldump to manage the thing.

Reading the page you linked and following some link there, I also read this one; if it could work around your problem you'd just need to add the flag to the mysqldump_options in movefile.yml. I've already used the --single-transaction to solve different problems and it never led to collateral effects, FWIW

korkey128k commented 4 years ago

Doing a --single-transaction in the mysqldump_options worked great. Thanks @pioneerskies!