Ошибка 1290 mysql secure file priv как отключить

I created a NodeJS import script if you are running nodeJS and you data is in the following form (double quote + comma and n new line)

INSERT INTO <your_table> VALUEs( **CSV LINE **)

This one is configured to run on http://localhost:5000/import.

I goes line by line and creates query string

"city","city_ascii","lat","lng","country","iso2","iso3","id"
"Tokyo","Tokyo","35.6850","139.7514","Japan","JP","JPN","1392685764",
...

server.js

const express = require('express'),
   cors = require('cors'),
   bodyParser = require('body-parser'),
   cookieParser = require('cookie-parser'),
   session = require('express-session'),
   app = express(),
   port = process.env.PORT || 5000,
   pj = require('./config/config.json'),
   path = require('path');

app.use(bodyParser.json());
app.use(cookieParser());
app.use(cors());


app.use(
   bodyParser.urlencoded({
      extended: false,
   })
);

var Import = require('./routes/ImportRoutes.js');

app.use('/import', Import);
if (process.env.NODE_ENV === 'production') {
   // set static folder
   app.use(express.static('client/build'));

   app.get('*', (req, res) => {
      res.sendFile(path.resolve(__dirname, 'client', 'build', 'index.html'));
   });
}

app.listen(port, function () {
   console.log('Server is running on port: ' + port);
});

ImportRoutes.js

const express = require('express'),
   cors = require('cors'),
   fs = require('fs-extra'),
   byline = require('byline'),
   db = require('../database/db'),
   importcsv = express.Router();

importcsv.use(cors());

importcsv.get('/csv', (req, res) => {

   function processFile() {
      return new Promise((resolve) => {
         let first = true;
         var sql, sqls;
         var stream = byline(
            fs.createReadStream('../PATH/TO/YOUR!!!csv', {
               encoding: 'utf8',
            })
         );

         stream
            .on('data', function (line, err) {
               if (line !== undefined) {
                  sql = 'INSERT INTO <your_table> VALUES (' + line.toString() + ');';
                  if (first) console.log(sql);
                  first = false;
                  db.sequelize.query(sql);
               }
            })
            .on('finish', () => {
               resolve(sqls);
            });
      });
   }

   async function startStream() {
      console.log('started stream');
      const sqls = await processFile();
      res.end();
      console.log('ALL DONE');
   }

   startStream();
});

module.exports = importcsv;

db.js is the config file

const Sequelize = require('sequelize');
const db = {};
const sequelize = new Sequelize(
   config.global.db,
   config.global.user,
   config.global.password,
   {
      host: config.global.host,
      dialect: 'mysql',
      logging: console.log,
      freezeTableName: true,

      pool: {
         max: 5,
         min: 0,
         acquire: 30000,
         idle: 10000,
      },
   }
);

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Disclaimer: This is not a perfect solution — I am only posting it for devs who are under a timeline and have lots of data to import and are encountering this ridiculous issue. I lost a lot of time on this and I hope to spare another dev the same lost time.

Ubuntu 16.04 (EASY): Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

Then, just write there

mysql> SELECT * FROM train INTO OUTFILE '/var/lib/mysql-files/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

Mac OSX: Mysql installed via MAMP

Find out where you are allowed to write

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

NULL means you’re screwed so you have to create the file «~/.my.cnf»

Enable read/write for MySQL installed via MAMP (on Mac):

1. open «MAMP» use spotlight
2. click «Stop Servers»

3. edit ~/.my.cnf (using vi or your favorite editor) and add the following lines:

   $ vi ~/.my.cnf

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

1. click «Start Servers» (in MAMP window)

Now check if it works:

a. start mysql (default MAMP user is root, password is also root)

$ /Applications/MAMP/Library/bin/mysql -u root -p 

b. in mysql look at the white-listed paths

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /Users/russian_spy/          |
+---------------------------+
1 row in set (0.00 sec)

c. Finally, test by exporting a table train into a CSV file

mysql> SELECT * FROM train INTO OUTFILE '/Users/russian_spy/test.csv' FIELDS TERMINATED BY ',';
Query OK, 992931 rows affected (1.65 sec)

mysql>

When you update your site with new data from a file, you may see the error “Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement”.

Why did it happen all of a sudden? Is your data corrupted?

Not to worry. This is quite easy to fix and usually happens after a MySQL upgrade.

As part of our Server Management Services, we help server owners and webmasters resolve MySQL errors such as this.

Today we will help you fix error code 1290 in a few easy steps.

Why this MySQL error code 1290 appears?

From version 5.7.6 onwards MySQL introduced a new security feature.

This will prevent website attackers from loading malicious data directly into your database.

It is done by restricting the databases to load data from only a single special directory in the server.

That directory is accessible only by the server administrator, and therefore attackers won’t be able to load malware.

By default this directory will be “/var/lib/mysql-files/” in Linux servers.

So, if you have a program or script that loads site data from a different location, MySQL will think that it’s an unauthorized upload attempt, and block it.

Is there any solution?

We have two solutions for your problem with code 1290.

• One is moving the data file into the directory
• The other is reconfiguring and restarting MySQL server

Today, let’s discuss these in detail.

Fixing the error by moving the data file into the directory

Recently, one of our customers using Linux approached us with the error code 1290.

He said he was able to load files without failure before. But from the last few days, he is facing this error.

When checked, we found that he recently upgraded his MySQL to 5.7.6 and the default path is restricting the upload.

Let’s see how we fix this.

We asked him to use the query to determine where –secure-file-priv option is.

SHOW VARIABLES LIKE 'secure_file_priv'

This returned a variable along with its value as:

+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

We asked the customer to use  /var/lib/mysql-files/ as the file path to run LOAD DATA as this is the configured directory and he needs to move the data file here.

Finally, suggested running the LOAD DATA again with the full path.

This fixed the error.

For Windows Users

In Windows, we do the following for fixing the MySQL error 1290

We look for the file being used when the service starts at MySQL57 Windows service.

From there, we get a path similar to C:ProgramDataMySQLMySQL Server 5.7my.ini.

Open the file and under [mysqld] section we can see secure-file-priv along with value as shown:

secure-file-priv=”C:/ProgramData/MySQL/MySQL Server 5.7/Uploads”

This is the path we need to use. So, reissue the LOAD DATA statement with this right path for fixing the error.

Fixing the MySQL error 1290 by reconfiguring and restarting

In some cases, changing the directory won’t solve the issue. In such a case we need to do the following:

For Windows Users

• Go to start menu and type services.msc then press Ctrl+Shift+Enter to run it as an administrator.
• Locate the MySQL service and double-click to open its properties dialog.
• Check the Path to Executable for the –defaults-file option to determine where my.ini file is located.
• In windows, C:ProgramDataMySQLMySQL Server 5.7my.ini is the default file being used when the service starts.

Note: It may vary according to various versions

• Stop the service then close the dialog.
• From the start menu, take notepad then press Ctrl+Shift+Enter to run it as an administrator.
• Then open my.ini file previously located in notepad and search the file for secure-file-priv.
• finally, comment out the line by adding a # in front.

For Linux Users

In Linux, we suggest searching for either of the most common locations: /etc/my.cnf or /etc/mysql/my.cnf.

Search the file for secure-file-priv. We get it along with the value. Comment the line and restart the service.

This will fix the issue.

But, we won’t suggest this as it is not secure.

The secure way is always by moving the data file into the directory.

[Need assistance in fixing MySQL errors? Click here to talk to our experts.]

Conclusion

In short, we discussed in detail on MySQL error code 1290 and saw how our Support Engineers find fix for this in different scenarios.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;