View Issue Details

IDProjectCategoryView StatusLast Update
16429Development Otherpublic2020-07-13 16:50
Reporterjenseeckhout Assigned ToDenisChenu  
PrioritynoneSeverityfeature 
Status resolvedResolutionfixed 
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';
        }
DenisChenu

DenisChenu

2020-07-09 12:23

developer   ~58830

@jenseeckhout : can you cjeck if proposed solution (use App()->getConfig('mysqlEngine')) work for you ?
If yes : it's great if you can make the pull request :)

DenisChenu

DenisChenu

2020-07-13 15:56

developer   ~58924

@jenseeckhout : ? no news ?

I can make the pull request, but want to know if it fix your issue.

jenseeckhout

jenseeckhout

2020-07-13 16:00

reporter   ~58925

@DenisChenu Sorry, I lost track of this ticket! Yes, the issue is solved! MySQL replication now correctly picks up the engine. The PR is already done but waiting to be merged, if I'm correct: https://github.com/LimeSurvey/LimeSurvey/pull/1447

DenisChenu

DenisChenu

2020-07-13 16:01

developer   ~58926

Yes : but see my comment …

I really think we don't need a new settings can you just revert your commit and test with

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

at https://github.com/LimeSurvey/LimeSurvey/blob/d905cdce4ad13e401a2ceb4ac50b13144b799431/application/core/db/MysqlSchema.php#L18

DenisChenu

DenisChenu

2020-07-13 16:02

developer   ~58927

https://github.com/LimeSurvey/LimeSurvey/pull/1447

DenisChenu

DenisChenu

2020-07-13 16:50

developer   ~58929

Thank you @jenseeckhout

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
2020-07-09 12:23 DenisChenu Note Added: 58830
2020-07-13 15:56 DenisChenu Assigned To => DenisChenu
2020-07-13 15:56 DenisChenu Status new => feedback
2020-07-13 15:56 DenisChenu Note Added: 58924
2020-07-13 16:00 jenseeckhout Note Added: 58925
2020-07-13 16:00 jenseeckhout Status feedback => assigned
2020-07-13 16:01 DenisChenu Note Added: 58926
2020-07-13 16:02 DenisChenu Assigned To DenisChenu => ollehar
2020-07-13 16:02 DenisChenu Status assigned => testing
2020-07-13 16:02 DenisChenu Note Added: 58927
2020-07-13 16:50 DenisChenu Assigned To ollehar => DenisChenu
2020-07-13 16:50 DenisChenu Status testing => resolved
2020-07-13 16:50 DenisChenu Resolution open => fixed
2020-07-13 16:50 DenisChenu Note Added: 58929