SQL file: MySQL import errors

[hankszeto]

Importing the .sql file into MySQL/MaraiDB results in errors:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO auspostcodes (otherid, pcode,locality,state,auspostcodes.long,l...' at line 2

The issue seems to be with the table name and column names in the INSERT INTO statement. The column names shouldn't be prefixed with the table name (at least from MySQL's point of view).

INSERT INTO `auspostcodes` (otherid, pcode,locality,state,auspostcodes.long,lat,deliveryoffice,auspostcodes.type,dateofupdate) VALUES ('230','0200','ANU','ACT','0','0','','','2020-02-06 05:03')

Here is the corrected SQL statement using backticks to quote the reserved words:

INSERT INTO `auspostcodes` (otherid, pcode, locality, state, `long`, lat, deliveryoffice, `type`, dateofupdate) VALUES ('230','0200','ANU','ACT','0','0','','','2020-02-06 05:03')

Here is a sed command to do a bulk change to the SQL file:

sed -i '' -e 's/auspostcodes\.long/`long`/g' -e 's/auspostcodes\.type/`type`/g' australian_postcodes.sql

The other error the came up was that a semi-colon ; is needed between each insert statement.

Here is another sed command to fix this:

sed -i '' -e '/INSERT INTO `auspostcodes`.*[^;]$/s/$/;/' australian_postcodes.sql

After these changes I was able to import the .sql file into MariaDB.