70 replies to this topic

[pederb]

I'm getting following SQL error message, the first part took but not the last part?

#1060 - Duplicate column name 'customers_old_last_update'

[geoffreywalton]

Looks like that field already exists, remove the offenting line from the sql and try again

HTH

G

[14steve14]

Jack

I have just tried adding the sql file in the 2.2 update section and get the following error in phpmyadmin

MySQL said:
#1048 - Column 'set_function' cannot be null

Have you come across this before

[geoffreywalton]

Edit the sql and remove DEFAULT NULL from the set_function line

HTH

G

[14steve14]

Cheers work like a dream.

[geoffreywalton]

In that case, I am off to sleep.

Just spent some time converting an rc2a shop db to 2.3.1 format, loads of error like that.

[img]http://forums.oscommerce.com//public/style_emoticons/default/whistling.gif[/img]

G

Edited by geoffreywalton, 05 January 2012, 12:15.

[pederb]

After updating I'm now getting following cronjobs error


Fatal error: Class 'aUri_Modules' not found in /home/wihirt/public_html/catalog/includes/modules/ultimate_seo_urls5/uri_modules/path_standard.php on line 25

Does this contribution not like ulitimate seo urls5 ?

Cheers

[Jack_mcs]

pederb, on 18 January 2012, 20:19, said:

After updating I'm now getting following cronjobs error


Fatal error: Class 'aUri_Modules' not found in /home/wihirt/public_html/catalog/includes/modules/ultimate_seo_urls5/uri_modules/path_standard.php on line 25

Does this contribution not like ulitimate seo urls5 ?

Cheers

99% of the changes in this contribution are in the admin, so SEO 5 won't be involved there, unless an installation mistake was made. On the shop side, the only change is to the product_into page so if it is failing there, you should remove the changes you made to that file, verify SEO 5 works and then make the change again, one at a time and checking it after each change.

[pederb]

Thxs Jack,

What is the best way checking if SEO Pro works?

Checking the url or?

[Jack_mcs]

pederb, on 19 January 2012, 23:40, said:

Thxs Jack,

What is the best way checking if SEO Pro works?

Checking the url or?

Turn it off in admin. If the problem still exists, it is due to something else.

[mr_absinthe]

Hi Jack, it appears that the customer basket is not being cleaned. Despite the fact that I have 30 days set as value in settings, I can still see records there from 2005. Same for the customers_basket_attributes, nothing is being deleted.

Edited by mr_absinthe, 01 February 2012, 16:16.

[Jack_mcs]

mr_absinthe, on 01 February 2012, 16:13, said:

Hi Jack, it appears that the customer basket is not being cleaned. Despite the fact that I have 30 days set as value in settings, I can still see records there from 2005. Same for the customers_basket_attributes, nothing is being deleted.

It's due to a coding error, which has been fixed in the next version. To apply the fix, find the following code in the admin/includes/modules/database_optimizer_common.php

			mysql_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added > " . $dateCustomers . ")");
			mysql_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added > '" . $dateCustomers . "'");	 //clear the customers basket table of entries greater than one month old

and replace it with

			mysql_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "')");
			mysql_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "'");	 //clear the customers basket table of entries greater than one month old

Edited by Jack_mcs, 01 February 2012, 17:29.

[mr_absinthe]

OK, it works now, however since I have 30 days set as value in settings, I was expecting to see one month old data in table customers_basket. After running the optimizer, there are only records from yesterday (oldest date_added is 20120201). Is there something I've missed?

[Jack_mcs]

mr_absinthe, on 02 February 2012, 15:11, said:

OK, it works now, however since I have 30 days set as value in settings, I was expecting to see one month old data in table customers_basket. After running the optimizer, there are only records from yesterday (oldest date_added is 20120201). Is there something I've missed?

In the admin/includes/modules/database_optimizer_common.php file, find this line

		    mysql_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "')");

and place this line above it

echo "delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from " . TABLE_CUSTOMERS_BASKET . " where customers_basket_date_added < '" . $dateCustomers . "')";

Then go to tools->Database Optimizer, check the "Remove Customer Sessions" box and update. The mysql command should be displayed on the page. The date will be at the end of the line in the form of yyyymmdd. It should be todays date minus the offset (30 days). Is it?

[mr_absinthe]

It is and the same date is the oldest record in the table now - 20120104.

delete from customers_basket_attributes WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from customers_basket where customers_basket_date_added < '20120104')

[Jack_mcs]

mr_absinthe, on 03 February 2012, 10:07, said:

It is and the same date is the oldest record in the table now - 20120104.

delete from customers_basket_attributes WHERE (`products_id`, `customers_id`) in (select `products_id`, `customers_id` from customers_basket where customers_basket_date_added < '20120104')

So are you saying the date is correct but it removes the wrong items?

[mr_absinthe]

No, this time it removed the correct items. It even works with the echo commented out - it is removing correct items, no idea why it didn't work yesterday. The calculation output is not showing the removed items however:

Store database has been optimized.
Customers tables were trimmed.
Initial size was 49209454.
Final, optimized, size is 49209454.

With about five years data the initial figure should be higher I guess.

[Jack_mcs]

mr_absinthe, on 03 February 2012, 14:40, said:

No, this time it removed the correct items. It even works with the echo commented out - it is removing correct items, no idea why it didn't work yesterday. The calculation output is not showing the removed items however:

Store database has been optimized.
Customers tables were trimmed.
Initial size was 49209454.
Final, optimized, size is 49209454.

With about five years data the initial figure should be higher I guess.

The majority of the size is usually due to the products and orders, which aren't changed. From what you said previously, the customers basket had already been handled so there wouldn't be any changes for it to make now. If you had restored that though and it still isn't showing a difference, you would need to look at the table in phpmyadmin before and after to see if it is changing.

[mamegaga]

Hello all !



I have the same problem as Lioelx and martin67, has a resolution it you it, I am on a waiter devoted with osc2.1.3.


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/shop/back_ans/includes/modules/database_optimizer.php on line 44

Warning: mktime() expects parameter 6 to be long, string given in /home/xxxx/public_html/shop/admin/includes/modules/database_optimizer.php on line 46

thk's

[Jack_mcs]

mamegaga, on 05 February 2012, 14:15, said:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/shop/back_ans/includes/modules/database_optimizer.php on line 44

It looks like the failure is due to not being able to read the database. Be sure you have made the required database changes for this contribution.