'creating / restoring db dumps with binary data (geometry data)
Here's the command I'm trying to run:
mysql -uuser -ppass --host=website.com mydb < mydb.sql
The problem is that when I restore this backup I get this error:
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '��-'.
So I tried this instead:
mysql -uuser -ppass --host=website.com --binary-mode=1 mydb < mydb.sql
That got me this error:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
I generated the mydb.sql file using mysqldump I tried to do mysqldump --binary-mode=1 but got this error:
mysqldump: [ERROR] unknown variable 'binary-mode=on'.
So how do I create / restore dumps of binary SQL DB's?
Here's the DB schema if that helps:
CREATE TABLE `county_shapes` (
`OGR_FID` int NOT NULL AUTO_INCREMENT,
`SHAPE` geometry NOT NULL /*!80003 SRID 4269 */,
`statefp` varchar(2) DEFAULT NULL,
`countyfp` varchar(3) DEFAULT NULL,
`countyns` varchar(8) DEFAULT NULL,
`geoid` varchar(5) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`namelsad` varchar(100) DEFAULT NULL,
`lsad` varchar(2) DEFAULT NULL,
`classfp` varchar(2) DEFAULT NULL,
`mtfcc` varchar(5) DEFAULT NULL,
`csafp` varchar(3) DEFAULT NULL,
`cbsafp` varchar(5) DEFAULT NULL,
`metdivfp` varchar(5) DEFAULT NULL,
`funcstat` varchar(1) DEFAULT NULL,
`aland` decimal(14,0) DEFAULT NULL,
`awater` decimal(14,0) DEFAULT NULL,
`intptlat` varchar(11) DEFAULT NULL,
`intptlon` varchar(12) DEFAULT NULL,
UNIQUE KEY `OGR_FID` (`OGR_FID`),
SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=InnoDB AUTO_INCREMENT=3235 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
