Who's online table keeps crashing.

Hi,

My who's online table keeps on crashing and takes the whole website down.

Can anyone please advice what I can do with this?

website: https://www.bequjuice.com

First, go to phpMyAdmin and locate your database to find your tables ...

Then click the checkbox next to:
whos_online

Now, in the bottom Dropdown, select:
Repair

See if that corrects the problem ...

If not, you could try remaking it and seeing if that fixes it:

DROP TABLE IF EXISTS whos_online;
CREATE TABLE whos_online (
  customer_id int(11) default NULL,
  full_name varchar(64) NOT NULL default '',
  session_id varchar(128) NOT NULL default '',
  ip_address varchar(15) NOT NULL default '',
  time_entry varchar(14) NOT NULL default '',
  time_last_click varchar(14) NOT NULL default '',
  last_page_url varchar(255) NOT NULL default '',
  host_address text NOT NULL,
  user_agent varchar(255) NOT NULL default '',
  KEY idx_ip_address_zen (ip_address),
  KEY idx_session_id_zen (session_id),
  KEY idx_customer_id_zen (customer_id),
  KEY idx_time_entry_zen (time_entry),
  KEY idx_time_last_click_zen (time_last_click),
  KEY idx_last_page_url_zen (last_page_url)
) ENGINE=MyISAM;

Ajeh,

Yes, that does fix it. But the table crashed yesterday and I repaired it through phpMyAdmin. Then this morning it's crashed again. So I repaired again with phpMyAdmin.

Do you suggest I remake it? Where do I put the code above?

If you have tried and failed Repair in phpMyAdmin, then click on the SQL link in the header and paste the SQL code to DROP/CREATE it again and see if that corrects the problem ...

Ajeh,

So I'm still having issues with tables crashing. The who's online and sessions tables keep crashing continuously. I have to check the website constantly throughout the day to see if it crashed to repair it. Otherwise the site will be down.

Do you have any idea about what could be causing this?

When I do the repair the website is back up and running but then it crashed again the next day or even same day. I don't want to mess up the site completely where I cant repair it. Will dropping the tables take the site down?

**NOTE: THE CODE HERE IS SPECIFIC TO v1.3.9h. DO NOT USE ON OTHER VERSIONS!!!
**
You could set your site to Down for Maintenance for a minute, then run the following SQL in the Tools ... Install SQL ...

DROP TABLE IF EXISTS whos_online;
CREATE TABLE whos_online (
  customer_id int(11) default NULL,
  full_name varchar(64) NOT NULL default '',
  session_id varchar(128) NOT NULL default '',
  ip_address varchar(15) NOT NULL default '',
  time_entry varchar(14) NOT NULL default '',
  time_last_click varchar(14) NOT NULL default '',
  last_page_url varchar(255) NOT NULL default '',
  host_address text NOT NULL,
  user_agent varchar(255) NOT NULL default '',
  KEY idx_ip_address_zen (ip_address),
  KEY idx_session_id_zen (session_id),
  KEY idx_customer_id_zen (customer_id),
  KEY idx_time_entry_zen (time_entry),
  KEY idx_time_last_click_zen (time_last_click),
  KEY idx_last_page_url_zen (last_page_url)
) ENGINE=MyISAM;

DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
  sesskey varchar(32) NOT NULL default '',
  expiry int(11) unsigned NOT NULL default '0',
  value mediumblob NOT NULL,
  PRIMARY KEY  (sesskey)
) ENGINE=MyISAM;

These will drop those two tables and rebuild them for you ...

Do I have to set the site down for maintenance or can I just run the code?

Just want to make sure I'm doing this right. I'm going to tools then Install SQL Patches. In the dialog box there I run the codes you provided once at a time?

My version of zencart is: v1.3.9h

Please let me know if I got this right.

You can just run the SQL code without the Down for Maintenance ... The advantage of the Down for Maintenance is you can post a 15 minute warning that the site will be down for 5 minutes and to login to save current carts for your customers ...

If no one is on your site right now, then you can just run the two SQLs ...

They do run quickly, as in a few seconds.

The Down for Maintenance would just be a nice for your customers who might be shopping and not logged in so they have a change to login so that their current cart is saved before you run those two SQL.

They can be run separately or together in the Install SQL Patches ...

Ajeh,

Thanks for the help. I ran the codes and I guess we'll just have to wait and see if the tables crash again.

Hi Ajeh,

So the rebuilding the tables worked for a few days but yesterday night the sessions table crashed. I repaired it. Then this morning who's online crashed. This takes the whole site down. I rebuilt the tables again.

Any idea why this is happening? I really don't know what to do at this point.

Please help.

If you look at the Tools ... Who's Online ... in your Zen Cart Admin, about how many users are on your site most of the time?

If you look at the Tools ... Server Info ... what do you see in the Top section between the Menu and the PHP version information, including the PHP version information?

