oscommerce & MySQL

It has been awhile since I looked at the oscommerce software package. It is a great platform for building out a web store online.

However when they ask if you are above "MySQL\V5" or below it starts to make me nervous. Apparently I am not alone with the concern that InnoDB should be the storage engine of choice.

• http://forum.opencart.com/viewtopic.php?f=24&t=24939&start=60

So I decided to dig a little more.....

I am assuming that you are running a more updated MySQL or at the very least you plan on doing that very soon. 



> SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL  GROUP BY TABLE_SCHEMA, ENGINE \G
*************************** 1. row ***************************
TABLE_SCHEMA: oscommerce
      ENGINE: MyISAM
count_tables: 62
        size: 795816
  index_size: 546816


SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce'  AND ENGINE IS NOT NULL   GROUP BY TABLE_SCHEMA, TABLE_NAME;

+--------------+---------------------------------------+--------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME                            | ENGINE | size   | index_size |
+--------------+---------------------------------------+--------+--------+------------+
| oscommerce   | address_book                          | MyISAM |   1024 |       1024 |
| oscommerce   | administrators                        | MyISAM |   9268 |       9216 |
| oscommerce   | administrators_access                 | MyISAM |   9236 |       9216 |
| oscommerce   | administrators_log                    | MyISAM |   4096 |       4096 |
| oscommerce   | administrator_shortcuts               | MyISAM |   4096 |       4096 |
| oscommerce   | banners                               | MyISAM |   4096 |       4096 |
| oscommerce   | banners_history                       | MyISAM |   1024 |       1024 |
| oscommerce   | categories                            | MyISAM |   3192 |       3072 |
| oscommerce   | categories_description                | MyISAM |  11348 |      11264 |
| oscommerce   | configuration                         | MyISAM |  32908 |       7168 |
| oscommerce   | configuration_group                   | MyISAM |   2948 |       2048 |
| oscommerce   | counter                               | MyISAM |   1034 |       1024 |
| oscommerce   | countries                             | MyISAM |  39816 |      30720 |
| oscommerce   | credit_cards                          | MyISAM |   2656 |       2048 |
| oscommerce   | currencies                            | MyISAM |   3192 |       3072 |
| oscommerce   | customers                             | MyISAM |   1024 |       1024 |
| oscommerce   | fk_relationships                      | MyISAM |   7652 |       2048 |
| oscommerce   | geo_zones                             | MyISAM |   2104 |       2048 |
| oscommerce   | languages                             | MyISAM |   5224 |       5120 |
| oscommerce   | languages_definitions                 | MyISAM |  90292 |      24576 |
| oscommerce   | manufacturers                         | MyISAM |   9292 |       9216 |
| oscommerce   | manufacturers_info                    | MyISAM |   4176 |       4096 |
| oscommerce   | modules                               | MyISAM |   2568 |       2048 |
| oscommerce   | newsletters                           | MyISAM |   1024 |       1024 |
| oscommerce   | newsletters_log                       | MyISAM |   4096 |       4096 |
| oscommerce   | orders                                | MyISAM |   1024 |       1024 |
| oscommerce   | orders_products                       | MyISAM |   1024 |       1024 |
| oscommerce   | orders_products_download              | MyISAM |   1024 |       1024 |
| oscommerce   | orders_products_variants              | MyISAM |   1024 |       1024 |
| oscommerce   | orders_status                         | MyISAM |  10332 |      10240 |
| oscommerce   | orders_status_history                 | MyISAM |   1024 |       1024 |
| oscommerce   | orders_total                          | MyISAM |   1024 |       1024 |
| oscommerce   | orders_transactions_history           | MyISAM |   1024 |       1024 |
| oscommerce   | orders_transactions_status            | MyISAM |  10324 |      10240 |
| oscommerce   | products                              | MyISAM |   8596 |       8192 |
| oscommerce   | products_description                  | MyISAM |  17924 |      15360 |
| oscommerce   | products_images                       | MyISAM |   3216 |       3072 |
| oscommerce   | products_images_groups                | MyISAM |   3280 |       3072 |
| oscommerce   | products_notifications                | MyISAM |   1024 |       1024 |
| oscommerce   | products_to_categories                | MyISAM |   4123 |       4096 |
| oscommerce   | products_variants                     | MyISAM |   4156 |       4096 |
| oscommerce   | products_variants_groups              | MyISAM |   3216 |       3072 |
| oscommerce   | products_variants_values              | MyISAM |   4348 |       4096 |
| oscommerce   | product_attributes                    | MyISAM |   4136 |       4096 |
| oscommerce   | product_types                         | MyISAM |   9236 |       9216 |
| oscommerce   | product_types_assignments             | MyISAM |  10328 |      10240 |
| oscommerce   | reviews                               | MyISAM |   1024 |       1024 |
| oscommerce   | sessions                              | MyISAM |   6816 |       2048 |
| oscommerce   | shipping_availability                 | MyISAM |   3124 |       3072 |
| oscommerce   | shopping_carts                        | MyISAM |   1024 |       1024 |
| oscommerce   | shopping_carts_custom_variants_values | MyISAM |   1024 |       1024 |
| oscommerce   | specials                              | MyISAM |   1024 |       1024 |
| oscommerce   | tax_class                             | MyISAM |   2152 |       2048 |
| oscommerce   | tax_rates                             | MyISAM |   4144 |       4096 |
| oscommerce   | templates                             | MyISAM |   2160 |       2048 |
| oscommerce   | templates_boxes                       | MyISAM |   3732 |       2048 |
| oscommerce   | templates_boxes_to_pages              | MyISAM |  11968 |      11264 |
| oscommerce   | weight_classes                        | MyISAM |   3172 |       3072 |
| oscommerce   | weight_classes_rules                  | MyISAM |   4288 |       4096 |
| oscommerce   | whos_online                           | MyISAM |  10332 |      10240 |
| oscommerce   | zones                                 | MyISAM | 375892 |     247808 |
| oscommerce   | zones_to_geo_zones                    | MyISAM |   5147 |       5120 |
+--------------+---------------------------------------+--------+--------+------------+



