Description:
+ view table replication problem
=== view table ==============================================================================
CREATE VIEW view_test AS
SELECT
r.rec_idx AS idx
,mc.mcom_idx AS mcom_idx
,mc.company_nm AS company_name
,r.title AS title
,UNIX_TIMESTAMP(r.edit_dt) AS edit_date
,CASE r.closing_dt
WHEN '9999-12-31 00:00:00' THEN '-1'
WHEN '9999-01-01 00:00:00' THEN '0'
ELSE UNIX_TIMESTAMP(r.closing_dt)
END AS closing_day
,UNIX_TIMESTAMP(r.boldstart_dt) AS bc_start
,UNIX_TIMESTAMP(r.boldend_dt) AS bc_end
,mc.company_cd AS company_kind
,r.education_cd AS final_edu
,r.career_cd AS career_level
,r.career_max AS career_limit1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=8,lc.code,NULL)),',,,'),',' ,1),',',-1) AS upjong /*업종*/
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,1),',',-1) AS jikjong1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,2),',',-1) AS jikjong2
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,3),',',-1) AS jikjong3
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,4),',',-1) AS jikjong4
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,5),',',-1) AS jikjong5
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,6),',',-1) AS jikjong6
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,7),',',-1) AS jikjong7
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,8),',',-1) AS jikjong8
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,9),',',-1) AS jikjong9
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',',10),',',-1) AS jikjong10
,c.com_idx AS united_company_idx
,c.sales_rank AS rank2004 /* */
,c.group_level AS group_level /* */
,c.stock_gb AS u_company_kind
,IF(IFNULL(c.foreign_comp_fl,'n')='y',1,IF(IFNULL(c.public_fl,'n')='y',2,NULL)) AS oversea /*A:1,B:2*/
,r.top_fl AS notice_ok
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,1),',',-1) AS area1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,2),',',-1) AS area2
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,3),',',-1) AS area3
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,4),',',-1) AS area4
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,5),',',-1) AS area5
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,6),',',-1) AS area6
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,7),',',-1) AS area7
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,8),',',-1) AS area8
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,9),',',-1) AS area9
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',',10),',',-1) AS area10
,r.sex_lim AS sex
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=2,lc.code,NULL)),',,,'),',',1),',',-1) AS job_type1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=2,lc.code,NULL)),',,,'),',',2),',',-1) AS job_type2
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=2,lc.code,NULL)),',,,'),',',3),',',-1) AS job_type3
,r.age_min AS age_limit1
,r.age_max AS age_limit2
,r.collect_cnt AS collect_number
,r.salary_cd AS salary_code
,IF(TRIM(mc.homepage)!='',mc.homepage,NULL) AS homepage
,r.read_cnt AS read_cnt
FROM
recruit r /* infomation */
,membercompany mc
LEFT OUTER JOIN company c ON mc.com_idx = c.com_idx
,recruitbranch rb
,lascode lc
WHERE r.closing_dt > NOW()
AND mc.mcom_idx = r.mcom_idx
AND rb.rec_idx = r.rec_idx
AND lc.kcode = rb.kcode
AND lc.tcode IN (1,2,8,9)
GROUP BY r.rec_idx
;
How to repeat:
==========================================================================================================
mysql version 5.0.67.
master server has created but slave server has not created.
mysql version 5.0.67 replication master binary log
=========================================================================================================
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test` AS CREATE
=========================================================================================================
Suggested fix:
mysql version 5.0.51a
master server has created and slave server has created.
mysql version 5.0.51a replication master binary log
=========================================================================================================
SET TIMESTAMP=1220323307/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
CREATE VIEW view_test AS
SELECT
r.rec_idx AS idx
,mc.mcom_idx AS mcom_idx
,mc.company_nm AS company_name
,r.title AS title
,UNIX_TIMESTAMP(r.edit_dt) AS edit_date
,CASE r.closing_dt
WHEN '9999-12-31 00:00:00' THEN '-1'
WHEN '9999-01-01 00:00:00' THEN '0'
ELSE UNIX_TIMESTAMP(r.closing_dt)
END AS closing_day
,UNIX_TIMESTAMP(r.boldstart_dt) AS bc_start
,UNIX_TIMESTAMP(r.boldend_dt) AS bc_end
,mc.company_cd AS company_kind
,r.education_cd AS final_edu
,r.career_cd AS career_level
,r.career_max AS career_limit1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=8,lc.code,NULL)),',,,'),',' ,1),',',-1) AS upjong /*업종*/
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,1),',',-1) AS jikjong1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,2),',',-1) AS jikjong2
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,3),',',-1) AS jikjong3
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,4),',',-1) AS jikjong4
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,5),',',-1) AS jikjong5
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,6),',',-1) AS jikjong6
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,7),',',-1) AS jikjong7
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,8),',',-1) AS jikjong8
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',' ,9),',',-1) AS jikjong9
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=9,lc.code,NULL)),',,,'),',',10),',',-1) AS jikjong10
,c.com_idx AS united_company_idx
,c.sales_rank AS rank2004 /* */
,c.group_level AS group_level /* */
,c.stock_gb AS u_company_kind
,IF(IFNULL(c.foreign_comp_fl,'n')='y',1,IF(IFNULL(c.public_fl,'n')='y',2,NULL)) AS oversea /*A:1,B:2*/
,r.top_fl AS notice_ok
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,1),',',-1) AS area1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,2),',',-1) AS area2
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,3),',',-1) AS area3
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,4),',',-1) AS area4
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,5),',',-1) AS area5
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,6),',',-1) AS area6
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,7),',',-1) AS area7
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,8),',',-1) AS area8
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',' ,9),',',-1) AS area9
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=1,lc.code,NULL)),',,,,,,,'),',',10),',',-1) AS area10
,r.sex_lim AS sex
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=2,lc.code,NULL)),',,,'),',',1),',',-1) AS job_type1
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=2,lc.code,NULL)),',,,'),',',2),',',-1) AS job_type2
,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(IF(lc.tcode=2,lc.code,NULL)),',,,'),',',3),',',-1) AS job_type3
,r.age_min AS age_limit1
,r.age_max AS age_limit2
,r.collect_cnt AS collect_number
,r.salary_cd AS salary_code
,IF(TRIM(mc.homepage)!='',mc.homepage,NULL) AS homepage
,r.read_cnt AS read_cnt
FROM
recruit r /* infomation */
,membercompany mc
LEFT OUTER JOIN company c ON mc.com_idx = c.com_idx
,recruitbranch rb
,lascode lc
WHERE r.closing_dt > NOW()
AND mc.mcom_idx = r.mcom_idx
AND rb.rec_idx = r.rec_idx
AND lc.kcode = rb.kcode
AND lc.tcode IN (1,2,8,9)
GROUP BY r.rec_idx
/*!*/;
DELIMITER ;
=========================================================================================================