search

welaika / wordmove

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

MySQL POINT type crashes search & replace #465

Closed taliesinpenbardd closed 4 years ago

taliesinpenbardd commented 6 years ago

Hey all,

Thanks to a GMaps plugin, I've discovered the POINT type field in MySQL : ref, ref. The issue is referenced (even if they say it's not a bug, they don't dare to say it's a feature).

When I do SELECT id, latlng from prefix_table where id=1;, I get gibberish : 

+----+---------------------------+
| id | latlng                    |
+----+---------------------------+
|  1 |        ��+,�E@?�a�'�@ |
+----+---------------------------+

I'd have to do SELECT id, AsText(latlng) from prefix_table where id = 1; to get a readable answer : 

+----+---------------------------+
| id | AsText(latlng)            |
+----+---------------------------+
|  1 | POINT(43.720098 7.193511) |
+----+---------------------------+

And SELECT id, hex(latlng) from prefix_table where id = 1; :

+----+----------------------------------------------------+
| id | hex(latlng)                                        |
+----+----------------------------------------------------+
|  1 | 0000000001010000001FF5D72B2CDC45403FAA61BF27C61C40 |
+----+----------------------------------------------------+

Of course, the fun part is that search and replace chrashes when encountering that gibberish. No more exporting and importing db.

Is it possible to automagically detect the MySQL type of field and export/import it accordingly ? Or would it be better to add a rule in movefile.yml for this field ?

I don't know if the suggested patch (--hex-blob=POINT,LINE) has been resolved (it's been 6 years, though, I hope it's been). But that could be a lead. EDIT: adding mysqldump_options: "--hex-blob=POINT" in movefile doesn't resolve the search & replace crash...

Let me know if I can give more intel to help resolving that.

Cheers. L

alessandro-fazzi commented 6 years ago

Hi @taliesinpenbardd ,

most of the time it could be enough to ignore that table in mysqldump_options. Is that a separate table?

taliesinpenbardd commented 6 years ago

I guess it could be ignored, but it's part of the website (a map with the client's agencies on it) and it's regularly updated, so I'd prefer to work on it on my local environment. It's a separate table, but with the global prefix, so it's part of WP (WP Gmaps Pro plugin).

alessandro-fazzi commented 6 years ago

Fact is that we could manage to ignore columns during adapt, but not during import. Dump with non-utf chars will fail during import process if the table and the column are declared as UTF-8, but if they are declared differently, they could be ok...

We have to inspect in deep the issue anyway.

alessandro-fazzi commented 6 years ago

adding mysqldump_options: "--hex-blob=POINT" in movefile doesn't resolve the search & replace crash...

Have you stated the hex-blob in both local and remote stages? Anyway I think the problem is more on the MySQL side: hex blob will convert BLOB type columns, but as far as I undestood your column type is POINT. Am I right?

taliesinpenbardd commented 6 years ago

Hey, Yes, the hex-blob option has been defined on both local and remote. Yes, you are right, the column type is POINT. I'm a bit at loss...

alessandro-fazzi commented 6 years ago

Hi @taliesinpenbardd ,

would you mind to report the exact error you get? Also: what sql_adapter are you using? default or wpcli?

taliesinpenbardd commented 6 years ago

Hey @pioneerskies,

The error is as follows, with sql_adapter: default (mysqldump_options: "--hex-blob=BIT" but I tried with POINT, BLOB and anything I could think of) : 

Traceback (most recent call last):
    21: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/ruby_executable_hooks:15:in '<main>'
    20: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/ruby_executable_hooks:15:in 'eval'
    19: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/wordmove:23:in '<main>'
    18: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/wordmove:23:in 'load'
    17: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/exe/wordmove:6:in '<top (required)>'
    16: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor/base.rb:444:in 'start'
    15: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor.rb:369:in 'dispatch'
    14: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor/invocation.rb:126:in 'invoke_command'
    13: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor/command.rb:27:in 'run'
    12: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:78:in 'pull'
    11: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:41:in 'handle_options'
    10: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:41:in 'each'
     9: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:42:in 'block in handle_options'
     8: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:79:in 'block in pull'
     7: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/deployer/ssh.rb:46:in 'pull_db'
     6: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/deployer/ssh/default_sql_adapter.rb:30:in 'adapt_remote_db!'
     5: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/deployer/ssh/default_sql_adapter.rb:42:in 'adapt_sql'
     4: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/sql_adapter/default.rb:18:in 'adapt!'
     3: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/sql_adapter/default.rb:26:in 'replace_vhost!'
     2: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/sql_adapter/default.rb:38:in 'replace_field!'
     1: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/sql_adapter/default.rb:45:in 'serialized_replace!'
/Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/sql_adapter/default.rb:45:in 'gsub!': invalid byte sequence in UTF-8 (ArgumentError)

When I change to sql_adapter: wpcli, it gives me : 

Error: Erreur lors de la connexion à la base de données. Cela signifie soit que l’identifiant ou le mot de passe dans votre fichier 'wp-config.php' n’est pas correct, soit que nous ne pouvons pas contacter le serveur de base de données à l’adresse 'vvv.dev:3306'. Cela peut signifier que votre serveur de base de données est tombé.
Traceback (most recent call last):
    16: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/ruby_executable_hooks:15:in '<main>'
    15: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/ruby_executable_hooks:15:in 'eval'
    14: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/wordmove:23:in '<main>'
    13: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/bin/wordmove:23:in 'load'
    12: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/exe/wordmove:6:in '<top (required)>'
    11: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor/base.rb:444:in 'start'
    10: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor.rb:369:in 'dispatch'
     9: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor/invocation.rb:126:in 'invoke_command'
     8: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/thor-0.19.4/lib/thor/command.rb:27:in 'run'
     7: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:78:in 'pull'
     6: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:41:in 'handle_options'
     5: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:41:in 'each'
     4: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:42:in 'block in handle_options'
     3: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/cli.rb:79:in 'block in pull'
     2: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/deployer/ssh.rb:46:in 'pull_db'
     1: from /Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/deployer/ssh/wpcli_sql_adapter.rb:37:in 'adapt_remote_db!'
/Users/lucdlb/.rvm/gems/ruby-2.4.0/gems/wordmove-3.0.2/lib/wordmove/deployer/base.rb:107:in 'run': Return code reports an error (Wordmove::ShellCommandError)

Which is quite logical, since I'm out of the Docker instance. When I SSH the Docker container and try to pull, it says Wordmove isn't installed (nor is NodeJS). For reference, I'm using Local by Flywheel (formerly Pressmatic) as a local server.

Any other info I could give you ?

alessandro-fazzi commented 4 years ago

Closing because stalled. But nowadays the wpcli adapter should work even if called from another context finding the right wp-config.php file