As you all know I am a huge fan of the Prosper202 & Tracking202 team. Wes & Steven are some of the most down to earth and cool cats in the affiliate marketing industry. I run a lot of traffic through prosper202. I happen to wipe the databases on one of my prosper202 installations quite often. I had recently wiped the database on one of the installs and a few days later my spy and analyze view stopped working. This had me concerned so I hit up a few affiliate buddies who tried to help me. I was in a bind and couldn’t figure out what was up so I called up Wes Mahler. This dude went above and beyond to help me resolve the issue. Since it’s such an easy fix i figured I’d post some step by step directions so other people don’t need to bother the prosper202 team.
If your SPY/Analyze views stop working and you are no longer seeing subids getting passed along to the affiliate network, here is what you need to do:
1) Open the MySQL section in your cPanel
2) Scroll to the bottom of that page and launch phpMyAdmin
3) In the left menu bar of phpMyAdmin select the database that is running your prosper202 install
4) This will launch the database and show you all of the tables within the database
5) Find the table called ‘202_clicks_spy’
6) Click the trash can icon next to that table (MAKE SURE YOU ARE DOING THIS ON THE CORRECT TABLE)
7) The message Do you really want to TRUNCATE ‘202_clicks_spy’ will popup. Click ok.
8) The message Table 202_clicks_spy has been emptied – Your Prosper202 is now fixed.
What is happening is that there is a memory limit on the tables within MySQL. On my server it appears to be 16MiB. Once that limit is reached it causes the Prosper202 install to stop functioning properly. Wes sent me a text later on letting me know this as well:
If you want to adjust the max memory size of the table max_heap_size is the mysql setting that increasing from 16mb+ will fix the issue. Once the setting changes, the server will need to be restarted for it to work.
I hope this helps someone who runs into the same problem.
good to hear that u got it working. Why did u wipe out 202 database tho? backup data?
I think what you can also do which is easier is do a reboot of your MySQL server which will clear the spy table.
@Martin
He’s just clearing the spy table itself, not the database (data). You don’t need to back it up.
If you have shell access to your server and have permissions to alter your my.cnf configuration file, check out:
http://wiki.mysqltuner.com/MySQLTuner
It’s extremely easy to run. Most mysql performance problems can be fixed by tuning your configuration. This tool will analyze your mysql transactions and make recommendations. I run it every so often and make the adjustments. It made a HUGE difference in performance for me.
Great information. Overall I think Wes and Steven need to work on their db integration to support more massive Prosper202 installations, where traffic volumes are higher; but its very hard to find points to nitpick about Prosper202 because it is such a great free tool.
I’ve actually taken some inspiration from the T202 team and created my own set of adjunct analysis tools for Prosper202 and Tracking202 Visitor data. Please check them out and give me your feedback. They are free for anyone’s use.
Thanks man, I’ve come back to this several times now.
@Justin – Anytime brotha 🙂
Updated so you don’t have to manually do it each time, we just change the MYSQL TABLE type so it’s no longer a memory table, which removes having any limit at all.
the issue with the above post is it dose clears it, but it’ll fill up again if you have lots of traffic. this fix changes the table type so it won’t have a memory limit any more and will never happen again:
http://prosper.tracking202.com/forum/12/29/prosper202-db-crashing-from-too-much-data-to-fast
[…] some time ago I wrote a post called How To Fix Prosper202 If Spy/Analyze Stops Working. If you run a lot of traffic to your Prosper202 setup you probably find yourself having to dump […]
prada ?? ??
3mulatto