Post by moshair » Wed Apr 01, 2020 12:45 am

Hello,
I have a website on ipage uses Open Cart 3, sometimes I see only a blank page with this text:

Code: Select all

Warning: mysqli::set_charset(): Error executing query in /***/shop/system/library/db/mysqli.php on line 13
The shop returns to work again after few minutes without doing anything, I contacted the support they made check and repair for tables but the problem still exists. I have also Drupal script installed but it is working file while Open-cart is not working.

Any idea how to solve this problem.

Regards,
Last edited by moshair on Wed Apr 01, 2020 7:48 pm, edited 2 times in total.

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by paulfeakins » Wed Apr 01, 2020 7:13 pm

moshair wrote:
Wed Apr 01, 2020 12:45 am
I have a site on ipage
What's that then?

For quick, professional OpenCart support please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - Reigate, Surrey, United Kingdom

Post by straightlight » Wed Apr 01, 2020 9:51 pm

Which mySQLi version are you using? If v8 or above, you need to remove the charset validation's parameter manually from the system/library/db/mysqli.php file.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Fri Apr 03, 2020 4:53 am

straightlight wrote:
Wed Apr 01, 2020 9:51 pm
Which mySQLi version are you using? If v8 or above, you need to remove the charset validation's parameter manually from the system/library/db/mysqli.php file.
Thank you for your reply, I found these information in the hosting panel:

Platform Type Debian
MySQL Version 5.6.32
Perl Version 5.8.8
PHP Version 7.3

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by straightlight » Fri Apr 03, 2020 5:04 am

There was a recent post about it but can't seem to find it now.

In system/library/db/mysqli.php file,

find:

Code: Select all

$this->connection->set_charset("utf8");
replace with:

Code: Select all

$this->connection->set_charset("");
See if that solves the issue.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Fri Apr 03, 2020 5:14 am

straightlight wrote:
Fri Apr 03, 2020 5:04 am
There was a recent post about it but can't seem to find it now.

In system/library/db/mysqli.php file,

find:

Code: Select all

$this->connection->set_charset("utf8");
replace with:

Code: Select all

$this->connection->set_charset("");
See if that solves the issue.
Thank you, I made that replace, I have two languages installed in the cart (English, and Arabic), the Arabic characters became like this ???? ????
The problem doesn't happens always, just sometimes.

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by straightlight » Fri Apr 03, 2020 5:19 am

Replace the code to its original code. In php.ini file of your root opencart folder (and perhaps in the admin/php.ini file if it does exist), ensure that no semi-colon are showing at the end of each lines.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Fri Apr 03, 2020 5:45 am

straightlight wrote:
Fri Apr 03, 2020 5:19 am
Replace the code to its original code. In php.ini file of your root opencart folder (and perhaps in the admin/php.ini file if it does exist), ensure that no semi-colon are showing at the end of each lines.
I replaced the code to its original, I didn't find any php.ini files.

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by straightlight » Fri Apr 03, 2020 5:48 am

moshair wrote:
Fri Apr 03, 2020 5:45 am
straightlight wrote:
Fri Apr 03, 2020 5:19 am
Replace the code to its original code. In php.ini file of your root opencart folder (and perhaps in the admin/php.ini file if it does exist), ensure that no semi-colon are showing at the end of each lines.
I replaced the code to its original, I didn't find any php.ini files.
Since you cannot find php.ini file at the root location of your store, see for: .user.ini file.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Fri Apr 03, 2020 6:03 am

straightlight wrote:
Fri Apr 03, 2020 5:48 am
moshair wrote:
Fri Apr 03, 2020 5:45 am
straightlight wrote:
Fri Apr 03, 2020 5:19 am
Replace the code to its original code. In php.ini file of your root opencart folder (and perhaps in the admin/php.ini file if it does exist), ensure that no semi-colon are showing at the end of each lines.
I replaced the code to its original, I didn't find any php.ini files.
Since you cannot find php.ini file at the root location of your store, see for: .user.ini file.
There is no user.ini

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by straightlight » Fri Apr 03, 2020 6:18 am

Not user.ini but .user.ini (dot at the beginning). If you cannot see php.ini or .user.ini file, then this is an issue already. Opencart already delivers this file out-of-the-box from the original installation (php.ini). Simply re-upload this file from the original ZIP file downloaded from opencart.com and ensure all semi-colon are removed at the end of each lines. If no effect after saving the file and refreshing your store, rename the php.ini file into .user.ini file (dot at the beginning) and see from there if that solves the issue.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Fri Apr 03, 2020 7:21 am

