--db push/pull not working: Problems with mysqldump

frontenddevguy commented 4 years ago

I left a couple of comments on an old and closed issue, but perhaps better to open a fresh one to get to the root of this. It seems many people have been struggling with this one.

I could never get the db to push or pull (everything else worked great) and so just resolved to use WP Migrate DB plugin. However, that code has now forked in a few repos and I cannot get any of them to work anymore, hence needing this to work as the only alternative to using a less than ideal plugin or manually search/replacing urls in the db.

I have narrowed the problem down to Bash not knowing where mysqldump is installed. Even though I am using Zsh, the original error I was getting was: sh: mysqldump: command not found

Following on from my previous comments on the above referenced issue, I am not using vagrant, just AMPPS and Flywheel on OSX.

I am guessing that the wrong mysqldump is being referenced, since I went ahead and installed the mysql pkg installer (which was missing for some reason) that is now located at /usr/local/mysql/bin/mysql

It seems I need to tell bash (referenced from zsh) to use /Applications/AMPPS/mysql/bin/mysqldump

I have tried this in my .bash_profile: ssh-add -A 2>/dev/null;

export PATH="/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin/mysql:/usr/local/mysql/bin/mysqldump:/Applications/AMPPS/mysql/bin/mysqldump:$PATH"

if which rbenv > /dev/null; then eval "$(rbenv init -)"; fi export PATH=/usr/local/bin:/usr/local/rvm/gems/ruby-1.9.3-p194/bin:/usr/local/rvm/gems/ruby-1.9.3-p194@global/bin:/usr/local/rvm/rubies/ruby-1.9.3-p194/bin:/usr/local/rvm/bin:/usr/bin:/bin:/usr/sbin

So locate mysqldump now at least gives: /Applications/AMPPS/mysql/bin/mysqldump /Applications/AMPPS/mysql/bin/mysqldumpslow /Applications/AMPPS/mysql/man/man1/mysqldump.1 /Applications/AMPPS/mysql/man/man1/mysqldumpslow.1

Although which mysqldump gives: /usr/local/bin/mysqldump

