70 replies to this topic

[Jack_mcs]

This addon provides an easy method for keeping your database fit. All osCommerce shops require a MySQL database to function but little attention is ever given to it. Just ask yourself when was the last time you optimized your database? Unfortunately, this is a big mistake since MySQL is prone to developing problems. Some are fixed as the database runs but some are not and, over time, these grow and can severely effect your shops performance. This addon will automatically tune-up the database, speeding it up and correcting problems that many shop owners are not even aware of. Here are some of its features:

• Defragment the tables
  
• Optimize the keys
  
• Strip the customers tables of old data
  
• Strip the sessions table of old data
  
• Strip the user tracking table (if present) of old data
  
• Remove old credit card numbers

Edited by Jack_mcs, 06 February 2011, 04:31.

[PowerHowzer]

I like this contribution, been looking wanting this for awhile now. Thank you, Jack. One question, what do you mean you need cron job? My host does not provide the cron job feature. Can I still use this contribution?

[PowerHowzer]

PowerHowzer, on 06 February 2011, 04:39, said:

I like this contribution, been looking wanting this for awhile now. Thank you, Jack. One question, what do you mean you need cron job? My host does not provide the cron job feature. Can I still use this contribution?

EDIT:

Tested. All working! You do not need to have cron jobs to make this mod work but it helps. For the future, it would be nice to know what is being optimized and how. Seeing the numbers, I don't really know if its working or not. Just seeing filesize going down.

[martina]

Jack_mcs, on 06 February 2011, 04:28, said:

This addon provides an easy method for keeping your database fit. All osCommerce shops require a MySQL database to function but little attention is ever given to it. Just ask yourself when was the last time you optimized your database? Unfortunately, this is a big mistake since MySQL is prone to developing problems. Some are fixed as the database runs but some are not and, over time, these grow and can severely effect your shops performance. This addon will automatically tune-up the database, speeding it up and correcting problems that many shop owners are not even aware of. Here are some of its features:

• Defragment the tables
  
• Optimize the keys
  
• Strip the customers tables of old data
  
• Strip the sessions table of old data
  
• Strip the user tracking table (if present) of old data
  
• Remove old credit card numbers

Thanks Jack,
I optimized my database and the result is great:
Store database has been optimized.
Database was optimized.
Initial size was 26809745.
Final, optimized, size is 5486202!!
Regards,
Martina

[Jack_mcs]

PowerHowzer, on 06 February 2011, 06:43, said:

EDIT:

Tested. All working! You do not need to have cron jobs to make this mod work but it helps. For the future, it would be nice to know what is being optimized and how. Seeing the numbers, I don't really know if its working or not. Just seeing filesize going down.

It really needs to be ran regularly, even if twice a year, and cron is the best way to do that. If your host doesn't offer cron, you can use my Cron Simulator contribution.

As for what it does, an explanation of each item is given in the admin. The numbers will vary with each database. For example, if you don't store credit card numbers in your shop and only run that option, the script will always say it ran but nothing will have been changed since there isn't anything to change, in that case.

[Jack_mcs]

Someone pointed out to me that there is a mistake in the install instructions for 2.3. It should read:

Quote

FIND:

array(
'code' => FILENAME_BACKUP,
'title' => BOX_TOOLS_BACKUP,
'link' => tep_href_link(FILENAME_BACKUP)
),

ADD BENEATH:

array(
'code' => FILENAME_DATABASE_OPTIMIZER,
'title' => BOX_TOOLS_DATABASE_OPTIMIZER,
'link' => tep_href_link(FILENAME_DATABASE_OPTIMIZER)
),

Edited by Jack_mcs, 06 February 2011, 12:24.

[johnnybebad]

Hi I havent installed this mod yet, but have no doubts it would be useful as it comes from Jack.

my query is, what does the optimisation do exactly.

If I ran the optimization, things are being changed but what exactly is being changed.

sorry for souning thick but I am trying to work out what things would change.

ie if rearranging the tables so they are sequential and use up the numbers in order ie no gaps, in config I have 500 rows but my next entry would be 6473, it renumbers it 1 to 500? next entry being 501.

this would then in the products table make the entries change if I have delted any old products(discontinued never to be seen again) so my urls for acessing them would change so i need to update my sitemaps, googlebase etc etc.

Am I thinking along the correct lines, or way off, just want to get thiongs straight in my head before using this one.

Cheers

[PostcodeSite]

Hello Jack,
Id like to ask what this feature does in more detail...
# Strip the customers tables of old data

What data is stripped from the customers tables?

[Jack_mcs]

johnnybebad, on 06 February 2011, 12:34, said:

Hi I havent installed this mod yet, but have no doubts it would be useful as it comes from Jack.

my query is, what does the optimisation do exactly.

If I ran the optimization, things are being changed but what exactly is being changed.

