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

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

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

On a backup copy of your site try VM3.2.1 - available on http://dev.virtuemart.net/projects/virtuemart/files

[SteveBab]

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

Code Select Expand

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

Code Select Expand

  `cat_params` blob NOT NULL DEFAULT '',
instead of this:

Code Select Expand

  `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]

Milbo is working on the issue right now!

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

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

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

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

[David]

Yeah, the last build finally without complaints  .