Strange intermittent SQL error

[Gary Fischman]

This has happened twice since I've moved to vB:

Database error in vBulletin 3.0.7:

Invalid SQL: 
		SELECT thread.threadid, thread.title, thread.lastposter, thread.lastpost, thread.postusername, thread.dateline, forum.forumid, forum.title AS forumtitle, post.pagetext AS preview
		FROM thread AS thread
		INNER JOIN forum AS forum ON(forum.forumid = thread.forumid)
		LEFT JOIN post AS post ON (post.postid = thread.firstpostid)
		LEFT JOIN deletionlog AS deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread')
		WHERE 1=1
			AND thread.forumid IN(5,51,52,20,16,2,14,15,46,1,10,55,56,38,57,11,12,23,3,48,17,18,25,26,30,58,22,47,4,13,31,19,9,27,49,21,40,41,42,43,44,45,8,35,36,34,32,33,29,37,50)
			AND thread.visible = 1
			AND open <> 10
			AND deletionlog.primaryid IS NULL
		ORDER BY thread.dateline DESC
		LIMIT 15
	
mysql error: Error writing file '/tmp/MYygAXHp' (Errcode: 28)

mysql error number: 3

Date: Monday 06th of June 2005 10:40:25 AM
Script: http://forum.miata.net/vb/external.php?type=js

Now before anyone jumps in and tells me that I'm out of file space, or that my permissions are incorrect:

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda8              1004024    327976    625044  35% /tmp

and

drwxrwxrwt    4 root     root         4096 Jun 29 07:22 /tmp

My question is, what is being written to /tmp? I've never seen any temporary files in there. When does it happen? It seems to be coming from the Javascript syndication call. Could this really be a locking problem of some sort that merely *appears* like a file system problem?

Note: Both times this happened, it occurred in flood. I.e. - I received about 15 or 20 email notices of the error in rapid succession, then everything was fine and it didn't happen again until 3 weeks later. Then same thing - about 10 or 15 notices, then everything was fine again.

[Steve Machol]

Unfortunately this is definitely an out-of-space error. It's the only thing an error 28 can mean:

Error code 28: No space left on device

My guess is that your /tmp partition was temporarily full when this error occurred.

[Gary Fischman]

Unfortunately this is definitely an out-of-space error. It's the only thing an error 28 can mean:

Error code 28: No space left on device

My guess is that your /tmp partition was temporarily full when this error occurred.

You know, I'm a pretty smart guy, and I knew that would be your answer. That's why I specifically asked "what is being written to /tmp?"

You see, I have more than half a Gig in my /tmp partition, and nothing on my server writes to it except vB/MySql. And since I've never seen any temporary files there, clearly they're being written and deleted very quickly. So when does vB/MySql write to /tmp? How much? Are you saying that half a gig of temp space isn't sufficient for vB?

Lets not just chalk this up to "you /tmp partition was temporarily full". Lets try to figure out the cause so it doesn't continue to happen.

[Steve Machol]

I really don't know what is being written to the /tmp directory. I do know what this error means, and I'm sorry if you disagree. If you can find an alternate explanation for error 28, then please let me know. So far I have only found the one explantion.

And your entire server uses the /tmp directory, not just vB.

[Steve Machol]

Here's a explanation of this error from the MySQL site:

http://www.mysql.com/news-and-events/newsletter/2003-10/a0000000249.html

[Wayne Luke]

It is a temporary out of space error caused by temporary files being created and deleted in the normal course of work. The reason you wouldn't see a file would be because it wasn't able to be created in its entirety and deleted by MySQL.

I would suggest increasing the size of your temp partition. You should have at least twice your memory allocated for temporary files when running database applications.

[Jerry]

Depending on the size of the database that temp table could be really quite big, also there may be more than one at once.

[George L]

You know, I'm a pretty smart guy, and I knew that would be your answer. That's why I specifically asked "what is being written to /tmp?"

You see, I have more than half a Gig in my /tmp partition, and nothing on my server writes to it except vB/MySql. And since I've never seen any temporary files there, clearly they're being written and deleted very quickly. So when does vB/MySql write to /tmp? How much? Are you saying that half a gig of temp space isn't sufficient for vB?

Lets not just chalk this up to "you /tmp partition was temporarily full". Lets try to figure out the cause so it doesn't continue to happen.

here's more info on mysql tmpdir as well http://dev.mysql.com/doc/mysql/en/temporary-files.html

MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally /tmp, /var/tmp, or /usr/tmp. If the filesystem containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a filesystem where you have enough space.