which mysql gives mysql not found (when unalias mysql is uncommented in .zshrc. Otherwise it gives mysql: aliased to nocorrect mysql. And of course every change requires a restart of iTerm!!

I found a related issue here and also posted on SO

There is also a ticket on flywheel regarding the same issue.

Now the error when attempting to pull a remote db is: mysqldump: Got error: 1045: Access denied for user 'xxxxx (using password: YES) when trying to connect

Regarding this error, I had also replied to this ticket a couple of years ago (apologies for not replying to the last comment), where it seems the error may be related to the live server and not my local?

Any ideas how this should be done?

alessandro-fazzi commented 4 years ago

Before I'll read it deeply:

following looks wrong to me:

PATH="/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin/mysql:/usr/local/mysql/bin/mysqldump:/Applications/AMPPS/mysql/bin/mysqldump:$PATH"

Next would be better:

PATH="/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/Applications/AMPPS/mysql/bin:$PATH"

Remember: I'm just reacting so don't blame me at first take 😝

alessandro-fazzi commented 4 years ago

@frontenddevguy are you able to tell us if the error is local, remote or both? It should be easy to understand from wordmove's logs. Keep in mind that on pull or push we're always doing a connection to both local db and remote db: 1 for the backup and 2 for the export than back to 1 for the import, whatever 1 and 2 would be local or remote.

locate mysqldump does not mean anything from the executable commands perspective, so I'd advise about simply ignore that information. It's useful just for you to know where you have binaries.

Generally speaking this is an environment problem; I'd like to avoid to tell to wordmove an exact path of the binaries since having binaries in $PATH is a start point to work in a CLI environment.

which mysqldump should give your expected path. ssh user@remotehost which mysqldump should give the expected path on your remote server.

mysqldump: Got error: 1045: Access denied for user 'xxxxx (using password: YES) when trying to connec

Could or could not be a related error: if the mysqldump executable is not the expected one, then it's probably related because you have more mysql versions and more clients (mysql/mysqldump are clients to MySQL server) trying to connect to different versions. Otherwise I think it's another story and another issue.

I hope to manage to help you further.

alessandro-fazzi commented 4 years ago

Oh...I forgot! wordmove doctor will help you check your local configuration about mysql and mysqldump 😄

frontenddevguy commented 4 years ago

Thanks so much for responding quickly, and apologies for my limited knowledge of CLI.

Doctor gives: ▬▬ Using Movefile: ./Movefile ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

▬▬ Validating movefile section: global ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ✅ success | Formal validation passed

▬▬ Validating movefile section: local ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ✅ success | Formal validation passed

▬▬ Validating movefile section: staging ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ✅ success | Formal validation passed

▬▬ Validating movefile section: runcloud ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ❌ error | [/vhost] '': not matched to pattern /^https?:\/\//.

▬▬ Using Movefile: ./Movefile ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

▬▬ Checking local database commands and connection ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ❌ error | mysql command is not in $PATH ✅ success | mysqldump command is in $PATH ❌ error | We can't connect to the MySQL server using credentials specified in the Movefile. Double check them or try to debug your system configuration.

            The command used to test was:

            mysql --host=127.0.0.1 --user=root --password=root -e'QUIT'
❌  error |   We can't connect to the database using credentials
            specified in the Movefile, or the database does not
            exists. Double check them or try to debug your
            system configuration.

            The command used to test was:

            mysql --host=127.0.0.1 --user=root --password=root local -e'QUIT'

▬▬ Checking local wp-cli installation ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ✅ success | wp-cli is correctly installed ❌ error | wp-cli is not up to date. Use wp cli update to update to the latest version.

▬▬ Checking rsync ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ✅ success | rsync is installed at version 2.6.9

▬▬ Checking SSH client ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ ✅ success | SSH command found

The remote host gives: root@localhost:~# which mysqldump /usr/bin/mysqldump

which looks as it should?

I tried the PATH as suggested but it gives the same result

alessandro-fazzi commented 4 years ago

apologies for [...]

Hey never meant to be rude expressing myself; you don't need to excuse for anything!

I really really hate these bundles (AMPPS and friends). That said...

Would you mind to let me see the output of

ls /Applications/AMPPS/

and

ls /Applications/AMPPS/mysql/

please? I have not the heart to install it on my dev machine, sorry! 😝

frontenddevguy commented 4 years ago

Hey no worries, I see that you're going above and beyond to help out!

❯❯❯ ls /Applications/AMPPS/ Ampps.app ampps perl php-7.0 tmp Amupdate.app apache php php-7.1 var Icon? conf php-5.3 phpMyAdmin var_bak Pure-FTPd extra php-5.4 private version RockMongo mongodb php-5.5 python www SQLiteManager mysql php-5.6 share

❯❯❯ ls /Applications/AMPPS/mysql/ COPYING data include my-new.cnf README docs lib share bin etc man support-files

Both mysql and mysqldump are present in /bin

Yes, AMPPS seems not very well maintained. Local by Flywheel is much better. I do need something quick and easy for setting up local dev environments. My only concern with Flywheel is that it's created by commercial hosting. Fingers crossed it remains reliable enough and free.

alessandro-fazzi commented 4 years ago

It's mind blowing...

If you

echo $PATH shows that you have /Applications/AMPPS/mysql/bin in the list
ls /Applications/AMPPS/mysql/bin shows that you have mysql there
which mysql have to print something!

What's the output of file /Applications/AMPPS/mysql/bin/mysql? Is it a binary executable?

What happens if you do /Applications/AMPPS/mysql/bin/mysql -u YOUR_USER? Do you connect to something?

frontenddevguy commented 4 years ago

Sorry for the delay. It shows: /Applications/AMPPS/mysql/bin/mysql: Mach-O executable i386

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/Applications/AMPPS/var/mysql.sock' (61)

frontenddevguy commented 4 years ago

Finally getting somewhere.

Now:

/A/A/m/bin ❯❯❯ /Applications/AMPPS/mysql/bin/mysql -u root -p              ✘ 1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.36 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

I had to add export PATH=${PATH}:/Applications/AMPPS/mysql/bin/ to my.zshrc file

I have again tried pulling a remote db and the error looks a little different..

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
    local | mysqldump --host=127.0.0.1 --user=xxxxxx --password=xxxxxxxxxx --result-file="/Applications/AMPPS/www/soundmandala.test/wp-content/local-backup-1575628196.sql" xxxxx
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT COLUMN_NAME,                       JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS                WHERE SCHEMA_NAME = 'xxxxx' AND TABLE_NAME = 'mandala_commentmeta';': Unknown table 'column_statistics' in information_schema (1109)
/usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/base.rb:93:in `run': Return code reports an error (Wordmove::ShellCommandError)
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/base.rb:179:in `save_local_db'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/ssh/wpcli_sql_adapter.rb:29:in `backup_local_db!'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/ssh.rb:47:in `pull_db'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/cli.rb:79:in `block in pull'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/cli.rb:42:in `block in handle_options'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/cli.rb:41:in `each'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/cli.rb:41:in `handle_options'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/cli.rb:78:in `pull'
    from /usr/local/lib/ruby/gems/2.4.0/gems/thor-0.19.4/lib/thor/command.rb:27:in `run'
    from /usr/local/lib/ruby/gems/2.4.0/gems/thor-0.19.4/lib/thor/invocation.rb:126:in `invoke_command'
    from /usr/local/lib/ruby/gems/2.4.0/gems/thor-0.19.4/lib/thor.rb:369:in `dispatch'
    from /usr/local/lib/ruby/gems/2.4.0/gems/thor-0.19.4/lib/thor/base.rb:444:in `start'
    from /usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/exe/wordmove:6:in `<top (required)>'
    from /usr/local/bin/wordmove:23:in `load'
    from /usr/local/bin/wordmove:23:in `<main>'

alessandro-fazzi commented 4 years ago

Hi @frontenddevguy ,

it seems like the are some foreign tables in the database. I do not think this would happen with a standard WP database. Consider to find out which these foreign tables are and exclude them from the dump using standard mysqldump flags passed through the mysqldump_options movefile's key.

I'd bet a plugin added a table there; and probably it was not done in the right way or that table is incompatible with your local mysql version (check to have version or at least features parity from remote to local)

frontenddevguy commented 4 years ago

Aha thanks for the heads up. I searched and found this, which seems fairly definitive and generic (no special WP setup): SO answer

My version of MySQL Workbench did not show the option mebntioned there.

So I added [mysqldump] column-statistics=0 to the end of my.cnf in AMPPS/mysql/etc

Also added this to my-new.cnf in AMPPS/mysql

Neither has affected the error

frontenddevguy commented 4 years ago

Also, moving forward, I am planning to migrate my local sites from AMPPS to Local by Flywheel.

I can push/pull files here as I can on AMPPS, but the db error for Flywheel sites is: mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

As far as I understand it each site instance has its own docker install where the database will be. What would be the solution for working with that?

I'm really just trying to get something that works at this point :|

alessandro-fazzi commented 4 years ago

I'd take a look at #564 for the problem with flywheel solution.

Every setup has its own concerns; luckily wordmove simply uses mysql and mysqldump, but one has to tune it for his really own environment in order to make the connections work.

Personally I neither use Local nor AMPPS, so it's hard for me to push more forward with the troubleshooting. From the wordmove's point of view an "Access denied" is actually no more than an access denied, if you know what I mean ^__^"

Eventually I could try to use Local just to dive into the problem, if Local is getting popular (is it?); but I'd need some sort of fast lane walkthrough to get quickly to the problem, since I'm not interested in learning all their documentation :P

If you'll somehow help me, I should try to help you more

frontenddevguy commented 4 years ago

I seem to be really going down the rabbit hole with this one!

As far as MySQLWorkbench and setting [mysqldump] column-statistics=0 that is a bit of a quagmire. According to the post on serverfault newer versions of MySQLWorkbench had the option removed to unset this, and also Win/OSX menus appear to be different, which makes following instructions confusing. If editing my.cnf doesn't fix the problem then I don't know what else to try there.

Yes, I guess Local is quite popular, and it's really simple to set up. I was recommended it by a digital agency that does a lot of bespoke WordPress development. There are a couple of posts I found by searching "local by flywheel wordmove" but people are talking about replacing urls in wp_options there. These kind of workflows along with the like of 'all in 1 WP migration' plugins would grind productivity down to being very slow.

But https://github.com/welaika/wordmove/pull/565 looks promising and I commented there to ask how that would work.

What do you use for managing WordPress installs locally?

alessandro-fazzi commented 4 years ago

What do you use for managing WordPress installs locally?

Hehe...probably you'd be surprised

brew install php@7.2 mysql@5.7 + wp-cli is my setup. Then I fire off an unsophisticated wp server. That's it...

Actually @ welaika we use a sophisticated theme development environment (https://wordless.readthedocs.io/en/latest/), but under the wood of the complex structure, we locally develop on wp server.

Switching php version could be afforded brew linking or brew unlinking different version or - better - with something like phpbrew; switching mysql versions is more complicated, so I'd prefer to link/unlink versions using brew (but it is barely needed).

Does it sounds oversimplified? I swear we're an agency and we do from mid to mid/big wordpress projects ofter with more developers, CI, CD and so on...and I think it just works 😛

But #565 looks promising and I commented there to ask how that would work.

I'm also looking forward to have a follow up from there :) Fingers crossed!

alessandro-fazzi commented 4 years ago

BTW using "Local" I'd try something like this

  database:
    name: "local"
    user: "root"
    password: "root"
    host: "localhost"
    mysqldump_options: '--socket "/Users/fuzzy/Library/Application\ Support/Local/run/t1K8tNBor/mysqld.sock"'
    mysql_options: '--socket "/Users/fuzzy/Library/Application\ Support/Local/run/t1K8tNBor/mysqld.sock"'

Note that I had to escape the space in Application Support using Application\ Support.

Also note that your socket path will be different.

My result is

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

    local | mysqldump --host=[secret] --user=[secret] --password=[secret] --result-file="[secret]/wp-content/local-backup-1575983249.sql" --socket /Users/fuzzy/Library/Application\ Support/Local/run/t1K8tNBor/mysqld.sock local
mysqldump: [Warning] Using a password on the command line interface can be insecure.

    local | gzip -9 -f "[secret]/wp-content/local-backup-1575983249.sql"

   remote | mysqldump --host=[secret] --user=sshwordmove --password=[secret] --result-file="[secret]/wp-content/dump.sql" sshwordmove

   remote | gzip -9 -f "[secret]/wp-content/dump.sql"

   remote | get: [secret]/wp-content/dump.sql.gz [secret]/wp-content/dump.sql.gz
zlib(finalizer): the stream was freed prematurely.
zlib(finalizer): the stream was freed prematurely.

   remote | delete: [secret]/wp-content/dump.sql.gz
zlib(finalizer): the stream was freed prematurely.

    local | gzip -d -f "[secret]/wp-content/dump.sql.gz"

    local | mysql --host=[secret] --user=[secret] --password=[secret] --database=local --socket /Users/fuzzy/Library/Application\ Support/Local/run/t1K8tNBor/mysqld.sock --execute="SET autocommit=0;SOURCE [secret]/wp-content/dump.sql;COMMIT"
mysql: [Warning] Using a password on the command line interface can be insecure.

    local | adapt dump for vhost

    local | wp search-replace --path=[secret] [secret] [secret] --quiet --skip-columns=guid --all-tables --allow-[secret]

    local | adapt dump for wordpress_path

    local | wp search-replace --path=[secret] [secret] [secret] --quiet --skip-columns=guid --all-tables --allow-[secret]

    local | delete: '[secret]/wp-content/dump.sql'

frontenddevguy commented 4 years ago

Thanks for this! Hm the first part sounds deceptively simple. How do you manage all the different sites locally? Are you using Docker? And does this system allow for https locally?

I had a look at my local path and it is Application Support/Local by Flywheel But there is no run folder in there, and searching my Mac for mysqld.sock yielded no results.

alessandro-fazzi commented 4 years ago

I bootstrapped a new site from within Local and named it test.

I'm not using docker (why should I do here?), I'm really just in my terminal.

I have just 1 site - I installed Local in order to do the test... - but I'd expect from Local a new socket for each site.

This is my local config in movefile.yml

global:
  sql_adapter: 'wpcli'

local:
  vhost: "http://test.local"
  wordpress_path: "/Users/fuzzy/Local Sites/test/app/public" # use an absolute path here

  database:
    name: "local"
    user: "root"
    password: "root"
    host: "localhost"
    mysqldump_options: '--socket "/Users/fuzzy/Library/Application\ Support/Local/run/t1K8tNBor/mysqld.sock"'
    mysql_options: '--socket "/Users/fuzzy/Library/Application\ Support/Local/run/t1K8tNBor/mysqld.sock"'

I'd just advise to change the default path for site source:

it's awful from an automation point of view to have spaces in paths...
wordmove does not expect to have spaces there and this could lead to some problems (for example with hooks I thought...)
wordmove is not so straight in the escape process: it escapes everywhere, but I've the feeling it is escaping differently while using the wordpress_path and while constructing the mysql commands...this is probably a latent bug somewhere (shhhhh!)

These are my mysql binaries:

 which mysql
/usr/local/bin/mysql

 which mysqldump
/usr/local/bin/mysqldump

 ll (which mysql)
lrwxr-xr-x  1 fuzzy  admin    38B Sep  2 11:52 /usr/local/bin/mysql@ -> ../Cellar/mysql@5.7/5.7.27_1/bin/mysql

 ll (which mysqldump)
lrwxr-xr-x  1 fuzzy  admin    42B Sep  2 11:52 /usr/local/bin/mysqldump@ -> ../Cellar/mysql@5.7/5.7.27_1/bin/mysqldump

 mysqldump --version
mysqldump  Ver 10.13 Distrib 5.7.27, for osx10.14 (x86_64)

 mysql --version
mysql  Ver 14.14 Distrib 5.7.27, for osx10.14 (x86_64) using  EditLine wrapper

alessandro-fazzi commented 4 years ago

Please keep in mind mysql and mysqldump are just clients and we're using them to connect to a server. I have no freaking idea where flywheel guys put their database server 😅 but I really don't mind since they give me a socket path. I've tested with Sequel Pro: when you use it as a client you have the option to write the socket path, when you use mysql you have the command line flag --socket.

I know I'm making it easier than it actually is, but there is really nothing X-File classified here :)

alessandro-fazzi commented 4 years ago

@frontenddevguy I misunderstood your previous message!

Hm the first part sounds deceptively simple. How do you manage all the different sites locally? Are you using Docker? And does this system allow for https locally?

Was about my personal setup! Sorry.

cd path/to/site1 && wp server will start site1; cd path/to/site2 && wp server will start site2; nothing crazy
I can confirm I do not use docker locally for WP
this setup does not allow me to have certified https locally; good point. Obviously (?) you can setup a site on https but you'll get the tedious browser warning :)

frontenddevguy commented 4 years ago

Wow this is a mad one..

So, I was using an earlier version of 'Local by Flywheel'. This version did not have the socket information displayed, and was using VirtualBox which they decided to abandon in the newer version. The newer version is called 'Local'.

Having installed Local, I tried to export the sites and re-import them as per the Local instructions (using a zip file). Brand new sites would import, but anything with a real database would not. I was getting errors on line 4, duplicate entry (in Wordfence tables). Once I resolved that, the site import would then fail saying 'local' already exists (here is a local forum post about that) The suggested solution did not work.

So anyway, if I just set up a new site in Local (having changed the default folder from 'Local sites' to 'websites', I could just pull the files and db from my live environment, since these are all up to date.

I was able to create a new site and pull files from live with Wordmove succesfully using new Local.

My Movefile now looks like this:

global:
  sql_adapter: "wpcli"

local:
  vhost: "http://soundmandala.local"
  wordpress_path: "/Users/Yan/websites/soundmandala/app/public" # use an absolute path here

  database:
    name: "local"
    user: "root"
    password: "root"
    host: "localhost"
    mysqldump_options: '--socket "/Users/Yan/Library/Application Support/Local/run/fo24A7PGW/mysqld.sock"'
    mysql_options: '--socket "/Users/Yan/Library/Application Support/Local/run/fo24A7PGW/mysqld.sock"'

Just verifying that both these new entries should be pointing to the same path? These are what is displayed in Local in "DATABASE' tab.

Now If I try to pull the db I get:

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
/usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/base.rb:128:in `mysql_dump_command': undefined method `[]' for nil:NilClass (NoMethodError)

My .zshrc has at the end of the file:

export PATH=${PATH}:/Applications/AMPPS/mysql/bin
unalias mysql

This gives:

~/w/s/a/public ❯❯❯ which mysql                                                                     ✘ 1
/Applications/AMPPS/mysql/bin/mysql
~/w/s/a/public ❯❯❯ which mysqldump
/usr/local/bin/mysqldump
~/w/s/a/public ❯❯❯

Perhaps I now need to switch back to bash to rule out any crazy path complexity with ohmyzsh. Here someone is saying $which is not even reliable and to use $type or $whence instead.

I will keep hacking away at this even though I am stretching my understanding.. :))

