View Issue Details

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

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 https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html), 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.

Activities

DenisChenu

DenisChenu

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 ?

jenseeckhout

jenseeckhout

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 :)

DenisChenu

DenisChenu

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