MySQL creates all temporary files as hidden files. This ensures that the temporary files are removed if mysqld is terminated. The disadvantage of using hidden files is that you do not see a big temporary file that fills up the filesystem in which the temporary file directory is located.

For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form SQL_*.

ALTER TABLE creates a temporary table in the same directory as the original table.

[Gary Fischman]

I really don't know what is being written to the /tmp directory. I do know what this error means, and I'm sorry if you disagree. If you can find an alternate explanation for error 28, then please let me know. So far I have only found the one explantion.

Steve - I never disagreed. No need to get defensive. I'm trying to figure out what part of vB uses /tmp.

And your entire server uses the /tmp directory, not just vB.

Yes, I understand how *nix works. I also know that nothing I currently have running has much use for /tmp.

Here's a explanation of this error from the MySQL site:

http://www.mysql.com/news-and-events...000000249.html

They mention a "tmpdir" variable, however I don't have one. Presumably it defaults to /tmp?

I would suggest increasing the size of your temp partition. You should have at least twice your memory allocated for temporary files when running database applications.

Does this apply to the MySQL client box or the server box? In fact, is the error being generated on the MySQL client or the server? It appears to be coming from the client machine, which has 1G of memory. So should /tmp on that box have 2G? The server box has 4G of memory.

Note: Based on the message in the error, it strongly suggests that this is being caused by the Javascript syndication bits. Can someone perhaps look at that code and maybe offer a suggestion as to what would eat half a gig of disk space?

Incidentally, the filename given in the error message is not a *nix hidden file. So it makes me question whether that's a MySQL generated error.

[George L]

Hi Gary does seem the error is from your mysql server, and if you read my post and quoted info in post #8 http://www.vbulletin.com/forum/showp...93&postcount=8

when tmpdir variable in mysql isn't set it defaults to using server's /tmp directory

When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk space required is determined by the following expression:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

The row pointer size is usually four bytes, but may grow in the future for really big tables.

if you check the error message you posted above it's an ORDER BY query that it choked on so pretty sure it's mysql related tmp message.

May i ask how large your thread, post and forum tables are ?

As to size of /tmp it should be 2 to 3x times the size of the largest vB or mysql database table you have, so if thread table happen to be 1GB in size, you'd want 2-3GB free space on /tmp if post table is largest and say 2GB in size you'd want 4-6GB of free space

Or you can set a tmpdir setting in my.cnf and point it to else where and tell mysql to use that directory for tmp files i.e. tmpdir = /home/mysqltmp

[Gary Fischman]

when tmpdir variable in mysql isn't set it defaults to using server's /tmp directory

Yep - caught that just after I finished my post.

May i ask how large your thread, post and forum tables are ?

Thread: 140k rows, 25 Meg
Post: 1.33 million rows, 685 Megs
Forum: 54 rows, 104k

As to size of /tmp it should be 2 to 3x times the size of the largest vB or mysql database table you have, so if thread table happen to be 1GB in size, you'd want 2-3GB free space on /tmp if post table is largest and say 2GB in size you'd want 4-6GB of free space

Or you can set a tmpdir setting in my.cnf and point it to else where and tell mysql to use that directory for tmp files i.e. tmpdir = /home/mysqltmp

So my largest table is actually the postindex table and its about 750M. So I should have around 2.5 - 3GB?

And that's on the Database server box, right? Not the client machine?

[George L]

yup 3-4GB would be minimum i'd add the sized up for post, thread, postindex, and forum tables and times that by 3x

in /etc/my.cnf on mysql server you can add under [mysqld] group the line

tmpdir = /home/tmp

or somewhere where you have free disk space in abundance

note, you can also get error 28 low free disk space when a file reaches max file size limit of your OS i.e. 2GB or 4GB file size limits if you have such a limit

you can check by typing

create file sized 2100MB or 4200MB to test file system limit

dd if=/dev/zero of=bigfile bs=2100 count=1048576

or

dd if=/dev/zero of=bigfile bs=4200 count=1048576

[Gary Fischman]

Thanks - I just bumped my /tmp space up on the server. That should do the trick.

[George L]

Ok let us know how it goes

[Gary Fischman]

I'm at 2.5 Gig of /tmp space, and its still happening on occasion. If I look at /tmp immediately after getting notified of the error, the volume usage is back down to 2%, and there's no evidence that the problem ever occurred. Recovery is usually within 60 seconds. But during that 60 seconds, there may be over 100 of the SQL errors generated. (And presumably, every user who got the error probably timed out.)

The thing that's bothering me is that the error message always contains a reference to the syndication script. (http://forum.miata.net/vb/external.php?type=js)

I still suspect that there's something wrong in the syndication script. What is that script doing? No one has answered that in this thread.