Only dump tables with the correct prefix

halvardos commented 9 years ago

When trying to wordmove pull -d the following error message is encountered:

 ▬▬ ✓ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
mysqldump --host=localhost --user=XXXXXX --password=XXXXXX superbra --result-file=/Users/halvard/Documents/Mekom/Prosjekter/superbra/wp-content/local-backup-1442313509.sql
    local | mysqldump --host=localhost --user=XXXXXX --password=XXXXXX superbra --result-file=/Users/halvard/Documents/Mekom/Prosjekter/superbra/wp-content/local-backup-1442313509.sql
mysqldump --host=XXX.XXX.XX --user=XXXXX --password=XXXXX XXXXX --result-file=XXX/XXX/XXX/wp-content/dump.sql
   remote | mysqldump --host=XXX.XXX.XX --user=XXXXX --password=XXXXX XXXXX --result-file=XXX/XXX/XXX/wp-content/dump.sql
   remote | get: XXX/XXX/XXX/wp-content/dump.sql /Users/halvard/Documents/Mekom/Prosjekter/superbra/wp-content/dump.sql
   remote | delete: XXX/XXX/XXX/wp-content/dump.sql
    local | adapt dump
/Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:44:in `gsub!': invalid byte sequence in UTF-8 (ArgumentError)
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:44:in `serialized_replace!'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:36:in `replace_field!'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:25:in `replace_vhost!'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:17:in `adapt!'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/deployer/base.rb:171:in `adapt_sql'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/deployer/ssh.rb:31:in `pull_db'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/cli.rb:49:in `block in pull'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/cli.rb:36:in `block in handle_options'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/cli.rb:34:in `each'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/cli.rb:34:in `handle_options'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/lib/wordmove/cli.rb:48:in `pull'
    from /Library/Ruby/Gems/2.0.0/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
    from /Library/Ruby/Gems/2.0.0/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
    from /Library/Ruby/Gems/2.0.0/gems/thor-0.19.1/lib/thor.rb:359:in `dispatch'
    from /Library/Ruby/Gems/2.0.0/gems/thor-0.19.1/lib/thor/base.rb:440:in `start'
    from /Library/Ruby/Gems/2.0.0/gems/wordmove-1.3.1/exe/wordmove:6:in `<top (required)>'
    from /usr/bin/wordmove:23:in `load'
    from /usr/bin/wordmove:23:in `<main>'

I have interrupted the process before it deletes the dump.sql file, and what i noticed is that it dumps all tables in the DB, as we have a lot of wordpress installs on the server with different prefixes I'm thinking this might have something to do with it.

Push works fine, and the files seems to be identical, same version of mysqldump, encoding, etc.

alessandro-fazzi commented 9 years ago

Have you already took a look at ~~https://github.com/welaika/wordmove/wiki/invalid-byte-sequence-in-UTF-8-(ArgumentError)-with-wordmove-init~~ https://github.com/welaika/wordmove/wiki/invalid-byte-sequence-in-UTF-8-while-pushing---pulling-db ?

Sry...previously copy/pasted the wrong URL...

halvardos commented 9 years ago

Hi

I'm sorry i had not read that wiki entry, but that explains why pushing works and not pulling.

The tables I'm working with (all under the same prefix) all have the correct encoding. But on the server wordmove dumps all tables, including heaps of tables from other wordpress installs, probably some of them have problems with encoding.

Should wordmove limit the dump to tables with the correct prefix? I can't say that i see any downside to limiting the dump.

alessandro-fazzi commented 9 years ago

That's ok; thanks for taking the time to read it.

Actually I don't know if wordmove should dump only x_ prefixed tables. One thing is sure: that's a really different behaviour from the present one. Just think about a situation like:

wp01_posts wp01terms wp01[...all the standard wp tables] wp01_my_custom_plugin_table

because of the impossibility to tell mysqldump to dump only wp01_* tables, we should add some logic in order to dump tables from information_schema filtering by table name, than use the result as the dump input options...

Absolutely not impossible, but it isn't effortless. And anyway, why the hell someone should have multiple WPs into a single database? I think this approach is quite an anarchist one... you don't think so? Mine isn't sarcasm at all: is all about trying to comprehend other developers perspectives.

Thanks for the time you spent on this discussion.

Cheers

halvardos commented 9 years ago

First of all; thank you for spending time discussing this :)

I think this approach is quite an anarchist one... you don't think so? -@pioneerskies

Yes :) But you don't always get to choose these things.

Having several wp_installs on the same DB is far from ideal and in this case is only really a problem on a test server we run. And that is our problem, not Wordmove :)

But at the same time it is not hard to see a case where one would use the DB for something not related to the wordpress install. And having wordmove actually altering these tables without warning is potentially destructive.

Consider the following case:

You store your customers in a table (lets call it table X), stored form a cron job from some old system.(the example is not really that important, but some sensitive data that is not wordpress related). You push your local install of wordpress from your local install to the server. You then pull changes from server Make changes locally again. Push. But while you were making changes locally table X was changed by someone else, now when you pushed you also pushed an old version of table X.

I know you make backups for each push/pull, but overwriting a table not related to wordpress, and not initially in your push is not expected behaviour and might go unnoticed. Potentially hurting a business.

I look forward to hearing your opinion.

alessandro-fazzi commented 9 years ago

Just an occasional note while I'm out of office due to a conference :) You're on a right path with your example. I think we're elaborating on something potentially important. I've never thought about it before, probably because we always pretend to have separated DBs for such things, but yes...there's the possibility to compromise data.

I've also never thought about it, but Wordmove should have (not sure because the piece of code was developed by a colleague) a method to retrieve wp-config.php configs...and there's the db prefix also...

It doesn't turns out that changing the actual behaviour would be that easy, but it could be a good starting point...

Cheers and thanks

halvardos commented 9 years ago

Thank you, I hope to be able to try and implement this change and make a PR but it might take a week or more before i have time to give it a shot. (also given that I'm not a native Ruby speaker it might take some time).

I'll keep this topic open for now, so we can discuss it further when you return from the conference.

alessandro-fazzi commented 9 years ago

Just took a look and I have to retreat on my own hypothesis: we can't auto-extract prefix from wp-config.php with actual code base, because in wp-config.php everything is defined as constant using

define('NAME', 'value')

EXCEPT (!!!!!) the table prefix, which is instead a wonderful dropped-in-the-air variable. Gosh...

alessandro-fazzi commented 9 years ago

@Jeger and @anyoneinterested,

an idea popped in my brain so I decided to put it down to a branch.

Notes:

near totally untested (no automatic tests and just a bit of poor man's testing) and moreover I broke the test suite (as expected)
forked from master, so the branch hasn't all the cool feature of the dev branch (and so the pre-release gem)
if you'd like to test a bit (on a test wp install with a test database...) you'll have to uninstall wordmove with gem uninstall wordmove, clone the repo, checkout the prefixed_tables branch, run rake install. This should build and install the local version of the gem.

halvardos commented 8 years ago

@pioneerskies I'm sorry for the long wait, I have not had any time to test until now.

During basic testing i could not find any problems. I have tried both push and pull with a database with only one, and several different prefixes, i also tested with a wpxxxxx prefix when a wp prefix was present, and vica versa.

This seems promising! :)

Is there anything else i can do?

halvardos commented 8 years ago

Found a potential issue:

If i have "wp_" defined as local prefix and "wpsomething" as remote prefix in my movefile, pushing to an empty remote creates tables with "wp_" prefix.

alessandro-fazzi commented 8 years ago

Oh man! You're all the way right! If prefixes are different we need to implement a substitution on the dump, probably adding a new method for a bit of regex-balck-magic inside the (mysql adapter)[https://github.com/welaika/wordmove/blob/prefixed_tables/lib/wordmove/sql_adapter.rb]

(and now we have two problems)(cit.)

LudovicFauchet commented 8 years ago

Hello,

I have the same problem except in my case i had the same local and remote prefix.

Does this issue is fixed ?

Cheers

mysqldump --host=localhost --user=xxxx --password=xxxx xxxx --result-file=/Users/ludovicfauchet/Dropbox/sites/clients/MyJungly/ultradome360/wp/wp-content/local-backup-1444722368.sql
    local | mysqldump --host=localhost --user=xxxx --password=xxxx xxxx --result-file=/Users/ludovicfauchet/Dropbox/sites/clients/MyJungly/ultradome360/wp/wp-content/local-backup-1444722368.sql
mysqldump --host=xxxx --user=xxxx --password=xxxx xxxx --result-file=/home/ultradomkd/www/wp-content/dump.sql
   remote | mysqldump --host=xxxx --user=xxxx --password=xxxx xxxx --result-file=/home/ultradomkd/www/wp-content/dump.sql
   remote | get: /home/ultradomkd/www/wp-content/dump.sql /Users/ludovicfauchet/Dropbox/sites/clients/MyJungly/ultradome360/wp/wp-content/dump.sql
   remote | delete: /home/ultradomkd/www/wp-content/dump.sql
    local | adapt dump
/Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:44:in `gsub!': invalid byte sequence in UTF-8 (ArgumentError)
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:44:in `serialized_replace!'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:36:in `replace_field!'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:25:in `replace_vhost!'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/sql_adapter.rb:17:in `adapt!'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/deployer/base.rb:171:in `adapt_sql'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/deployer/ssh.rb:31:in `pull_db'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/cli.rb:49:in `block in pull'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/cli.rb:36:in `block in handle_options'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/cli.rb:34:in `each'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/cli.rb:34:in `handle_options'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/lib/wordmove/cli.rb:48:in `pull'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/thor-0.19.1/lib/thor.rb:359:in `dispatch'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/thor-0.19.1/lib/thor/base.rb:440:in `start'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/gems/wordmove-1.3.1/exe/wordmove:6:in `<top (required)>'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/bin/wordmove:23:in `load'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/bin/wordmove:23:in `<main>'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `eval'
    from /Users/ludovicfauchet/.rvm/gems/ruby-2.2.3/bin/ruby_executable_hooks:15:in `<main>'

ncko commented 8 years ago

I'm in this situation right now. The prefixes on my local and staging servers are different, so staging does not reflect my changes.

alessandro-fazzi commented 8 years ago

@LudovicFauchet your problem seems to be far different from the implementation of this feature. Please refer to this wiki entry.

@nicklolsen et alter: the development branch named prefixed_table never reached to implement the feature thus was never merged. We'll consider to continue the work in the near future.

tjsteinhaus commented 8 years ago

Any updates on this? I'm running into an issue where I want to build a WordPress plugin that can trigger a push and pull between a live and staging site for my SEO team. I'm running into an issue where if the Gravity Forms table on the live site is updated with new form data a push from staging will override these entries if the push is older than the entries. It would be nice for an exclude tables/skip tables option.

alessandro-fazzi commented 8 years ago

Hmmm. No, the feature is actually stalled and nobody pushed the initial work forward. But your need to exclude tables seems a different one from supporting different table prefixes. Wordmove hasn't any logic to list/recognize/exclude/include a subset of tables in the database...this would be a completely new one to build from the ground up.

maiorano84 commented 7 years ago

Echoing the sentiments about the skip table option. I went through the source code and I'm not seeing any explicit calls to mysqldump (I don't really know Ruby all that well), but from the logs above, it does look as if mysqldump is run at some point in the command lifecycle.

Would it not be a simple matter of appending --ignore-table options to the command responsible for the dump? Once again, I only took a cursory glance at the code, so I don't actually know what's going on under the hood here. But I do think it's worth a look, since form entries are definitely something you don't want to overwrite in a production environment.

alessandro-fazzi commented 7 years ago

Please @maiorano84 take a look here when we speak about the Movefile's database section

maiorano84 commented 7 years ago

I CAN READ, I SWEAR

Seriously, not sure how I missed that part. Thanks, brotato.

alessandro-fazzi commented 7 years ago

AHAHAH @maiorano84 you make me think to https://www.youtube.com/watch?v=DKtjBqJ4NxA

kingluddite commented 7 years ago

My database was not syncing properly. I saw that the db was the same on production as local but for some reason the current active theme was not correct. It was pointing to an old theme I was using months ago. Super strange. After an hour of troubleshooting I figured it had to do with the table prefix. On production wp-config.php had wp_ and on local it had moldr_. I just opened the local wp-config.php and changed it to wp_ and then used wordmove pull -e production -d and that did the trick. Really have no idea why it worked but figured I'd document it just in case it might help someone else.

alessandro-fazzi commented 6 years ago

Closing and moving conversation in #442

welaika / wordmove

Only dump tables with the correct prefix #219