More MySQL 'ORDER BY' Woes

[Tim Lodge]

I read about the difficulties Marjolein had with 'ORDER BY' yesterday, only to run into a similar problem of my own this morning! I just happened to check the small ads page at http://www.drascombe-association.org.uk/smallads.htm and found that it wasn't showing any results from the database. After an hour or so of hair-tearing I discovered that I could get the results to display again by removing the 'ORDER BY' statement from the following query:

$Query = "SELECT *, DATE_FORMAT(adate, '%D %M %Y') AS displaydate
FROM $TableName1 AS t1, $TableName2 AS t2
WHERE t1.boattype = t2.type AND t1.sold='no' AND t1.adate >= '$LastDanDate'
ORDER BY t1.adate DESC";

I'm pleased to have got it working again, but it's less than ideal that the adverts now appear in more or less random order. I've also had to amend most of the admin pages in the same way.

I suspect strongly - and I'm awaiting confirmation - that my hosting provider has done a version upgrade. The old pages still work on my test server, which is running MySQL 3.23.49, while the production server is running MySQL 4.1.18.

It seems to be the fact that I'm querying more than one table which breaks it. Can anyone spot a problem with my query, or suggest a workaround?

[Tim Lodge]

This has all been fixed. I got the following reply from my hosting provider:

The /tmp directory became full at 4am due to an end of month run. As php uses the /tmp directory this may have affected some code, as we got an error when running some php code today of no space left on device (device being the /tmp folder). Php has to be able to create files in the /tmp folder to work with certain coding, so this may be the fault. We have now rectified this, so may be your code would work again as it was before. Apologies for any inconvenience. Sure enough, the old version of my page worked again. I could have done with knowing this before I wasted a couple of hours trying to fix it myself! Anyway, at least it isn't a MySQL version problem, which would have been a bit more of a problem.

[iamback]

This has all been fixed. I got the following reply from my hosting provider:

Sure enough, the old version of my page worked again. I could have done with knowing this before I wasted a couple of hours trying to fix it myself! Anyway, at least it isn't a MySQL version problem, which would have been a bit more of a problem.Gosh. I am almost speechless.
Glad to hear it's fixed... but if this is a system /tmp folder on a shared server (which it sounds like) this should of course never happen. It's not even something you can workaround with ini_set() (although some ini directives have a default starting with /tmp).

Note that if you are using file-based sessions, this also normally uses /tmp, so if those (for non-persistent sessions) are not being cleaned out frequently enough, the /tmp device can get full; but - still assuming this is shared hosting and not a dedicated server of VPS - that's not solely up to you but to the combined effect of all sites hosted on the same system using the same /tmp device... (this is a well-know disadvantage of using file-based sessions on a shared system).

BTW, looking at your description again, and the fact you could make it work again (crippled) by removing the ORDER BY clause, suggests it's MySQL running out of (temp?) memory rather than PHP: both queries will just deliver a resource identifier to PHP which you must then process to pull the information out of it - and the amount of that information is not different when it's ordered or when it's not!

[Tim Lodge]

Marjolein
Gosh. I am almost speechless.
Glad to hear it's fixed... but if this is a system /tmp folder on a shared server (which it sounds like) this should of course never happen. ...
As it happens, I've been less than happy about this particular host for a while now. I've moved all the paying clients to another server, and I'm intending to move this one (a freebie) when the current period they've paid for runs out.

I don't know enough about server configuration to run one myself (apart from the out-of-the-box My JSAS server which I do my development on), nor do I wish to learn!

[gary]

the /tmp directory became full
For a way to define your own session directory see this (http://www.php.net/manual/en/ref.session.php#54881) php.net article

[iamback]

For a way to define your own session directory see this (http://www.php.net/manual/en/ref.session.php#54881) php.net articleYes, that is indeed possible: It will help to avoid the problems with sessions as a result of a full /tmp (or even a /tmp directory with very many files in them). If you are using file-based sessions it is indeed a good idea to move them to a directory under your own control. (Or not to use files at all, but instead use a database.)

But on a shared system whether the /tmp directory becomes full may still be due to other clients' websites on teh same box - so while moving your session files may avoid problems with those sessions you may still encounter problems with PHP due to a full /tmp directory. And not all php.ini settings with /tmp in their (default) path can be changed by PHP at runtime.

The fact remains that on a shared hosted system it's the resposibility of the host to ensure that the /tmp direcory does not become full: the admins of the hosted sites have no access to it or control over it. If /tmp does become full it's a symptom of bad system management or an overloaded box.

[gary]

But on a shared system whether the /tmp directory becomes full may still be due to other clients' websites on teh same box - so while moving your session files may avoid problems with those sessions you may still encounter problems with PHP due to a full /tmp directory.DUH!
I can't believe I overlooked that point -- the odds are very high that the space one uses may well be on the same partition as /tmp.

PostgreSQL Session Save Handler (http://www.php.net/manual/en/ref.session-pgsql.php) documentation.

[iamback]

I can't believe I overlooked that point -- the odds are very high that the space one uses may well be on the same partition as /tmp.Actually from what I know about hosting, the odds are very high that /tmp is a global system directory on a separate partition; and likely with everyone's hosted system on a separate partition, too, and a size limit on everything. The /tmp would be shared by everyone's systems though so if PHP makes use of that at all (even if not for storing session files) it's going to be limited by what the other systems are doing. Even on a VPS, there is likely to be a shared root-level /tmp directory (or device) that is being used by your system.

I'm using a nice class to store my sessions in a MySQL table, BTW.

[gary]

Actually from what I know about hosting, the odds are very high that /tmp is a global system directory on a separate partitionVPS can be done either way - chroot (virtual root) or discrete partition space.
Single-domain web-hosting/mail/SQL is more likely done with shared partition space to improve memory utilization.I'm using a nice class to store my sessions in a MySQL table, BTW.Would expect nothing less from you.

[iamback]

VPS can be done either way - chroot (virtual root) or discrete partition space.Indeed - I only said "likely" for VPS. My hosting deal is VPS with a virtual root, and it's still common though newer virtualization software is now becoming more common (various flavors) that does away with that and gives you discrete space with real (rather than virtual) root access.

For selecting a new host I'll be looking for the latter, having experienced the disadvantages of virtual root access: big difficulties installing software that expects real root access, for instance - the whole "vinstall" deal is meant to work around that, the host having done all the adaptations for you. It often means big changes in make files and such and recompiling an app to do that - I once struggled through that (not having much experience with make) and am not looking forward to ever having to do that again. But then what is offered with "vinstall" is normally a limited choice of (commonly-used) packages. If you need something else you're out of luck and will have to do it yourself... and without support.

This is getting rather off-topic but I'm mentioning it anyway for those lurkers who at some time might be looking for a VPS-type hosting solution: carefully consider what kind of root access is provided and whether you might ever need to install software not in the standard set supplied.