If you are running a store, with customer data then stability should be an important factor in your database of choice. I would like to update these to InnoDB easily. 


>SELECT

CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE=InnoDB;') as query
INTO OUTFILE '/tmp/update_oscommerce.sql'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')  AND ENGINE IS NOT NULL AND TABLE_SCHEMA = 'oscommerce'
GROUP BY TABLE_SCHEMA, TABLE_NAME;



This query creates a simple ALTER TABLE for all of the oscommerce tables. If you have set your tables with a prefix into a database with other tables you can adjust query accordingly. 


mysql -p < /tmp/update_oscommerce.sql

So did it work? Yes and you will have to be aware that you will see a different in the size and index size. 


> SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL  GROUP BY TABLE_SCHEMA, ENGINE \G
*************************** 1. row ***************************
TABLE_SCHEMA: oscommerce
      ENGINE: InnoDB
count_tables: 62
        size: 3407872
  index_size: 2031616


 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce'  AND ENGINE IS NOT NULL  GROUP BY TABLE_SCHEMA, TABLE_NAME;

+--------------+---------------------------------------+--------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME                            | ENGINE | size   | index_size
+--------------+---------------------------------------+--------+--------+------------+
| oscommerce   | address_book                          | InnoDB |  65536 |      49152
| oscommerce   | administrators                        | InnoDB |  32768 |      16384
| oscommerce   | administrators_access                 | InnoDB |  32768 |      16384
| oscommerce   | administrators_log                    | InnoDB |  65536 |      49152
| oscommerce   | administrator_shortcuts               | InnoDB |  32768 |      16384
| oscommerce   | banners                               | InnoDB |  49152 |      32768
| oscommerce   | banners_history                       | InnoDB |  32768 |      16384
| oscommerce   | categories                            | InnoDB |  32768 |      16384
| oscommerce   | categories_description                | InnoDB |  65536 |      49152
| oscommerce   | configuration                         | InnoDB |  81920 |      16384
| oscommerce   | configuration_group                   | InnoDB |  16384 |          0
| oscommerce   | counter                               | InnoDB |  16384 |          0
| oscommerce   | countries                             | InnoDB |  65536 |      49152
| oscommerce   | credit_cards                          | InnoDB |  16384 |          0
| oscommerce   | currencies                            | InnoDB |  32768 |      16384
| oscommerce   | customers                             | InnoDB |  32768 |      16384
| oscommerce   | fk_relationships                      | InnoDB |  16384 |          0
| oscommerce   | geo_zones                             | InnoDB |  16384 |          0
| oscommerce   | languages                             | InnoDB |  65536 |      49152
| oscommerce   | languages_definitions                 | InnoDB | 147456 |      32768
| oscommerce   | manufacturers                         | InnoDB |  32768 |      16384
| oscommerce   | manufacturers_info                    | InnoDB |  49152 |      32768
| oscommerce   | modules                               | InnoDB |  16384 |          0
| oscommerce   | newsletters                           | InnoDB |  16384 |          0
| oscommerce   | newsletters_log                       | InnoDB |  49152 |      32768
| oscommerce   | orders                                | InnoDB |  49152 |      32768
| oscommerce   | orders_products                       | InnoDB |  49152 |      32768
| oscommerce   | orders_products_download              | InnoDB |  49152 |      32768
| oscommerce   | orders_products_variants              | InnoDB |  49152 |      32768
| oscommerce   | orders_status                         | InnoDB |  49152 |      32768
| oscommerce   | orders_status_history                 | InnoDB |  49152 |      32768
| oscommerce   | orders_total                          | InnoDB |  32768 |      16384
| oscommerce   | orders_transactions_history           | InnoDB |  32768 |      16384
| oscommerce   | orders_transactions_status            | InnoDB |  49152 |      32768
| oscommerce   | products                              | InnoDB | 114688 |      98304
| oscommerce   | products_description                  | InnoDB |  81920 |      65536
| oscommerce   | products_images                       | InnoDB |  32768 |      16384
| oscommerce   | products_images_groups                | InnoDB |  32768 |      16384
| oscommerce   | products_notifications                | InnoDB |  49152 |      32768
| oscommerce   | products_to_categories                | InnoDB |  49152 |      32768
| oscommerce   | products_variants                     | InnoDB |  49152 |      32768
| oscommerce   | products_variants_groups              | InnoDB |  32768 |      16384
| oscommerce   | products_variants_values              | InnoDB |  49152 |      32768
| oscommerce   | product_attributes                    | InnoDB |  65536 |      49152
| oscommerce   | product_types                         | InnoDB |  32768 |      16384
| oscommerce   | product_types_assignments             | InnoDB |  49152 |      32768
| oscommerce   | reviews                               | InnoDB |  65536 |      49152
| oscommerce   | sessions                              | InnoDB |  16384 |          0
| oscommerce   | shipping_availability                 | InnoDB |  32768 |      16384
| oscommerce   | shopping_carts                        | InnoDB |  65536 |      49152
| oscommerce   | shopping_carts_custom_variants_values | InnoDB |  81920 |      65536
| oscommerce   | specials                              | InnoDB |  32768 |      16384
| oscommerce   | tax_class                             | InnoDB |  16384 |          0
| oscommerce   | tax_rates                             | InnoDB |  49152 |      32768
| oscommerce   | templates                             | InnoDB |  16384 |          0
| oscommerce   | templates_boxes                       | InnoDB |  16384 |          0
| oscommerce   | templates_boxes_to_pages              | InnoDB |  65536 |      49152
| oscommerce   | weight_classes                        | InnoDB |  32768 |      16384
| oscommerce   | weight_classes_rules                  | InnoDB |  49152 |      32768
| oscommerce   | whos_online                           | InnoDB |  65536 |      49152
| oscommerce   | zones                                 | InnoDB | 606208 |     376832
| oscommerce   | zones_to_geo_zones                    | InnoDB |  65536 |      49152
+--------------+---------------------------------------+--------+--------+------------+


