Opened 7 years ago

Closed 6 years ago

#2184 closed defect (invalid)

MySQL Error during 'v.in.ogr' in GRASS 7 SVN (2014-01-31)

Reported by: justinzane Owned by: grass-dev@…
Priority: normal Milestone: 7.0.0
Component: Vector Version: svn-trunk
Keywords: MySQL, OGR, import Cc: justinzane
CPU: x86-64 Platform: Linux

Description

It seems that there is a failure to properly quote the CREATE TABLE command. Simply adding backticks "`" around the table name and all column names allows the command to execute.

This looks like create table `natural` (`cat` integer, `osm_id` varchar (11), `name` varchar (48), `type` varchar (16));.

Additionally, even though the CREATE TABLE failed and therefore the import failed, a map is created and cannot be removed since its backing data table never existed.

So, two separate issues need to be fixed. First the quoting. Second is the need to wait until the import has succeeded before allocating a 'Map'.

v.in.ogr dsn=/home/justin/downloads/osm_CA/natural.shp
...
DBMI-MySQL driver error:
Unable to execute:
create table natural (cat integer, osm_id varchar ( 11 ),
name varchar ( 48 ), type varchar ( 16 ))
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 'natural (cat integer, osm_id varchar (
11 ), name varchar ( 48 ), type varchar (' at line 1
ERROR: Unable to create table: 'create table natural (cat integer, osm_id varchar ( 11 ), name varchar ( 48 ), type varchar ( 16 ))'

Change History (2)

comment:1 Changed 7 years ago by justinzane

Cc: justinzane added

Note that this may properly belong under "Database".

Also, in the off chance it is wanted, I can provide all configure/build logs.

comment:2 Changed 6 years ago by neteler

Resolution: invalid
Status: newclosed

According to

backticks are required if the identifier is a reserved word, contains white space, etc.

The word "NATURAL" is a reserved word in MySQL (see Table 9.2 in the first link). To solve this conflict for you, you can use the "cnames" parameter of v.in.ogr to change the column names during import. The GRASS DB drivers should not try to automatically launder names.

To give more help in the manual, I have added some remarks in r59058 (backported in r59059 and r59060).

Closing.

Note: See TracTickets for help on using tickets.