0

Attempting to load a file into the DB. 

CREATE TEMPORARY TABLE IF NOT EXISTS tt_emails (
    id INT PRIMARY KEY AUTO_INCREMENT,
    util_account_id VARCHAR(40) NULL,
    email VARCHAR(344) NOT NULL,
    optout INT NOT NULL
    );

LOAD DATA INFILE '/shared/implementation/emails.tsv'
    INTO TABLE tt_emails (util_account_id, email, optout);

The emails.tsv file has 3 columns, while the temporary table I made has 4. I'm not sure if this is correct syntax; I included a 4th column to have an id primary key column.

I get the following error when I run this code:

ERROR 13 (HY000): Can't get stat of '/nfs/shared/implementation/ngma/CO-48812_NGMA_Load_email/exclude_emails.tsv' (Errcode: 13)
Edward Anderson
  • 13,591
  • 4
  • 52
  • 48
Asif
  • 748
  • 3
  • 9
  • 32
  • 1
    "can't get stat" - the mysql user account (system account, not the account you're logging into mysql with) doesn't have the rights to all/part of the path leading up to that file. – Marc B Apr 19 '16 at 19:13

1 Answers1

0

It's a unix/linux file system permissions problem.

The Operating System is not allowing the OS user permission to access the specified file.

The permission on every directory in the path to the file must be at least read+execute, and permission on the file itself must be at least read.

The OS user that is attempting to access the file is the unix/linux user account that the MySQL Server process is running as. (That is frequently "mysql", but it doesn't have to be, it really depends on how MySQL was installed and setup.

From shell prompt on the mysql server host, run 

ps -ef | grep mysqld

The output should contain a line that contains mysqld. For example: 

mysql      2247  1233  0 Mar21 ?    00:24:09 /opt/mysql/bin/mysqld --basedir=/opt/...

That first field in the output shows the OS user that the MySQL server is running under. In this case, it's OS user "mysql"

If we login to the OS as "mysql", or do an "su - mysql" to switch to that user), we will have the same permissions issue attempting to access to that file.

/nfs/shared/implementation/ngma/CO-48812_NGMA_Load_email/exclude_emails.tsv

Either the permissions on the file don't allow the OS user "mysql" to read the file, or the permissions on the directories above the file don't allow "read+execute" by OS user "mysql".

The permissions on the directories and files would need to be changed appropriately.

As a kludgy workaround, copy the file to /tmp (From a shell prompt, logged in as OS user that does have "read" permission on the file). The /tmp directory should allow "read+execute" by everyone, including the OS user "mysql"

 cp /nfs/shared/implementation/ngma/CO-48812_NGMA_Load_email/exclude_emails.tsv /tmp/

And then you can change the permissions on the file to allow OS user "mysql" to read the file. To make the file readable by everyone:

 chmod ugo+r /tmp/exclude_emails.tsv

Then connect to the MySQL server and try unning a LOAD DATA referencing the new /tmp/exclude_emails.tsv file.

Your other option of course would be to set the permissions appropriately on the original file (so that the "mysql" user (or whatever OS user mysqld is running as) has the necessary permissions, and on all of the directories in the path.

To summarize... this isn't really a MySQL server issue. It's an issue with Operating Systems permissions which prohibit the access to the file that the mysql OS user is trying to perform.

spencer7593
  • 106,611
  • 15
  • 112
  • 140