Interesting. Was just dealing with this with someone. Managed to learn some totally bodacious stuff, although not how to spell that particular word. Error 28 means "disk full" which doesn't mean your web space - it theoretically means your MySQL space, but even that's not really true.

Step one: go to your database and optimize your tables on principal.

I googled the error statement and learned that in some cases doing a 'repair' to the database solved the issue, or at least it seemed to for some people.

Step two: go to your database and repair your tables on principal.

In this case it didn't help, so we tinkered some more. Guess what! The crux of this particular biscuit is the JOIN statement in the sql string, or at least it was for one particular individual. How do I know that? We could go to his stats page and get the page loaded until it came time to display the Recently Commented - a hack of mine. It too uses the JOIN thing, so I asked him to delete it. Lo and bee hole it worked - the stats page would serve up it's information, including the Heavy Hitters component of the sidebar (which is another hack of mine). I therefore searched google for the text in the error and the single word 'join'.

http://www.google.com/search?hl=en&q=%22Got+error+28+from+table+handler%22+join&btnG=Google+Search

One of the top responses is a host forum thread in which the host reveals they fixed the issue by clearing the cache behind the tmp folder. They indicated that cpanel did not do it's thing properly and they took care of the issue for the user. Thus it seems safe to assume that a disk space error can come from not only MySQL space but also space allotted to MySQL in the tmp directory, which I guess it has to use when it joins tables.

Step three: if you still have the stats folder see if you can access it directly. If so talk to your host about tmp cache issues and mention how JOINs seem to mess up the whole world.

Unfortunately that did not fix this particular user's issue. Seems he went to his host with all this information about JOIN and tmp cache and learned that his server was extremely unbalanced. I don't understand that statement because it either falls over and goes boom or it's balanced okay in my book, but whatever: this guy and his host expect to see things working properly when the host fixes the server load balance issue.

Therefore, and in closing, optimize, repair, and talk to your host about possible tmp cache issue and/or server load balance issues and mention that this problem seems to be related to your application JOINing tables as part of it's query.

Or not!

Oh by the way it does JOIN to hook up comments to posts even if there aren't any. It figures that out by joining. INNER JOIN to be precise, but my Recently Commented hack uses only JOIN and my heavy hitters hack just does a good old fashioned "yo db wazzup!!!"

(1) I'm not sure where to "optimize". It's not an option off cpanel in the MySQL DB's and if it's in phpMyAdmin 2.6.0(pl3) I didn't find it.

(2) I did both a "check" (all tables OKAY) and a repair (all tables OKAY and without any sort of messages that would indicate a problem in a particular table ... assuming such feedback is part of the MySQL Acct Maintenance).

(3) I'm not sure about stats. I took out stats in my _main.php & I looked thru all the subdirs in my b2evo install and didn't find a stats folder.

That's interesting info about the error message. I'll drop my host a note & see what they can tell me from their end regarding the cache. (Hopefully my host's server isn't in peril from toppling over.)

The good news was that, eventually, I was able to get past the error. Concerned that our b2evo DB was hosed, I got into phpMyAdmin & just pawed around the tables, browsing to make certain that I could still 'see' the data. All appeared in order (but it was my first foray into phpMyAdmin). I thought maybe by just opening, closing, looking, etc., it might fix whatever 'bad state' the DB was in. Must've worked.

If your mysql-databases are on a server that you don't own, there is a big chanche that that server is full.
And you can't help it.
It's your host provider who has to deal with it.
It means that he's allowing far too mutch databases on one server (for profit offcourse)

Thanks for your help everyone. It sure freaked me out when we didn't have a site and instead had a big, fat MySQL error!

