Illegal Collation
Posted by:
Nirav Shah
Date: February 28, 2013 04:12AM
Below Procedure gives illegal collation in If condition.
If I change:
DATEDIFF(@dt,@end_date)< 7 to ADDDATE(@dt,7)< @end_date
It will through error Illegal collation in < condition.
If I change:
IN start_date VARCHAR(50), to IN start_date Date,
IN end_date VARCHAR(50) to IN end_date Date
It will give error while replicating data.. & replication failes.
I am using mysql 5.5 version. & I dont have clue that how i fix this in my all environment. As you can see that collation issue occures for variable comparision.. So database table is not needed to be checked.
DELIMITER $$
DROP PROCEDURE IF EXISTS `PopulateClassWeek`$$
CREATE PROCEDURE `PopulateClassWeek`(
IN classid VARCHAR(50),
IN start_date VARCHAR(50),
IN end_date VARCHAR(50)
)
BEGIN
DECLARE s_d, e_d DATE;
DECLARE chk BOOL;
DECLARE ExistingWn,CurrentWn INT;
SELECT @chk := IF (DATE(start_date) = st AND DATE(end_date) = ed, FALSE, TRUE)
FROM
(SELECT MIN(class_week_start_date) st ,MAX(class_week_end_date) ed
FROM lcs_analytics_dim_class_week
WHERE class_id = classid) A ;
IF @chk THEN
-- delete from lcs_analytics_dim_class_week where class_id = classid;
-- SELECT class_week_id UUID,class_week_no weekNo,class_id ,class_week_start_date week_start_date,class_week_end_date week_end_date FROM lcs_analytics_dim_class_week WHERE class_id=classid
-- insert into lcs_analytics_dim_class_week
SELECT @ExistingWn:= IF(MAX(class_week_no) IS NULL,0,MAX(class_week_no)) wn
FROM lcs_analytics_dim_class_week
WHERE class_id = classid ;
SELECT @CurrentWn := MAX(WeekNo) wn FROM (SELECT WeekNo, classid class_id, DATE(week_start_date) week_start, DATE(week_end_date) end_date FROM
(SELECT @rownum:=@rownum+1 AS WeekNo, @dt week_start_date,IF(DATEDIFF(@dt,@end_date)< 6,ADDDATE(@dt,6),@end_date) week_end_date, @dt:=IF(DATEDIFF(@dt,@end_date)< 7,ADDDATE(@dt,7),@end_date) FROM
(SELECT * FROM
(SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)a CROSS JOIN
(SELECT @strt_date:= start_date a,@end_date:= end_date b,@dt:=start_date c,@rownum:=0 )b) d)e
WHERE week_start_date < @end_date
)f ;
UPDATE lcs_analytics_dim_class_week cw,
(SELECT WeekNo, classid class_id, DATE(week_start_date) week_start, DATE(week_end_date) end_date FROM
(SELECT @rownum:=@rownum+1 AS WeekNo, @dt week_start_date,IF(DATEDIFF(@dt,@end_date)< 6,ADDDATE(@dt,6),@end_date) week_end_date, @dt:=IF(DATEDIFF(@dt,@end_date)< 7,ADDDATE(@dt,7),@end_date) FROM
(SELECT * FROM
(SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)a CROSS JOIN
(SELECT @strt_date:= start_date a,@end_date:= end_date b,@dt:=start_date c,@rownum:=0)b) d)e
WHERE week_start_date<@end_date
)f SET cw.class_week_start_date=f.week_start,cw.class_week_end_date=f.end_date WHERE cw.class_id=f.class_id AND cw.class_week_no=f.weekNo
;
IF @ExistingWn > @CurrentWn THEN
-- delete
DELETE FROM lcs_analytics_dim_class_week WHERE class_id = classid AND class_week_no>@CurrentWn;
ELSE
-- Insert
INSERT INTO lcs_analytics_dim_class_week(class_week_no,class_id,class_week_start_date,class_week_end_date) SELECT * FROM (SELECT WeekNo, classid class_id, DATE(week_start_date) week_start, DATE(week_end_date) end_date FROM
(SELECT @rownum:=@rownum+1 AS WeekNo, @dt week_start_date,IF(DATEDIFF(@dt,@end_date)< 6,ADDDATE(@dt,6),@end_date) week_end_date, @dt:=IF(DATEDIFF(@dt,@end_date)< 7,ADDDATE(@dt,7),@end_date) FROM
(SELECT * FROM
(SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)a CROSS JOIN
(SELECT @strt_date:= start_date a,@end_date:= end_date b,@dt:=start_date c,@rownum:=0 )b) d)e
WHERE week_start_date<@end_date
)f WHERE weekno > @ExistingWn;
END IF;
END IF;
END$$
DELIMITER ;