Doctrine 1.2 & MySQL : Menggunakan LOAD DATA LOCAL INFILE

Pada Ubuntu 12.04 dan menggunakan PHP versi 5.3,10 serta MySQL versi 5.5 ketika menjalan LOAD DATA LOCAL INFILE, seperti pada potongan kode sumber dibawah ini, yang diambil dari AlisJK [1].

....

public static function allocateToDb($db_name) { //allocated AllocatedTable::truncate() ; $output_dir = sfConfig::get("sf_root_dir").DIRECTORY_SEPARATOR."data".DIRECTORY_SEPARATOR."alokasi".DIRECTORY_SEPARATOR.$db_name.DIRECTORY_SEPARATOR."output".DIRECTORY_SEPARATOR ; $allocated_csv = $output_dir.DIRECTORY_SEPARATOR."allocated-raw.csv" ; $sql = "LOAD DATA LOCAL INFILE '{$allocated_csv}' REPLACE INTO TABLE allocated FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES"; $q = Doctrine_Manager::getInstance()->getCurrentConnection(); $result = $q->execute($sql); //failed FailedTable::truncate() ; $output_dir = sfConfig::get("sf_root_dir").DIRECTORY_SEPARATOR."data".DIRECTORY_SEPARATOR."alokasi".DIRECTORY_SEPARATOR.$db_name.DIRECTORY_SEPARATOR."output".DIRECTORY_SEPARATOR ; $failed_csv = $output_dir.DIRECTORY_SEPARATOR."failed.csv" ; $sql = "LOAD DATA LOCAL INFILE '{$failed_csv}' REPLACE INTO TABLE failed FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES"; $q = Doctrine_Manager::getInstance()->getCurrentConnection(); $result = $q->execute($sql); return $result ; }

....

Tidak berjalan sesuai yang diinginkan, dan terjadi error :

1148 Error Message: The used command is not allowed with this MySQL version

Solusinya, harus mengkompilasi ulang PHP yang sudah dipatch [4], cara pada [2] atau [3] pun tidak bisa.

Jika ingin menggunakan LOAD DATA INFILE, kita harus grang permission FILE ke user :

GRANT FILE on *.* to 'username'@'localhost' IDENTIFIED BY 'password';

Referensi

• 1. alokasi.class.php di AlisJK, https://bitbucket.org/wildanm/alisjk/src/dc5a0dd1597c761287cebd83e0d0b1a6c20c3906/lib/vendor/openthinklabs/alokasi.class.php?at=default

  2. MySQL PHP configuration does not allow LOAD DATA LOCAL INFILE, http://serverfault.com/questions/451100/mysql-php-configuration-does-not-allow-load-data-local-infile

  3. 12,04 MySQL PHP LOAD DATA LOCAL INFILE problem, http://ubuntuforums.org/showthread.php?t=1970615

  4. Install MySQL 5.5 From Source (Load Data Local Infile Issues?), http:// www.sudo-juice.com/install-mysql-5-5-from-source-ubuntu-linux/