We have an active survey that errors with "Got error 139 from storage engine" when trying to update a row. This survey has several text boxes per page. Reading the MySQL documentation, it appears that InnoDB can only handle 8000 bytes per row including the first 768 bytes of each blob (text, in this case) in the row. LimeSurvey is configured with $databasetabletype='InnoDB', but it appears it's still trying to do more than InnoDB can handle.

Is there a solution to this problem? Switch to MyISAM?

LimeSurvey 1.87+ (build 8518)
MySQL 5.0.45 using InnoDB

Thanks in advance.

Yeah definately a switch to MyIsam should solve the issue.

That's what I was figuring. I did some testing, and it doesn't seem there's any way to use more than 9-10 text fields in a single survey. Any time a user fills all of them with 768 or more characters, the UPDATE will fail.

I realize this probably means a significant change to the database schema to fix properly. Is there any plan to do this? Failing that, could the survey admin be warned about this when $databasetabletype='InnoDB' is set?

Actually I am not aware of such a issue at all. The only limitation with InnoDB I know of is that the number of fields in the result table cannot be more than 1000. This would show up in failing to activate the survey. I have never hear of actual UPDATE problems. It would be interesting what kind of limitation does kick in there.
Maybe it is jsut a simple Postgres config matter, but the PostGres guys would be the better people to ask.

This is MySQL, not Postgres. Here's some discussion of this specific error:

forums.mysql.com/read.php?22,63584,63872#msg-63872

The limitation is inherent in the InnoDB storage engine. Because LimeSurvey allows rows of arbitrary length (defined by the number of questions in a survey and their types), it can easily exceed the limitations in InnoDB. Since LimeSurvey knows which database engine is being used (it's even defined by the user in $databasetabletype), it would be nice if it checked surveys before they run into this limitation.

Testing this yourself is easy. Point LimeSurvey at a MySQL instance using InnoDB tables by default. Create a survey with 11 or more text fields. Execute the survey and enter at least 768 characters in each text field. You'll get an error, and the actual SQL statement will be emailed to the survey admin.

Yeah I see - sorry I meant MySQL. I would say InnoDB sucks big time and I was not aware of such a limitation. You can set any table type using the config param. We cannot possiblty test for every table type out there - that would be too many. MyIsam is the only one fully supported. Easiest would be to export the whole DB, use a text editor and change everything to MyISAM, then import again.

From config.php:

$databasetabletype = 'InnoDB'; // Storage engine mysql should use when creating survey results tables and token tables (if mysql is used). If available, InnoDB is recommended. Default is myISAM.

Might want to change "InnoDB is recommended" there.

InnoDB is a very powerful database engine, and is highly preferable over MyISAM in most enterprise applications for its speed and robustness. Most enterprise applications don't allow users to (effectively) generate arbitrary table schemas on the fly. This is something I've only seen in LimeSurvey, and may be worth some reevaluation by the developers.

Thanks for your responses on this, and I'll consider this resolved. We'll be moving to MyISAM (after testing) during our next maintenance period.

Hm.. hjave a look at

www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/

Can you try and make the table definition use the DYNAMIC row format and see if that works better?

dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-row-format.html

We're on MySQL 5.0 (5.0.77), so it doesn't have the new storage plugin architecture, which is when that option became available in InnoDB. We could try a different version, but it'd mean a full DB dump and reload (dev.mysql.com/doc/innodb-plugin/1.0/en/i...on-restrictions.html).

Thanks for the links though, I hadn't looked into that much until now.

Hm.. I was under the impression that is row format is already available in 5.0?
I remove the 'recommendation' from the config.