Since I happen to have innodb_file_per_table set I will get .ibd files per table of course as well.

> select @@innodb_file_per_table ;

+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+



A quick test of the administration site as well as testing the shopping cart shows everything working just fine so far.  An easy fix that will depend on table sizes as to how fast it is done for you.  This example is with a fresh install. 

If you have it replicated, then being able to turn off the slave and update the tables on the slave first would be a good start.  Then rotate the master unless you can afford downtime. 

If you do not have it replicated.. then you should look into it . You should also and I hope you do, have it backed up daily at the least. 

NoSQL :: PHP :: MEMCACHE :: INNODB :: MySQL

Now that MySQL 5.6 has been out for a little while and Percona 5.6 is, at the time of writing this, in alpha, we will all start to look more into the NoSQL solutions related to MySQL. ( I will continue to watch and see what MariaDB does in this regard. ) But anyway... since developers and management continue to be curious about what NoSQL can offer....

This is just to give a high level overview of using Memcache as a NoSQL solution and keep the  MySQL & InnoDB database and data in tact.

While this blog is not the first on this topic, I hope it ties a few things together for some people.

Good reads and references are here:
https://blogs.oracle.com/mysqlinnodb/entry/get_started_with_innodb_memcached
http://schlueters.de/blog/archives/152-Not-only-SQL-memcache-and-MySQL-5.6.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-setup.html
https://blogs.oracle.com/jsmyth/entry/nosql_with_mysql_s_memcached
http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
https://blogs.oracle.com/mysqlinnodb/entry/nosql_to_innodb_with_memcached
https://blogs.oracle.com/MySQL/entry/mysql_5_6_is_a
https://blogs.oracle.com/mysqlinnodb/entry/get_started_with_innodb_memcached
http://planet.mysql.com/entry/?id=32830
http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html#nosql
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-troubleshoot.html

