PDA

View Full Version : MySQL error every day


Hengis
12-07-2009, 12:20 PM
Hi there.

I receive a single MySQL error every day at exactly 3:09am.
Database error in vBulletin 3.8.1:

Invalid SQL:
CREATE TABLE vb_GAB_temp_stats
SELECT gabid,typeid,m,d,y,script,forum,sum(count) `count`,dateline FROM `vb_GAB_stats`
GROUP BY gabid,typeid,m,d,y,script,forum,dateline;

MySQL Error : Error writing file '/tmp/MYNTjWFr' (Errcode: 28)
Error Number : 3
Request Date : Sunday, December 6th 2009 @ 03:03:20 AM
Error Date : Sunday, December 6th 2009 @ 03:09:10 AM
Script :

Could you please help me with this?

Morgan
12-09-2009, 05:01 PM
Error code 28 means that there is "no space left on device" i.e., one or more partitions on your server is out of space or doesn't have enough space to complete the operation. My guess is that your vb_GAB_stats table is very large so the vb_GAB_temp_stats cannot be created because the server doesn't have enough space. You can either ask your host to give you more space, or you'll need to run these queries to delete the stats and start over as there isn't enough room to compress the stats:

# warning: deletes all statistics data !!!!
DROP TABLE IF EXISTS vb_GAB_temp_stats;
TRUNCATE TABLE vb_GAB_stats;

Hengis
12-09-2009, 05:14 PM
Thank you Morgan.

Is there a way to deactivate the stats and then re-activate them in the future if needed?

Morgan
12-09-2009, 05:55 PM
Go to the ACP -> GAB -> Banners and for each banner listed, there are the Track Impressions and Track Clickthroughs options that you can set to no to not track stats, but even if you do that, you will probably still get the database error message because the vb_GAB_stats table already contains data.

Hengis
12-09-2009, 06:00 PM
Ok, thanks.

Is it perfectly safe for the rest of my site if I drop that table?

Morgan
12-09-2009, 06:12 PM
You do not want to drop the vb_GAB_stats table. You do want to run the queries in post two if you want to delete the stats and start fresh with new stats.

Hengis
12-09-2009, 06:14 PM
Yes, I think I should.

I have 4,166,467 records in vb_GAB_stats.

Is it best to run them from PHPMyAdmin or from within the VB ACP?

Morgan
12-09-2009, 06:19 PM
Nope, you do not want to drop the vb_GAB_stats table. Run the two queries in post two. That'll do the right thing. You can run the queries from phpMyAdmin if you want.

Hengis
12-09-2009, 06:21 PM
Right, I will do what is in post 2 now.

Thank you for your support.