Post by Joe1234 » Sun Oct 22, 2023 12:34 pm

I modified a mod. It's been working fine for months, but all of a sudden I started getting this 500 error stating
"Too many keys specified; max 64 keys allowed"
First time I'm getting this and I don't understand why or how to go about fixing it. I'm not adding 64 things (as far as I know). Even more frustrating, after a refresh everything works fine, and nothing at all seems to be amiss. This last time I got this it was 22 items I was updating. I know it's a little vague with the info I'm providing, but what should I look at specifically, or what information is needed for deeper help.
Last edited by Joe1234 on Tue Oct 24, 2023 6:53 pm, edited 1 time in total.

v3.0.3.8
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by SohBH » Sun Oct 22, 2023 2:53 pm

This is database error.

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by Joe1234 » Sun Oct 22, 2023 9:08 pm

Meaning it's something for the server host to dive into and fix....even though, seemingly, it is only happening with one particular function----so far?

v3.0.3.8
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by Cue4cheap » Sun Oct 22, 2023 10:57 pm

Joe1234 wrote:
Sun Oct 22, 2023 12:34 pm
I modified a mod. It's been working fine for months, but all of a sudden I started getting this 500 error stating
"Too many keys specified; max 64 keys allowed"
First time I'm getting this and I don't understand why or how to go about fixing it. I'm not adding 64 things (as far as I know). Even more frustrating, after a refresh everything works fine, and nothing at all seems to be amiss. This last time I got this it was 22 items I was updating. I know it's a little vague with the info I'm providing, but what should I look at specifically, or what information is needed for deeper help.
What mod, what modification, what function is it calling?
Mike

cue4cheap not cheap quality


Active Member

Posts

Joined
Fri Sep 20, 2013 4:45 am

Post by Joe1234 » Mon Oct 23, 2023 5:52 am

I'm using ADV Profit Module v4.9 and a product import module. The ADV Profit Module v4.9 only updates its tables when creating a product the "normal way". So this causes an issue with the admin product list after importing new items since it has columns in the list related to cost and such have not been created. Basically new products will not be listed (I don't remember if it also caused issues on the front end). So I created this function that will display a button with the amount of new products that need to be updated and once pressed will refresh and update all the tables needed. This is the function that is all of a sudden creating this issue when pressed.

Controller

Code: Select all

	  	 <!--update cost after product import-->
		<operation error="log">
			<search><![CDATA[$this->load->model('extension/module/adv_profit_module');]]></search>
			<add position="replace" offset="1"><![CDATA[
//<!-- MY CUSTOM ----------------------"MY CUSTOM Admin Product List"------------------- -->
			$results2 = $this->model_catalog_product->custom_getProductsCostCompare();
			$data['countDifference'] = $results2["productCount"] - $results2["costCount"];

			$this->load->model('extension/module/adv_profit_module');
			$data['categories'] = $this->model_extension_module_adv_profit_module->getProductsCategories(0);

			$this->load->controller('extension/module/adv_profit_module');
			$data['custom_update_adv'] = str_replace('&amp;', '&', $this->url->link('extension/module/adv_profit_module/custom_ProductImportRefresh', 'user_token=' . $this->session->data['user_token'] . $url, true));
//<!-- MY CUSTOM ----------------------"MY CUSTOM Admin Product List"------------------- -->
			]]></add>
		</operation>
	   <!--update cost after product import-->

Model

