Error updating 3.0.18 to 3.2 Column length too big for column 'cat_params'

Started by Capogr, March 23, 2017, 12:47:13 PM

Previous topic - Next topic

Capogr

Hi i am trying to update from 3.0.18 to 3.2 and when i try to upload com_virtuemart.3.2.0 i get

Column length too big for column 'cat_params' (max = 16383); use BLOB or TEXT instead SQL=ALTER TABLE `#__virtuemart_categories` ADD `cat_params` varchar(17000) NOT NULL DEFAULT '' AFTER `hits`

Thank you in advance

Milbo

The next release vm3.2.1 will use 16383 and also any other params field will use 16383 or text, the reason is the utf8mb4, which allows a maximum of 16383 and it will be the new default of the next mysql version.

thank you for reporting.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

SteveBab

Hi Milbo,

Thanks for clarifying. I'm having this issue too, and it has broken some functionality of my store.

What do you suggest I do to roll back/or fix the issue?

SteveBab

Hi Milbo,

Our shop is broken after updating to 3.2 because of this issue. Please let me know how to remedy the situation. I could manually create tables of the lower size, but I fear that may break other functionality.

Please advise.

jenkinhill

Kelvyn
Lowestoft, Suffolk, UK

Retired from forum life November 2023

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

SteveBab

Updated to 3.2.1, which resolved this issue on install, but led to another problem:

]Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs SQL=ALTER TABLE `#__virtuemart_categories` ADD `cat_params` varchar(16383) NOT NULL DEFAULT '' AFTER `hits`

I did something which may be dangerous, but seems to have brought my shop back to life:

I changed line 149 of install.sql to this:
  `cat_params` blob NOT NULL DEFAULT '',
instead of this:
  `cat_params` varchar(16383) NOT NULL DEFAULT '',

I don't see any negative side effects yet, but I will post here if I do.



SteveBab

BTW, honored to have your reply Jenkinhill!

I've been using VM for many years. Your work has been fantastic.

Please let me know if I was reckless with my solution...

jenkinhill

Kelvyn
Lowestoft, Suffolk, UK

Retired from forum life November 2023

Please mention your VirtueMart, Joomla and PHP versions when asking a question in this forum

Milbo

I reuploaded the release 5 minutes ago. Just try with the new release, it uses either text or smaller sizes. the 16383 are meant for the full row and not just one column. Before it was around 24 000. The problem comes that the table installs with utf8mb4, instead the given utf8, I dont know the reason for it.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

CDCG

I just upgraded from 3.2 to 3.2.1 and now have the error everyone else is talking about:

1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs SQL=ALTER TABLE `#__virtuemart_customs` CHANGE COLUMN `custom_value` `custom_value` VARCHAR(4096) COMMENT 'DEFAULT VALUE' AFTER `custom_tip`

Is there another patch coming out?  Should you take down 3.2 and/or 3.2.1 until this is resolved so as not to take down running sites?

Thanks, CDCG

Milbo

ahh damn

this row is changed before, the other row is change to text

`custom_params` text '',


It may work with just rerunning the table updater.

or change manually the `custom_params`in __virtuemart_customs to `custom_params` text '', and execute then the table updater.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

dsrpmedia

I`m getting this error as well, not quite following what I am supposed to do

should I uninstall and go back to 3.0.18?

SteP[IT]

Joining here from another similar tread: manually changing custom_params field to text in __virtuemart_customs before upgrading solves the error. You must manually change the field type and then re-apply the upgrade, and you get the installation completed.
J 3.9.2 - VM 3.4.3

MMC EDIZIONI - Italian High-Quality PaperBooks Publisher
Site: https://www.mmcedizioni.it

Milbo

I reupped it quickn dirty again. But it would be nice to understand the reason for the whole hazzle.

What we know is, that your tables use utf8mb4, despite that any "CREATE TABLE" uses "DEFAULT CHARSET=utf8". But somehow, the tables of the people with the problem are set to utf8mb4. The max size per row for char and varchar is the same, but utf8 uses less bytes, than utf8mb. So a varchar with utf8 can be  (65535-3)/3 = 21844 and with utf8mb4 only  (65535-3)/4 = 16383.

I know that joomla uses since j3.5.1 the utf8mb4 as default. But that does not affect our tables. I wonder also how it can affect updaters, because the problem existed already with vm3.0.18, just not for the cat_params, but for a lot other tables.

So I really wonder why your db suddenly create this error and not already before.

Anyway, whenever I was in doubt that the new size is too small, I set it to "text". So custom_params, shipment_params, and payment_params use text now.
Should I fix your bug, please support the VirtueMart project and become a member
______________________________________
Extensions approved by the core team: http://extensions.virtuemart.net/

David

Joomla 3.8.3, VirtueMart 3.4.2