straightlight wrote:
Fri Apr 03, 2020 6:18 am
Not user.ini but .user.ini (dot at the beginning). If you cannot see php.ini or .user.ini file, then this is an issue already. Opencart already delivers this file out-of-the-box from the original installation (php.ini). Simply re-upload this file from the original ZIP file downloaded from opencart.com and ensure all semi-colon are removed at the end of each lines. If no effect after saving the file and refreshing your store, rename the php.ini file into .user.ini file (dot at the beginning) and see from there if that solves the issue.
Sorry I remembered that I deleted the php.ini from OC folder because it was causing a problem (Internal Server Error). I uploaded it again the same thing happened even if it is empty. I changed its name to .user.ini as you said, the shop works fine but how can I know if the server apply that file commands.

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by straightlight » Fri Apr 03, 2020 8:52 am

moshair wrote:
Fri Apr 03, 2020 7:21 am
straightlight wrote:
Fri Apr 03, 2020 6:18 am
Not user.ini but .user.ini (dot at the beginning). If you cannot see php.ini or .user.ini file, then this is an issue already. Opencart already delivers this file out-of-the-box from the original installation (php.ini). Simply re-upload this file from the original ZIP file downloaded from opencart.com and ensure all semi-colon are removed at the end of each lines. If no effect after saving the file and refreshing your store, rename the php.ini file into .user.ini file (dot at the beginning) and see from there if that solves the issue.
Sorry I remembered that I deleted the php.ini from OC folder because it was causing a problem (Internal Server Error). I uploaded it again the same thing happened even if it is empty. I changed its name to .user.ini as you said, the shop works fine but how can I know if the server apply that file commands.
The best path would be to contact your host to find out which ini file will really respond to your changes.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Fri Apr 03, 2020 5:45 pm

straightlight wrote:
Fri Apr 03, 2020 8:52 am
moshair wrote:
Fri Apr 03, 2020 7:21 am
straightlight wrote:
Fri Apr 03, 2020 6:18 am
Not user.ini but .user.ini (dot at the beginning). If you cannot see php.ini or .user.ini file, then this is an issue already. Opencart already delivers this file out-of-the-box from the original installation (php.ini). Simply re-upload this file from the original ZIP file downloaded from opencart.com and ensure all semi-colon are removed at the end of each lines. If no effect after saving the file and refreshing your store, rename the php.ini file into .user.ini file (dot at the beginning) and see from there if that solves the issue.
Sorry I remembered that I deleted the php.ini from OC folder because it was causing a problem (Internal Server Error). I uploaded it again the same thing happened even if it is empty. I changed its name to .user.ini as you said, the shop works fine but how can I know if the server apply that file commands.
The best path would be to contact your host to find out which ini file will really respond to your changes.
Thank you, it seems .user.ini is working, the problem happened again, now I see this error:

Code: Select all

Warning: mysqli::set_charset(): Error executing query in /hermes/bosnaweb19a/b2220/ipg.***/shop/system/library/db/mysqli.php on line 13
Fatal error: Uncaught Exception: Error: User 'yanbootshop' has exceeded the 'max_questions' resource (current value: 75000)<br />Error No: 1226<br />SELECT value FROM cho_setting WHERE `code` = 'config' AND `key` = 'config_timezone' in /hermes/bosnaweb19a/b2220/ipg.***/shop/system/library/db/mysqli.php:40 Stack trace: #0 /hermes/bosnaweb19a/b2220/ipg.***/shop/system/storage/modification/system/library/db.php(33): DB\MySQLi->query('SELECT value FR...') #1 /hermes/bosnaweb19a/b2220/ipg.***/shop/system/framework.php(80): DB->__construct('mysqli', '***.ipag...', 'yanbootshop', 'kGz=8m7_PKc5bK_...', 'opencart19', '3306') #2 /hermes/bosnaweb19a/b2220/ipg.***/shop/system/startup.php(104): require_once('/hermes/bosnawe...') #3 /hermes/bosnaweb19a/b2220/ipg.***/shop/index.php(19): start('catalog') #4 {main} thrown in /hermes/bosnaweb19a/b2220/ipg.***/shop/system/library/db/mysqli.php on line 40

It seems the problem from the hosting server with the value (max_questions resource (current value: 75000). )
Is there something can I do in the OC settings like disabling google sitemap ?

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by straightlight » Fri Apr 03, 2020 7:05 pm

