Mysql Read From File Output Errors and Continue

October 13, 2022 Post a Comment

Do you need to save your results from a MySQL query to a CSV or text file?

It's easy to do in MySQL. You can do this using an IDE or the command line, using a built-in MySQL command.

Let's take a look.

Basic Query

Let's use a simple example of a SELECT statement for this.

                SELECT id, first_name, last_name FROM customer;

Here are the results:

id	first_name	last_name
1	John	Smith
2	Mary	McAdams
3	Steve	Pitt
4	Mark	Cousins
5	Shaun	Jones
7	Amy	McDonald
8	Brad	Swan
10	Wendy	Johnson

We may see these results in the output of our command line or in the IDE, such as MySQL Workbench.

How can we save them into a text file?

We could copy and paste them, but that's slow and manual.

Save MySQL Results to a File

There's a built-in MySQL output to file feature as part of the SELECT statement.

We simply add the words INTO OUTFILE, followed by a filename, to the end of the SELECT statement.

For example:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt';

This will create a new file called myoutput.txt that contains the results of this query, in a folder called temp.

What happens if you get this error (like I did)?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

I'll explain more about resolving this later in this guide.

For now, assuming you can run the statement to generate the file, it will look like this:

The text in the file is:

1 John Smith 2 Mary McAdams 3 Steve Pitt 4 Mark Cousins 5 Shaun Jones 7 Amy McDonald 8 Brad Swan 10 Wendy Johnson

As you can see, the fields are separated by tabs. This is the default behaviour, but it can be changed.

Changing Parameters to Set Comma Separated Values

You can change the parameters of this INTO OUTFILE keyword to change how the file is written.

There are several extra parameters. These are some of the most common:

FIELDS TERMINATED BY: this indicates the character(s) that are used to end a field.
ENCLOSED BY: this indicates the character(s) that will be used to surround each field.
LINES TERMINATED BY: this indicates the character(s) that are used to end a line and start a new line.

For example, to select the data to a CSV file and enclose each field in a double quote:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

If we run this statement, we can check the file, which will look like this:

The text in the file is:

"1","John","Smith" "2","Mary","McAdams" "3","Steve","Pitt" "4","Mark","Cousins" "5","Shaun","Jones" "7","Amy","McDonald" "8","Brad","Swan" "10","Wendy","Johnson"

So that's how you can generate a CSV or text file in MySQL. You just add the INTO OUTFILE keyword to the end of a SELECT query and specify some parameters.

Include Headings in Output File

You might have noticed that there are no column headings in the output file.

How can you get column headings to display? Unfortunately, there's no easy option you can enable.

One commonly-mentioned way is to use a UNION ALL to select the column headings and the data.

So, your query would look like this:

                SELECT 'id', 'first_name', 'last_name' UNION ALL SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

This would mean your column headings would be shown in the file:

"id","first_name","last_name" "1","John","Smith" "2","Mary","McAdams" "3","Steve","Pitt" "4","Mark","Cousins" "5","Shaun","Jones" "7","Amy","McDonald" "8","Brad","Swan" "10","Wendy","Johnson"

However, there are a few issues with this:

Data Types

This only works if the data types are characters. If you have any other types (such as numbers or dates) in your data, then you'll get issues with your query.

This is because the data type of the column is determined by the first query in the UNION. All of the column headers are text values, so when the second part of the UNION query is run, it may try to add a date into a character column and cause an error.

Can Have Issues with Ordering

If your query includes an ORDER BY clause, then your column headings won't show correctly.

This is because the ORDER BY clause goes at the end of the query, and will include your row of column headers in the ordering. This could mean that your column headers will end up at a place in the results that is not the top.

You could get around this by putting your main query in a subquery and then using UNION on that.

For example:

                SELECT * FROM  (   SELECT 'id', 'first_name', 'last_name'   UNION ALL   (     SELECT id, first_name, last_name     FROM customer     ORDER BY first_name ASC   ) ) sub INTO OUTFILE '/temp/myoutput.txt';

This should ensure that your column headers are shown at the top.

Possible Performance Issues

If you try to use a UNION or UNION ALL, the MySQL database may try to use a different execution plan to display the data, even though you're only adding a single row.

This could mean the runtime is a lot slower.

So, it's something you should test before you start using this as a permanent solution.

What if the File Already Exists?

So, what happens if you try this command and the file already exists?

For example, assuming the myoutput.txt file exists, you run this command:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/temp/myoutput.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

You'll get this message:

Error Code: 1086. File '/temp/myoutput.txt' already exists

This is pretty clear. The file already exists. So you need to use a filename that does not exist.

Error With Secure-File-Priv

Are you running this SELECT INTO OUTFILE and getting an error about "secure-file-priv"?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

This essentially means that MySQL has a specific set of directories that can be used for input and output, and the file we're attempting to write is not in that directory.

To resolve this, we need to:

Find out what the specific directories are
Add this directory path to our OUTFILE parameter so our file is generated there.

To find out where the directories are, we can run either of these two commands:

                SELECT @@GLOBAL.secure_file_priv;

@@GLOBAL.secure_file_priv

/usr/files/

                SHOW VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	/usr/files/

The output in this example shows the value to be '/usr/files/'.

So, all you need to do is add this path to the start of the OUTFILE path.

Your SELECT statement would then look like this:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/usr/files/temp/customer.txt'

Secure-File-Priv is NULL

If you have the error above, and you look for the secure_file_priv variable, you may find it is NULL. This happens most often on MacOS with MAMP.

                SHOW VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	NULL

To resolve this, we need to add this variable to the my.cnf file.

First, stop your MAMP server by opening MAMP and clicking Stop.

Open a Terminal window.

Enter in this command:

                vi ~/.my.cnf

Your screen should look like this.

Press Enter to run the command. This opens up an editor for the file my.cnf.

Copy the following lines into the Terminal window:

                [mysqld_safe]                [mysqld]                secure_file_priv="/Users/BB/"

(Replace the letters BB with whatever your user folder is)

Press the : key and the cursor will be moved to the bottom.

Enter wq after the : which will write to the file and quit.

Press Enter, and you will return to the normal Terminal window.

Now, start MAMP again.

You can test the variable was set by running one of the commands from earlier:

                SHOW VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	/Users/BB/

Now, update your SELECT INTO statement to add the value here to the path of the OUTFILE:

                SELECT id, first_name, last_name FROM customer INTO OUTFILE '/Users/BB/customer.txt';

The file will now be generated.

Conclusion

So that's how you can generate a text or CSV file from the results of a MySQL query. You add an INTO OUTFILE parameter to the end of your SELECT query.

If there are any issues with error messages, they are usually permission-related.

williamstandidelf.blogspot.com

Source: https://www.databasestar.com/mysql-output-file/

Williams Tandidelf