Please help us help you and fill where relevant:
Your LimeSurvey version: 5.6.14
Own server or LimeSurvey hosting: own server
Survey theme/template:n.s.
==================
Hi all,
I am currently preparing for the second wave of a survey for a research project at the University. We conducted wave 1 in Limesurvey version 3 and I now wanted to switch to Version 5 for the second wave, as Version 3 was announced to be shut down in September. Now I am facing the problem that I cannot activate my survey in the new version. Unfortunately, the error message occurs that I have too many (sub-)questions in my survey ("The survey response table could not be created. Usually this is caused by too many (partial) questions in your survey. Please try to delete questions from your survey"). However, we are talking about exactly the same survey as in wave 1 which was already online (only in Version 3). Did anybody face similar issues?

Thank you very much in advance!

First of all: is it really "exactly" the same survey? Did you maybe activate some other features, add items / answer options?

And then: is it the same database engine? Because different database engines have different limits.
www.limesurvey.org/manual/General_FAQ#...regarding_survey_size

I am not aware that LS3 and LS5 use the database differently, but it could well be that LS 5 needs more columns out of the box, maybe. And if you have been close to the limit already before in LS3 it might go over in LS5. 

When you export the data collected with this survey, how many columns do you get in Excel / CSV?

I tried with literally exactly the same survey (copied it into the new version) and unfortunately, it did not work.

That is actually a good idea! Unfortunately, it is not quite clear to me how I can find out about the database engine of Version 3 and 5. However, when exporting the answers to excel, I have 552 columns. According to your link, it should be possible to get at least 1000 as an output (across different database engines), so I am still confused about the error message... 

 

552 colums are no where near the limit and shouldn't cause any problems.

Regarding the database engine used for each installation you will have to talk to the person responsible for the installation. But I don't think you should run into this error message based on the 552 columns.

Any chance that you can upload or send the LSS file for a test here in my installations? I totally understand if you can't do this due to the survey being confidential or something.

Unfortunately I cannot share the lss file due to confidentiality. But I highly appreciate your help!

You can upload it to the Limesurvey Cloud, with your Limesurvey account you should have access to the free tier of the Limesurvey Pro Cloud. Just try to upload it and see what happens. This should help to exclude that this is any issue with your installation.

Update: You were right about the database engines! Apparently, my university used MyIsam in Version 3 but switched to InnoDB in Version 5, which does not seem to be compatible.
Does anyone know why this is the case? Can this also be a bug of Limesurvey?
 

Hello,
person responsible for the installation here.

Indeed with LS5 we switched the database engine to InnoDB. It is very hard to find good reasons for still using MyISAM in 2023, especially in a larger scaled environment.

The error message seems a bit misleading: In fact the row size gets too large, which can be caused by too many columns, true, but the amount of columns is not the troublemaker in that case though.

What would be best practice? Mixing database engines? Updating the migration scripts with something like to bypass the problem?

Best regards,
Frank

I am not an expert on server administration nor on databases. I just tried to narrow possible issues down.

With the number of questions / columns given, the survey should not run into the limits, but somehow it does.

It worked before, so I tried to narrow down what has changed. One thing seems to be the database engine, but InnoDB shouldn't cause the problem.

The database engine might actually not be the problem.

I recommend to have also a look at other settings, e.g. PHP, max_vars, execution time, script size, etc.

And I would also open a bug ticket.

knorke wrote:

Hello,
person responsible for the installation here.

Indeed with LS5 we switched the database engine to InnoDB. It is very hard to find good reasons for still using MyISAM in 2023, especially in a larger scaled environment.

The error message seems a bit misleading: In fact the row size gets too large, which can be caused by too many columns, true, but the amount of columns is not the troublemaker in that case though.

What would be best practice? Mixing database engines? Updating the migration scripts with something like

Code:

ALTER TABLE survey_123 ROW_FORMAT=DYNAMIC;

to bypass the problem?

Best regards,
Frank
 

In 3.X : we use InnoDB for internal table (survey/question/answer etc …) but MyIsam for survey table.

If you know some options that can be great to add it
You can test on
github.com/LimeSurvey/LimeSurvey/blob/a1...eyActivator.php#L321

www.yiiframework.com/doc/api/1.1/CDbSchema#createTable-detail $options is added after the CREATE TABLE instruction

And it's best to have a issue reported about a solution if adding )

Hello,

thx for pointing out the code on GitHub. Unfortunately at this point I can only throw in some thoughts, because the database variables in particular needs some serious digging. At the moment I would come up with something like: But I wouldn't feel comfortable putting this anywhere but my own server tbh.

So to put this aside, as far as I understand, mixing database engines on survey tables is not a thing right now, because simply everytime a survey is activated the table gets the default database engine, which is InnoDB or MyISAM. If there is no underlying reason, why not let "the not default engine" be the fallback engine? So e.g. instead of throwing an error when creating an InnoDB table fails, try MyISAM first instead.

As mentioned, nothing more than wild thoughts at this point.

Adding the table structure that fails under InnoDB here just for future references as attachment.

Best regards,
Frank
 

Please : report issue and feature request here.

Forum user are for user, bot LimeSurvey team

I think i'm the only core dev who came to forum …