alessandro-fazzi commented 4 years ago

@frontenddevguy I've edited your code to make it more legible and I noticed that the indentation under the database key was wrong. While editing I polished it, but that could bring to the error

/usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/base.rb:128:inmysql_dump_command': undefined method []' for nil:NilClass (NoMethodError)

so you should double check that part of configuration.

Unfortunately I'm a fish user and I cannot help you with zsh-related troubleshooting :( Just noticing you are unaliasing mysql but not mysqldump...but I have no idea what I'm saying 👅

Just verifying that both these new entries should be pointing to the same path?

If the new entries are mysql_options and mysqldump_options, that's right.

It feels like you're making some steps forward...I'm cheering on you ;)

frontenddevguy commented 4 years ago

Thanks, you're right the indentation was incorrect.

I noticed that AMPPS MySQL would not start. Apache started again only after rebooting. I had to stop the system MySQL that was running (in OSX preferences > MySQL), and then AMPPS MySQL would start.

After that I get this error:

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
    local | mysqldump --host=localhost --user=root --password=root --result-file="/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576258062.sql" --socket /Users/Yan/Library/Application Support/Local/run/fo24A7PGW/mysqld.sock local
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/Users/Yan/Library/Application' (2) when trying to connect

alessandro-fazzi commented 4 years ago

