Cron.sh needs to be run periodically. The chain initiates and ends in the individual Magento module cron jobs being executed per their configs.
You can also manually trip a cron event by calling cron.php directly from your web browser.
Using the shell script cron.sh takes care of an execution issue (can’t remember what exactly) that would otherwise crop up if you call cron.php directly from your crontab.
Despite my best efforts, I wasn’t having success getting the logs cleared with Magento’s cron, so I went ahead and truncated all the “log” files in phpmyadmin. The database dropped from 3GBs to around 200MB - much improved. Unfortunately, when I try to export the smaller DB via ‘mysqldump’ at the command line, the resulting file produced is STILL the 3 GB size from before! Exporting from phpmyadmin is better in that in produces a 200MB file, but I run into problems trying to re-import it on my local development machine.
Is there something I need to do - similar to a rebuild, perhaps - after truncating multiple large tables to maintain the integrity of the mySQL database? I somehow messed something up.....
You could try gzipping the mysqldump file to if it’s too large, Our database was 250Mb+ in phpmyadmin, 180Mb from mysqldump and 19Mb after being gzipped.
Is it possible that my cron job is working for SOME products? Could the promotions go away at random for other products? When I hit “APPLY PROMOTIONS” it fixes my issues… but before that its only like half the products that have issues. All at random times…
Since I’ve added the cron things are working much better but some of my products’ promotions are still going away.
PS I’m essentially using the promotions for the user groups to give users levels of discounts.
After more digging, I realize the problem is many of the sales_flat_quote_* tables in the database, which are taking up more than 2 GB!
I looked at this link (thank you, BTW) which discusses changing the cron.php file, but it doesn’t really say what it will do. Will this edit remove the large sales_flat_quote tables? If not, is there a good solution to get these smaller? I can’t imagine what is in there and a quick search on the forums didn’t reveal much.....
Despite my best efforts, I wasn’t having success getting the logs cleared with Magento’s cron, so I went ahead and truncated all the “log” files in phpmyadmin. The database dropped from 3GBs to around 200MB - much improved. Unfortunately, when I try to export the smaller DB via ‘mysqldump’ at the command line, the resulting file produced is STILL the 3 GB size from before! Exporting from phpmyadmin is better in that in produces a 200MB file, but I run into problems trying to re-import it on my local development machine.
Is there something I need to do - similar to a rebuild, perhaps - after truncating multiple large tables to maintain the integrity of the mySQL database? I somehow messed something up.....
Welll…
Sounds like the first problem is to get all the log tables shrunk down in size. Truncating them and doing a MySQLDump shouldn’t still be producing huge files. The dump is a bazillion mysql commands that recreate the tables, not an actual binary backup of the file, so eliminated data should equal smaller mysqldump output. Not sure what’s happening here???
If you’re running Magento 1.4.x.x, it includes scripts to do log table maintenance. You can fire it off with the system crontab for routine maintenance.
php -f ./shell/log.php help gives you a listing of the various options
php -f ./shell/log.php status gives you a listing of the log tables, how many rows and the size of storage space contained in the tables and indexes.
php -f ./shell/log.php clean --days 15 will clear all but the last 15 days of log contents (minimum 1 day)
php -f ./shell/log.php clean clears everthing per your settings in system setups under log maintenance
Since most of this started with a couple truncate operations, in phpMyAdmin, if you click on the table in consideration and go to the operations tab, you have the following options:
CHECK TABLE checks a table or tables for errors. CHECK TABLE works for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. For MyISAM tables, the key statistics are updated as well.
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.
OPTIMIZE TABLE works only for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. It does not work for tables created using any other storage engine.
As a for instance, we can assume the report_event table is getting a little crufty. We can check it for problems, if it passes, then optimize it with the following commands:
Thank you for such a detailed and comprehensive reply! This was exactly what I needed. Along with these tips, and a few other things, I was able to shrink the DB to 107 MB (from 3.5GB) and get a clean export.
Here are a few tips I discovered that, as someone new to this, I hope will help others:
1) Using the mysql and mysqldump command line tools are MUCH more reliable than importing & exporting from phpmyadmin with large DBs. Taking a few minutes to get familiar with them will save you a lot of headache down the road.
2) The “tail” command is very useful in seeing if a DB export was successful. Some of my early problems stemmed from the fact that the phpmyadmin tool had an error 1/2 way through export, but did\’t report it, and I was trying to import a .sql dump that was corrupt. To ensure you have a complete, finished .sql dump file you can run from a command line:
tail -n 50 databasename.sql
...which will output the last 50 lines of the DB to your screen. If the dump completed successfully, you\’ll see something like:
Dump completed on 2011-07-08 14:46:41
This is much more convenient than trying to open up a 100MB+ DB file with a text editor, which can often be really cumbersome.
3) The sales_flat_quote_* series of tables also gets enormous over time, and as it is listed on the 2nd page of phpmyadmin for a standard Magento install, can be easier to overlook than the log_* series of file. There are numerous other posts that deal with this.
Thanks for the reminder on “tail” as incomplete database dumps were some of my earliest Magento Fail hall of fame entries.
Our web developer put us on a really inadequate shared test server that had issues with tiny default MySQL log files (transactional integrity system) and 128MB memory_limit for php. Despite being told over and again, they never quite got the concept of the sheer amount of data our company works with. I begged and plead to get the memory_limit increased (never happened) and after several bad dumps in phpMyAdmin, got them to make a tweak or two, which a month later, came back because of the low memory_limit.
As you said, there’s a really good reason to use the command line. It’s quicker, doesn’t waste resources making things pretty and therefore more reliable. In the end, the Magento backups were the only thing working, so I would pull them from the test server and restore them internally on our dev server where I had allocated the resources really needed for Magento to run in order to do the things that would fail because Magento would throw an “out of memory” error.
Since someone asked somewhere along the line, here’s an update to the Cron Job Monitor Script that A) tells you the UTC time, b) the server time and offset and c) your store time (single storefront). It’s being run in a folder under the Magento root folder.
<?php //Magento Cron Job Monitor. GNU/GPL //oliver.higgins@gmail.com //provided without warranty or support // //modifications by chiefair to retrieve Magento connection and database //info, display time, connection info and all job execution states //================================================================
/** Get current date, time, UTC and offset **/ $date = date("Y-m-d"); $time = date("H:i:s T"); $offset = date("P"); $utc = gmdate("H:i:s");
//================================================================ // End of report
mysql_close($conn); ?>
Somewhere along the line when I get some time, I’ll also do the PDO rewrite as suggested. As you can see, it can get a little confusing when you can have three different time zones to chase down as our store does. Execution times are logged in UTC,
Can I change the cron_expr for “catalogrule_apply_all” from: 0 1 * * * to: 0 0 * * * ??
Or is there a good reason why it’s set too 1 o clock
I want to make this change because it seems like my product rules turn inactive at 00:00 and are activated at 01:00.
(can some one confirm this for me?)
Thanks for all the valuable tips here – it really helped me getting closer to a solution. But, I have still one problem. First, I’m running Magento 1.4.1.1 on a Mac OS X Server (10.6).
Here are my issue:
1. When running this command directly in the Terminal I get the “confirmation” email sent (looks like it works as supposed to):
sudo php ./cron.php
2. If I have this entry in root’s crontab no email is sent:
php /Library/WebServer/Documents/snus2/cron.php
It looks like cron.php starts to run because the CPU load (%) rising up to 100% (higher and higher for each time cron trigger the cron.php page). And the PHP process is then in 90-95% area.
Anyone knows what the problem is and how to solve it?
PHP Notice: Undefined index: SCRIPT_NAME in /path/to/cron.php on line 36
PHP Notice: Undefined index: SCRIPT_FILENAME in /path/to/cron.php on line 37
X-Powered-By: PHP/5.2.5
Content-type: text/html
