Log files writing to database, increasing its size exponentially; need to delete

I am running version 1.3.8a on the default template. Also running MMySQL version 5.0 and php version 5.2.17 and it has not been upgraded.

I am having an issue finding the log files that are being written to the database. At some point in 2011, I set the ZC to keep log files, and since then it has been creating an ever and ever larger database, since it is writing it to the database instead of to a file on the server. Due to this, my hosting company has deleted the database as being too large. In 2011, it was 5MB, and it ballooned to 2.6GB by 2014.

What I need to do is delete the log files, which I was unable to do manually because they were too large. Can you please tell me where the log files are located in the database so that they can be deleted manually? I also need to change the setting so that this does not continue to happen. Can you please tell me where the setting is to stop ZC from writing the log files, or to change it so that the log files are written to a file on the server and not to the database? Thank you!

1. Log "files" are written to the filesystem. Not to the "database".
2. The "database" is in MySQL which is where your store's order history and product catalog are located.

So the way you worded your post makes it hard to determine exactly where your specific problem is.

I'm assuming you're really not referring to the MySQL database at all when you used the word "database", and that you really meant "server filesystem". And you were probably referring to the "cache" folder. Or maybe the "includes/modules/payment/paypal/logs" folder, or both.

You said that in 2011 you turned on a feature to store your ZC logs. To turn them off is as simple as turning off that feature ... whichever it was. But, since you didn't say which feature, we can't guess which one you're referring to.

Perhaps it's something in Admin->Configuration->Logging ?
Perhaps it's in Admin->Modules->Payment->your-paypal-module->Edit ?

AND MOST IMPORTANTLY, YOU REALLY NEED TO UPGRADE TO A MODERN VERSION SINCE YOURS HAS WELL-ADVERTISED SECURITY FLAWS AND IS VULNERABLE TO HACKERS.

Thank you for your fast reply! I am unable to check anything on the site's configuration at the moment since the database was deleted, and I am still trying to restore it. However, this is what has been happening.

Each time I use the database Control Panel to create a "backup" of the database, this creates a .sql file in a folder on the filesystem. Each of these backups is progressively larger and larger in size. Thus, something is being written to the database to increase its size. It does not sound like it is the log file, as you mentioned in your response. It seems like it is actually something being written to the database files.

At this time, I have taken one of the old database backups and I have renamed and restored it to a brand new database, and am in the process of getting the site to come back up again. Unfortunately, though, it seems that it may continue having this same problem, since I do not know what is causing it, and consequently be taken back down again. It took three years to go from 1/2Gb to 2.6Gb, so I will have some time to figure it out.

When I tried to restore the database, as I mentioned above, I get the following error message when I try to open the site again: "1146 Table [DBName]db_cache' doesn't exist
in:
[db_cache table]

This is a version of the database from a few years ago before it started getting bloated.

Can you please tell me how I can fix this to get the site live again? Thanks for your help!

Since it's 1.3.8a, I'm gonna make a wild guess and say it's the email_archive table that's caused your database to bloat. If your config was set to archive emails, each and every email sent through your system was stored in the database, and with 1.3.8 it's very likely your "Tell a Friend" feature was abused and thousands of emails were being sent each day (and, of course, all stored in the DB). I've recently had a client whose DB was a little over 2 GB, and email_archive alone was 1.9 GB...

Regarding your backups, I'm afraid I don't know what to tell you. If I were you, I'd try using the most recent backup to get the site running and then continue from there. It's possible that the restore process times out before restoring the complete database so it doesn't get to the db_cache table. If that's the case, I'd try the redneck method (assuming you're on a shared host) and would try to download the DB to computer and then import it on localhost - that would allow you to avoid timeout errors... Of course, this is all just guessing, it's really hard to know EXACTLY what's wrong in your case...

BTW, if you restore some OLD(er) backup, I hope you know you'll lose all order and customer information from the date of backup 'til today...

I have a recent (enough) SQL file of the DB. The issue is, I cannot open it in Notepad++ to edit out the unnecessary entries, and it's too large to upload to the server.

Ultimately, I want to get that version of the database back up and running. But I cannot edit it, so I am stuck. My hosting provider suggested SQLLite. I have no knowledge about that program.

Right now we took a DB from a few years ago, before the bloat, and changed the configuration files to point to that new DB with the restored SQL. The response was as I described; there was a msg about missing a DB called db_cache.

It was missing when I checked.

I found another post http://www.zen-cart.com/showthread.php?73455-Database-error-site-crashed!!!-db_cache-is-marked-as-crashed-and-should-be-repair&highlight=create+db_cache+table

and I executed the following via MySQL:

-- Table structure for table zen_db_cache