(Especially because I haven't backed anything up. What do you guys do about backups? Do you backup the DB and store it locally?)

We heard back from our host. Their reply:

We upgraded the CPanel service. It used the temp folder to store upgrade files, leaving little room for MySQL temp files. Once the upgrade was finished, the temp files were deleted & the problem was eliminated.

I am happy that they had an answer (and that it's fixed), but it would seem to me they should allocate more tmp space if they KNOW there's going to be a squeeze.

You're correct Topanga ... not a thing I can do.[/quote]

I have a cronn job that makes a backup of my database twice a week.
I send that gz-file to a gmail account.

How do you set that up?

(Only been using b2evo for a month and right now, not backing up anything, relying only on the hosting service.)

(Makes me nervous!)

Thanks.

-Scott

do you have cpanel of plesk ?
That is the backoffice-suite that your provider has given you for accessing your files, databases, etcetera...

It is in there that you can enter a cronnjob..

There must be a help-function..

<?
$datestamp = date("Y-m-d");      // Current date to append to filename of backup file in format of YYYY-MM-DD

/* CONFIGURE THE FOLLOWING SEVEN VARIABLES TO MATCH YOUR SETUP */
$dbuser = "";            // Database username
$dbpwd = "";            // Database password
$dbname = "";            // Database name. Use --all-databases if you have more than one
$filename= "backup-$datestamp.sql.gz";   // The name (and optionally path) of the dump file
$to = "you@remotesite.com";      // Email address to send dump file to
$from = "you@yourhost.com";      // Email address message will show as coming from.
$subject = "MySQL backup file";      // Subject of email

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
$result = passthru($command);

$attachmentname = array_pop(explode("/", $filename));   // If a path was included, strip it out for the attachment name

$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "<<<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($filename))));

$headers = "From: $from\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"".$mime_boundary."\"\r\n";

$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content.= $message."\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Disposition: attachment;\r\n";
$content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content.= "Content-Transfer-Encoding: base64\r\n\r\n";
$content.= $data."\r\n";
$content.= "--" . $mime_boundary . "\r\n";

mail($to, $subject, $content, $headers);

unlink($filename);   //delete the backup file from the server
?>

cron job command is :
php /path/to/file

cpanel usually has 2 ways to set up cron jobs, stk, the UNIX way or the newb way ;)

lifted from http://www.notonebit.com/

Thanks for taking the time to reply, whoo.

Couple of questions:

1) I don't really want to email the file, preferring to fetch it periodically using FTP. Since that's the case, I'm thinking that it could be simplified to:

<? 
$datestamp = date("Y-m-d");         // Current date 
$dbuser = "MYUserName";             // Database username 
$dbpwd = "SuperSecretPW";          // Database password 
$dbname = "--all-databases";         // Database name. Use --all-databases if you have more than one 

$filename= "../sqlBackUps/backup-$datestamp.sql.gz";   // The name (and optionally path) of the dump file 

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename"; ?>

(2) If you only have one DB, will '--all-databases' still work?

(3) What are the steps to RESTORE a GunZipped SQL backup file? According to the phpMyAdmin doc, you select your DB, go to SQL query & then simply type in the "location of the text file" (choosing 'gzipped' as the compression type) Is it REALLY that simple? Have you done this before?

I'm thinking of doing a test run, but of course, I'm afraid of losing everything!! :D

stk wrote:

Thanks for taking the time to reply, whoo.

Couple of questions:

1) I don't really want to email the file, preferring to fetch it periodically using FTP. Since that's the case, I'm thinking that it could be simplified to:

<? 
$datestamp = date("Y-m-d");         // Current date 
$dbuser = "MYUserName";             // Database username 
$dbpwd = "SuperSecretPW";          // Database password 
$dbname = "--all-databases";         // Database name. Use --all-databases if you have more than one 

$filename= "../sqlBackUps/backup-$datestamp.sql.gz";   // The name (and optionally path) of the dump file 

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename"; ?>

(2) If you only have one DB, will '--all-databases' still work?

(3) What are the steps to RESTORE a GunZipped SQL backup file? According to the phpMyAdmin doc, you select your DB, go to SQL query & then simply type in the "location of the text file" (choosing 'gzipped' as the compression type) Is it REALLY that simple? Have you done this before?

