Mysql – Importing XML into an AWS RDS instance

amazon-web-servicesMySQLxml

I'm trying to load some xml into an AWS RDS (mySql) instance.

The xml looks like: (it's an xml dump of the ISO-3661 codes)

<?xml version="1.0" encoding="UTF-8"?>
<countries>
  <countries name="Afghanistan" alpha-2="AF" alpha-3="AFG" country-code="004" iso_3166-2="ISO 3166-2:AF" region-code="142" sub-region-code="034"/>
  <countries name="Åland Islands" alpha-2="AX" alpha-3="ALA" country-code="248" iso_3166-2="ISO 3166-2:AX"  region-code="150" sub-region-code="154"/>
  <countries name="Albania" alpha-2="AL" alpha-3="ALB" country-code="008" iso_3166-2="ISO 3166-2:AL" region-code="150" sub-region-code="039"/>
  <countries name="Algeria" alpha-2="DZ" alpha-3="DZA" country-code="012" iso_3166-2="ISO 3166-2:DZ" region-code="002" sub-region-code="015"/>

The command that I'm running is:

 LOAD XML LOCAL INFILE '/var/www/ISO-3166_SMS_Country_Codes.xml' INTO TABLE `ISO-3661-codes`(`name`,`alpha-2`,`alpha-3`,`country-code`,`region-code`,`sub-region-code`);

The error message I get is:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

The infile that is referenced exists, I've selected a database before running the command and I have appropriate privileges on the database. The column names in the database table exactly match the xml field names.

Best Answer

There are two things here. One is server-side, the other is client-side.

On the server (AWS RDS), check your parameter group to ensure local_infile is set to 1. By default it is enabled in RDSland for 5.1 and 5.5.

Second, assuming you are using the mysql commandline, start it with the local-infile option:

mysql --local-infile -hhostname -uusername -p databasename

You can also set local-infile in your my.cnf, though it is tied to the machine in that case; YMMV, IANAL, FSCK, etc.

Note this 5.1 documentation says "By default, all MySQL clients and libraries in binary distributions are compiled with the --enable-local-infile option, to be compatible with MySQL 3.23.48 and before." That doesn't appear to be the case with recent distros, at least:

$ mysql --help | grep ^local-infile  # Ubuntu 12.04
local-infile                      FALSE
$ mysql --help | grep ^local-infile  # Ubuntu 12.04, fairly stock AWS AMI
local-infile                      FALSE
$ mysql --help | grep ^local-infile  # OSX 10.8
local-infile                      FALSE
Related Topic