load-data.sql: ERROR 1054 (42S22) at line 30: Unknown column 'iso_code' in 'field list'

[reedy]

Following on from #1103

Importing airlines...
Importing airports...
Importing routes...
Importing countries...
ERROR 1054 (42S22) at line 30: Unknown column 'iso_code' in 'field list'

[reedy]

https://github.com/jpatokal/openflights/blame/master/sql/load-data.sql#L35

(name, iso_code, dafif_code);

vs

https://github.com/jpatokal/openflights/blob/master/sql/create.sql#L97-L105

DROP TABLE IF EXISTS `countries`;
CREATE TABLE `countries` (
  `junk` text,
  `code` varchar(2) NOT NULL,
  `name` text,
  `oa_code` varchar(2) default NULL,
  `dst` char(1) default NULL,
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I guess iso_code, dafif_code should be code, oa_code or oa_code, code ?

[reedy]

Or maybe it's right?

And after create-tables.sql, airlines-upgrade.sql and airports-upgrade.sql need running? In which order? Which isn't documented, but would be much more helpful if create-tables.sql was actually canonical...

[reedy]

Can we get a dump of the current schema structure from the live site?

[jpatokal]

Here's the live DB:

CREATE TABLE `countries` (
  `name` text,
  `iso_code` varchar(2) DEFAULT NULL,
  `dafif_code` varchar(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`dafif_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `airlines` (
  `name` varchar(80) DEFAULT NULL,
  `iata` varchar(2) DEFAULT NULL,
  `icao` varchar(3) DEFAULT NULL,
  `callsign` text,
  `country` text,
  `country_code` varchar(2) DEFAULT NULL,
  `alid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `alias` text,
  `mode` char(1) DEFAULT 'F',
  `active` varchar(1) DEFAULT 'N',
  `source` text,
  `frequency` int(11) DEFAULT '0',
  PRIMARY KEY (`alid`),
  UNIQUE KEY `no_duplicates` (`name`),
  KEY `iata` (`iata`),
  KEY `icao` (`icao`)
) ENGINE=InnoDB AUTO_INCREMENT=25989 DEFAULT CHARSET=utf8

CREATE TABLE `airports` (
  `name` text NOT NULL,
  `city` text,
  `country` text,
  `country_code` varchar(2) DEFAULT NULL,
  `iata` varchar(3) DEFAULT NULL,
  `icao` varchar(4) DEFAULT NULL,
  `x` double NOT NULL,
  `y` double NOT NULL,
  `elevation` int(11) DEFAULT NULL,
  `apid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `timezone` float DEFAULT NULL,
  `dst` varchar(1) DEFAULT NULL,
  `tz_id` text,
  `type` text,
  `source` text,
  PRIMARY KEY (`apid`),
  UNIQUE KEY `icao_idx` (`icao`),
  UNIQUE KEY `iata_idx` (`iata`),
  KEY `y` (`y`),
  KEY `x` (`x`),
  KEY `iata` (`iata`)
) ENGINE=InnoDB AUTO_INCREMENT=14986 DEFAULT CHARSET=utf8

The mess originates from long-incomplete work to do a bulk update of the airline and airport databases. The *-upgrade.sql scripts are temporary and should no longer be needed

[reedy]

Yeah, so if I run airlines-upgrade.sql and airports-upgrade, I can then import...

root@ubuntu64-openflights:/var/www/openflights# cat sql/load-data.sql | mysql -u root -h 10.13.37.212 --local-infile=1 -p flightdb2
Enter password: 
Importing airlines...
Importing airports...
Importing routes...
Importing countries...
Importing locales...
Done.