Nothing is being changed as far as your shop is concerned. The optimization doesn't refer to rewriting some table to it operated more efficiently, or anything like that. The optimize and analyze functions are functions built into mysql to perform certain housekeeping functions. They are never ran from with oscommerce so they never, in most case, get ran. The other items are removing old entries from the tables. Those are entries you will never need. For example, the sessions table contains tracking information for your customers. If you have had your shop open for five years, then it contains tracking information for five years worth of customers, unless it has been edited during that time. Do you really want to store tracking information for a customer from five years ago? Even if that customer was still active on your site, or even alive for that matter, your site has probably changed so it may not even be accurate. The sessions option will remove all of those old entries. Where it starts deleting them depends on the settings in admin. The same is true for the other options.

[Jack_mcs]

PostcodeSite, on 06 February 2011, 12:58, said:

Hello Jack,
Id like to ask what this feature does in more detail...
# Strip the customers tables of old data

What data is stripped from the customers tables?

Nothing from the customers table. The customers_basket and customers_basket_attributes get emptied of old data. They are similar to the sessions table in that they store what a customer has in their cart. There's not any reason to store that information for more than a month, if that.

[MHWD]

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\catalog\admin\includes\modules\database_optimizer_common.php on line 59

Not sure why this is being thrown - only happening on optimize and analyze obviously. Any ideas what would cause this?

[Jack_mcs]

MHWD, on 07 February 2011, 23:10, said:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\catalog\admin\includes\modules\database_optimizer_common.php on line 59

Not sure why this is being thrown - only happening on optimize and analyze obviously. Any ideas what would cause this?

That error would indicate it is failing due to the SHOW TABLE STATUS mysql call. I can't think of why that would fail unless you are using a very old version of mysql, or maybe some buggy version, though I can't find reference to such a problem.

[MHWD]

Jack_mcs, on 08 February 2011, 00:35, said:

That error would indicate it is failing due to the SHOW TABLE STATUS mysql call. I can't think of why that would fail unless you are using a very old version of mysql, or maybe some buggy version, though I can't find reference to such a problem.

I couldn't find a reference either, it's on xampp with PHP Version: 5.3.1 & MySQL 5.1.41.

In addition - if I run the SHOW TABLE STATUS in the sql query it works with no problems.


Thank you though

Edited by MHWD, 08 February 2011, 03:03.

[bgagew]

Jack_mcs, on 06 February 2011, 04:28, said:

This addon provides an easy method for keeping your database fit. All osCommerce shops require a MySQL database to function but little attention is ever given to it. Just ask yourself when was the last time you optimized your database? Unfortunately, this is a big mistake since MySQL is prone to developing problems. Some are fixed as the database runs but some are not and, over time, these grow and can severely effect your shops performance. This addon will automatically tune-up the database, speeding it up and correcting problems that many shop owners are not even aware of. Here are some of its features:

• Defragment the tables
  
• Optimize the keys
  
• Strip the customers tables of old data
  
• Strip the sessions table of old data
  
• Strip the user tracking table (if present) of old data
  
• Remove old credit card numbers

[Lioelx]

hi,
need help for this error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 43

Warning: mktime() expects parameter 6 to be long, string given in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 45

[Jack_mcs]

Lioelx, on 10 February 2011, 03:42, said:

hi,
need help for this error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 43

Warning: mktime() expects parameter 6 to be long, string given in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 45

The second error is most likely due to the first. For the first, try changing line 43 in admin/includes/modules/database_optimizer.php

$mainDate = mysql_fetch_array($query, MYSQL_ASSOC);

to

$mainDate = mysql_fetch_array($query, MYSQL_BOTH);

or

$mainDate = mysql_fetch_array($query);

Edited by Jack_mcs, 10 February 2011, 04:33.

[Lioelx]

Hi, I tried the changes you told me but still leaving the same error, check the box to analyze the database and displays the same mistakes.
Greetings

Quote

View PostLioelx, on 10 February 2011, 04:42, said:

The second error is most likely due to the first. For the first, try changing line 43 in admin/includes/modules/database_optimizer.php

$mainDate = mysql_fetch_array($query, MYSQL_ASSOC);

to

$mainDate = mysql_fetch_array($query, MYSQL_BOTH);

or

$mainDate = mysql_fetch_array($query);

[Jack_mcs]

Lioelx, on 10 February 2011, 15:33, said:

Hi, I tried the changes you told me but still leaving the same error, check the box to analyze the database and displays the same mistakes.
Greetings

I don't have an answer for you then. The code is common code. All I can think of is that there is something different in the version of php/mysql your host uses. You could try asking your host about it though most won't look at third-party code.

[martin67]

Jack_mcs, on 10 February 2011, 17:29, said:

I don't have an answer for you then. The code is common code. All I can think of is that there is something different in the version of php/mysql your host uses. You could try asking your host about it though most won't look at third-party code.

I had the same problems as Lioelx. In my case it was that I used a different name for the database_optimizer table than the default. This is really a (minor) bug in the code.

In admin/include/modules/database_optimizer.php, change line 42 from

$query = mysql_query("select last_update from database_optimizer");
to
$query = mysql_query("select last_update from " . TABLE_DATABASE_OPTIMIZER);

In the same way, change all references (5) to database_optimizer in admin/include/database_optimzer_common.php


Jack_mcs, maybe you can fix this in the next release?

Regards,
/Martin

[pederb]

Hi Jack,

Thank you another great contribution :-)

Does the version checker you created work in 2.3.1?

Cheers