3

I have a csv file that needs to be imported to MySQL.

Initially I settled with MySQL's built-in 'LOAD DATA LOCAL INFILE' statement to import the CSV file. For some good reasons the usage of this statement is disabled on our server. Reasons

As my research and developments into this matter led me to concrete conclusion that there is no way to overcome this situation without making changing into my.cnf. Which of-course I don't want to touch.

Research

  1. Security Issues with LOAD DATA LOCAL
  2. enable LOAD DATA LOCAL INFILE
  3. “Load data local infile” command not allowed
  4. LOAD DATA LOCAL INFILE fails - from php, to mysql (on Amazon rds)

Therefore I decided to parse the CSV in PHP and make bulk inserts (multiple rows in one insert statement).

I am planning to keep both options open to import csv file to MySQL, so the PHP code will initially try for option (a), if not possible only then proceed to option (b), because my code is run on multiple server, each has different settings for MySQL database.

As 'a' is always faster than 'b' (no presumptions).

So is there a way to check if LOAD DATA LOCAL INFILE is enabled on MySQL server.

a. LOAD DATA LOCAL INFILE

b. CSV bulk inserts (through loop).

Community
  • 1
  • 1
Abhishek Madhani
  • 1,155
  • 4
  • 16
  • 26
  • you could use the show grants but you need to have your database priveleges set to do that. If your not allowed to LOAD DATA I would doubt that you would have permission to see the grant table. –  Aug 19 '13 at 11:03
  • @jeff in MySQL `SHOW GRANTS;` displays `GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD '*29C9C5B6F078D678CF2EF66AAF14685C9121ECD8'` in first coloumn and `GRANT ALL PRIVILEGES ON 'database'.* TO 'username'@'%'` in second coloumn – Abhishek Madhani Aug 19 '13 at 11:07

0 Answers0