Baffled By File Not Found Error -- Csv Read

AustinGeek · July 7, 2013, 6:57pm

I received the following error whil trying to read a CSV file in from my www folder. I am perplexed. It is there and permissions are wide open 777.

Ubuntu Linux LTS12.04 / NGINX / Yii 1.12

I am trying to read this using the MySQL LOAD DATA command.

My controller:


 

 public function actionImportCSV() {

	

	$filepath = Yii::getPathOfAlias('webroot').'/';

	$filename = 'june1.csv' 


	$sql="LOAD DATA INFILE '".$filepath.$filename."'

	INTO TABLE `network_analytics`

	FIELDS TERMINATED BY ','

	OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'

	IGNORE 0 LINES

	(`publisher_id`, `site_id`, `placement_id`, `imp_date`, `imps`, `imps_resold`, `imps_kept`, `revenue`, `media_cost`, `revenue_rpm` ) ";

	


	$connection=Yii::app()->db;

	$transaction=$connection->beginTransaction();

	try

	{

	 $connection->createCommand($sql)->execute();

	 $transaction->commit();

	}

	catch(Exception $e) // an exception is raised if a query fails

	 {

	  print_r($e);

	  exit;

	  $transaction->rollBack();

	 }

	 $this->redirect(array("index"));

    }

What am I doing wrong?

There was a bug with LOAD DATA LOCAL INFILE …but I modified my config/main and it seems to have gotten around the error.

Just won’t open the file now.

Thanks for any advice!

AustinGeek · July 7, 2013, 7:06pm

I tried to execcute the load command from within mysql at the prompt

and received

ERROR 29 (HY000): File ‘/usr/share/nginx/www/q1media/june1.csv’ not found (Errcode: 13)

bettor · July 7, 2013, 7:17pm

try this:

https://bugs.launchpad.net/ubuntu/+source/mysql-dfsg-5.0/+bug/244406

OR

AustinGeek · July 7, 2013, 7:52pm

Thank You ! This has given me something to investigate.

I found this on your link.

This is due to the apparmor profile for mysqld that disallows the mysqld

process to read files in /var/www.

You can emulate the load data infile SQL statement with the mysqlimport

utility and use the LOCAL option (so that the file is sent to the mysqld

rather then mysqld opening the file directly).

Another option is to update mysqld apparmor profile to allow read access

to your file. See https://wiki.ubuntu.com/DebuggingApparmor for more

information on this.

I moved the files to my /home/ folder (out of /var/www) and it still would not read.

So I may hav to use the mysqlimport…

AustinGeek · July 7, 2013, 8:14pm

Bettor –

Thank you, Thank you, Thank you, My Friend !

That was it! Ubuntu 12.04LTS had the AppArmor defined for the system and so I had to make a couple of modififications to

the profile.

AustinGeek · July 7, 2013, 8:19pm

Just for other’s benefit.

Ubuntu Linux 12.04 LTS

check to see if AppArmor is installed and enforcing security.

$> sudo aa-status

if response is

99X profiles are in enforce mode

/usr/bin/mysqld

That means AppArmor is turned on.

edit the /etc/apparmor.d/usr.sbin.mysqld

add an entry for your directory

/example/ r,

/example/* rw,

reload AppArmor

/etc/init.d/apparmor

jbowler · July 31, 2013, 2:00am

I’m still getting this error, after trying the AppArmor trick:

Error on load data local infile, CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version.

I can execute this mysql statement fine from the terminal, logged in as my mysql user, or from a file:

LOAD DATA LOCAL INFILE ‘$file’ INTO TABLE import_$object FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\"’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES; ";

The problem occurs when trying to execute from Yii. From what I’ve see online, this may be a php bug. I’m on php 5.3.5, using mysql 5.5.22.

This is a big blocker for me right now - any help would be greatly appreciated.

Ubuntu 12.04 32 bit, php 5.3.5, mssql 5.5.22.