3

I created simple table:

SHOW DATABASES;
CREATE DATABASE observe;

USE observe;

CREATE TABLE `see_me` (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE,
file_contents LONGBLOB // or VARBINARY(MAX)
);

and a file called t.txt. How can I insert the file into the field file_contents with sql commands and extract it likewise into a file?

The insert may be something like 

 INSERT INTO see_me(id, file_contents) 
 SELECT '1', BulkColumn FROM OPENROWSET (BULK '/Users/usr_00/lab/t.txt', SINGLE_BLOB) as ...;

Can someone advise?

Ursa Major
  • 851
  • 7
  • 25
  • 47
  • Can somebody help with what. Please tell us what the probelm is – RiggsFolly Apr 16 '19 at 23:53
  • https://stackoverflow.blog/2018/04/26/stack-overflow-isnt-very-welcoming-its-time-for-that-to-change/ "Let’s stop judging users for not knowing things. (We’re a Q&A site!) " – Ursa Major Apr 17 '19 at 04:59
  • 1
    Mysql does not have openrowset, have you looked at load data infile? and what does t.text look like? – P.Salmon Apr 17 '19 at 07:04
  • t.txt can be of any form from text to binary. what is the mysql load data infile command? – Ursa Major Apr 17 '19 at 16:20
  • I am seeing this ** mysql> UPDATE see_me -> SET file_contents=LOAD_FILE('/Users/usr/lab/t.txt') -> WHERE id=1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 ** however, the table remains unchanged. – Ursa Major Apr 17 '19 at 16:36
  • Like [this](https://stackoverflow.com/questions/10729824/how-to-insert-blob-and-clob-files-in-mysql)? As well as [here](https://dba.stackexchange.com/questions/125464/update-a-column-using-load-file-function) where they discuss setting `secure-file-priv` in mysql configuration and restarting so it knows its root directory and gain access to it before using `load_file`. – JNevill Apr 19 '19 at 19:31
  • INSERT INTO see_me(id, file_contents) VALUES (1, LOAD_FILE('/Users/usr/lab/t.txt')); returns **ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'** INSERT INTO see_me(id, file_contents) VALUES (2, LOAD_FILE('/Users/usr/lab/t.txt')); **remains null on the field** – Ursa Major Apr 19 '19 at 20:21
  • You seem to have a mixture of SQL Server and MySQL syntax -- Which are you running?? – Rick James Apr 20 '19 at 15:32
  • Is the question about fetching a file from disk? Or about shoving it into a `LONGBLOB`? Or both? – Rick James Apr 20 '19 at 15:35
  • can you suggest the exact command syntax? – Ursa Major Apr 23 '19 at 18:21
  • R u using MySQL workbench in Mac or Ubuntu? – Pallamolla Sai Apr 25 '19 at 02:37
  • There are instances it is Mac,and in some machines, they are ubuntu. – Ursa Major Apr 25 '19 at 03:08
  • for mac or ubuntu we need to search for my.cnf file of mysql..this links might help..https://stackoverflow.com/questions/10757169/location-of-my-cnf-file-on-macos https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location – Pallamolla Sai Apr 25 '19 at 04:19
  • This link explains how to set properties for secure-file-priv https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option https://stackoverflow.com/questions/2482234/how-do-i-find-the-mysql-my-cnf-location – Pallamolla Sai Apr 25 '19 at 04:25
  • could you please tell me did my answer helped you? – Pallamolla Sai Apr 25 '19 at 14:01
  • There's some webpage that you expecto to use that method for storing/retrieving files? Or this is just you to load and retrieve the files (via some mysql client)? – Julio Apr 25 '19 at 14:12

3 Answers3

4

You can insert a file content to a field with help LOAD_FILE function. For example:

INSERT INTO see_me (file_contents)
VALUES (LOAD_FILE('/var/lib/mysql-files/myfile.txt'));

or in an update query

UPDATE see_me
SET file_contents = LOAD_FILE('/var/lib/mysql-files/myfile.txt');

If you want to extract the file content into the new file you could use SELECT .. INTO DUMPFILE. For example:

SELECT file_contents INTO DUMPFILE '/var/lib/mysql-files/myfile.txt'
FROM see_me
WHERE id = <your_id>;

But, as said @VonC, keep in mind if secure_file_priv system variable is nonempty you should work with files which only located in a defined directory

Maksym Fedorov
  • 6,383
  • 2
  • 11
  • 31
2

Try, as in here to check where MySql has upload access, as specified in the manual:

select @@secure_file_priv;

If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
(Prior to MySQL 8.0.17, the file must be readable by all, not just readable by the server.)

On Windows, you would have to escape the \, as show in this answer

VonC
  • 1,262,500
  • 529
  • 4,410
  • 5,250
2

After working a lot the following thing worked for me. First thing is we have to make sure whether we have proper permissions to read files from specific directory. For me it wasn't there. So I changed some files.

  1. I went to this location (C:\ProgramData\MySQL\MySQL Server 8.0\my.ini) and checked the path secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" and this location I created a file "check.txt" in which I have content.

  2. I went to mysql workbench and checked some properties. Run the following command

    SHOW GLOBAL VARIABLES LIKE 'local_infile';

if it shows OFF we have to make it ON using following command.

SET GLOBAL local_infile=1;

  1. Now let's check directory of secure-file-priv it should 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/uploads' run the below command

    SHOW VARIABLES LIKE "secure_file_priv" //you will see path as 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/uploads'

Now till now we changed the properties. Now we are ready to read file and create files using mysql.

  1. Read data from txt file (I have already created check.txt in "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"). Later created a temporary table in which I am storing the data in check.txt 

    CREATE TEMPORARY TABLE foo (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,content 
longblob); // created temporary table called "foo"

// reading the data from txt file

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/check.txt'
INTO TABLE foo 
#FIELDS TERMINATED BY '' ENCLOSED BY '' ESCAPED BY ''
LINES STARTING BY '' TERMINATED BY '' // mysql creates new record for every line
(content); 

 //by running above command entire txt data came to content column (mysql created only 1 row bcz we didn't mention lines terminated by. So it will copy entire txt data in 1st row of "content" column of "foo" table)

  2. Writing content to file(in "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" directory )

    SELECT * FROM foo
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/out.txt'; // out.txt will automatically created by mysql

BY running above commands you can perform read and write operations. You can use above "foo" table data in your "see_me" table and perform read or write operations.

data from "foo" table where id="1" into "see_me" table (file_contents)

Following links helped me.

mysql bugs documentation

setting secure-file-priv in mac and ubuntu

my.cnf file in mac

my-cnf location

Pallamolla Sai
  • 2,337
  • 1
  • 13
  • 14
  • The folder structure is different in mac and ubuntu. I am not able to locate the said path or file, i.e. my.ini, etc. – Ursa Major Apr 25 '19 at 00:02
  • If you are using mqsqlworkbench run the command I mentioned. while running the command if you are getting any sql error(this operations is not allowed etc. ) then you have to change my.cnf file for permissions(you can change my.cnf file by going through the links I mentioned). Could you please just go through the links? – Pallamolla Sai Apr 25 '19 at 04:34