See this solution: https://stackoverflow.com/questions/330 ... n-shared-h . If that does not solve it, contact your host.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com Administrator / Quality Assurance Analyst / Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by moshair » Sun Apr 05, 2020 7:18 pm

straightlight wrote:
Fri Apr 03, 2020 7:05 pm
See this solution: https://stackoverflow.com/questions/330 ... n-shared-h . If that does not solve it, contact your host.
Thank you for you support.
Shared web hosting doesn't allow that. I tried to contact the host support via chat, they don't reply due to corona virus problem. I'm sure if they replied they will say check your script. I have online statistics add-on, before moments the error happened again while there were only 3 users online, 10 users visited the shop in that hour, 79 page view . I downloaded the site log, there is no suspicious visits. What do you think the cause of that error (.. exceeded the 'max_questions')? I have these installed add-ons.

seogenerator302.ocmod.zip 24/07/2019
timezone.ocmod.zip 28/08/2019
plus-minus-oc3.ocmod.zip 28/08/2019
cartalert.ocmod.zip 28/08/2019
opencart-3-x-export-import-multilingual-3-22-cloud.ocmod.zip 15/12/2019
bnit.it-fix-security-notification-storage.ocmod.zip 22/02/2020
enable-disable-product_3x.ocmod.zip 22/03/2020
quantityincrementdecrement.ocmod.zip 04/04/2020

Is there something related to Open Cart can I do, like disabling features that consumes a lot of quires.?

What I must ask about when choosing a VPS for a website that have a maximum of 200 users per day? the shop has about 1500 products.

Regards,

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by letxobnav » Sun Apr 05, 2020 8:23 pm

I would start by counting all the queries your site is performing per page or per hour.
Then look at which particular queries are executed the most (seo url will probably be in there).
If you want to stay with a limited hosting plan, reduce your query count by eliminating extensions or start caching.
You can for instance easily cache the seo url results, that will not make things run faster but it drastically reduces the query count.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member
Online

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by moshair » Mon Apr 06, 2020 7:34 pm

letxobnav wrote:
Sun Apr 05, 2020 8:23 pm
I would start by counting all the queries your site is performing per page or per hour.
Then look at which particular queries are executed the most (seo url will probably be in there).
If you want to stay with a limited hosting plan, reduce your query count by eliminating extensions or start caching.
You can for instance easily cache the seo url results, that will not make things run faster but it drastically reduces the query count.
Thank you, that was useful, I used DigitalElephantDebugger add-on. I got these results

a category page
queries: 360

home page with just featured products.
queries: 279

a product page
queries: 266

I disabled "Use SEO URLs" and "Category Product Count" now the results are:

a category page
queries: 104

home page with just featured products.
queries: 90

a product page
queries: 89

max page view for example yesterday was 114 (114* 360=41,040) this 114 page view was from normal persons not bots.

I can move to VPS if it is really necessary, some hosts play with numbers to force you move to a higher plan.

Do you know if there is seo url cache add-on for OC 3.0.3.2?

Regards,

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm

Post by letxobnav » Mon Apr 06, 2020 11:43 pm

There is no seo url cache add on I am aware of, I wrote it myself just to see if it would speed up the site, not so much.
It does however reduce the amount of queries alot.

You add 2 defines in root config.php

Code: Select all

// SEO
// cache seo keywords in constant by one query in startup
define('USE_SEO_CACHE', true);
// additionally, store the seo keywords cache in file (might actually slow things down, reading cache file vs query)
define('USE_SEO_CACHE_FILE', false);
in catalog/controller/startup/startup.php after:

Code: Select all

		// OpenBay Pro
		$this->registry->set('openbay', new Openbay($this->registry));

you add:

Code: Select all

		// SEO CACHE if seo urls enabled and seo cache enabled
		if ($this->config->get('config_seo_url') && USE_SEO_CACHE) {
			$pairs = false;
			// define cache storage by store and language
			$seo_pair_cache_file = 'seo.'.$this->config->get('config_store_id').'.'.$this->config->get('config_language_id');
			// read cache storage if enabled
			if (USE_SEO_CACHE_FILE) $pairs = $this->cache->get($seo_pair_cache_file);
			// if no storage or no storage enabled fetch all seo query/keyword pairs for store and language
			if (!$pairs) {
				$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "' order by keyword ASC ");
				$pairs = array();
				// build query/keyword pairs
				foreach ($query->rows as $row) $pairs[$row['query']] = $row['keyword'];
				// store the pairs in file if enabled
				if (USE_SEO_CACHE_FILE) $this->cache->set($seo_pair_cache_file, $pairs);
				// free memory
				unset ($query);
			}
			// define the pair constant
			define('SEO_PAIRS', $pairs);
			// free memory
			unset($pairs);
		}
		


