Welcome, Guest
Username: Password: Remember me

TOPIC: Unable to edit Question Groups or Questions

Unable to edit Question Groups or Questions 1 year 5 months ago #94125

  • itfixt
  • itfixt's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 14
  • Karma: 0
The server has 2GB of RAM and there are a number of other sites running on it, none with high traffic.

This is typical:
Real memory 1.94 GB total, 756.31 MB used

It was the limit for this particular Apache virtual server that I changed - not the limit for everything. I am running PHP-FPM so each web site runs as a separate process with its own memory space. The default is 128MB.
This space accidentally left blank
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94127

  • holch
  • holch's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 2640
  • Thank you received: 313
  • Karma: 115
Well, then I find it strange that your instance of Limesurvey already uses 120MB, so that the 7-8MB of this process can't be processed anymore.

As I said, I am not an expert on this. So I think it makes sense that you make a bug report and add a copy of your survey (you can make it private, so no one besides the developers have access to it).

Please describe in detail the problem with the error message etc. so they can have a look at it. Because 128MB of memory should be enough for LS and the underlying webserver infrastructure, I think. But as I said, I am not an expert... ;-)
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94135

  • DenisChenu
  • DenisChenu's Avatar
  • OFFLINE
  • Moderator Lime
  • Posts: 6276
  • Thank you received: 801
  • Karma: 241
holch wrote:
Please describe in detail the problem with the error message etc. so they can have a look at it. Because 128MB of memory should be enough for LS and the underlying webserver infrastructure, I think. But as I said, I am not an expert... ;-)
Not an expert too, but right, think this was a great idea to do a bug report : www.limesurvey.org/en/community-services/bug-tracker

Denis
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94138

  • itfixt
  • itfixt's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 14
  • Karma: 0
Agreed - it strikes me as odd that the memory use should be like that, even knowing little of LimeSurvey. That memory doesn't even include the webserver infrastructure - it is purely the PHP process running the script under the UID/GID of the file owner; Apache child processes are separate!

Anyway, I will raise a bug as you suggested with an export of the survey attached.

Many thanks to the people who answered me so quickly - very much appreciated. The survey was done by a client after I had "found" LS & set it up for them on my server - if I make any money out of this, there will be a donation to LS :)

Cheers
Dave
This space accidentally left blank
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94371

  • itfixt
  • itfixt's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 14
  • Karma: 0
Following on from the above, when trying to activate the survey, LS reports an error trying to create the table. The error is that there are too many columns. Not surprising as there are 5617 columns!

I've attached a file with the Create Table query in it and also a dump of the survey. I'm wondering if this is a limitation of LS itself - that it's not well suited to this type of survey - or if it's badly structured and refactoring it will avoid this problem. It is possible to increase the max columns but not by that much, so this is a show-stopper. The only other way I can see round this is persuading LS to use InnoDB instead of MyISAM but that may invoke the Law of Unintended Consequences :/

It just crossed my mind that if the previous issue *was* due to bug, then curing that will fix this issue too, but any constructive comments are welcome.

PS - I hadn't got round to filing the bug report yet due to family illness... but I will get it done this afternoon.
This space accidentally left blank
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94375

  • holch
  • holch's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 2640
  • Thank you received: 313
  • Karma: 115
5617 columns is quite a lot!

Here some information about the limits of the databases:
docs.limesurvey.org/General+FAQ#What_lim...does_LimeSurvey_have...

Note: 'Survey size' refers to the maximmum number of questions (and answers) in your survey - don't confuse this with the number of replies on your survey (there is no limitation on that). Theoretically there would also be no limit on the maximum number of questions/answers in LimeSurvey. But the database engine you are using has several limits. The most important one is the limit on the number of fields(columns) in the result table.
MySQL ISAM: The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.
MySQL InnoDB: Maximum number of 1000 columns
MS SQL Server 2000: Maximum number of 1024 columns
Postgres: Maximum number of 250-1600 columns depending on column types. The maximum number of columns can be quadrupled by increasing the default block size to 32k. See Installation FAQ and PostgreSQL FAQ.