I'm thinking of doing a test run, but of course, I'm afraid of losing everything!! :D

1. try it. you wont hurt anything testing that code

2. i dont know, OR phpmyadmin will tell you the name

3. yes it really is that simple :) yes I have :) not using that script though

Continuing to experience Error 28 failures.

I found more information from mySQL.com and I'm posting it here in case others experience this problem. (I'm trying to work our hosting provider, but they're suggesting things like browser cache and such ... next, they'll be telling us we have a virus on our computer!! >:-< )

Further information from: Mysql.com

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

What is error 28 and how can I avoid it?

Using the command line tool perror you can find out, what the error 28 means.

> perror 28
Error code 28: No space left on device

If you get his error, you need to check all filesystems where MySQL operates. It may be single filesystem or as we recommend you can have datadir, tmpdir and log files split into dedicated filesystems. MySQL often creates temporary tables for some queries - most of them are placed in tmpdir, but some into database directory (e.g. ALTER TABLE). You must ensure, that there is enough free disk space available for MySQL.

You can check out, that environment variables for MySQL are properly set.

Check the value of tmp_table_size:

mysql> show variables like "tmp_table_size";
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+

This tells, what is the limit, when reached, in-memory temporary tables will automatically be converted to a disk-based MyISAM table.

Then you need to find out, where the 'tmpdir' points

mysql> show variables like "tmpdir";
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| tmpdir | /tmp/mysql/ |
+---------------+-------------+

And check, that there is plenty of space available in that device.

In the beginning, I told myself that I would take a backup every week, and that I would store it.
But at the end, there is allways something that slips into, and you will see, that just at that moment, the moment that your backup is 2 weeks, old, that you get a breakdown.

stk, why don't you want to email it ?
I email it to a gmail account (1000 mb).
It's allways there, and I don't have to bother of spaceproblems.

i tested your question about using --all-databases, yes it works, stk.

very nice script, it works, and ive taken your suggestion topanga and have it going to a gmail account :)

Topanga, Whoo ...

I finally got around to setting up a cron job to backup the b2evo database and used that script you posted (above) ... (the FULL version w/email and everything ... now that I have a gmail account) ;) THANKS for your input!! (Took me awhile, eh?)

I've learned some things you guys might be interested in:

When I ran a test (it ran fine) and tried to restore the database, the job failed (SQL barfed because the antispam table was ALREADY created). Now, I had a look at the mySql reference for the mysqldump command - http://dev.mysql.com/doc/mysql/en/mysqldump.html

Interestingly, it says that

As of MySQL 4.1, --opt is on by default, but can be disabled with --skip-opt.

My host is running v4.3.10 (which one can easily check by inserting the

<?php echo phpinfo(); ?>

into a new/existing file). HOWEVER ... it doesn't appear that it IS defaulted (despite my version), because

--opt

This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly. As of MySQL 4.1, --opt is on by default, but can be disabled with --skip-opt.

See how the --add-drop-table flag is SUPPOSED to be ON? (meaning ... IF the table exists, DROP it, before trying to ADD it - eliminating that ERROR message I got when trying to do a restore over an EXISTING b2evo DB )

We'll it wasn't (for me). Is it for you guys?

I changed the code mysqldump command line from:

mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename

to

mysqldump --opt -u $dbuser -p$dbpwd $dbname | gzip > $filename

which took care of that problem (and shortened the password flag.

ALSO ... I didn't realize it, but you can kick off the job manually, from your browser, by navigating to that file. I did add owner execute permission, but I don't think that's necessary. (Didn't have group/world Xecute either). Anyway ... I could see a back-office button ... "Backup Database NOW" so when you make a TON of changes to your Blog, just tap that button and it fires off a backup that gets emailed to your gmail (or wherever) account.

PS ... I'm STILL getting error 28 messages when I refresh/navigate/post my blog. Frustrating. The host has been very good about TRYING to fix it (doubling the cache, reconfiguring the disk partitions & the mySql server) and the problem has lessened, but NOT gone away.