MySQL ERROR 1 (HY000): Can’t create/write to file
In my first post on the RDX Blog, I’ll be describing a common permissions error DBAs receive (Errcode: 13 – Permission denied) and how to address it.
> desc foo_table;
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_3ff6_0.MYI' (Errcode: 13 - Permission denied)
The error is documented here: https://dev.mysql.com/doc/refman/5.6/en/cannot-create.html
What happened to the permissions on the /tmp folder? It is not allowing writes. First, we have to fix that then start looking into what or who changed permissions in the directory.
chmod 1777 /tmp
I will use this error as an example..
First, look at the entire error message and do not focus on the first error you see. For example, if you have an Errcode:
do not focus on ERROR 1
do not focus on HY000
You would be wasting your time since the Errcode gives you all the information you need. If that happened to be the only error message information that was passed along to you, then you do have resources available to look up errors:
- https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html
- https://dev.mysql.com/doc/internals/en/
I would also stress that you should double check the error log to confirm all error messages. Just because someone sends you an error does not mean it is the entire story. Always check your logs.
If you do run across an error that gives you little description, it is true that you have the ability to learn more about the error.
“describing the last error encountered during a call to a system or library function.” — https://man7.org/linux/man-pages/man3/perror.3.html
# perror 13
OS error code 13: Permission denied
Related to the error above, it is also possible to change your tmpdir location if that was required. In this case it was not, but it you ever need to change or override the defaults, you can find your current tmpdir with this:
> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+
You can then edit the my.cnf and place tmpdir=/tmp in whatever location you prefer.
Thanks for reading! Stay tuned for my next post, MySQL Optimization Tip- thread_cache_size, next week.