Stock by Attribute v4.0 addon for v1.3.5-1.3.9

[catmint]

Thanks, I have set the enable dynamic dropdowns to 2, but frustratingly the stock quantities are still not showing (did a clean page refresh as well on the web site just in case).

Thanks, Gordon

[mc12345678]

Thanks, I have set the enable dynamic dropdowns to 2, but frustratingly the stock quantities are still not showing (did a clean page refresh as well on the web site just in case).

Thanks, Gordon

Besides all of the files located in the includes and the admin base directory, there are two additional files that must be in the proper place to further support SBA: includes/modules/YOUR_TEMPLATE/attributes.php (which adds capability to the file rather than limit it) and includes/templates/YOUR_TEMPLATE/templates/tpl_modules_attributes.php both of which are found under the base fileset having the includes directory.

[catmint]

Brilliant, it was the attributes.php that was in the wrong place. Stock levels are now present, although the £ sign has changed to the coding word rather than the symbol, but I am not bothered about that as the priority is the stock attributes.

Many many thanks for your prompt help, it is greatly appreciated, Gordon

[mc12345678]

Brilliant, it was the attributes.php that was in the wrong place. Stock levels are now present, although the £ sign has changed to the coding word rather than the symbol, but I am not bothered about that as the priority is the stock attributes.

Many many thanks for your prompt help, it is greatly appreciated, Gordon

Welcome. Besides having instruction to identify what files/folders need to go where, it seems that the installer possibly could attempt to verify the presence of the needed file(s), though in some cases one may choose to modify the base version instead of the override and the plugin could/should recognize this possibility with regards to file existence. The "next" step to that is some sort of validation that whatever file(s) were not stored in the expected location that they still at least offer the expected operation.

As to the currency display issue. I'm not yet sure if that is a general html in a dropdown problem or if there is a "conversion" not performed that should be in generating the text of the line. Since the additional cost is not on one of the "shorter" entries, I hadn't previously seen how it was displayed to know whether there was an issue created by using the SBA dropdown or not... seems unusual that it would have changed because really SBA was using the same data that was otherwise available and basically just adding to it instead of replacing it.

Will look into that because the sanitized currency symbol on the dropdown doesn't seem right. Thanks for reporting it.

[mc12345678]

Brilliant, it was the attributes.php that was in the wrong place. Stock levels are now present, although the £ sign has changed to the coding word rather than the symbol, but I am not bothered about that as the priority is the stock attributes.

Many many thanks for your prompt help, it is greatly appreciated, Gordon

This is because of a change that was incorporated into lines 192/193 of includes/classes/class.products_with_attributes_class_stock.php to sanitize to the maximum extent the name that would be displayed in the drop-down list, specifically to capture any other potential needed sanitization that was not included in line 192. I might therefore suggest removing the comment on line 192 and then commenting out line 193. This would at least restore the proper display of the text and currency symbol until possibly another alternative is considered.

Therefore change:

      //close tag and display text
//      $field .= '>' . zen_output_string($values[$i]['text'], array('"' => '&quot;', '\'' => '&#039;', '<' => '&lt;', '>' => '&gt;')) . '</option>' . "\n";
      $field .= '>' . zen_output_string_protected($values[$i]['text']) . '</option>' . "\n";

to:

      //close tag and display text
      $field .= '>' . zen_output_string($values[$i]['text'], array('"' => '&quot;', '\'' => '&#039;', '<' => '&lt;', '>' => '&gt;')) . '</option>' . "\n";
//      $field .= '>' . zen_output_string_protected($values[$i]['text']) . '</option>' . "\n";

I may even suggest modifying that a little to:

      //close tag and display text
      $field .= '>' . zen_output_string($values[$i]['text'],  array('"' => '&quot;', '\'' => '&#039;', '<' =>  '&lt;', '>' => '&gt;', ' & ' => ' &amp; ')) . '</option>' . "\n";
//      $field .= '>' . zen_output_string_protected($values[$i]['text']) . '</option>' . "\n";

to address converting "lone" '&' symbols to their corresponding html entity, but leaving combination type symbols (£) alone so that they can be properly handled/displayed.

Note that the "lone" symbol is identified by having a space before and after the & which is included in the replaced value(s) as well.. Unfortunately, this replacement will not work if the only content in the field is an &. In that case the & will not be "converted".

[catmint]

Great, that has sorted that out as well.

Many thanks again for your support, Gordon

[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+Scri pt, 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?

[mc12345678]

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(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`)
    );

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.

[tmpinsnty]

First of all, thank you so much for all the hard work in creating this plugin. And thank you for the help here and in other places.

So I changed to varchar amount to 190 -> didn't work
removed all three unique ids and installing worked.
Adding any one back cause the error.

Should I just take off the unique or give up the this mod? I think I want it because I can 'turn off' my attribute status when we are out of stock of one size/color. Instead of deleting the attribute and then having to re-add it (plus remembering to add the up-charges) is silly. I thought I added an older version or the code someone put in that allowed an attribute_status field. It didn't actually work quite right, but I was able to but the attribute_status = 0 in the database and that works.

It's a would be nice but not it's not like I HAVE to have it.

M

[mc12345678]

First of all, thank you so much for all the hard work in creating this plugin. And thank you for the help here and in other places.

So I changed to varchar amount to 190 -> didn't work
removed all three unique ids and installing worked.
Adding any one back cause the error.

Should I just take off the unique or give up the this mod? I think I want it because I can 'turn off' my attribute status when we are out of stock of one size/color. Instead of deleting the attribute and then having to re-add it (plus remembering to add the up-charges) is silly. I thought I added an older version or the code someone put in that allowed an attribute_status field. It didn't actually work quite right, but I was able to but the attribute_status = 0 in the database and that works.

It's a would be nice but not it's not like I HAVE to have it.

M

Technically, it seems that could do without any of the additional unique keys, though I can't remember if there is an internal test/check to see if a custom id is entered as a unique value which from your described usage is likely not to be a problem. There's a plan to incorporate such an independent check/test, but it hasn't been a priority (yet).

So you could continue to operate the software without those three unique key entries, though it looks like a little could be done in the admin processing to test for and insert new items with consideration of allowing/rejecting duplicates meaning there may be a few operations of insertion that do not quite as cleanly prevent creating duplicate variant entries/stock increases, but this is not something that can't be through. There are multiple sensical ways to update particular records/entries and maintain operation.

I did a cursory review to see if customid as a key is essential or what effect it would have. The only thing I see is that without that table definition, then it is possible to enter the same customid for two different records. This may be desirable for some, it may not be for others. Maintaining unique customids otherwise would require a little bit of code to check for the existence and if present to prevent. The biggest obstacle is in attempting to come out of a duplicate customid condition to a unique customid condition in a way that makes sense to the user.

If anything it identifies that perhaps there are some originally builtin and until now functional considerations that if they are to remain a part of the code need to be handled in a different way that is more software driven rather than database structure driven.

So, I don't see any immediate operational issue with not using the additional three unique keys. When the appropriate duplication checking area(s) have been updated will post something here so that the change(s) can be incorporated and restore "normal" operation. :)