The mySQL ISAM engine is the most tricky one (see mysql documentation for more information). As it allows only up to 65,535 bytes per row and utf8 characters can require up to three bytes per character the maximum may be only 21,844 characters (but this depends on your DB encoding).
You can roughly calculate the size of your survey like this:

Every multiple numerical question: 20 chars for each answer
Every multiple choice & array question answers: 5 chars for each answer
Every other question type: 5 chars
Add 10% for the usual overhead

So if I read this correctly, you would be worse off with InnoDB, because there the limit is 1000 columns.

I am a little surprised that the databases are so limited. Aren't there databases that could cover more columns out there?
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94382

  • itfixt
  • itfixt's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 14
  • Karma: 0
Thanks for the swift reply Holch.

It turns out that MySQL has a hard limit of 4096 columns no matter which engine you use.

Actually, there is another row limit in that the total of field sizes in a row cannot be > 64k. This affects the size of the survey (not number of respondents) as the MySQL system table that holds each table definition in a single row, and so the row cannot hold more than around 2400 entries if all the field types are the smallest possible and not UTF8. In practice, the number is much smaller. Again, this is for all engines. For some reason, I always thought that InnoDB could handle much larger things in general than MyISAM. Oh well, live & learn :/

Postgresql can handle up to 6400 columns if you recompile it with a 32k blocksize, but the range is 1000~6400 so trying that would be a bit of a lottery.

Added later: I just checked and even Oracle, probably the most powerful commercial DBMS around, only handles up to 1000 columns/table!

In sum, this is beginning to look like a bit of a showstopper for this survey; unless we can find a way to re-factor it so that it generates a more reasonable numer of columns, it looks like we'll have to look at something else, possibly even a custom web app :{
This space accidentally left blank
Last Edit: 1 year 5 months ago by itfixt.
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94383

  • holch
  • holch's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 2640
  • Thank you received: 313
  • Karma: 115
I was also suprised that the databases are so limited.

But to be honest, your survey must be a real monster. What the hell are you trying to do there??? ;-)

Maybe various tables could be joined to increase the possible number of columns?

Because you'll probably run into the same problem with a lot of applications that depend on a database somehow.
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94392

  • DenisChenu
  • DenisChenu's Avatar
  • OFFLINE
  • Moderator Lime
  • Posts: 6276
  • Thank you received: 801
  • Karma: 241
Hello,

An possible solution is to separate your survey in more survey: split survey in 4 survey (for example).
- In each survey (except the first) add an equation question type :
-- code ANSWERID
- In first survey change EndURL to : yourSECONDsurveylink/ANSWERID/{SAVEDID} (maybe need adaptation, depends on your system)
- in other survey: yourNEXTsurveylink/ANSWERID/{ANSWERID}

And set "Automatically load URL when survey complete?" to YES in all survey except the last one.

Then the user see only one survey (deactivate "Show progress bar" too), and you have the same number in each survey to link betwwen answers.

Need some work, but think it can be OK.

A question for your survey : did you use a lot of "Text display " question type ? This one add a column: think this was a bug but need some dev to correct it.

Denis
The administrator has disabled public write access.

Unable to edit Question Groups or Questions 1 year 5 months ago #94399

  • itfixt
  • itfixt's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 14
  • Karma: 0
They're not - 1000 fields in an RDBMS is a helluva lot. In normal use, it's very rare to see more than 200.

The survey isn't that bit but it appears that the way it has been structured isn't really suitable for the way LS does things and we can't see a simple way to re-factor it to change that. If anyone else has suggestions on that front, we're listening :)

You can't split the columns amongst multiple tables as the whole thing is generated programatically by LS itself. The only solution I can see would be to re-write the LS module that stores survey responses - not a trivial task!! I would probably have a single table with one answer per row, each with a reference back to the question it was answering, a respondent Id and the answer itself. That might result in a great many (shortish) rows, which I believe works well with InnoDB. OTOH, the LS team may have already look at that & discarded it for reasons I don't know :) (e.g. it might make result analysis difficult)

This is not a problem I have encountered before with a DB app; as I said, even 200 columns in a single table is exceptional.
This space accidentally left blank
The administrator has disabled public write access.
Moderators: ITEd
Time to create page: 0.129 seconds
Donation Image