News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]

Started by Rune Rasmussen, August 04, 2022, 21:54:05 PM

Previous topic - Next topic

Rune Rasmussen

When running the tool 'Update VirtueMart tables to UTF8MB4' it failed, with error:

Quote1074 Column length too big for column 'category_description' (max = 16383); use BLOB or TEXT instead

Meaning it converted tables down to _virtuemart_categories, but couldn't continue on from _virtuemart_categories_en_gb

So I then also noticed _virtuemart_categories_en_gb and _virtuemart_categories_nb_no is different, strangely enough.

I guess nb-no is more correct, so I adjusted en-gb accordingly, and ran the tool again just to get:

Quote1074 Column length too big for column 'mf_category_desc' (max = 16383); use BLOB or TEXT instead

And then the next en-gb, and the next, and the next ...
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team

Mike J

Hello Rune,

I've pulled those errors too trying to get VM 4.0.2.10661 -  4.0.6.10690 to fly.

While I tried switching tables to UTF8MB4 also, I later found that moving to mysql 5.7.39 sorted those errors out ( I'm not sure what version of mysql you're running right now but certainly worth a shot ).

Going further though, I discovered that VM 4.0.2.10661 gets broken by J! 4.x.x 

VM 4.0.4.10688 & 4.0.6.10690 have their own problems too so my best advice is hold at J! 3.10.10 / VM 4.0.2 10661 if you can... I hear that VM 4.0.8 is not too far off.
VirtueMart 4.0.2 10661
Joomla! ‎3.10.11
PHP 7.4.30
mysql 5.7.39

Rune Rasmussen

Hi Mike,



MySQL Version   8.0.29-cll-lve


The real issue, like I see it, is that the structure of en-gb tables are outdated on this installation for some reason.
Changing the fields in question to TEXT etc. so they match the nb-no tables solved it for me.
Maybe the fixing tool needs to do check and fix those things also.


Quote from: Mike J on August 05, 2022, 11:02:26 AM
Going further though, I discovered that VM 4.0.2.10661 gets broken by J! 4.x.x 

VM 4.0.4.10688 & 4.0.6.10690 have their own problems too so my best advice is hold at J! 3.10.10 / VM 4.0.2 10661 if you can... I hear that VM 4.0.8 is not too far off.


Indeed, no reason to rush for J4. But VM4 still seems surprisingly immature even on J3, considering it has been out since April, and had two (stable .0.4/.0.6) bug fix releases since. So maybe too many people are sitting on their fence, they don't test, and they don't report bugs - also because of how some greats them in the forum from what I've seen.


Can't see any progress in the public SVN for 4.0.8, 29 days since last activity, but maybe they have gone in stealth mode. Anyhow, hopefully it's on it way yes. :)

http://dev.virtuemart.net/projects/virtuemart/repository
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team

Mike J

Hello Rune,

RE: "The real issue," you're probably right but I do recall getting beyond these errors:

Quote1074 Column length too big for column 'category_description' (max = 16383); use BLOB or TEXT instead
1074 Column length too big for column 'mf_category_desc' (max = 16383); use BLOB or TEXT instead

... By tweaking msql and possibly php versions (I remember defeating them distinctly because they drove me crazy!). If I recall correctly, I was running en-gb tables too but I can't be 100% certain of that right now.

A real clear directive from VM revealing the exact version we should be using with each VM version/release may help users short circuit the process of trial and error.

RE "some greats in the forum". Yes, some of them do come out swinging a little heavy handed from time to time. VM is not just software, it's also people but I guess that cuts both ways.

QuoteCan't see any progress in the public SVN for 4.0.8, 29 days since last activity, but maybe they have gone in stealth mode. Anyhow, hopefully it's on it way yes. :)

Yes, it's getting a little concerning. I've heard they're off on holidays, but I hope that that's not just code for something else... I can't hang on much longer as I need to get to J! 4 for other reasons. Don't want to drop VM because I love it!
VirtueMart 4.0.2 10661
Joomla! ‎3.10.11
PHP 7.4.30
mysql 5.7.39

Rune Rasmussen

#4
Quote from: Mike J on August 05, 2022, 17:36:12 PM
1074 Column length too big for column 'category_description' (max = 16383); use BLOB or TEXT instead
1074 Column length too big for column 'mf_category_desc' (max = 16383); use BLOB or TEXT instead