if catalog/controller/startup/seo_url.php

You replace this part:

Code: Select all

		foreach ($data as $key => $value) {
			if (isset($data['route'])) {
				if (($data['route'] == 'product/product' && $key == 'product_id') || (($data['route'] == 'product/manufacturer/info' || $data['route'] == 'product/product') && $key == 'manufacturer_id') || ($data['route'] == 'information/information' && $key == 'information_id')) {
					$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE `query` = '" . $this->db->escape($key . '=' . (int)$value) . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

					if ($query->num_rows && $query->row['keyword']) {
						$url .= '/' . $query->row['keyword'];

						unset($data[$key]);
					}
				} elseif ($key == 'path') {
					$categories = explode('_', $value);

					foreach ($categories as $category) {
						$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE `query` = 'category_id=" . (int)$category . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

						if ($query->num_rows && $query->row['keyword']) {
							$url .= '/' . $query->row['keyword'];
						} else {
							$url = '';

							break;
						}
					}

					unset($data[$key]);
				}
			}
		}

with this:

Code: Select all

		foreach ($data as $key => $value) {
			if (isset($data['route'])) {
				if (($data['route'] == 'product/product' && $key == 'product_id') || (($data['route'] == 'product/manufacturer/info' || $data['route'] == 'product/product') && $key == 'manufacturer_id') || ($data['route'] == 'information/information' && $key == 'information_id')) {
					if (USE_SEO_CACHE && SEO_PAIRS) {
						if (isset(SEO_PAIRS[$key . '=' . $value])) {
							$url .= '/' . SEO_PAIRS[$key . '=' . $value];
							unset($data[$key]);
						}
					} else {
						$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE `query` = '" . $this->db->escape($key . '=' . (int)$value) . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
						if ($query->num_rows && $query->row['keyword']) {
							$url .= '/' . $query->row['keyword'];
							unset($data[$key]);
						}
					}
				} elseif ($key == 'path') {
					$categories = explode('_', $value);
					foreach ($categories as $category) {
						if (USE_SEO_CACHE && SEO_PAIRS) {
							if (isset(SEO_PAIRS['category_id=' . $category])) {
								$url .= '/' . SEO_PAIRS['category_id=' . $category];
								unset($data[$key]);
							} else {
								$url = '';
								break;
							}
						} else {
							$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "seo_url WHERE `query` = 'category_id=" . (int)$category . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");
							if ($query->num_rows && $query->row['keyword']) {
								$url .= '/' . $query->row['keyword'];
							} else {
								$url = '';
								break;
							}
						}
					}
					unset($data[$key]);
				}
			}
		}
instead of executing a query for every seo query/keyword it fetches the seo keyword from the SEO_PAIRS constant in memory if enabled which was created using a single query.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member
Online

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by moshair » Tue Apr 07, 2020 3:27 am

letxobnav wrote:
Mon Apr 06, 2020 11:43 pm
There is no seo url cache add on I am aware of, I wrote it myself just to see if it would speed up the site, not so much.
It does however reduce the amount of queries alot.
...
.....
instead of executing a query for every seo query/keyword it fetches the seo keyword from the SEO_PAIRS constant in memory if enabled which was created using a single query.
Thank you, I tried it on local-host I got a blank page with this text:

Code: Select all

Warning: Constants may only evaluate to scalar values in D:\xampp56\htdocs\shop\catalog\controller\startup\startup.php on line 219
Fatal error: Cannot use isset() on the result of an expression (you can use "null !== expression" instead) in D:\xampp56\htdocs\shop\catalog\controller\startup\seo_url.php on line 88
This is line 219 in startup.php:

Code: Select all

define('SEO_PAIRS', $pairs);
And this is line 88 in seo_url.php:

Code: Select all

if (isset(SEO_PAIRS[$key . '=' . $value])) {
Regards,

Newbie

Posts

Joined
Sun Jul 21, 2019 3:27 pm
Who is online

Users browsing this forum: paulfeakins and 29 guests