DROP TABLE IF EXISTS zen_db_cache;
CREATE TABLE zen_db_cache (
cache_entry_name varchar(64) NOT NULL default '',
cache_data blob,
cache_entry_created int(15) default NULL,
PRIMARY KEY (cache_entry_name)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now, the table is there (with our correct prefix) but the site just gets a white screen at the admin and cart.

So until we get the newest DB back, how do we proceed from here with the old one?

Also, why is an **older **version of the DB missing the table if it's part of the default install and is later there, getting very bloated?

starrysky:

I have a recent (enough) SQL file of the DB. The issue is, I cannot open it in Notepad++ to edit out the unnecessary entries, and it's too large to upload to the server.
Although probably an overkill for you, but I'd suggest installing Eclipse or NetBeans and then play with your SQL file. I was never a fan of N++... :smile:

Now, the table is there (with our correct prefix) but the site just gets a white screen at the admin and cart.
Do you have the Debug file in place? If not, get it here and then check the error logs (it will be stored in your store_root/cache directory) - it will tell you the exact reason why you're getting a blank page and what you're missing.

Also, why is an **older **version of the DB missing the table if it's part of the default install and is later there, getting very bloated?
Who knows... :wink:

balihr:

Although probably an overkill for you, but I'd suggest installing Eclipse or NetBeans and then play with your SQL file. I was never a fan of N++... :smile:

Do you have the Debug file in place? If not, get it here and then check the error logs (it will be stored in your store_root/cache directory) - it will tell you the exact reason why you're getting a blank page and what you're missing.

Who knows... :wink:

I checked, and can't even open the cache folder. This happened once before when I had logging on. It created a file so big years ago, my hosting provider couldn't remove it from their server.

I'll check out Netbeans and Eclipse.

Thank you.

starrysky:

I checked, and can't even open the cache folder. This happened once before when I had logging on. It created a file so big years ago, my hosting provider couldn't remove it from their server.

And again, one more redneck solution: connect to the server and simply rename your current cache directory to something like #cache. Then, just create a new cache directory and add the .htaccess and index.php files from the original package and you're good to go - new logs will be the only thing there and will be accessible.

But, keep in mind that all this advice is literally just to get the site back up for the next few days - you REALLY need to upgrade to the latest version as Dr.Byte told you...

balihr:

And again, one more redneck solution: connect to the server and simply rename your current cache directory to something like #cache. Then, just create a new cache directory and add the .htaccess and index.php files from the original package and you're good to go - new logs will be the only thing there and will be accessible.

But, keep in mind that all this advice is literally just to get the site back up for the next few days - you REALLY need to upgrade to the latest version as Dr.Byte told you...

Thanks...I did just that and renamed the directory! : ) After doing so, I recall that's what we had to do last time. MY hosting provider couldn't even delete the dir this time. lol

I made a new cache with .htaccess and index.php. This error msg now appears along with the white screen:

[09-Jun-2014 18:26:02] PHP Fatal error: Call to a member function MoveNext() on a non-object in /home/content/m/y/s/mysite/html/Cart/includes/init_includes/init_db_config_read.php on line 25

This is our last site that is on 1.3.8A. The rest are 1.5X and a few 1.39H.

One day they'll all be upgraded.

I'm afraid I have no idea - I've never encountered this error. But, the line IS indicating some cache issue...

Maybe, just maybe, try temporary changing includes/init_includes/init_db_config_read.php on line 18 from

$configuration = $db->Execute('select configuration_key as cfgkey, configuration_value as cfgvalue
                                 from ' . TABLE_CONFIGURATION, '', $use_cache, 150);

to

$configuration = $db->Execute('select configuration_key as cfgkey, configuration_value as cfgvalue
                                 from ' . TABLE_CONFIGURATION, '', '', 150);

If that doesn't help, I hope Dr.Byte or Ajeh or someone else will shed some light here...

balihr:

I'm afraid I have no idea - I've never encountered this error. But, the line IS indicating some cache issue...

Maybe, just maybe, try temporary changing includes/init_includes/init_db_config_read.php on line 18 from

$configuration = $db->Execute('select configuration_key as cfgkey, configuration_value as cfgvalue
from ' . TABLE_CONFIGURATION, '', $use_cache, 150);

> to
> ```
$configuration = $db->Execute('select configuration_key as cfgkey, configuration_value as cfgvalue
                                 from ' . TABLE_CONFIGURATION, '', '', 150);

No, don't do that.

The error importing db_cache sounds like either a problem with a timeout on importing the data (in which case none of the data after that db_cache table exists either), or the prefix of all the tables' names is wrong.

And if your hosting company's server has problems like being unable to delete files even with root permissions, and can't import a backup successfully, then maybe the host is the real problem.

Instead of deleting the database it would have been more practical to find out which tables were excessively large and do appropriate pruning.

Can you convince your hosting company to do the database restore FOR you? From the MOST RECENT available backup, NOT AN ANCIENT ONE! Even if just for a couple days so you can inspect what's amuck?

Who IS your host anyway? Those limits they're imposing sound rather low.

Another thing to consider is restoring the most recent backup to MySQL running on your own PC and then inspecting possible issues with it (starting with seeing how much storage is being required for each table, thus having a clue to resolving the problem).