tmpinsnty:
This is a fresh install of ZenCart 1.5.5f. I just built this web server and it has the latest stable releases to my knowledge. I'm on Debian 9. This is my Server info from my admin.
I have Categories center box for index page, COWOA, One page, Dynamic Price Update, Debug backstrace, and my payment/shipping modules installed.
I keep getting this error when I try to run the install script:
```
[31-Jan-2018 14:42:28 UTC] Request URI: /MY_ADMIN/stock_by_attr_install.php?selectSBAinstall=installAll&getSBAinstallPage=Run+Script, IP address: 173.8.96.194
#1 trigger_error() called at [/var/www/clients/client0/web1/web/includes/classes/db/mysql/query_factory.php:171]
#2 queryFactory->show_error() called at [/var/www/clients/client0/web1/web/includes/classes/db/mysql/query_factory.php:143]
#3 queryFactory->set_error() called at [/var/www/clients/client0/web1/web/includes/classes/db/mysql/query_factory.php:270]
#4 queryFactory->Execute() called at [/var/www/clients/client0/web1/web/MY_ADMIN/stock_by_attr_install.php:963]
#5 addSBAtable() called at [/var/www/clients/client0/web1/web/MY_ADMIN/stock_by_attr_install.php:2784]
[31-Jan-2018 14:42:28 UTC] PHP Fatal error: 1071:Specified key was too long; max key length is 767 bytes :: CREATE TABLE IF NOT EXISTS products_with_attributes_stock (
stock_id int(11) NOT NULL AUTO_INCREMENT,
products_id int(11) NOT NULL,
product_attribute_combo varchar(255) DEFAULT NULL,
stock_attributes varchar(255) NOT NULL,
quantity float NOT NULL DEFAULT '0',
sort int(11) NOT NULL DEFAULT '0',
customid varchar(255) DEFAULT NULL,
title varchar(100) DEFAULT NULL,
PRIMARY KEY (stock_id),
UNIQUE KEY idx_products_id_stock_attributes (products_id,stock_attributes),
UNIQUE KEY idx_products_id_attributes_id (product_attribute_combo),
UNIQUE KEY idx_customid (customid)
); ==> (as called by) /var/www/clients/client0/web1/web/MY_ADMIN/stock_by_attr_install.php on line 963 <== in /var/www/clients/client0/web1/web/includes/classes/db/mysql/query_factory.php on line 171
>
> Is it a MySQL/MariaDB issue? if so how can I remedy it?
More than likely the key that is causing the issue on the current system configuration is:
UNIQUE KEY idx_products_id_stock_attributes (products_id,stock_attributes),
as a result of stock_attributes being 255 characters (~765 Bytes) combined with products_id being an integer (4 Bytes) puts the key over the 767 Bytes limit by 2 bytes (when using utf8, if using utf8mb4, then well the result is 1020 Bytes instead of the 767).
So to correct this condition, there are a couple of things that could be done, possibly the most "flexible" is to modify the table definition such that the varchar related fields have a small enough size that the database could eventually be transitioned to utf8mb4 (if not already) and support continuing to have the unique keys identified above. (As a result of this notification may need to rethink that assignment of a unique key anyways. The code tends to prevent two or more entries from clashing at least for that particular entry, so it may not even be necessary, but would only suggest that after additional review).
There are some settings that can be applied if the mySql version were 5.6 or above and MariaDb 10.0 and above; however, in trying to keep things applicable to more systems, a more appropriate solution (to address the key issue only) would be one that supports continuing having the key and potential future use of utf8mb4 to do this, I would suggest changing:
CREATE TABLE IF NOT EXISTS products_with_attributes_stock (
stock_id int(11) NOT NULL AUTO_INCREMENT,
products_id int(11) NOT NULL,
product_attribute_combo varchar(255) DEFAULT NULL,
stock_attributes varchar([B]255[/B]) NOT NULL,
quantity float NOT NULL DEFAULT '0',
sort int(11) NOT NULL DEFAULT '0',
customid varchar(255) DEFAULT NULL,
title varchar(100) DEFAULT NULL,
PRIMARY KEY (stock_id),
UNIQUE KEY idx_products_id_stock_attributes (products_id,stock_attributes),
UNIQUE KEY idx_products_id_attributes_id (product_attribute_combo),
UNIQUE KEY idx_customid (customid)
);
to:
CREATE TABLE IF NOT EXISTS products_with_attributes_stock (
stock_id int(11) NOT NULL AUTO_INCREMENT,
products_id int(11) NOT NULL,
product_attribute_combo varchar(255) DEFAULT NULL,
stock_attributes varchar(190) NOT NULL,
quantity float NOT NULL DEFAULT '0',
sort int(11) NOT NULL DEFAULT '0',
customid varchar(255) DEFAULT NULL,
title varchar(100) DEFAULT NULL,
PRIMARY KEY (stock_id),
UNIQUE KEY idx_products_id_stock_attributes (products_id,stock_attributes),
UNIQUE KEY idx_products_id_attributes_id (product_attribute_combo),
UNIQUE KEY idx_customid (customid)
);
Now, that does have a potential impact on the combination(s) of attributes as the code is currently written. The number (integer) that is generated for each option name/option value combination is stored as text and when more than one such attribute is identified then the next pair is also stored with a comma between. Therefore if an attribute_id were to approach the "upper" limit of 2147483647 or 4294967295 (if the number is stored unsigned) then that one attribute alone takes 10 characters adding an additional attribute would take an additional 11 characters for each additional attribute (comma plus up to 10 characters), therefore the maximum limit in the database scheme applied above would be to have a maximum of 17 attributes for a single variant. That also said, that's considered way more than necessary and/or ever suggested for any product.
To further the database creation with possibility of utf8mb4 being used would be to reduce the other varchar(255) identifiers down to 191 instead, possibly... I've done some reading and can't recall if the 191 limit is specific to keys or to individual fields as well, but either way it seems like that's more than enough room for anything needed. :)
Please advise if the above minor change is successful so that it can be incorporated into the distribution.
The other "trial-and-error" approach would be to remove the unique key declarations, attempt to install, if successful, remove the install, then add one of the unique key designations in, install and repeat as necessary until it fails. Then if it fails on the last addition again remove the other unique key designators except for the last added and try again, should fail at that point again and would require applying changes like described above.
On another "side" note, if not mistaken debug backtrace (or a slightly modified version) is already incorporated into ZC 1.5.5 so it does not need to be specifically installed.
