mysqlimport gives error when run as cron job but not interactively (Ubuntu 14.02)

Question

I have a bash script which downloads and then imports a CSV file into a local MySQL. The database is MySQL 5.6 on Ubuntu 14.04, I'm loading data with the following statement (bash):

 mysqlimport --ignore-lines=1 --lines-terminated-by='\n' --fields-terminated-by=',' --verbose --local --user=xx --password=xxxxxxxx mydb /var/www/mysite/data/daily_info.csv

the problem is that when I launch the script from the command line, with the same user, it runs fine from download through the import phase. I added a crontab entry to run the script with the command

crontab -u theuser -e

The user 'theuser' is the same that launches correctly the script from the command line. 'daily_info' is also the name of the table to which data is added by the mysqlimport command.

I'd expect that the crontab task would run as 'theuser' allowing access to all the folders and file, just as the interactive shell does, but this seems not true. The scheduled job gives me the this error message

mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: daily_info

I've found many questions related to the secure-file-priv issue but the answers don't fit well with this case, I suspect that the command in my script (mysqlimport) is running with a different user, but I can't diagnose it.

Any help would be appreciated. Thanks.

Answer 1

I didn't find an explanation but arrived to a (partial) solution to my own question. I hope others may contribute to enhance this. When run from the command line the script runs ok, when it's activated from crontab it applies what's set in the secure_file_priv mysql variable. So I modified the my.cnf file adding a line under the [mysqld] group as shown here:

[mysqld]
...other settings here...
secure-file-priv=/mypath/datafolder/

After that you must restart mysql and check if the change takes effect with this query:

mysql> show variables like 'secure-file-priv';
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| secure_file_priv | /mypath/datafolder/    |
+------------------+------------------------+
1 row in set (0.01 sec)

Notice that when the db loads the .cnf file the '-' character is converted into '_' (underscore).