Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]

[Rune Rasmussen]

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

1074 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:

1074 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 ...

[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.

[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.

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

[Mike J]

Hello Rune,

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

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

... 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.

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.

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!

[Rune Rasmussen]

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.

[pinochico]

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.

Yes, you are right.

But I see progress in VM DEV chat.

[Mike J]

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...

[Mike J]

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.

Yes, you are right.

But I see progress in VM DEV chat.

Many thanks Pinochico,

That gives us hope.

Regards

[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.

Code Select Expand


}else if(strpos($name,'desc')!==false) {
/*if(VmConfig::get('dblayoutstrict',true)){
$fields[$name] = 'varchar('.VmConfig::get('dbdescsize',12500).') '.$linedefault;
} else {*/
$fields[$name] = 'text ';
//}
[code]

[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?

[Milbo]

You should take another look. As I said the normal table updater should have set that to text already years ago.

[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.

Code Select Expand

# 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