Code: Select all

  
	 <!--update cost after product import-->
		<operation>
			<search><![CDATA[public function uninstall() {]]></search>
			<add position="before"><![CDATA[
	public function custom_ProductImportRefresh() {
	//<!-- MY CUSTOM BEGIN ----------------------"MY CUSTOM Admin Product List"------------------- -->
	
		$query_product = $this->db->query("SELECT p.product_id, isbn FROM `" . DB_PREFIX . "product` p WHERE p.product_id NOT IN (SELECT p.product_id FROM " . DB_PREFIX . "product_cost pc, " . DB_PREFIX . "product p WHERE pc.product_id = p.product_id)");
		foreach ($query_product->rows as $result) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_cost SET product_id = '" . (int)$result['product_id'] . "', supplier_id = '0', cost = '" . (int)$result['isbn'] . "', cost_amount = '" . (int)$result['isbn'] . "', cost_percentage = '0.00', cost_additional = '0.0000', costing_method = '0'");
		}

		$query_option = $this->db->query("SELECT pov.product_option_value_id, pov.product_id FROM `" . DB_PREFIX . "product_option_value` pov WHERE pov.product_option_value_id NOT IN (SELECT pov.product_option_value_id FROM " . DB_PREFIX . "product_option_cost poc, " . DB_PREFIX . "product_option_value pov WHERE poc.product_option_value_id = pov.product_option_value_id)");
		foreach ($query_option->rows as $result) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "product_option_cost SET product_option_value_id = '" . (int)$result['product_option_value_id'] . "', product_id = '" . (int)$result['product_id'] . "', cost = '0.0000', cost_amount = '0.0000', cost_prefix = '', costing_method = '0', sku = ''");
		}

		$query_order_product = $this->db->query("SELECT op.order_product_id, op.order_id, op.product_id, op.price FROM `" . DB_PREFIX . "order_product` op, `" . DB_PREFIX . "order` o WHERE o.order_id = op.order_id AND o.order_status_id > 0 AND op.order_product_id NOT IN (SELECT op.order_product_id FROM " . DB_PREFIX . "order_product_cost opc, " . DB_PREFIX . "order_product op WHERE opc.order_product_id = op.order_product_id)");
		foreach ($query_order_product->rows as $result) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product_cost SET order_product_id = '" . (int)$result['order_product_id'] . "', order_id = '" . (int)$result['order_id'] . "', product_id = '" . (int)$result['product_id'] . "', supplier_id = '0', cost = '0.0000', base_price = '" . (float)$result['price'] . "'");
		}
	
		$this->db->query("ALTER TABLE `" . DB_PREFIX . "order_product_cost` ADD INDEX (order_id);");

		$query_order = $this->db->query("SELECT o.order_id FROM `" . DB_PREFIX . "order` o WHERE order_status_id > 0 AND o.order_id NOT IN (SELECT oc.order_id FROM " . DB_PREFIX . "order_cost oc)");
		foreach ($query_order->rows as $result) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "order_cost SET order_id = '" . (int)$result['order_id'] . "', payment_cost = '0.0000', shipping_cost = '0.0000', extra_cost = '0.0000'");
		}

		$query_return = $this->db->query("SELECT r.return_id, r.order_id FROM `" . DB_PREFIX . "return` r WHERE r.return_id NOT IN (SELECT r.return_id FROM " . DB_PREFIX . "return_cost rc, " . DB_PREFIX . "return r WHERE rc.return_id = r.return_id)");
		foreach ($query_return->rows as $result) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "return_cost SET return_id = '" . (int)$result['return_id'] . "', order_id = '" . (int)$result['order_id'] . "', cost = '0.0000'");
		}

		$this->response->redirect($this->url->link('catalog/product', 'user_token=' . $this->session->data['user_token'] . '&type=module', true));

	}
//<!-- MY CUSTOM END ----------------------"MY CUSTOM Admin Product List"------------------- -->

			]]></add>
		</operation>	  
	   <!--update cost after product import-->

v3.0.3.8
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by SohBH » Mon Oct 23, 2023 3:05 pm

Code: Select all

$this->db->query("ALTER TABLE `" . DB_PREFIX . "order_product_cost` ADD INDEX (order_id);");
This code is attempting to add an index to the order_product_cost table on the order_id column.
You have been repeatedly add index until reached the maximum limit of 64 indexes on this table.

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by Joe1234 » Mon Oct 23, 2023 9:46 pm

OK, so I assume to fix this I should 1/ go into that table and delete all the excess indexes, and 2/ delete that part of the code? If that isn't what I should have done, let me know so I can restore what I deleted. Thanks.

v3.0.3.8
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am

Post by SohBH » Tue Oct 24, 2023 3:34 pm

Yes that is all should have done.

Business Web Development | Content Creation | Analytics and Reporting | SEO


User avatar
Active Member

Posts

Joined
Mon Nov 02, 2020 12:01 am
Location - Malaysia

Post by Joe1234 » Tue Oct 24, 2023 6:52 pm

Thanks

v3.0.3.8
I'm here for a reason, if your response is contact a/the developer, just don't reply.


Active Member

Posts

Joined
Sat Jan 01, 2022 5:47 am
Who is online

Users browsing this forum: Semrush [Bot] and 27 guests