Possible Regression on Oracle Conversion for SAKAI_CLUSTER
Description
Environment
Test Plan
is related to
Activity
Hudson CI Server October 31, 2012 at 7:18 PM
Integrated in kernel-trunk #566 (See http://builds.sakaiproject.org:8080/job/kernel-trunk/566/)
- Change datatype for sakai cluster to check DST in oracle (Revision 115399)
Result = SUCCESS
Matthew Jones October 31, 2012 at 5:56 PM
Also change UPDATE_TIME in SAKAI_CLUSTER to TIMESTAMP WITH LOCAL TIME ZONE in kernel and reference in KNL-725.
Matthew Jones October 31, 2012 at 5:55 PM
It was decided we would just revert the changes in 2.9.x and consider this in the future.
revert 93136 against kernel
revert 93137 against reference
Matthew Jones October 31, 2012 at 5:53 PM
Without dropping the index it gave this error:
ORA-30556: functional index is defined on the column to be modified
So this would be what the script for Oracle should be.
CREATE TABLE SAKAI_EVENT_KNL_734 AS (SELECT * FROM SAKAI_EVENT);
DELETE FROM SAKAI_EVENT;
ALTER TABLE SAKAI_EVENT MODIFY (EVENT_DATE TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO SAKAI_EVENT SELECT * FROM SAKAI_EVENT_KNL_734;
– You may want to DROP TABLE SAKAI_EVENT_KNL_734 to free up some space
DROP INDEX SAKAI_SESSION_START_END_IE;
CREATE TABLE SAKAI_SESSION_KNL_734 AS (SELECT * FROM SAKAI_SESSION);
DELETE FROM SAKAI_SESSION;
ALTER TABLE SAKAI_SESSION MODIFY (SESSION_START TIMESTAMP WITH LOCAL TIME ZONE);
ALTER TABLE SAKAI_SESSION MODIFY (SESSION_END TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO SAKAI_SESSION SELECT * FROM SAKAI_SESSION_KNL_734;
CREATE INDEX SAKAI_SESSION_START_END_IE ON SAKAI_SESSION
(
SESSION_START,
SESSION_END,
SESSION_ID
);
– You may want to DROP TABLE SAKAI_SESSION_KNL_734 to free up some space
Matthew Jones October 31, 2012 at 4:05 PM
Well there's already there, and the conversion can't work without them. So the options are:
1) Create the backup tables
2) Don't do the conversion
3) Test if the alternate conversion works without the delete (I was planning on trying that but testing on Oracle kinda stinks)
In KNL-725, the field was converted to TIMESTAMP WITH TIME ZONE, however this was reported by two people to cause problems with clusters going out of Sync.
This entire issue came up because both Omer (at rice)
http://collab.sakaiproject.org/pipermail/sakai-dev/2012-September/018931.html
And Jaco (At opencollab)
http://collab.sakaiproject.org/pipermail/sakai-dev/2012-September/019064.html
said that there were warnings about sessions getting closed and clusters leaving when the field on SAKAI_CLUSTER was converted by the 2.9.x script to "TIMESTAMP WITH TIME ZONE". It should fix the DST problem but caused this other problem.
Leaving it as DATETIME (the existing field type) would still leave the DST problem, converting to TIMESTAMP would give you increased precision but still leave the DST problem.
I believe that converting to TIMESTAMP WITH LOCAL TIME ZONE could potentially resolve both clustering and DST, or it might break still break the clustering. I'm going to commit this fix.
There was also a commit to KNL-734 which changed SAKAI_EVENT and SAKAI_SESSION which should also match this type.