This was tested using Windows 10 and Windows Server 2016, both running MySQL 8.0.x. Modify default options for the MySQL Server in the start up file at C:ProgramDataMySQLMySQL Server 8.0my.ini: secure-file-priv'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads' secure-file-priv'' 2. These could be inserted into the variable if needed. I omitted field terminations, enclosures, and line terminations because they were the default values. This produces a file: X:/mysql_secure/my_script_data/my_file_prefix_190827_suffix.csv. Unfortunately, with all these set appropriately the secure-file-priv variable appears to block reading files from the designated secure directory. SET := CONCAT("SELECT * FROM my_database.table WHERE conditions INTO OUTFILE "_suffix.csv'") MYSQLWORKBENCH DATABASE BANCODEDADOS ERRORCODENeste vídeo mostro como resolver os erros de importação de arquivo csv.In this video I show how to solve th. That said, you need to explicitly set the variable to an empty string in your configuration to disable this security feature. SELECT 'my_script_data/') INTO in Windows returns a path with '\' The mysql documentation on securefilepriv states that the variable is set to a default, platform-specific filesystem location, to make it secure by default. To create a timestamped CSV file with your output use the following: - # VARIABLES That means the following path must already exist: X:/mysql_secure/my_script_data/ In your SQL script, store your output directory in a variable, using CONCAT() to append other file paths as needed (they will have to be created manually before executing, unfortunately). If you do not, the MySQLxx service will not start. NOTE: Make sure to save your file in the same encoding that it was originally written in (usually UTF-8). Do this by modifying the my.ini file ( create a backup first), searching for secure-file-priv and providing an absolute path: Let's assume you want to store the output in a subdirectory to make it easier to manage e.g./my_script_data/ inside the secure folder, but you might run this script on multiple servers, possibly with different secure directories.įirstly, for each server, set the secure_file_priv to the most appropriate directory, perhaps on another drive e.g.
0 Comments
Leave a Reply. |