Those are the fields in _virtuemart_categories_en_gb and _virtuemart_manufacturercategories_en_gb, who when looking at the structure (in t.ex. phpMyAdmin) are set to varchar(19000), and they needs to be edited and change to TEXT without a length. Same for other en-gb tables, except _virtuemart_vendors_en_gb where the length set for the fields vendor_letter_header_html and vendor_letter_footer_html needs to be reduced to 6500 (if I recall correctly). If there is more language tables in use, they might need a edit too.
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team

pinochico

QuoteCan't see any progress in the public SVN for 4.0.8, 29 days since last activity, but maybe they have gone in stealth mode. Anyhow, hopefully it's on it way yes.

Yes, you are right.

But I see progress in VM DEV chat.
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds for FB, GMC,.. from products, categories, orders, users, articles, acymailing subscribers and database table
Virtuemart Email Manager - customs email templates
Import products for Virtuemart - from CSV and XML
Rich Snippets - Google Structured Data
VirtueMart Products Extended - Slider with products, show Others bought, Products by CF ID and others filtering products

Mike J

Quote from: Rune Rasmussen on August 05, 2022, 18:43:17 PM
Quote from: Mike J on August 05, 2022, 17:36:12 PM
1074 Column length too big for column 'category_description' (max = 16383); use BLOB or TEXT instead
1074 Column length too big for column 'mf_category_desc' (max = 16383); use BLOB or TEXT instead

Those are the fields in _virtuemart_categories_en_gb and _virtuemart_manufacturercategories_en_gb, who when looking at the structure (in t.ex. phpMyAdmin) are set to varchar(19000), and they needs to be edited and change to TEXT without a length. Same for other en-gb tables, except _virtuemart_vendors_en_gb where the length set for the fields vendor_letter_header_html and vendor_letter_footer_html needs to be reduced to 6500 (if I recall correctly). If there is more language tables in use, they might need a edit too.


OK, I stand corrected there. Thank you.

I think I was up against those issues on J! 4 about 3 - 4 weeks ago but my recall has grown a little fuzzy. I still found other problems with VM 4.0.2 (stable, compatible J4), 4.0.4 (stable, compatible J3/J4), 4.0.6 (stable, compatible J3/J4) though that made them unusable for live production sites taking actual orders.

After rolling back MariaDB to MYSQL, trying different versions of PHP while applying a number of other tweaks at a server level, I then started with just core VM & J! installations... Bingo.

The last place I thought to look was at VM 4.0.2 (stable, compatible J4), 4.0.4 (stable, compatible J3/J4), 4.0.6 (stable, compatible J3/J4) because they were and still are being presented as stable and compatible...
VirtueMart 4.0.2 10661
Joomla! ‎3.10.11
PHP 7.4.30
mysql 5.7.39

Mike J

Quote from: pinochico on August 05, 2022, 21:06:57 PM
QuoteCan't see any progress in the public SVN for 4.0.8, 29 days since last activity, but maybe they have gone in stealth mode. Anyhow, hopefully it's on it way yes.

Yes, you are right.

But I see progress in VM DEV chat.

Many thanks Pinochico,

That gives us hope.

Regards
VirtueMart 4.0.2 10661
Joomla! ‎3.10.11
PHP 7.4.30
mysql 5.7.39

Milbo

I just was in a longer holiday than usual, 5 weeks instead of 3. The problem here is that Rune has a very old db layout. So vm does not update his tables,...

