Description:
CREATE TABLE sessions(
sessionId int NOT NULL AUTO_INCREMENT,
startTime timestamp NOT NULL,
endTime timestamp,
userId int,
PRIMARY KEY (sessionId),
foreign KEY (userId) REFERENCES users(userId) ON DELETE cascade on update cascade
);
I create this table.
after i insert a element in that table and then i run this update;
update sessions
set endTime = VendTime
where sessionId = VsessionId;
and this update updates both startTime and endTime, and not only endTime as supposed to.
How to repeat:
CREATE TABLE sessions(
sessionId int NOT NULL AUTO_INCREMENT,
startTime timestamp NOT NULL,
endTime timestamp,
userId int,
PRIMARY KEY (sessionId),
foreign KEY (userId) REFERENCES users(userId) ON DELETE cascade on update cascade
);
DELIMITER $
CREATE PROCEDURE spGetSession(
IN Vcookie varchar(256), VentryTime timestamp)
Begin
declare VuserId int;
declare VlastSessionId int;
if(select count(cookie) from users where cookie = Vcookie) = 0 then
insert into users(cookie) value (Vcookie);
end if;
set VuserId = (select userId from users where cookie = Vcookie);
set VlastSessionId = (select max(sessionId) from sessions where userId = VuserId);
if(VlastSessionId = null) then
insert into sessions(startTime, userId) value(VentryTime, VuserId);
else if(select count(sessionId) from sessions where
(select count(sessionId) from sessions where (select endTime from sessions where sessionId = VlastSessionId) > VentryTime
or (select endTime from sessions where sessionId = VlastSessionId) ='0000-00-00 00:00:00')) = 0 then
insert into sessions(startTime, userId) value(VentryTime, VuserId);
end if;
end if;
select max(sessionId) from sessions where userId = VuserId;
end $
DELIMITER ;
call spGetSession('abcdefg', now());
DELIMITER %
CREATE PROCEDURE spEndSession(
IN Vcookie varchar(256), VendTime timestamp)
Begin
declare VuserId int;
declare VsessionId int;
set VuserId = (select userId from users where cookie = Vcookie);
set VsessionId = (select max(sessionId) from sessions where userId = VuserId);
update sessions
set endTime = VendTime
where sessionId = VsessionId;
end %
DELIMITER ;
call spEndSession('abcdefg', now());
Suggested fix:
Make the update change only the value he is suposed to.
Description: CREATE TABLE sessions( sessionId int NOT NULL AUTO_INCREMENT, startTime timestamp NOT NULL, endTime timestamp, userId int, PRIMARY KEY (sessionId), foreign KEY (userId) REFERENCES users(userId) ON DELETE cascade on update cascade ); I create this table. after i insert a element in that table and then i run this update; update sessions set endTime = VendTime where sessionId = VsessionId; and this update updates both startTime and endTime, and not only endTime as supposed to. How to repeat: CREATE TABLE sessions( sessionId int NOT NULL AUTO_INCREMENT, startTime timestamp NOT NULL, endTime timestamp, userId int, PRIMARY KEY (sessionId), foreign KEY (userId) REFERENCES users(userId) ON DELETE cascade on update cascade ); DELIMITER $ CREATE PROCEDURE spGetSession( IN Vcookie varchar(256), VentryTime timestamp) Begin declare VuserId int; declare VlastSessionId int; if(select count(cookie) from users where cookie = Vcookie) = 0 then insert into users(cookie) value (Vcookie); end if; set VuserId = (select userId from users where cookie = Vcookie); set VlastSessionId = (select max(sessionId) from sessions where userId = VuserId); if(VlastSessionId = null) then insert into sessions(startTime, userId) value(VentryTime, VuserId); else if(select count(sessionId) from sessions where (select count(sessionId) from sessions where (select endTime from sessions where sessionId = VlastSessionId) > VentryTime or (select endTime from sessions where sessionId = VlastSessionId) ='0000-00-00 00:00:00')) = 0 then insert into sessions(startTime, userId) value(VentryTime, VuserId); end if; end if; select max(sessionId) from sessions where userId = VuserId; end $ DELIMITER ; call spGetSession('abcdefg', now()); DELIMITER % CREATE PROCEDURE spEndSession( IN Vcookie varchar(256), VendTime timestamp) Begin declare VuserId int; declare VsessionId int; set VuserId = (select userId from users where cookie = Vcookie); set VsessionId = (select max(sessionId) from sessions where userId = VuserId); update sessions set endTime = VendTime where sessionId = VsessionId; end % DELIMITER ; call spEndSession('abcdefg', now()); Suggested fix: Make the update change only the value he is suposed to.