Author Topic: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]  (Read 203 times)

Rune Rasmussen

  • Jr. Member
  • **
  • Posts: 176
  • Is the stable release stable?
    • SYNTAX ERROR
  • VirtueMart Version: Latest ;)
Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« on: August 04, 2022, 21:54:05 pm »
When running the tool 'Update VirtueMart tables to UTF8MB4' it failed, with error:

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

Quote
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 ...
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team

Mike J

  • Jr. Member
  • **
  • Posts: 70
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #1 on: August 05, 2022, 11:02:26 am »
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.10
PHP 7.4.30
mysql 5.7.39

Rune Rasmussen

  • Jr. Member
  • **
  • Posts: 176
  • Is the stable release stable?
    • SYNTAX ERROR
  • VirtueMart Version: Latest ;)
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #2 on: August 05, 2022, 11:40:40 am »
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
Rune Rasmussen - https://www.syntaxerror.no/

Norwegian Translation Team

Mike J

  • Jr. Member
  • **
  • Posts: 70
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #3 on: August 05, 2022, 17:36:12 pm »
Hello Rune,

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

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

Quote
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!
VirtueMart 4.0.2 10661
Joomla! ‎3.10.10
PHP 7.4.30
mysql 5.7.39

Rune Rasmussen

  • Jr. Member
  • **
  • Posts: 176
  • Is the stable release stable?
    • SYNTAX ERROR
  • VirtueMart Version: Latest ;)
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #4 on: August 05, 2022, 18:43:17 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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1522
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #5 on: August 05, 2022, 21:06:57 pm »
Quote
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.
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

  • Jr. Member
  • **
  • Posts: 70
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #6 on: August 06, 2022, 03:30:12 am »
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.10
PHP 7.4.30
mysql 5.7.39

Mike J

  • Jr. Member
  • **
  • Posts: 70
Re: Failed - Update VirtueMart tables to UTF8MB4 [VM 4.0.6]
« Reply #7 on: August 06, 2022, 03:31:37 am »
Quote
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
VirtueMart 4.0.2 10661
Joomla! ‎3.10.10
PHP 7.4.30
mysql 5.7.39