Errcode: 2, WordPress unable to create/write to file

Question

So I've asked around basically everywhere (including here) but I'm going to try again because literally noone seems to want to help and I'm desperate af.

[Fri Dec 13 06:52:28.286593 2013] [:error] [pid 13101] [client 124.169.101.176:62647] WordPress database error Can't create/write to file '/var/tmp/#sql_1ff_0.MAI' (Errcode: 2) for query SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') ORDER BY t.name ASC LIMIT 50 made by do_accordion_sections, call_user_func, wp_nav_menu_item_taxonomy_meta_box, get_terms, referer: http://wardport.com/wordpress/wp-admin/nav-menus.php

As you can see from that^ I'm getting errcode 2, which by perror 2 means "No such file or directory". Most sources tell me that a) there is no space to work in the directory, which there is (it's actually completely empty!) or b) mysql doesn't have permission to write to said directory (/var/tmp/) which it does (desperate attempt http://puu.sh/5LeRg.png).

What the hell is wrong my server this is so frustrating

Answer 1

This file you can't create seems to be an AriaDB container file for a temporary table. Are you running MariaDB? If so, what version?

A temp file is not strange for this query.

Your mysqld is somehow misconfigured, or perhaps the server running it is misconfigured.

Do you have access to a simple (non-WordPress) mysql client? If so, try the same query there just to rule out the whole WordPress / PHP infrastructure as the cause of the problem. If that query fails, try a simpler one like

  SELECT COUNT(*) FROM wp_posts

This one may not fail, because it doesn't need to create and then sort a temp table. The point is to get a simple test case that fails. Don't overthink this, just get yourself a simple query that fails.

Is this your own server? If so, you should be able to find an error log for your mysqld process. It is probably a file with a name ending in .err someplace in your MySQL data directory. tail it (look at the last few hundred lines) and see if mysqld is explaining in more detail what's wrong. Run your simple failing test case query and look again.

/var/tmp should be writeable by every user on the system; *nix falls over in a heap if it isn't. It should also have plenty of space on it.

FWIW, this isn't a programming question (unless you're working on the internals of the version of MySQL you're using). It's a sysadmin question.

Answer 2

MariaDB is trying to create temporary table while execution SELECT for sorting.

I wonder if you change MySQL variable tmpdir to /tmp

mysql> SHOW VARIABLES like '%tmp%';
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
...
| tmpdir            | /var/tmp        | <= probably, current value is '/var/tmp'
+-------------------+-----------------+

[mysqld]
user    = ...
port    = ...
basedir = ...
datadir = ...
tmpdir  = /tmp  <= change here and restart MariaDB
...

UPDATE

I not sure service mysqld restart has the functionality changing option when startup. But, mysql.server has this. probably mysql.server is /etc/rc.d/init.d/ (assuming CentOS or Fedora). Could you try this?

$ ./mysql.server start --tmpdir=/tmp

I have tested with own my server. tmpdir is changed well.

Answer 3

So basically I completely gave up, did a thorough cleansing of my server, uninstalling MariaDB and deleting all of my databases along with it (wasn't much in them to begin with, although I'm sure it wouldn't have hurt to back them up and restore them later :P). I then restarted my server, reinstalled MariaDB & Re-ran the WordPress install and voilà everything worked.

It may be that only one step of this process fixed the problem, E.G. I may have simply needed to reboot my server to fix things up. If you have this same error give it a go!

Thanks again to everyone for trying to help. :)