Tuesday, September 27, 2016

Importing files into mysql with LOAD_FILE but data returning NULL

I really wanted to be able to import text files into a column for a little project I'm working on. I know that MySQL isn't the ideal place for storing TEXT files but the project was small and so this is what I wanted. But the LOAD_FILE simply wouldn't work. Eventually I found that someone had posted a work around. You have to put the files in directoy inside of this directory: /var/lib/mysql and then give the correct permissions.

I found the information from here for the "ugly workaround":
http://stackoverflow.com/questions/4607486/mysql-load-file-returning-null

Steps I performed:

Create a new directory under mkdir -p  /var/lib/mysql/:

mkdir -p  /var/lib/mysql/upload_data/

Create a test file:

ps -ef > /var/lib/mysql/upload_data/test_import.txt

Verify the file has some data:

head  /var/lib/mysql/upload_data/test_import.txt

Changing permissions wasn't needed on my system but others said it might be needed:

chown mysql:mysql /var/lib/mysql/upload_data/*
chmod go+rw /var/lib/mysql/upload_data/*


Give your user the needed permissions:

mysql -u root
mysql>
  GRANT file ON *.*  to 'test'@'%';
  GRANT file ON *.*  to 'test'@'localhost';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'%' IDENTIFIED BY 'test_password';
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'test_password';
exit

mysql -u test
mysql>  SHOW GRANTS;

DROP TABLE IF EXISTS `test`.`table_a`;
CREATE TABLE IF NOT EXISTS `test`.`table_a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `output` text COLLATE utf8_unicode_ci COMMENT 'program output from command execution if available',
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='stores test files';

REPLACE INTO `test`.`table_a` (`id`, `output`) VALUES (1, NULL);

SELECT hex(LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt'));

UPDATE test.table_a
  SET output=LOAD_FILE('/var/lib/mysql/upload_data/test_import.txt')
  WHERE id=1;

SELECT * FROM test.table_a WHERE id =1;

One of the problems with this ugly work around is the new folder in var/lib/mysql/ will look like a new database (unless your data directory is some where else). One way to get around this it to make the folder hidden by starting the folder name with a period.

No comments:

Post a Comment