View Issue Details

IDProjectCategoryView StatusLast Update
0005916Simple:Pressinstallation/upgradepublic2018-07-15 19:26
ReporterYellow SwordfishAssigned ToYellow Swordfish 
PriorityhighSeverityN/AReproducibilityhave not tried
Status testingResolutionopen 
Product Version5.7.5.2 
Target Version5.8Fixed in Version 
Summary0005916: SP May not install correctly on InnoDB tables
DescriptionOne user has had the problem of the sfforums and sftopics tables not being created due to indexing issues when the default engine is InnoDB. Should be an easy fix up I believe but need to research it.

See this forum thread for all the details:

https://simple-press.com/support-forum/sp5-general-topics/invalid-database-query-error-and-cant-create-forum/
TagsNo tags attached.
change_log_textSP May not install correctly on InnoDB tables
typedefect

Activities

Yellow Swordfish

Yellow Swordfish

2018-07-12 15:56

administrator   ~0019831

Last edited: 2018-07-12 15:56

View 2 revisions

OK - some information on this - and a suggested solution.

There have to be some very specific circumstances for this to occur. These are:
  1. The default engine has to be InnoDB
  2. The default Charset and Collation has to be utf8mb4
  3. Certain MySQL Variables have to be specifically set


Under these circumstances when SP is installed these two tables can fail to be created. (I am unsure whether this still happens on version 5.6 and above but certainly happened on 5.5.x. But did NOT happen to me using 5.6.x)

The cause of the problem is the use of an index on forum_slug (sfforums) and topic_slug (sftopics). The two fields are set up as VARCHAR 200. But an index using utf8mb4 has a maximum VARCHAR length of 191.

This can be seen in our own database if converted to InnoDB where these two indices are shown as 191 chars and labelled 'part'.

One solution is to reduce the max length of the slug but this, of course, could have an adverse effect on existing users data so is not recommended.

A better and my proposed solution is simply to remove the index. It was quite possibly my doing but when you consider it, each slug is completely unique so why index it? It has no benefit as the resultant index has the same 'row' count as the actual data.

And it safely eradicates the problem.

Mr Papa

Mr Papa

2018-07-14 16:53

administrator   ~0019832

Removing the indexes seemsike the proper solution here... Agree, not sure there is any benefir to them.

Concur with proposal....
svn

svn

2018-07-15 19:26

administrator   ~0019833

Changeset [15666] by andy on 2018-07-15 15:26:54 -0400 (Sun, 15 Jul 2018)

test issue 0005916
Remove slug indexing on forums and topics tables

 Changed Files:

U trunk/sp-startup/install/sp-upgrade.php

 Differences:

 http://websvn.simple-press.com/revision.php?repname=Simple:Press&path=%2F&rev=15666

Issue History

Date Modified Username Field Change
2018-07-11 11:05 Yellow Swordfish New Issue
2018-07-12 15:56 Yellow Swordfish Note Added: 0019831
2018-07-12 15:56 Yellow Swordfish Note Edited: 0019831 View Revisions
2018-07-14 16:53 Mr Papa Note Added: 0019832
2018-07-15 19:25 Yellow Swordfish Assigned To => Yellow Swordfish
2018-07-15 19:25 Yellow Swordfish Status new => assigned
2018-07-15 19:26 svn =>
2018-07-15 19:26 svn Note Added: 0019833
2018-07-15 19:26 svn Status assigned => testing