Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Forum bug

  • ymca
  • ymca's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
4 years 1 month ago #193228 by ymca
MyIsam vs InnoDB in installation was created by ymca
Hi,

I have started to install LimeSurvey and go an option to choose from MyIsam or InnoDB.
Saw somewhere that for big projects I should use "innoDB".
When I chose it, it showed me a warning that told me to read more about it in the manual.

been searching information about this but could not figure it out yet.

Found this:

manual.limesurvey.org/General_FAQ#What_l...does_LimeSurvey_have ...

Looks like information that a tech guy will understand, I don't understand it.
An example of survey size will help greatly.

Also saw here that there's a plan to fix that downsize of column limitations:

manual.limesurvey.org/LimeSurvey_roadmap#Planned_features


So what should I choose?
Is there a survey size number info that I can understand, to let me know with what DB to use?

Thank you.

LS Version 4.2+
The topic has been locked.
  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
4 years 1 month ago #193230 by Joffm
Replied by Joffm on topic MyIsam vs InnoDB in installation
Well, it is not as difficult as you think.

In the manual it says about the column limitation:
(BTW: This is a limitation of MySQL, not of LimeSurvey)

MySQL ISAM: The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.
(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, it can require up to three bytes per character. The maximum may be only 21,844 characters (but this depends on your DB encoding). )
In practice you will have a column limit of about 1570 columns.

MySQL InnoDB: Maximum number of 1000 columns.


Now remember:
Each entry field in your survey is one column.
Single questions: One column
Multiple questions and arrays: One column per subquestion
Arrays(text/number): One column per subquestion and per answer option

And of course some management overhead.



And the remark in the roadmap: We all hope that it will be in some future.

Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless
The topic has been locked.
  • ymca
  • ymca's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
4 years 1 month ago - 4 years 1 month ago #193231 by ymca
Replied by ymca on topic MyIsam vs InnoDB in installation
Thanks.

So in general, the difference in these DB's are 570 columns.

1 question( Or answer?) = 1 column.

For example I can make 1 survey of 250 questions and 750 answers?

Don't know what this means:

And of course some management overhead.


And about the column limitations in the roadmap, does it matter what DB I choose now to get the fix later?

LS Version 4.2+
Last edit: 4 years 1 month ago by ymca.
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
4 years 1 month ago #193232 by holch
Replied by holch on topic MyIsam vs InnoDB in installation
The number of columns depends on the question type.

Single answer questions usually only require one column (where the code of the answer is stored), while questions where more than one answer can be given require 1 column per possible answer (to mark if each answer was chosen or not).

So if you only have single answer questions you can fit more questions into one questionnaire than if you have a lot of multiple answer questions.

Looks like information that a tech guy will understand, I don't understand it.

If you install Limesurvey onto your own server YOU ARE somehow the "Tech guy". ;-)

As soon as you take over the responsibility of installing a software on a server, you will have to get at least a little bit into "tech", to make sure you know what you are doing. There is no way around it, besides going towards SaaS, where others will do the installation and administration for you.

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The topic has been locked.
  • ymca
  • ymca's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
4 years 1 month ago #193237 by ymca
Replied by ymca on topic MyIsam vs InnoDB in installation

YOU ARE somehow the "Tech guy". ;-)

Thanks. There are levels of tech, so I guess I'm the minimum :laugh:

there's a plan to fix that downsize of column limitations

So it doesn't matter what I use "MyIsam" or "InnoDB", the fix will be for both databases?

LS Version 4.2+
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
4 years 1 month ago - 4 years 1 month ago #193239 by holch
Replied by holch on topic MyIsam vs InnoDB in installation

So it doesn't matter what I use "MyIsam" or "InnoDB", the fix will be for both databases?

I think the fix will have nothing to do with the database engine. Because the Limesurvey Developer Team will hardly be able to change the limitations of MySQL.

Refactor answer database to get rid of column limitations.


With my limited knowledge I only see two approaches for getting rid of column limiations:
1. Allowing to spread the resultstable of one survey over more than one table (and connect them via an ID)
2. Completely change the way Limesurvey stores results in the database.

In any way, I think this is nothing for the near future. I wouldn't expect anything like that in LS 4.x, maybe not even in LS 5.x because it will probably require quite some work and also testing.

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

Last edit: 4 years 1 month ago by holch.
The topic has been locked.
  • ymca
  • ymca's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
4 years 1 month ago #193242 by ymca
Replied by ymca on topic MyIsam vs InnoDB in installation
Now I get these errors:

Please fix the following input errors:
You need to enable large_file_prefix setting in your database configuration in order to use InooDb engine for LimeSurvey!
Your database configuration needs to have innodb_file_format and innodb_file_format_max set to use the Barracuda format in order to use InooDb engine for LimeSurvey!


LS Version 4.2+
The topic has been locked.
  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
4 years 1 month ago #193243 by Joffm
Replied by Joffm on topic MyIsam vs InnoDB in installation
Did you check your MySQL settings regarding innodb?

E.g. did you try to create an innodb database with a database tool like phpMyAdmin or TOAD, or another one?

Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless
The topic has been locked.
  • ymca
  • ymca's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
4 years 1 month ago - 4 years 1 month ago #193245 by ymca
Replied by ymca on topic MyIsam vs InnoDB in installation
I have ionos hosting.
In the past only made MySQL database and the LimeSurvey worked.

I see there is some option here about "Storage engines" and I see "innoDB" here.

It says

InnoDB is the default storage engine on this MySQL server.

.
I think it because I've installed the new 5.x WordPress on it (heard they now use innoDB).

I also see here in variables:

innodb_large_prefix is ON
innodb_file_format Barracuda
innodb_file_format_check ON
innodb_file_format_max Barracuda


So what is wrong?

LS Version 4.2+
Last edit: 4 years 1 month ago by ymca.
The topic has been locked.
  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
4 years 1 month ago #193246 by Joffm
Replied by Joffm on topic MyIsam vs InnoDB in installation

In the past only made MySQL database and the LimeSurvey worked.

And which engine did you use in your past installations?

Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless
The topic has been locked.
  • ymca
  • ymca's Avatar Topic Author
  • Offline
  • Elite Member
  • Elite Member
More
4 years 1 month ago #193247 by ymca
Replied by ymca on topic MyIsam vs InnoDB in installation
Didn't do anything.
Just created MySQL DB, installed WP 4.x on it, then installed LS 3.x on it.
That's it. Didn't know there were engines...

LS Version 4.2+
The topic has been locked.
  • DenisChenu
  • DenisChenu's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
4 years 1 month ago #193255 by DenisChenu
Replied by DenisChenu on topic MyIsam vs InnoDB in installation

ymca wrote: then installed LS 3.x on it.

LS3 didn't have InnoDb as engine

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose