'Exception Caught' error when attempting to publish entry with text content (EXP 3.3.2)
Development and Programming
Hi there,
We’ve come across an issue with a website we’re currently developing in ExpressionEngine 3.3.2.
We have a channel with around 40 fields (a mix of of text fields and textarea fields that appear in relevant collapsible tabs on the front end). When a certain amount of text has been entered and we attempt to publish the entry, it fails and we see the following error message:
Exception Caught
SQLSTATE[42000]: Syntax error or access violation: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.:
UPDATE `exp_channel_data` SET `field_id_23` = 'We can provide help to private homeowners carrying out repairs and maintenance to their properties.\n\nThe range of assistance available is set out in our Scheme of Assistance which is a public document detailing the type of assistance available and the criteria to be met to receive assistance.\n\nThe type of assistance available to owners includes the provision of information, advice, practical and technical assistance. Financial assistance is very limited and only available in specific circumstances. All assistance is subject to council priorities, available resources and tailored to individual needs and circumstances. Two adjoining owners experiencing the same problems of disrepair contacting the council might therefore not receive the same assistance package. Providing a range of assistance empowers owners to carry out works themselves and will help the council to assist more people in honouring their maintenance and repair obligations.\n\nWhere a disabled person\'s home no longer meets their needs, mandatory grant provision may be available. This will depend on the type of work required to make the persons home suitable for their needs and on whether their needs are deemed eligible for support under the Councils\' Priorities Framework. This is determined following an assessment of the person\'s needs which is carried out by an assessing officer from social work services.\n\nThe types of assistance that will be provided to disabled persons are described in the \'Scheme of Assistance\'.\n\nThe Scheme of Assistance details:\n
The mandatory assistance to be provided to disabled people.
Non mandatory/discretionary assistance that the council may offer to other owners and private landlords.
Details of the enforcement powers available to the council and what help the council will provide where these powers are used.
\n \nMortgage Arrears\nThere is a requirement for lenders to inform the council of the possibility of eviction due to arrears. This is called Section 11.\n\nWhat is Section 11?\nSection 11 of the Homelessness etc (Scotland) Act 2003 was implemented with effect from 1 April 2009. This part of the act requires all landlords, including private landlords, and creditors (mortgage and secured loan lenders) to notify North Lanarkshire Council when they raise possession proceedings or serve other eviction type action notices.\n\nWhy is this important?\nThe main function of a section 11 notice is to give the council ample knowledge that an individual, or household, are at risk of becoming homeless. Once aware North Lanarkshire Council may intervene to see if the eviction can be halted through mediation or other means, also, the notice will give the North Lanarkshire Council time to discuss alternative housing options with you.\n\nFurther Information can be found at http://www.northlanarkshire.gov.uk/index.aspx?articleid=20363' WHERE `entry_id` = '5'
/var/sites/w/westscotlandhousingoptions.co.uk/public_html/system/ee/legacy/database/drivers/mysqli/mysqli_connection.php:119Does anyone know what is causes this, and if so, is there is a work around for this? Google results point at MySQL being the issue but it seems strange because we’ve created entries on other ExpressionEngine sites with more text/content in the past and they’ve worked. It occurs both locally (MAMP) and on our webspace (tsohost) with pretty much the exact same amount of text to trigger the error.
Any advice or thoughts would be greatly appreciated. I can provide more info if needed.
Many thanks in advance!
I am having the exact same problem (also in EE 3.3.2). I have a channel that has 55 fields (many not containing any content) but the same error is thrown once too much text is added to a field. I thought it was an issue with there being too much text in that particular field, but it seems to be more a problem with hitting a limit in the entire entry.
Firstly, I didn’t think there was a hard upper limit … or even if there is, these entries don’t actually actually contain a huge amount of text all told, despite there being quite a lot of fields (many are optional and left empty). I can envisage an entry having much more, so if there is a limit, it seems way too low.
Secondly, this same entry worked fine in EE 2.10.1 - I am currently replicating (rather than upgrading) this website on EE3 as some aspects are being dropped and it’s easier to start from scratch and migrate content over … so I know this amount of content can exist in an EE channel entry; it just seems to have started being a problem with EE3.
So I am also keen to know more about any possible fixes! This could otherwise put the brakes on moving up to EE3.
EE uses InnoDB as an SQL storage engine now, and there is an 8K size limit per row. See if switching that table back to MyISAM helps. As a long term solution you might want to consider switching to the Barracuda file format. Your friendly database administrator would know more about that.
Oh, and do make (& verify!) backups before you attempt anything, but that goes without saying.
Thanks for your suggestion to switch from innodb to MyISAM, Ingmar.
I switched the table ‘exp_channel_data’ to MyISAM and it seems to have solved my problem.
For Diffsnow, or anyone else with this issue, try using the following code in phpMyAdmin/MySQL (in the SQL tab). Copy the code and alter it to match your database name (and other table names if required). Be sure to BACKUP your database before trying this, just in case!
USE db_name;
ALTER TABLE exp_channel_data ENGINE=MYISAM;Thanks again for the help 😊
Thanks Ingmar and CreativelinkUK.
I can also confirm that switching from InnoDB to MyISAM (and therefore removing the 8K size limit per row) allows me to do what was previously failing.
I changed the storage engine on the table a slightly different way (does exactly the same thing but doesn’t require using any code) … In phpMyAdmin, select the table (in this case, exp_channel_data) from the list in the left panel, go to the Operations tab, then under the heading Table Options, choose your preferred storage engine from the drop-down and click Go.
As always, backup the database before doing this!
Many thanks Diffsnow for letting us know about the non-code way of making this change! I would have used this drop-down method over using code if I’d known about it. Think I’ll be using that in the future 😊