Hey Yan,

if you'd look back at my https://github.com/welaika/wordmove/issues/552#issuecomment-564129312 you'll notice that in my snippet the space in the path was escaped like

Application\ Support

You're writing options for a command line and spaces must be escaped.

Wordmove does a lot of escaping here and there, for security reasons, or because it is masking where and how your configurations will be used; but I'm perfectly ok with wordmove not escaping spaces there, because we're are directly writing command line options and we have to be aware of this.

That said: escape the space like I've done and that error will go away ;)

alessandro-fazzi commented 4 years ago

I've updated the documentation @ https://github.com/welaika/wordmove/wiki/movefile.yml-configurations-explained with a note about escape on these 2 configurations in the database section

frontenddevguy commented 4 years ago

That was careless of me, thanks for pointing it out again.

Ok, now starting to get somewhere new.. :)

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
    local | mysqldump --host=localhost --user=root --password=root --result-file="/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576668805.sql" --socket /Users/Yan/Library/Application\ Support/Local/run/fo24A7PGW/mysqld.sock local
mysqldump: [Warning] Using a password on the command line interface can be insecure.
    local | gzip -9 -f "/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576668805.sql"
   remote | mysqldump --host=localhost --user=wp_shala_sound --password=[password here] --result-file="/home/runcloud/webapps/soundmandala/wp-content/dump.sql" [db name here]
   remote | gzip -9 -f "/home/runcloud/webapps/soundmandala/wp-content/dump.sql"
   remote | get: /home/runcloud/webapps/soundmandala/wp-content/dump.sql.gz /Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql.gz
   remote | delete: /home/runcloud/webapps/soundmandala/wp-content/dump.sql.gz
    local | gzip -d -f "/Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql.gz"
    local | mysql --host=localhost --user=root --password=root --database=local --socket /Users/Yan/Library/Application\ Support/Local/run/fo24A7PGW/mysqld.sock --execute="SET autocommit=0;SOURCE /Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql;COMMIT"
