0

I am trying to make a website running on a VPS linux server hosted by Inmotion that allows the user to upload a txt, csv or excel file. After uploading said file, it will be processed with some PHP calling MySQL functions. Everything works up to this point. After the processing has been finished, MySQL will write the database to an OUTFILE. This is where I run into my problem.

I cannot write the OUTFILE to any directory, it must be within the MySQL data directory or in "/var/etc/tmp" directory. Otherwise it throws a Errcode 13: permission denied error. Really, my question is this:

  1. If possible, how can I allow MySQL to write to a directory outside the data directory? If it is possible, how can this be done?

  2. If not possible, how can I move a file from the tmp file located in "var/etc/tmp" to a directory such as "/home/user/Directories"?

I hope I gave enough information. This is my first question StackExchage!

Thank you!

shayne_
  • 3
  • 5
  • 1
    Doing file processing within MySQL is really an unusual thing to do. Are you sure it's the best plan? – tadman Aug 08 '17 at 19:10
  • I think this has already been answered here https://stackoverflow.com/questions/17519794/how-to-change-the-default-path-of-mysql-into-outfile-csv-path – Guenther Aug 08 '17 at 19:15
  • Honestly, I think this is the best plan @tadman. We are dealing with anywhere from 500,000 rows up to millions of rows of data and my goal is to inset\rt a single character into a new column for each of the rows and give them their file back with the newly column and character. I need to get this done really, as fast as possible and I don't think PHP can process that amount of data as quickly as MySQL. Any thoughts if this isn't the best route? I'm very grateful for literally ANY help in this. – shayne_ Aug 09 '17 at 23:55
  • If you need speed, nothing but speed, I've found Node.js is ridiculously fast as far as scripting languages go. It's just JavaScript at its core. It can probably chomp through a million rows in less time than it takes to type in `node chomp.js myfile.txt`. – tadman Aug 10 '17 at 02:17
  • @shayne_ Please see my updated answer below, to see if that might be a possible route for you. – Canis Aug 10 '17 at 11:15

2 Answers2

1

As the commenters have already stated, using MySQL for writing processed data to a file is not something that would scale well, or be something that is easily understood by others collaborating with you or maintaining the system after you.

If you just need to dump a processed .csv somewhere, use PHP to do it for you. If you are dumping the .csv for later retrieval, and still have the data in MySQL, then why do you need the .csv?

To answer directly on number 2: Use PHP to move the file after it's written. 

rename("/var/etc/tmp/your_file.csv", "/home/user/Directories/your_file.csv");

EDIT

As per your comment stating the goal of this question, and as you are on Linux, you might be able to just use bash commands to do it for you. If you are adding the same character for all rows as a new column in a csv-file, you can do it with sed. For the sake of the example I am assuming the last column in the file does NOT have a delimiter after it. I am also just using capital A as a place-holder.

sed -e 's/$/,A/' -i filename.csv

Now I am a bit rusty on my Linux-foo, so please test it in your environment on non-important test-data first.

Canis
  • 4,130
  • 1
  • 23
  • 27
1

You can try to use mysql query like this

SELECT id,name,surname
FROM users
WHERE foo = 'bar'
INTO OUTFILE '/var/www/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

and remember to correct file permision after mysql create it

Mattia Moscheni
  • 144
  • 1
  • 2