In who's online it says 3 users right now:

01:02:42 0 ¥Guest 66.65.21.240 10:07:39 11:03:32
Time Since Clicked:
00:06:49 ago Session ID: a42e88a06111e277fae6334d8aa9edad
Host: cpe-66-65-21-240.nyc.res.rr.com
User Agent: Mozilla/5.0 (Windows NT 6.0; WOW64; rv:29.0) Gecko/20100101 Firefox/29.0
/index.php?main_page=product_info&cPath=1&products_id=4
00:10:59 0 ¥Spider 68.180.224.243 10:59:22 11:08:57
Time Since Clicked:
00:01:24 ago Session ID:
Host: b100119.yse.yahoo.net
User Agent: Mozilla/5.0 (compatible; Yahoo! Slurp; http://help.yahoo.com/help/us/ysearch/slurp)
/index.php?main_page=product_info&cPath=5&products_id=33
00:05:28 0 ¥Spider 95.34.26.88 11:04:53 11:04:59
Time Since Clicked:
00:05:22 ago Session ID:
Host: 88.26.34.95.customer.cdi.no
User Agent: Mozilla/5.0 (compatible; MJ12bot/v1.4.5; http://www.majestic12.co.uk/bot.php?+)
/index.php?main_page=index&m%C3%83%C2%83%C3%82%C2%A2%C3%83%C2%82%C3%82%C2%80%C3%83%C2%82%C3%82%C2%A6=index&cPath=8

Server Info:
Server Host: https://www.bequjuice.com (98.139.135.199) Database Host: mysql (10.10.10.10)
Server OS: FreeBSD hostingprod.com 7.4-YAHOO-20120302 FreeBSD 7.4-YAHOO-20120302 #0 y amd64 Database: MySQL 5.1.55
Server Date: 06/06/2014 11:11:30 Database Date: 06/06/2014 11:11:30
Server Up Time: Unsupported HTTP Server: Apache/1.3.37 (Unix) mod_ssl/2.8.28 OpenSSL/0.9.6b mod_fastcgi/2.4.2
PHP Version: 5.3.6 (Zend: 2.3.0) PHP Memory Limit: 20M PHP Safe Mode: Off

Was the info I provided useful at all?

The table crashed today even though I just remade it this morning. If I remove these completely will it take the site down?

Who is your hosting provider for your server?

Have you talked to them about the table crashing so frequently?

This is not a common occurrence, it might also be a good time to look into upgrading to Zen Cart v150 or v151 ... but that should not be a true reason for this issue ...

Is this a clean v139 or was it an upgrade?

I have yahoo. And yes, I've talked to them a few times about this. They're saying that everything is running fine on their end and that it's an issue with zencart. And since zencart is a third party product they cant really offer any support.

It's a clean v1.3.9h. I never used any previous or any other versions. I tried upgrading to recent version but the site was not working properly so I had to revert back to the previous version.

Is there a way to diagnose this issue? At this point it's crashing a few times a day. I really don't know what to do here. Please let me know if you have anything else I can try?

Try this SQL statement using phpMyAdmin or in your Zen Cart via Admin->Tools->Install SQL Patches

ALTER TABLE whos_online ENGINE=InnoDB;
ALTER TABLE sessions ENGINE=InnoDB;

This just causes MySQL to use a different internal storage mechanism which is slightly more resilient to very high traffic volume.

If that doesn't solve it, then your host probably has problems with their database server or with its configuration.

I don't think my site is high traffic. Maybe 200 hits per day. Should I still try running code above?
Also, would you recommend I switch hosts? and which would you recommend?

I am not a fan of Yahoo for real e-commerce hosting ...

I would recommend you look into our recommended hosting:
http://www.zen-cart.com/content.php?16-Compatible-Hosting

As to high traffic ... you're right, 200 hits/day isn't high, and therefore you should not be having these issues on a reliable host.

I suppose that if the crashing is a result of search engines spidering your page at high speed then they might be causing traffic spikes and causing the crash. But even then any properly-configured database should handle it fine.

That said, it's relatively harmless to make the changes I posted above. But it does depend on the server being properly configured for InnoDB ... and if they're not even properly configured for MyISAM then who knows how well they'll handle InnoDB.
Nevertheless, you can try the changes I posted, and if you want to go back for any reason, use the same statements and just substitute 'MyISAM' in place of 'InnoDB'.

I think it might be spiders. Since they are on the site when I check who's online.

I never had this issue until early this March. Didn't make any changes to the site, it just started happening.

I will try your recommendation and see if that does anything to fix the issue.

Thank you for the help.

Ajeh,

Just FYI if you encounter someone else having a similar problem. One of tech reps at yahoo was able to figure this out. The problem was with my wordpress blog. It was getting heavily spammed with comments. Since it's on the same data base it crashed zen cart tables. There were scripts that were constantly running and generating log error reports.