Now that’s a bit of a unexpected one – I exported all my Local Lightning (5.0.3) sites and started to re-import into Local (5.0.4), when a “Uh-oh! Unable to import site” popped up.
The mention of «Geometry field» would probably make me point in the general direction of a Google maps plugin, but still I’m surprised a site running without any problems in Local 5.0.3 can’t be imported into Local 5.0.4.
(Apart from that, the user experience with Local is just brilliant, I have to say. I’m not missing Valet+, Local is now just as fast, and I’m certainly not missing MAMP Pro.) 
Ah, another site with the same error.
Error: Command failed: /Applications/Local.app/Contents/Resources/extraResources/site-binaries/mysql-8.0.15/bin/mysql --default-character-set=utf8 -A --skip-column-names -e SET names ‘utf8’; use local; source /Users/philby/Sites/zwicker/app/sql/local.sql;
ERROR 1416 (22003) at line 1573 in file: ‘/Users/philby/Sites/zwicker/app/sql/local.sql’: Cannot get geometry object from data you send to the GEOMETRY field
I also ran into this a number of times. I was able to work around it by zipping up the sql files and the wp-content folder together and then use that zip to import.
1 Like
@philby, what Google Maps plugin are you using? I’d like to try to reproduce the issue with the GEOMETRY field.
Awesome, I’m very glad to hear!
One both sites, there’s WP Google Maps 7.11.35 and WP Google Maps Pro 5.76.
1 Like
So you didn’t use the Local export file, but a self-made file with just the SQL and wp-content?
I initially used Lightning’s Export feature for all of the sites. I got that error about 20% of the time. As all the sql files were in /app/sql/ I zipped them up with wp-content and dragged to import.
1 Like
Hey @philby –
I’ve been bitten by this error before and it’s pretty crappy. Here’s the summary:
- Local uses
mysqldumpfor exporting the database for the site - There is a long running bug (+10yrs) with
mysqldumpwhich means that it will exportPOINTcolumns as binary data: - https://bugs.mysql.com/bug.php?id=43544
What this means is that for plugins that store their data within the database as POINT’s, the default way to export the database (for example, using wp-cli, or Local’s export functionality) won’t work.
A tool like Adminer should work, or maybe another db client like SQL pro because they don’t use mysqldump.
To give you an idea of the underlying SQL that’s causing issues, here’s a screenshot:
and for searchability, here is a line that mysqldump will output:
INSERT INTO `wp_wpgmza` VALUES (4,1,'2140 North Northgate Way, Seattle, WA 98133, USA','','','','','47.709210','-122.332407','0','','1','',1,0,0,'','',_binary '\0\0\0\0\0\0\0�~[f\�\�G@\�D)F�^�');
Compared to what a different tool will output:
INSERT INTO `wp_wpgmza` VALUES (4,1,'2140 North Northgate Way, Seattle, WA 98133, USA','','','','','47.709210','-122.332407','0','','1','',1,0,0,'','',ST_GeomFromText('POINT(1 1)'));
Note that the correct data towards the end of the line should be ST_GeomFromText('POINT(1 1)') as opposed to a binary blob.
2 Likes
Hi Ben,
wow, now that’s what I call a bug report! Thanks a lot for this, much appreciated.
Since it’s only a few locations in the two SQL files, I might try and fix them in a text editor. If that fails, I’ll resort to Migrate DB Pro exporting and importing.
BTW, one of my dream ideas for Local Pro: it somehow recognizes an installed Migrate DB Pro and uses it to export DBs or maybe even make DB backups every day or hour 
1 Like
I love this idea! They do offer a WP-CLI command 
I’m beginning to suspect that MySQL 8 also plays a role in this – can’t remember seeing this error with other MySQL versions (but then I easily could be mistaken).
The same DB was pulled from the live server a few minutes earlier.
This was trying to push a DB from Local to the live server.
They most certainly do 
We have been seeing some issues crop up with thanks to the relative newness MySQL 8 as well.
In the meantime, there are some great tidbits here: Data contains characters which are invalid in the target database … Specifically, the WPMDB_STRIP_INVALID_TEXT constant.
Also, you may have luck converting the collation on the tables that are a newer collation such as utf8mb4_0900_ai_ci to a more compatible one by running:
ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
(I recommend backing up the table/database prior to doing this!)
Have had this happen as well. I think it was some Wordfence database table causing it.
Maybe the ability to use SQL 5.x should be set pretty high on the list of to-dos for Local.
Just had a case where Migrating a DB from SQL 8 to a SQL 5.x server failed.
The opposite migration worked just fine 2 days ago.
Not being able to safely migrate stuff from local dev to stage or live makes Local much less attractive, to me at least.
Have you tried @clay’s suggestion of adjusting the collation before migrating?
Running this query should fix up the character issue:
ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
By default, MySQL 8 uses a different collation but it can be converted for the sake of backward compatibility.
Can someone outline the steps in English for a newbie with no website experience? I’m not understanding the steps above. Thanks in advance!
@philby –
Since you’ve expressed some interest in the above error involving the POINT datatype, I wanted to share something that we noticed when reviewing how the WP Google Maps plugin integrates with Migrate DB Pro:
This obviously doesn’t solve the larger issue with MySQL exporting the POINT datatype, but is mostly meant to give some ideas for how others are working around the issue. We’re still thinking through how best Local can help with making this better, but maybe you’ll find it useful!
Hey @Raquel – Can you give some more details about what you’re encountering? If you have any screenshots that would be really helpful!
This topic is a little more technical and a little bit obscure, so I want to make sure that the problem you are facing is the same one as what was originally posted.