{question}
I get ERROR 1016 (HY000): Writing to this file is disallowed when trying to output a query to a local directory. Why is that? What can I do to avoid it?
{question}
{answer}
It is common to run queries redirecting the output to a file. In this example, we're trying to write the content of the information_schema.users table to a file named users.txt in the /tmp directory:
singlestore> SELECT * FROM information_schema.users INTO OUTFILE '/tmp/users.txt';
ERROR 1016 (HY000): Writing to this file is disallowed: /tmp/users.txt
SingleStore uses the engine variable secure_file_dir to restrict the directory to which any import or export operations should be limited for each node. Unless the `/tmp` directory is set as the secure_file_dir, the example above will fail with the error message. To avoid the error, either write to the directory specified as secure_file_dir or update the secure_file_dir to the desired directory. If the value is unspecified, the setup-clustercommand sets <defaultInstallDir>/db-files as the default value.
To check which directory the queries can output, run SELECT @@secure_file_priv
singlestore> SELECT @@secure_file_priv;
+--------------------------+
| @@secure_file_priv |
+--------------------------+
| /var/lib/memsql/db_files |
+--------------------------+
This variable can only be set on startup in the memsql.cnf file on each host. Edit memsql.cnf to set a different location or delete the variable line to remove the restriction.
Check here for more details.
{answer}