Post by discoverytdi » Wed Jan 18, 2017 12:53 am

Upgrading my site from opencart-1.5.6.4 to opencart- 2.3.0.2 and noticed that in the database all the tables are MyISAM engineexcept for the cart table which is InnoDB Engine. I checked the sql file in the original install package in case it was an upgrade error but found this
Table structure for table `oc_cart`
--

DROP TABLE IF EXISTS `oc_cart`;
CREATE TABLE `oc_cart` (
`cart_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`api_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`session_id` varchar(32) NOT NULL,
`product_id` int(11) NOT NULL,
`recurring_id` int(11) NOT NULL,
`option` text NOT NULL,
`quantity` int(5) NOT NULL,
`date_added` datetime NOT NULL,
PRIMARY KEY (`cart_id`),
KEY `cart_id` (`api_id`,`customer_id`,`session_id`,`product_id`,`recurring_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
is there a reason that oc is using 2 different Engines in the database.
Cheers
Peter

https://www.rvsolar.co.uk/


User avatar
New member

Posts

Joined
Fri Feb 17, 2012 6:43 am
Location - sunny lancashire UK

Post by IP_CAM » Wed Jan 18, 2017 2:48 am

Some Extension Creators use MyISAM, and some InnoDB, but as you wrote, it makes not much sense, to have both Engines doing their Job in one DB. And by use of this file Tool,
you can make your entire DB working under InnoDB, and in addition, you can FULL INDEX your DB as well, to get the best possible performance, It works wich all OC Versions.
---
Atomix INNO_DB Changer and DB Indexer Power Tool
The probably most efficient OC PHP File Tool Mod ever created!
http://www.ipc.li/os/opencart-turbo-master.zip

Good Luck ;)
Ernie
PX: And be aware, SOME (fulltext) MyISAM-driven Tables cannot be changed,
engine-wise, so, if you have one of them in your DB, just accept it!

Attachments

atomix.jpg

Atomix Turbo, the probably most efficient OC PHP File Tool Mod ever created! - atomix.jpg (297.2 KiB) Viewed 3876 times


Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'600+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by discoverytdi » Wed Jan 18, 2017 4:47 am

IP_CAM wrote:Some Extension Creators use MyISAM, and some InnoDB, but as you wrote, it makes not much sense, to have both Engines doing their Job in one DB. And by use of this file Tool,
you can make your entire DB working under InnoDB, and in addition, you can FULL INDEX your DB as well, to get the best possible performance, It works wich all OC Versions.
---
Atomix INNO_DB Changer and DB Indexer Power Tool
The probably most efficient OC PHP File Tool Mod ever created!
http://www.ipc.li/os/opencart-turbo-master.zip

Good Luck ;)
Ernie
PX: And be aware, SOME (fulltext) MyISAM-driven Tables cannot be changed,
engine-wise, so, if you have one of them in your DB, just accept it!
Read about Atomix a while back, but what I don't understand is why there should be that InnoDB engine in the stock oc download ???

https://www.rvsolar.co.uk/


User avatar
New member

Posts

Joined
Fri Feb 17, 2012 6:43 am
Location - sunny lancashire UK

Post by thekrotek » Wed Jan 18, 2017 5:23 am

discoverytdi wrote:Read about Atomix a while back, but what I don't understand is why there should be that InnoDB engine in the stock oc download ???
Probably, they were just doing some testing and left it as is. It doesn't hurt, doesn't make much sense either. So just ignore it :-)

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by qtwrk » Sun May 14, 2017 6:45 am

IP_CAM wrote:
Wed Jan 18, 2017 2:48 am
Some Extension Creators use MyISAM, and some InnoDB, but as you wrote, it makes not much sense, to have both Engines doing their Job in one DB. And by use of this file Tool,
you can make your entire DB working under InnoDB, and in addition, you can FULL INDEX your DB as well, to get the best possible performance, It works wich all OC Versions.
---
Atomix INNO_DB Changer and DB Indexer Power Tool
The probably most efficient OC PHP File Tool Mod ever created!
http://www.ipc.li/os/opencart-turbo-master.zip

Good Luck ;)
Ernie
PX: And be aware, SOME (fulltext) MyISAM-driven Tables cannot be changed,
engine-wise, so, if you have one of them in your DB, just accept it!
is it supposed to be normal I have few table shows ERROR while converting from myisam to innodb ?

New member

Posts

Joined
Tue Dec 09, 2014 5:21 am

Post by victorj » Sun May 14, 2017 3:05 pm

MySQL and mariadata base that the main database engine oc uses, has different Engines under the Hood each with their own advantages and disadvantages.

Myisam is used on tables with large bits of data like product descriptions.
It is able to handle large chunks of data in a effecient way, and is able to get bits off information from inside these fields to, handy for in$ search in product descriptions,used for search in websites, but it operates a bit slower.
Innodb is used on tables with short information in fields like the option tablet, its writen To handle this information fast and efficiënt, but can not get data from information inside a field, so it will only parse entire field to a search querie

In order to get the best resultaten when gathering data from a database each table should be looked at and proper engine should be applied to that table for optimal result.
So if you can apply innodb it Will speedup overall database performance resulting in a faster website.
Some tables however do have long text fields, innodb cant handle, so they need to be myisam.
During conversion these tables are not altered.
Having more Engines in a database is no problem, they Both do their job in the most efficiënt way to handle that specific data.

When on large shops database is converted and index tables are added, you will notice a significant increase in overall speed.

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by julianashcroft » Thu Oct 10, 2019 11:13 pm

Sorry to reopen an old post.

I have used turbo.php to change the database in my shop to InnoDB, seemed to go fine. When I click on the 'Add Indexes to the DB' button, all that happens is I get the 'Waiting for..." and after a few minutes it times out. Am I doing something wrong?

New member

Posts

Joined
Mon Feb 23, 2015 12:24 am

Post by victorj » Thu Oct 10, 2019 11:19 pm

on some hosting platforms adding indexes is not working for some reason.

you can try to use phpmyadmin
follow instructions given here
https://stackoverflow.com/questions/144 ... n-in-mysql

Koeltechnische deurrubbers eenvoudig online op maat bestellen.
Alle niet stekplichtige onderdelen zoals scharnieren, sloten, randverwarming en verlichting voor alle typen koelingen en vriezers.
https://koelcel-onderdelen.com


User avatar
Expert Member

Posts

Joined
Sat Jun 25, 2011 4:09 am
Location - Alkmaar Holland

Post by IP_CAM » Fri Oct 11, 2019 10:17 am

Make sure, not to have a low timeout set in the shop+admin /php.ini File.

Code: Select all

memory_limit = 256M;
max_execution_time = 36000;
upload_max_filesize = 999M;
safe_mode = Off;
mysql.connect_timeout = 20;
Check, by use of the Server MySql PHP Admin, to find out, which Table might not have been
set to INNODB by the Atomix Tool, and if you find one, run the Atomix Routine once again, or try
to change it to INNODB by use of MySql PHP Admin directly. Check here for info on such:
https://www.techandme.se/convert-myisam-to-innodb/
https://www.google.com/search?q=MYISAM+ ... 8&oe=utf-8
I recall to have found some 'unchangeable' Tables, related to some Mods I used once... ::)
but I do't recall details on that anymore.
Ernie

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'600+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by julianashcroft » Fri Oct 11, 2019 3:34 pm

Thanks for the reply, this is what I had in my ini.php file

Code: Select all

magic_quotes_gpc = Off;
register_globals = Off;
default_charset	= UTF-8;
memory_limit = 64M;
max_execution_time = 18000;
upload_max_filesize = 999M;
safe_mode = Off;
mysql.connect_timeout = 20;
session.use_cookies = On;
session.use_trans_sid = Off;
session.gc_maxlifetime = 12000000;
allow_url_fopen = on;
I changed those elements to match yours. It didn't make a difference and eventually timed out.

I did notice that when I ran the indexing script on turbo.php, a line of my .htaccess appears at the top of the screen, not sure if this is meant to or not. This is my .htaccess code which I think is pretty standard:

Code: Select all

# 1.To use URL Alias you need to be running apache with mod_rewrite enabled. 

# 2. In your opencart directory rename htaccess.txt to .htaccess.

# For any support issues please visit: http://www.opencart.com

Options +FollowSymlinks

# Prevent Directoy listing 
# Options -Indexes

# Prevent Direct Access to files

# SEO URL Settings
RewriteEngine On

# If your opencart installation does not run on the main web folder make sure you folder it does run in ie. / becomes /shop/ 

RewriteBase /shop/
RewriteRule ^sitemap.xml$ index.php?route=feed/google_sitemap [L]
RewriteRule ^googlebase.xml$ index.php?route=feed/google_base [L]
RewriteRule ^download/(.*) /index.php?route=error/not_found [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_URI} !.*\.(ico|gif|jpg|jpeg|png|js|css)
RewriteRule ^([^?]*) index.php?_route_=$1 [L,QSA]

### Additional Settings that may need to be enabled for some servers 
### Uncomment the commands by removing the # sign in front of it.
### If you get an "Internal Server Error 500" after enabling any of the following settings, restore the # as this means your host doesn't allow that.

# 1. If your cart only allows you to add one item at a time, it is possible register_globals is on. This may work to disable it:
# php_flag register_globals off

# 2. If your cart has magic quotes enabled, This may work to disable it:
# php_flag magic_quotes_gpc Off

# 3. Set max upload file size. Most hosts will limit this and not allow it to be overridden but you can try
# php_value upload_max_filesize 999M

# 4. set max post size. uncomment this line if you have a lot of product options or are getting errors where forms are not saving all fields
# php_value post_max_size 999M

# 5. set max time script can take. uncomment this line if you have a lot of product options or are getting errors where forms are not saving all fields
# php_value max_execution_time 200

New member

Posts

Joined
Mon Feb 23, 2015 12:24 am

Post by IP_CAM » Fri Oct 11, 2019 8:36 pm

when I ran the indexing script on turbo.php, a line of my .htaccess appears at the top of the screen
What line ? That should not be the case! :-\
Better remove the .htaccess file during that process, and try again.

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'600+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland
Who is online

Users browsing this forum: No registered users and 9 guests