SiT! Bugs - SiT!
View Issue Details
0001755SiT!setup/configpublic2011-12-26 14:382012-11-03 19:00
nicdev 
paulh 
normalmajoralways
resolvedfixed 
Current GIT 
3.90beta1Current GIT 
0001755: Inconsistencies in "setup-schema.php" can cause an upgrade to fail
When trying to upgrade from 3.65 to 3.90 I found some interesting inconsistencies, and some errors in the Schema file. The errors I can fix, but the inconsistencies are not that easy.

For example:
line 1628 of setup-schema:
ALTER TABLE `{$dbBillingMatrix}` CHANGE `id` `tag` VARCHAR( 32 ) NOT NULL ;
line 1729:
ALTER TABLE `{$dbBillingMatrix}` CHANGE `tag` `tag` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;

When upgrading from 3.65, the DB table 'billing matrix' column is indeed 'id' and we will rename it to 'tag', this is ok but what if the first pass of the setup schema fialed and you click "Recheck installation"?

On the second pass the column 'tag' does not exist anymore (we renamed it in the first pass) so the install script fails again ...

What I suggest here is to make some additional checks (like "SHOW COLUMNS FROM ...) and check if the column exists before trying to rename it.
No tags attached.
related to 0001743confirmed  Inconsistencies in DB model 
related to 0001403confirmed  We should store NULL rather than a empty string when as don't have the information 
Issue History
2011-12-26 14:38nicdevNew Issue
2012-01-07 12:36TomseRelationship addedrelated to 0001743
2012-01-07 12:36TomseRelationship addedparent of 0001403
2012-01-07 12:37TomseRelationship deletedparent of 0001403
2012-01-07 12:37TomseRelationship addedrelated to 0001403
2012-11-03 14:34ivanNote Added: 0004572
2012-11-03 14:47paulhNote Added: 0004576
2012-11-03 14:47paulhStatusnew => confirmed
2012-11-03 15:05paulhAssigned To => paulh
2012-11-03 15:05paulhStatusconfirmed => assigned
2012-11-03 19:00paulhNote Added: 0004580
2012-11-03 19:00paulhStatusassigned => resolved
2012-11-03 19:00paulhResolutionopen => fixed
2012-11-03 19:00paulhFixed in Version => Current GIT

Notes
(0004572)
ivan   
2012-11-03 14:34   
Need to re-test
(0004576)
paulh   
2012-11-03 14:47   
Can confirm the upgrade from 3.6 to 3.9 fails


pdating schema from 3.67 to v3.90…

A MySQL error occurred: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Support Email'' at line 2
You may have found a bug, if you think this is the case please report it.
Raw SQL: -- PH 2012-02-07 UPDATE `emailtemplates` SET subjectfield = '{incidentid} - {incidenttitle} WHERE name = 'Support Email'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = '{incidentid} - {incidenttitle}', body = 'Hi, Incident {incidentid} {incidenttitle} has been logged. The details of this incident are: Priority: {incidentpriority} Contact: {contactname} Site: {sitename} Regards {applicationname} -- {todaysdate} - {applicationshortname} {applicationversion} {globalsignature} {triggersfooter}' WHERE name = 'EMAIL_INCIDENT_CREATED_USER'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = '{incidentpriority} priority call ({incidentid} - {incidenttitle}) has been reassigned to you', body = 'Hi, Incident {incidentid} entitled {incidenttitle} has been reassigned to you. The details of this incident are: Priority: {incidentpriority} Contact: {contactname} Site: {sitename} Regards {applicationname} -- {todaysdate} - {applicationshortname} {applicationversion} {globalsignature} {triggersfooter}' WHERE name = 'EMAIL_INCIDENT_REASSIGNED_USER_NOTIFY'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = '{incidentid} - {incidenttitle} - Closed' WHERE name = 'EMAIL_INCIDENT_CLOSED_CONTACT'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = '{incidentid} - {incidenttitle} - Closed' WHERE name = 'EMAIL_INCIDENT_CLOSED_USER'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = 'Service Request #{incidentexternalid} - {incidenttitle} CLOSED - {incidentid}' WHERE name = 'EMAIL_EXTERNAL_INCIDENT_CLOSURE'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = '{applicationshortname} {incidentid} - {incidenttitle} updated', body = 'Hi {contactfirstname}, Your incident {incidentid} - {incidentid} has been updated, please log into the portal to view the update and respond. DO NOT respond to this e-mail directly, use the portal for your responses. Log into the portal at: {applicationurl}, where you can also reset your details if you do not know them. Regards, {signature} {globalsignature}' WHERE name = 'EMAIL_INCIDENT_UPDATED_CUSTOMER'

A MySQL error occurred: [1054] Unknown column 'subject' in 'field list'
You may have found a bug, if you think this is the case please report it.
Raw SQL: UPDATE `emailtemplates` SET subject = '{applicationshortname} {incidentid} - {incidenttitle}: feedback requested' WHERE name = 'EMAIL_SEND_FEEDBACK'

Summary: 8 Error(s) occurred while updating the schema, please resolve the problems reported and then try running setup again.
(0004580)
paulh   
2012-11-03 19:00   
as of 208231c upgrades are now consistent with clean installs