05767: Database Schema Foreign Key Inconsistencies - LimeSurvey bugs and feature requests

This bug affects 1 person(s).

ID	Project	Category	View Status	Date Submitted	Last Update

05767	Bug reports	Other	public	2012-02-06 23:00	2012-03-14 21:08

Reporter	tacman1123	Assigned To	c_schmitz
Priority	normal	Severity	minor
Status	closed	Resolution	fixed
Product Version	1.92RC3
Fixed in Version	2.00a2

Summary	05767: Database Schema Foreign Key Inconsistencies
Description	In the main survey table, the primary key sid is defined as INT(11), but almost all references to it in foreign keys are defined as INT UNSIGNED. Foreign key joins are much faster with identical data types, and InnoDB requires identical types to use cascading updates and deletes.
Steps To Reproduce	See admin/create-mysql.sql
Additional Information	A review of all the tables fields is probably worthwhile, and defining the foreign key relationships in InnoDB would make for a faster and more efficient database. I think this same issue may exist with User / UserGroups, etc.
Tags	No tags attached.

Bug heat	4
Complete LimeSurvey version number (& build)	155
I will donate to the project if issue is resolved	No
Browser
Database type & version	mysql 5
Server OS (if known)	all
Webserver software & version (if known)	Apache 2.2
PHP Version	5.3

User List	There are no users monitoring this issue.

c_schmitz 2012-02-07 12:17 administrator ~17277	Thank you. We will fix that. Please note that in general we do not support InnoDB anymore.

tacman1123 2012-02-07 12:40 reporter ~17278	How come? I thought it was more robust than MyISAM. Are you recommending PostGRES instead? Would you consider adding cascading update/deletes to the sql creation, and explicitly defining more of the foreign key relationships for version 2?

c_schmitz 2012-02-07 12:54 administrator ~17279	InnoDB has some serious limitations regarding the maximum number of columns which can be hit pretty fast by LimeSurvey whereas myISAM has a much higher limit (it is not completely fixed so we optimized LimeSurvey fora that). Although in some setups cascading updates/deletes may make sense it does not make for our scenario - especially if we want to be cross-DB compatible it is not really practical.

c_schmitz 2012-03-11 16:32 administrator ~17837	2.00alpha 2 Build 120212 released

Date Modified	Username	Field	Change
2012-02-06 23:00	tacman1123	New Issue
2012-02-07 12:16	c_schmitz	Assigned To	=> c_schmitz
2012-02-07 12:16	c_schmitz	Status	new => assigned
2012-02-07 12:17	c_schmitz	Note Added: 17277
2012-02-07 12:40	tacman1123	Note Added: 17278
2012-02-07 12:54	c_schmitz	Note Added: 17279
2012-02-27 01:41	c_schmitz	Status	assigned => resolved
2012-02-27 01:41	c_schmitz	Fixed in Version	=> 2.00a2
2012-02-27 01:41	c_schmitz	Resolution	open => fixed
2012-03-11 16:32	c_schmitz	Note Added: 17837
2012-03-11 16:32	c_schmitz	Status	resolved => closed
2012-03-14 21:08	c_schmitz	Changeset attached	=> Import 2012-03-09 13:30:34 Yii dd06fed8

LimeSurvey: Yii dd06fed8 2012-02-26 16:42:53 c_schmitz Details Diff	Fixed issue 05767: Database schema foreign key inconsistencies		Affected Issues 05767
	mod - installer/sql/create-mysql.sql		Diff File