1

I have created a table lfile. The structure of table is:

CREATE TABLE `lfile` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |

In the table I have 3 records:

select * from lfile;
+----+-------+
| id | name  |
+----+-------+
|  1 | ravi  |
|  2 | kumar |
|  3 | ark   |
+----+-------+

Now I want to update 'ravi' with 'ravikumar' using LOAD_FILE.

  UPDATE lfile SET name=LOAD_FILE('/home/ravi/Desktop/t') WHERE id=1;

But it updates the name column with a NULL value.

mysql> select * from lfile;
+----+-------+
| id | name  |
+----+-------+
|  1 | NULL  |
|  2 | kumar |
|  3 | ark   |
+----+-------+

Settings:

show variables like 'character_set_filesystem';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_filesystem | utf8  |
+--------------------------+-------+
  • The function returns NULL when the file was not readable - check the privileges etc. Try select LOAD_FILE('/home/ravi/Desktop/t'); show warnings; to see what the function returns. – jkavalik Jan 7 '16 at 9:13
  • I gave full permission to file.select LOAD_FILE('/home/ravi/Desktop/t'); query returns NULL after changing permission also.show warnings; returns nothing.In file i have only one word 'kumar'. – Ravi Kumar Jan 8 '16 at 6:28
  • Is the mysql on your local computer? What user do you use for mysql? Does it have the FILE privilege? Any apparmor/selinux or other things which might override user permissions? – jkavalik Jan 8 '16 at 7:02
  • Yes i am using MySQL server on my local machine.I am connecting mysql server with root user and file has full permission. – Ravi Kumar Jan 11 '16 at 7:23
0

Now i want to update 'ravi' with 'ravikumar'

LOAD FILE loads an entire table, not one column, not one cell.

Instead,

UPDATE lfile
    SET name = 'ravikumar'
    WHERE name = 'ravi';
  • Hi Rick James I read load_file function from this link dev.mysql.com/doc/refman/5.7/en/… i can update a column by using your query but i want to update from a file. – Ravi Kumar Jan 7 '16 at 8:50
  • Rick James,load file is used to load data into a table but what is the use case of LOAD_FILE function? – Ravi Kumar Jan 7 '16 at 9:09
  • Your link is broken; you wanted load_file. Do you have a file with just one word ('ravikumar') on one line? How do you know that that should replace name on the row with "ravi" ? – Rick James Jan 7 '16 at 15:54
  • yes I have file with one word in line. – Ravi Kumar Jan 7 '16 at 16:53
  • How do you know that that should replace name on the row with "ravi" ? – Rick James Jan 7 '16 at 16:57
0

Mysql has a security protection to avoid accessing any files on the disk. The root directory is defined as key in the section 'msqld' (in the file e.g. /etc/mysql/my.cnf)

[mysqld]
secure-file-priv = /var/lib/MyLocationOfTheFilesToLoad

Please avoid setting this to high in the directory structure (e.g. don't use: '/' or '/var' or '/etc' ). It will allow to load and overwrite files you might want to protect or keep secret.

This setting is global, and it is valid for all users, scripts, procedures and instances by default.

After the database has reload the configuration (e.g. by restarting mysql) the following SQL command will work:

UPDATE `table` 
  SET `field` = Load_File( '/var/lib/MyLocationOfTheFilesToLoad/test.txt' )
  WHERE `ID` = 4711;

Or in your case:

[mysqld]
secure-file-priv = /home/ravi/Desktop

and

UPDATE `lfile` 
  SET `name` = LOAD_FILE('/home/ravi/Desktop/t') 
  WHERE `id` = 1;

Please use this privilege with care, the system may be vulnerable after changeing the config file.

On linux systems e.g. use a path like /var/local/createdByYou/somewhere in the conf, and create symbolic links there to your final location to protect scanning the entire system.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.