When you try to run an SQL file using mysql command line program, you may encounter an error saying Failed to open file error 2.
The following example tries to run the source command and execute the query.sql file:
mysql> source query.sql
ERROR:
Failed to open file 'query.sql', error: 2
The code error 2 means that MySQL can’t find the .sql file that you want to execute.
To solve this error, you need to provide the absolute path to your file location.
You can find the absolute path of your file by opening the terminal in the directory or folder where your SQL file is located and run the pwd command.
For example, here’s the absolute path to the directory where I save the query.sql file:
$ pwd
/Users/nsebhastian/Desktop/SQL-files
Now I just need to add the path to the file when I issue the source command:
mysql> source /Users/nsebhastian/Desktop/SQL-files/query.sql
Please note that you need to use forward slashes (/) to separate your path sections. Using back slashes (\) may cause the same error.
Here’s an example:
mysql> source \Users\nsebhastian\Desktop\SQL-files\query.sql
ERROR:
Failed to open file '\Users\nsebhastian\Desktop\SQL-files\query.sql', error: 2
Even though the path is correct, MySQL expects a Unix-style path with forward slashes.
You should now be able to execute the SQL file. There are some other causes for error 2 in MySQL, so let’s take a look at that next.
Error 2 because of < or > symbols
Another thing that could cause the error is that you’re adding the greater than > or less than < symbol in front of the file path as shown below:
mysql> source < /Users/nsebhastian/Desktop/SQL-files/query.sql
ERROR:
Failed to open file '< /Users/nsebhastian/Desktop/SQL-files/query.sql',
error: 2
The greater than or less than symbol is commonly used to dump MySQL data to an SQL file or execute a script from the terminal without connecting to MySQL server.
You need to remove the symbol to execute the source command without error.
Error 2 because of semicolon when using \. command
The \. command is an alias of the source command that you can use to run an SQL file.
I don’t know if it’s a MySQL bug, but when you run the \. command with a semicolon ; at the end of the file path, you’ll get the error 2 response.
Take a look at the following example:
mysql> \. /Users/nsebhastian/Desktop/SQL-files/query.sql;
ERROR:
Failed to open file '/Users/nsebhastian/Desktop/SQL-files/query.sql;',
error: 2
But the same error won’t happen when you use the source command:
mysql> source /Users/nsebhastian/Desktop/SQL-files/query.sql;
...
# result
...
7 rows in set (0.00 sec)
To solve this issue, you need to omit the semicolon when you’re using the \. command.
And that’s how you fix the MySQL failed to open file error 2 issue.
Just keep in mind that the error is because MySQL can’t find the file you want to execute.
You probably need to check the path you passed into the source command and see if there’s any typo that causes the error.