# php --version

PHP 5.4.14 (cli) (built: Apr 27 2013 14:22:04)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies


Otherwise my php install also has this.... 


more /etc/php.d/memcache.ini

; ----- Options to use the memcache session handler
;  Use memcache as a session handler
session.save_handler=memcache
;  Defines a comma separated of server urls to use for session storage
session.save_path="tcp://localhost:11211"


First activate the plugin...

mysql> install plugin daemon_memcached soname "libmemcached.so";


Create a database so we do not just use the standard test db and demo_test used in all other examples. Just to show it is not locked to just that.


CREATE DATABASE nosql_mysql_innodb_memcache;
use nosql_mysql_innodb_memcache;

CREATE TABLE nosql_mysql (
   `demo_key` VARCHAR(32),
   `demo_value` VARCHAR(1024),
   `demo_flag` INT,
   `demo_cas` BIGINT UNSIGNED,
   `demo_expire` INT,
   primary key(demo_key)
)
ENGINE = INNODB;

use innodb_memcache;

INSERT INTO containers VALUES ('DEMO','nosql_mysql_innodb_memcache','nosql_mysql','demo_key','demo_value','demo_flag','demo_cas','demo_expire','PRIMARY');


select * from containers\G
*************************** 1. row ***************************
                  name: DEMO
             db_schema: nosql_mysql_innodb_memcache
              db_table: nosql_mysql
           key_columns: demo_key
         value_columns: demo_value
                 flags: demo_flag
            cas_column: demo_cas
    expire_time_column: demo_expire
unique_idx_name_on_key: PRIMARY


OK now lets just have some data in this table to prove that it runs like normal MySQL. 


mysql>use nosql_mysql_innodb_memcache;
mysql> insert into nosql_mysql VALUES ('key1','demo data','1',1,1);
Query OK, 1 row affected (0.04 sec)


select * from nosql_mysql  \G
*************************** 1. row ***************************
   demo_key: key1
 demo_value: demo data
  demo_flag: 1
   demo_cas: 1
demo_expire: 1
1 row in set (0.00 sec)


So let us see what we can do with PHP now... 

PHP CODE :

$memcache->connect('localhost', 11211) or die ("Could not connect");

$version = $memcache->getVersion();
echo "Server's version: ".$version."<br/>\n";

$memcache->set('key3', 'FROM PHP') or die ("Failed to save data at the server");
echo "Data from the cache:<br/>\n";
echo $memcache->get('key3');
?>


PHP OUTPUT :
Server's version: 5.6.10
Data from the cache:
FROM PHP


Just for grins we can do the typical example we see and use telnet as well.


telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
set key2 1 0 11
Hello World
STORED
get key2
VALUE key2 1 11
Hello World
END
get key3
VALUE key3 0 8
FROM PHP
END


OK let us see what we have in the MySQL database


set session TRANSACTION ISOLATION LEVEL read uncommitted;
SELECT @@GLOBAL.tx_isolation, @@tx_isolation \G
*************************** 1. row ***************************
@@GLOBAL.tx_isolation: REPEATABLE-READ
@@tx_isolation: READ-UNCOMMITTED


> select * from nosql_mysql\G
*************************** 1. row ***************************
   demo_key: key1
 demo_value: demo data
  demo_flag: 1
   demo_cas: 1
demo_expire: 1
*************************** 2. row ***************************
   demo_key: key2
 demo_value: Hello World
  demo_flag: 1
   demo_cas: 2
demo_expire: 0
*************************** 3. row ***************************
   demo_key: key3
 demo_value: FROM PHP
  demo_flag: 0
   demo_cas: 2
demo_expire: 0


So with PHP and using MySQL InnoDB memcache we can see that we are able to address code directly and easily. 

Of course others have their own opinions (http://blog.couchbase.com/why-mysql-56-no-real-threat-nosql) but I think this is just the start of how we will be able to use NoSQL with MySQL.