View Issue Details

IDProjectCategoryView StatusLast Update
16429Development Otherpublic2020-06-26 08:52
Reporterjenseeckhout Assigned To 
Status newResolutionopen 
Summary16429: Add config variable to force MyISAM engine in CREATE TABLE statements in MySQL

When using MySQL replication on a LimeSurvey MySQL database, the default storage engine variable is ignored. This makes it so that the replication could use InnoDB (if that is the replication's default) engine when creating lime_survey_xxx tables. If these have lots of columns (see, this causes replication to error and halt.
I suggest to introduce a new config variable that allows implicitly defining the storage engine in the create table statements. This way, MySQL replication does work correctly and will use MyISAM.

TagsNo tags attached.




2020-06-26 08:30

developer   ~58507

I don't check a lot but : if mysqlEngine is set to InnoDB : we must use InnoDB.

If there are reason to force InnoDB or MyISAM in create table statement (token and survey) : why not force it always ?

I mean : why adding a config var ?



2020-06-26 08:37

reporter   ~58508

@DenisChenu: you have a point there. However, since this is kind of an edge case (only if you are using MySQL replication with the replication's default engine on InnoDB), I thought adding a new config variable would be the least intrusive and has 0% chance of breaking things.
I do believe that in the past the engine was always forced to MyISAM, but that this statement was taken out to facilitate usage of InnoDB. In that philosophy, I thought it best to make it optional.
An alternative would be to detect which engine the application was created in (mysqlEngine config var) and always force it to that engine. As you just pointed out in the PR on github :)



2020-06-26 08:52

developer   ~58511

In 3.X :

        if (empty($options)) {
            $options = 'ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

In 4.X :

        if (empty($options)) {
            $options = 'DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

Then you broke less things with

        if (empty($options)) {
            $options = 'ENGINE='.App()->getConfig('mysqlEngine')' DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

Issue History

Date Modified Username Field Change
2020-06-25 19:30 jenseeckhout New Issue
2020-06-26 08:30 DenisChenu Note Added: 58507
2020-06-26 08:37 jenseeckhout Note Added: 58508
2020-06-26 08:52 DenisChenu Note Added: 58511