Warning: Using a password on the command line interface can be insecure.
    local | adapt dump for vhost
    local | wp search-replace https://soundmandala.net http://soundmandala.local --quiet --skip-columns=guid --all-tables --allow-root
Error: Error establishing a database connection.
/usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/base.rb:93:in `run': Return code reports an error (Wordmove::ShellCommandError)

alessandro-fazzi commented 4 years ago

I got that error only when I forgot to start the site from Local; once started the site also the database connection was back again ... 🤷‍♀ I fear I'm arrives all down the path

frontenddevguy commented 4 years ago

I thought the site was up and running but now see that Local shows an error:

Heads-up! Local's router is having trouble starting.

alessandro-fazzi commented 4 years ago

That was careless of me, thanks for pointing it out again.

Ok, now starting to get somewhere new.. :)

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
    local | mysqldump --host=localhost --user=root --password=root --result-file="/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576668805.sql" --socket /Users/Yan/Library/Application\ Support/Local/run/fo24A7PGW/mysqld.sock local
mysqldump: [Warning] Using a password on the command line interface can be insecure.
    local | gzip -9 -f "/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576668805.sql"
   remote | mysqldump --host=localhost --user=wp_shala_sound --password=[password here] --result-file="/home/runcloud/webapps/soundmandala/wp-content/dump.sql" [db name here]
   remote | gzip -9 -f "/home/runcloud/webapps/soundmandala/wp-content/dump.sql"
   remote | get: /home/runcloud/webapps/soundmandala/wp-content/dump.sql.gz /Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql.gz
   remote | delete: /home/runcloud/webapps/soundmandala/wp-content/dump.sql.gz
    local | gzip -d -f "/Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql.gz"
    local | mysql --host=localhost --user=root --password=root --database=local --socket /Users/Yan/Library/Application\ Support/Local/run/fo24A7PGW/mysqld.sock --execute="SET autocommit=0;SOURCE /Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql;COMMIT"
Warning: Using a password on the command line interface can be insecure.
    local | adapt dump for vhost
    local | wp search-replace https://soundmandala.net http://soundmandala.local --quiet --skip-columns=guid --all-tables --allow-root
Error: Error establishing a database connection.
/usr/local/lib/ruby/gems/2.4.0/gems/wordmove-4.0.1/lib/wordmove/deployer/base.rb:93:in `run': Return code reports an error (Wordmove::ShellCommandError)

Do you have a config like this in you wp-config.php?

define('DB_HOST', 'localhost:/Users/fuzzy/Library/Application Support/Local/run/t1K8tNBor/mysqld.sock');

This is """just""" wp-cli complaining about the connection, but that could be a minor problem.

frontenddevguy commented 4 years ago

No, just the standard WP format in wp-config.php.

/** The name of the database for WordPress */
define( 'DB_NAME', 'local' );

/** MySQL database username */
define( 'DB_USER', 'root' );

/** MySQL database password */
define( 'DB_PASSWORD', 'root' );

/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

/** Database Charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8' );

/** The Database Collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', '' );

On Local forums some people had conflicts with XAMPP etc, so I tried shutting down Apache. This has fixed the Local site crashing, but the error when pulling db is the same, and the site does not reflect any changes.

I am keeping AMPPS running with just MySQL on, and Apache stopped.

frontenddevguy commented 4 years ago

I checked the Local log file and it was indeed complaining about ports 80 and 445 being in use (before I stopped Apache)

alessandro-fazzi commented 4 years ago

Give it a try with the DB_HOST config I've suggested before

frontenddevguy commented 4 years ago

Ok, now I got:

▬▬ Pulling Database ▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
    local | mysqldump --host=localhost --user=root --password=root --result-file="/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576670346.sql" --socket /Users/Yan/Library/Application\ Support/Local/run/fo24A7PGW/mysqld.sock local
mysqldump: [Warning] Using a password on the command line interface can be insecure.
    local | gzip -9 -f "/Users/Yan/websites/soundmandala/app/public/wp-content/local-backup-1576670346.sql"
   remote | mysqldump --host=localhost --user=wp_shala_sound --password=[pwd] --result-file="/home/runcloud/webapps/soundmandala/wp-content/dump.sql" [dbname]
   remote | gzip -9 -f "/home/runcloud/webapps/soundmandala/wp-content/dump.sql"
   remote | get: /home/runcloud/webapps/soundmandala/wp-content/dump.sql.gz /Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql.gz
   remote | delete: /home/runcloud/webapps/soundmandala/wp-content/dump.sql.gz
    local | gzip -d -f "/Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql.gz"
    local | mysql --host=localhost --user=root --password=root --database=local --socket /Users/Yan/Library/Application\ Support/Local/run/fo24A7PGW/mysqld.sock --execute="SET autocommit=0;SOURCE /Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql;COMMIT"
Warning: Using a password on the command line interface can be insecure.
    local | adapt dump for vhost
    local | wp search-replace https://soundmandala.net http://soundmandala.local --quiet --skip-columns=guid --all-tables --allow-root
    local | adapt dump for wordpress_path
    local | wp search-replace /home/runcloud/webapps/soundmandala /Users/Yan/websites/soundmandala/app/public --quiet --skip-columns=guid --all-tables --allow-root
    local | delete: '/Users/Yan/websites/soundmandala/app/public/wp-content/dump.sql'

But no change to soundmandala.local

frontenddevguy commented 4 years ago

Okay!!!

Final hurdle was that I chose a site with different table prefixes...

I feel like there should be some kind of orchestral music for this magical moment :))

Please let me know how I can send you a tip for a well-earned beer

alessandro-fazzi commented 4 years ago

This is how I'll have my launch time today

I actually have a suggestion about how you could send a tip: what about re-doing all these things from the beginning, briefly documenting the steps in markdown and add those notes on our wiki under the "Knowledge base" paragraph? Eventually that content could be delivered also to the "Local by Flywheel" community, if any (any?).

This would be a sponsorship for Wordmove and a help for Local's users.

Hey, I know that it would be more expensive than a beer in terms of time! Just an idea you're obviously free to ignore!

But thirst thing first: hooray @frontenddevguy ! You got a big journey on this one! :D I really hope you'll get the deserved value in your work :)

alessandro-fazzi commented 4 years ago

To you the honor to close this epic issue <3

frontenddevguy commented 4 years ago

Ok, let me have a look through all that's been written and see what can be condensed into notes for the wiki. This is the spirit of Open Source I guess :)

The fact I am using AMPPS for MySQL, Local (newer version) for websites and Zsh for Terminal should offer some useful information for edge cases! I can also post over on the Local forum that I managed to get this working, and also I think there a lot of people who were relying on Migrate DB until it was forked and each fork eventually abandoned. Probably linking to here would help someone too.

It's such a vital part of WordPress workflow to be able to do all this stuff quickly so I bet many more people would benefit from knowing about Wordmove.

Thanks again for being so patient and helpful with all this!

welaika / wordmove

--db push/pull not working: Problems with mysqldump #552