0

I'm trying to use OSGeo4W Shell to populate my MySQL DB with data from a shapefile and am having some difficulty. Here's the command I'm using:

ogr2ogr -f MySQL MySQL:maps,host=localhost,user=root,password=password tl_2020_us_county.shp -nln county_shapes -update -overwrite -lco engine=InnoDB -skipfailures

When I run that command I get this error:

ERROR 1: MySQL error message:Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting. Description: CREATE TABLE county_shapes ( OGR_FID INT UNIQUE NOT NULL AUTO_INCREMENT, SHAPE GEOMETRY NOT NULL /*!80003 SRID 4269 */) ENGINE = InnoDB

If ogr2ogr were creating a MySQL dumpfile instead of trying to write to an SQL DB I could then either:

  1. do cat prepend.sql dump.sql | mysql ... where prepend.sql contained SET SESSION sql_require_primary_key = 0;
  2. modify the CREATE TABLE in the dumpfile to include a PRIMARY KEY.

But alas if there's a way to create dumpfile's using ogr2ogr that'd be news to me.

I also can't modify my mysqld-auto.cnf to add this in there as this is a managed database (like RDS on AWS).

If I could append to an existant table I could import it locally on a server where I can disable sql_require_primary_key in the mysqld-auto.cnf, get the schema from that import and then create that schema on the target server, either modifying it outright or by doing SET SESSION sql_require_primary_key = 0; before the CREATE TABLE but alas I can't even get that working as discussed at ogr2ogr can't append shapefile to existing Postgres table (altho I do note that that answer is using PostgreSQL vs MySQL).

I also thought that if I import it to a local DB I could create a dumpfile from that server and import it to the prod one but binary data is interfering with that

neubert
  • 360
  • 2
  • 15
  • Add a primary key to your table? – Ian Turton May 11 '22 at 07:40
  • @IanTurton - it's the CREATE TABLE query that's failing and I'm not the one creating the CREATE TABLE query - ogr2ogr is. If your proposal is that I modify the source code to ogr2ogr then that's a pretty extreme solution. Maybe, while I'm at it, I ought to make my own tool to convert a shapefile into a MySQL dumpfile! – neubert May 11 '22 at 08:54
  • Try removing the -update? – Ian Turton May 11 '22 at 10:16
  • @IanTurton - didn't help :( I added more detail to my orig question if that helps – neubert May 11 '22 at 10:23

0 Answers0