MySQL ERROR 1064 (42000) importing OpenGeoDB

Question

I want to import the OpenGeoDB into a database on my Debian VM with MySQL Ver 14.14 Distrib 5.6.17:

$ mysql -uroot -p -h localhost geodb < opengeodb-begin.sql

It fails with the error

ERROR 1064 (42000) at line 14: 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 'TYPE=InnoDB CHARACTER SET utf8' at line 6

The MySQL code around the line 6 in the opengeodb-begin.sql from the OpenGeoDB download area looks like:

/*
 * MySQL
 */



SET NAMES 'utf8';

BEGIN;
/*
 * Table structure for table 'geodb_type_names'
 */

create table geodb_type_names (
  type_id              integer not null,
  type_locale          varchar(5) not null,
  name                 varchar(255) not null,             /* varchar(500)? */
unique (type_id, type_locale)
) TYPE=InnoDB CHARACTER SET utf8;

So, the DB server seems to have a problem with TYPE=InnoDB CHARACTER SET utf8;.

At the same time it works on my remote Ubuntu server with MySQL Ver 14.14 Distrib 5.1.41.

The only significant difference I see is the MySQL version.

What can cause the error?

automatix automatix · Accepted Answer · 2014-05-17T16:16:28

OK, it seems really to be so, that the problem is the database server version. The keyword TYPE is not supported anymore and was replaced with ENGINE.

I replaced all TYPE=... with ENGINE=... in CREATE statements like

create table geodb_type_names (
  type_id              integer not null,
  type_locale          varchar(5) not null,
  name                 varchar(255) not null,             /* varchar(500)? */
unique (type_id, type_locale)
) ENGINE=InnoDB CHARACTER SET utf8;

Now it works.

MySQL ERROR 1064 (42000) importing OpenGeoDB

1 Answers