# Non-WP Custom Site: Exporting to OUTFILE secure-file-priv Error

**URL:** <https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394>\
**Category:** Support\
**Tags:** windows, mysql-8\
**Created:** [June 21, 2024, 5:18pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394 "2024-06-21T17:18:58Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![CodeSlayer2010](https://sea1.discourse-cdn.com/flex019/user_avatar/community.localwp.com/codeslayer2010/32/18772_2.png) [@CodeSlayer2010](https://community.localwp.com/u/CodeSlayer2010)\
**Post date:** [June 21, 2024, 5:18pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394/1 "2024-06-21T17:18:58Z")

</div>

### What issue or error are you experiencing?

I’m learning MySQL and have set up a **custom non-WordPress site** (as per [torquemag](https://torquemag.io/2022/07/use-local-by-flywheel-without-wordpress)), deleted all the WordPress files, and imported a MySQL database not of WordPress origin. I am using MySQL Workbench and attempting to export the file to a TSV (tab-separated CSV) with this code:

```sql

/* Export data into TSV */

SELECT * INTO OUTFILE '/tmp/dump.csv'

FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

FROM videos;

```

When I run the command I get this error:

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

I tried a couple possible solutions, but all failed:

1. Created a `my.ini` file in the `conf/mysql` folder as per this video, and added this code:

```ini

secure-file-priv=""

```

1. Renamed file to `my.ini.hbs`, but no-go. In fact, this is the error message from Local:

 ![image](https://us1.discourse-cdn.com/flex019/uploads/lwpforums/original/3X/b/d/bd37223483a34bcaafababef3210e2487dd16003.jpeg)

1. Ran each of these commands to get the secure file location, but the result is NULL for each:

```sql

select @@secure_file_priv;

show variables like "secure_file_priv";

SELECT @@GLOBAL.secure_file_priv;

```

I’m not sure where to go from here. I just want the SQL export command to be successful and I don’t see anything in the code that should be throwing that --secure-file-prive error.

**… To make matters worse,** I closed LocalWP, deleted the my.ini.hbs file, restarted Local. But, now the site won’t start and Local keeps throwing the my.ini `site can't start` error.

#### References:

- [database - How should I resolve --secure-file-priv in MySQL? - Stack Overflow](https://stackoverflow.com/questions/32737478/how-should-i-resolve-secure-file-priv-in-mysql)

- [Mysql 5.7: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement: LOAD DATA INFILE · Issue #9528 · matomo-org/matomo · GitHub](https://github.com/matomo-org/matomo/issues/9528)

* * *

### What steps can be taken to replicate the issue? Feel free to include screenshots, videos, etc

1. Create a blank WordPress site by clicking the “+” in LocalWP

2. Delete all the wordpress files in `app/public/`

3. Import a non-wordpress MySQL file of proprietary/confidental data into Adminer

4. Ensure the database has a table called `videos` and it has at least 3 columns and 3 rows of data

5. Run the following command sequence:

```sql

/* Export data into TSV */

SELECT * INTO OUTFILE '/tmp/dump.csv'

FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'

FROM videos;

```

* * *

### System Details

- **Local Version:**

- **Operating System (OS) and OS version:**

* * *

### Local Logs

Attach your Local Logs here ([Help Doc - Retrieving Local’s Log](https://localwp.com/help-docs/troubleshooting/retrieving-locals-log-file/))

* * *

_ **Security Reminder** _

Local does a pretty good job of scrubbing private info from the logs and the errors it produces, however there’s always the possibility that something private can come through. Because these are public forums, always review the screenshots you are sharing to make sure there isn’t private info like passwords being displayed.

---

<div class="post-metadata">

**Author:** ![ben.turner](https://sea1.discourse-cdn.com/flex019/user_avatar/community.localwp.com/ben.turner/32/5366_2.png) [@ben.turner](https://community.localwp.com/u/ben.turner)\
**Post date:** [June 21, 2024, 6:41pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394/2 "2024-06-21T18:41:55Z")

</div>

We don’t explicitly add that flag when MySQL starts, and a value of `NULL` means that reading/writing data is disabled.

I think the issue is that you need to explicitly configure MySQL to have a certain folder to write to. I didn’t try this with a blank site, but I think it’s the same process. Basically:

1. Update my.cnf.hbs to point to a specific folder
2. Restart the site so the configuration file is compiled and loaded
3. Ensure the value is set correctly
4. Run your query

I was able to do this with `wp_posts` data, so maybe this should work for you too?

 ![Zight 2024-06-21 at 11.36.51 AM.jpg](https://us1.discourse-cdn.com/flex019/uploads/lwpforums/original/3X/1/4/14198b3e819360d528c811217cdedf56c74e8113.jpeg)

---

<div class="post-metadata">

**Author:** ![CodeSlayer2010](https://sea1.discourse-cdn.com/flex019/user_avatar/community.localwp.com/codeslayer2010/32/18772_2.png) [@CodeSlayer2010](https://community.localwp.com/u/CodeSlayer2010)\
**Post date:** [June 21, 2024, 7:42pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394/3 "2024-06-21T19:42:42Z")

</div>

Thanks for the quick reply @ben.turner ! The export worked, but only when I set secure-file-priv as empty like this:

![image](https://us1.discourse-cdn.com/flex019/uploads/lwpforums/original/3X/7/5/75bdafcafc30ff2df52816b8d219ef704db5d759.jpeg)

When I tried to add a Windows-style path like this,

```ini
[mysqld]
{{#unless os.windows}}
skip-name-resolve
{{/unless}}

secure-file-priv="C:\Users\erich\Downloads"

```

I get that error code 1290 again:

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

I’m testing the SQL commands in MySQL Workbench, but I get the same results in Adminer:

 ![image](https://us1.discourse-cdn.com/flex019/uploads/lwpforums/original/3X/4/d/4d8034c86b41085d2d9b917637ab4646341e9ad7.png)

That seems to rule Workbench out as the root of the issue.

Next, I tried a Unix-style path like this,

```sh
secure-file-priv="/c/Users/erich/Downloads"

```

But, that gives me the “Uh-oh! Unable to start site” error from before.

Any ideas? Perhaps this is Windows-specific and somebody can test on a Windows computer?

**ALSO: As a side note** , I tried adding that to `my.cnf.hbs` as an attempt before I posted for help originally. I added the secure-file-priv definition immediatedly under the [mysqld] and I also tried adding it at the bottom as the very last statement. _ **Why didn’t any of those attempts work?** _

---

<div class="post-metadata">

**Author:** ![ben.turner](https://sea1.discourse-cdn.com/flex019/user_avatar/community.localwp.com/ben.turner/32/5366_2.png) [@ben.turner](https://community.localwp.com/u/ben.turner)\
**Post date:** [June 25, 2024, 3:51pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394/4 "2024-06-25T15:51:23Z")

</div>

Oof, this was interesting! 😂

I think there are a couple of things going on, but you probably can get this working by:

1. use either:
  - escaped backslashes
  - forward slashes instead of backslashes

2. use the full path to the `secure-file-priv` value

Here’s a screenshot of escaping the backslashes:

 ![Zight 2024-06-25 at 8.48.45 AM.jpg](https://us1.discourse-cdn.com/flex019/uploads/lwpforums/original/3X/5/4/54cbec4fcc098c91b4765a4b886bbcf8d970d78f.jpeg)

A little more detail about how I got there. I noticed that when I used single back slashes, my username of `ben.turner` was getting mangled with the inclusion of the bell character. For example, `C:\Users\ben.turner\Downloads` was turning the `\b` into a “bell” escape code. I fixed that by escaping the backslashes, so something like:

```auto
C:\\Users\\ben.turner\\Downloads

```

I would imagine that you would have something similar with `\erich` being interpreted as `<escape-sequence>rich`

You could use forward slashes instead, because it seems that Windows converts those correctly, and then you don’t need to type out backslash twice. Anyway, can you give that a shot and let me know how it goes?

---

<div class="post-metadata">

**Author:** ![CodeSlayer2010](https://sea1.discourse-cdn.com/flex019/user_avatar/community.localwp.com/codeslayer2010/32/18772_2.png) [@CodeSlayer2010](https://community.localwp.com/u/CodeSlayer2010)\
**Post date:** [July 17, 2024, 6:15pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394/5 "2024-07-17T18:15:30Z")

</div>

Thanks Ben! I’m catching up with work after returning from vacation, so I honestly cannot say when, but hopefully I will find time to test this out soonish 🙂

---

<div class="post-metadata">

**Author:** ![system](https://sea1.discourse-cdn.com/flex019/user_avatar/community.localwp.com/system/32/1_2.png) [@system](https://community.localwp.com/u/system)\
**Post date:** [October 15, 2024, 6:16pm UTC](https://community.localwp.com/t/non-wp-custom-site-exporting-to-outfile-secure-file-priv-error/43394/6 "2024-10-15T18:16:23Z")

</div>

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.