This can have two reasons,.. a bug, or a hidden config. I would check for a hidden config, which prevents the table updates. because I disabled the special hidden config dbdescsize to set the description size.

}else if(strpos($name,'desc')!==false) {
/*if(VmConfig::get('dblayoutstrict',true)){
$fields[$name] = 'varchar('.VmConfig::get('dbdescsize',12500).') '.$linedefault;
} else {*/
$fields[$name] = 'text ';
//}
[code]
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Rune Rasmussen

That's nice to hear Max, hope you had a great holiday, building up a lot of positive energy. :)

As it's more sites facing the same, I would lean against it being a bug (multilingual sites related?), also based on the fact that we don't add any hidden configs, never did.

Anyhow it seems like this might needs to be checked for, and handled somehow when needed, because a lot of users wouldn't be able to handle it themselves.

Btw! The code posted, which file is it from, so anyone interested can take a look at the full code?
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team

Milbo

You should take another look. As I said the normal table updater should have set that to text already years ago.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

Rune Rasmussen

What should I take a closer look at Max, and where?

Anyhow; what does it help that the table updater should have done it years ago, if it didn't? Especially if it has happened to more than one user?

Joomla have a DB check, maybe it would be a good thing to do something similar in VM to fix those errors, to make it easy for the users fixing old failed updates and whatever causing something like this? As you said yourself; «This can have two reasons,.. a bug, or a hidden config.»

PS! You didn't care respond to my question? Sure we can do a file search, but it's a good thing always posting which file and lines codes belongs too when posting code.


Anyhow, here you have the full cfg for whatever it's worth.
# Required configuration data for the VirtueMart installer
# http://www.virtuemart.net
# Copyright (c) 2004 - 2010 VirtueMart Team. All rights reserved.
# http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.php
# VirtueMart is free software. This version may have been modified pursuant
# to the GNU General Public License, and as distributed it includes or
# is derivative of works licensed under the GNU General Public License or
# other free or open source software licenses.
# $Id: virtuemart_defaults.cfg 3726 2011-07-16 12:01:49Z Milbo $
#
# Notes:
# - The hash-sign (#) is a comment character, but only if it's the first non-blank char on a line
# - The equal-sign (=) when the default value is empty, is optional
# - Constant values can be used by enclosing them in curly brackets ({}), eg:
   # some_path_value={JPATH_ROOT}{DS}virtuemart{DS}somewhere


[config]
##shop settings
shop_is_offline=0
offline_message=Our Shop is currently down for maintenance. Please check back again soon.
use_as_catalog=0
currency_converter_module=convertECB.php
order_mail_html=1
useSSL=0
dangeroustools=0
debug_enable=none
google_jquery=1
multix=none




##Shopfront
pdf_button_enable=1
show_emailfriend=1
show_printicon=0
show_out_of_stock_products=1
coupons_enable=0
show_uncat_child_products=0
coupons_default_expire=1,D
weight_unit_default=KG
lwh_unit_default=m
list_limit=20
showReviewFor=all
reviewMode=registered
showRatingFor=all
ratingMode=registered
reviews_autopublish=1
reviews_minimum_comment_length=100
reviews_maximum_comment_length=2000
vmtemplate=default
categorytemplate=default
showCategory=1
categorylayout=0
categories_per_row=3
productlayout=0
products_per_row=3


vmlayout=0
    show_featured=1
    featured_products_per_row=3
    show_topTen=1
    topten_products_per_row=3
    show_recent=1
    show_latest=1


##Paths
    assets_general_path=components/com_virtuemart/assets/
    media_category_path=images/stories/virtuemart/category/
    media_product_path=images/stories/virtuemart/product/
    media_manufacturer_path=images/stories/virtuemart/manufacturer/
    media_vendor_path=images/stories/virtuemart/vendor/
   forSale_path_thumb=images/stories/virtuemart/forSale/resized/
    img_resize_enable=1
    img_width=90
    img_height=90
    no_image_set=noimage.gif
    no_image_found=warning.png
   
##Product order settings


browse_orderby_field=p.virtuemart_product_id
browse_orderby_fields=array:product_sku|category_name|mf_name|product_name


browse_search_fields=array:product_sku|category_name|category_description|mf_name|product_name|product_s_desc




##Pricing
    show_prices=1
    price_show_packaging_pricelabel=0
    show_tax=1
    basePrice=1
    basePriceText=1
    basePriceRounding=-1
    variantModification=1
    variantModificationText=1
    variantModificationRounding=-1
    basePriceVariant=1
    basePriceVariantText=1
    basePriceVariantRounding=-1
    basePriceWithTax=1
    basePriceWithTaxText=1
    basePriceWithTaxRounding=-1
    discountedPriceWithoutTax=1
    discountedPriceWithoutTaxText=1
    discountedPriceWithoutTaxRounding=-1
    salesPriceWithDiscount=1
    salesPriceWithDiscountText=1
    salesPriceWithDiscountRounding=-1
    salesPrice=1
    salesPriceText=1
    salesPriceRounding=-1
    priceWithoutTax=1
    priceWithoutTaxText=1
    priceWithoutTaxRounding=-1
    discountAmount=1
    discountAmountText=1
    discountAmountRounding=-1
    taxAmount=1
    taxAmountText=1
    taxAmountRounding=-1


##Check stock
    addtocart_popup=1
    check_stock=0
    automatic_payment=1
    automatic_shipment=1
    agree_to_tos_onorder=0
    oncheckout_show_legal_info=1
    oncheckout_show_register=1
    oncheckout_show_steps=0
    oncheckout_show_register_text=COM_VIRTUEMART_ONCHECKOUT_DEFAULT_TEXT_REGISTER


##SEO
    seo_disabled=0
    seo_translate=0
    seo_use_id=0
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team