Today I noticed any new links I generate and attempt to run in the browser give the following error.

INSERT INTO 202_clicks_spy SET click_id='1824432', user_id = '1', aff_campaign_id = '67', ppc_account_id = '5', click_cpc = '0.01', click_payout = '1.00', click_filtered = '1', click_alp = '0', click_time = '1224527957'

The table '202_clicks_spy' is full

clear it out in PHPMYADMIN

202_clicks_spy EMPTY it through the phpmyadmin interface-

Although before you do that CONTACT UR HOST, as if your SPY TABLE is getting full it is going to halt everything.

Ask your host this

WHAT IS THE LIMIT FOR A MEMORY TABLE ON MYSQL?

and CAN I INCREASE MY LIMIT FOR MY MEMORY TABLES?

---

if they can't increase it we need to move hosts. as this will crash everything!

Ok, thanks so much for your help Wes. Just FYI if you plan on hosting with mediatemple the default max memory for heap tables is 8mb. I had to upgrade and change my mysql configurations settings to 128mb however this limit at some point will be reached as well.

Perhaps in a new release you can provide a method where we can dump and archive this data through the interface thus freeing up the memory.

Thanks for the heads up. Although we love our current hosting setup ^_~

you should be good with a 128MB memory table, it sounds like your doing alot of volume through your prosper202 install.

Yes, your the first person to ever max out the spy table so it has been noted now!

I actually had this happen to me last night on my dedicated server, lost a couple hundred bucks while eating dinner but shit happens I guess. I restarted the sql service and it fixed it and I raised the memory too.

ok that should help, let me know if the issue comes up again

i would just like to second the statement that in the future it would be awesome to be able to dump 202 data.

Dump All Content

Dump This Campaign

Dump That Campaign

that would freaking rock. my 202 is about to have a stroke and I have no idea how to upgrade my MYSQL (php = no)... would love to just dump and start over but cant.

idea has been recorded!

@hudoo i'm on Media Temple too and this just happened to me when I went over 1 Million subids on one of my 202 installs.

Thanks for having the answer to this posted. Made a disaster an easy fix.

damn. i am now having this happen daily. i'm maxing it every day. sheet mon. sheet. Wes maybe think of this in future releases? i'm just sayin'

Ok lets hack it to clear out the spy every hour, instead of every day. Do this hack, please make sure everything works after this. This should fix it malan, sorry about this.

Goto /202-cronjobs/index.php

Goto line 95 and add this

So it'll be right after the

if ($check_count == 0 ) {

add that line of code, TEST TO MAKE SURE IT WORKS,

that should clear out your spy every hour, instead of the default, every-day! lets see if that fixes it for you.

Please take personal note of this hack, because this isn't going to be the same if we do an update, so make sure if we do an update that you have a modified cronjob file.

Still doesn't work?

Do you know how to make a cronjob? or can your host make a cronjob for you? if so we can try a different script with a script setup with a cronjob. a cronjob is just a script that is executed at a specific interval of time, so we can run the clear spy table every 30 minutes for instance.

But try this first, what if u changed it to this instead: I changed it to clear out the last 6 hours, every hour instead of the last 24 hours.

/* -------- THIS CLEARS OUT THE CLICK SPY MEMORY TABLE --------- */

//this function runs everyday at midnight to clear out the temp clicks_memory table

$from = time() - 26400;

//this makes it so we only have the most recent last 24 hour stuff, anything older, kill it.

//we want to keep our SPY TABLE, low

$click_sql = "DELETE FROM 202_clicks_spy WHERE click_time < $from";

$click_result = _mysql_query($click_sql);

//$click_count = mysql_affected_rows();

i'll give this a shot. thank you sir.

ok will stand by

Hi Wes,

I know tracking202 pro is probably taking up all your time right now, but would you mind posting a little patch or code that I can use to fix this problem in the long term ( i know i can manually empty but I'm looking for something more stable)? I'm running on a dedicated server and pushing millions of clicks and this is causing me big problems... I'm setting up a fresh install on a fresh dedicated server and just want to make sure I have all the proper settings for everything to avoid this problem.

Maybe you could post a script that we could just set up a cron for and have run every 5 minutes or something? (On one of my installs we were literally getting this "The table '202_clicks_spy' is full" every 15 minutes unless we emptied it manually.

Thanks

How are you able to push millions of clicks daily with a dedicated server?? We´ve a cluster of 5 servers and only push 150000...

sorry i wasnt clear, millions total. probably more like 5k-10k per hour.

can you message me on skype, I will fwd you my sn and we'll get this taken care of, yes we can do a patch for you this weekend.

I will need you guys to setup a cronjob though.

also this limit can be fixed fast. can you modify the mysql settings and increase your mysql memory limit. this will fix the issue.

how do I modify the mysql memory limit? I've ran into this problem.

I just had this happen today and also lost money because of it. I emptied the table and it worked. The bad part is though, the script has only been installed a few hours. I'm on 3 dedicated servers, and one of them does nothing but mysql stuff.

Will adding the cron job code above help eliminate this in the future? I can set up my own cron jobs, would that be better? How would I go about it?