Bug #39171 view table replication problem(mysql version 5.0.67)

Submitted: 2 Sep 2008 3:46 Modified: 26 Jan 2010 9:18
Reporter: ilhwan kim Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.67 OS:Linux (centos 4.6 64bit)
Assigned to: CPU Architecture:Any

[2 Sep 2008 3:46] ilhwan kim
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 ;
=========================================================================================================
[2 Sep 2008 4:32] Valeriy Kravchuk
Thank you for a problem report. Please, send CREATE TABLE statements for all the tables used to build that view.
[2 Oct 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[19 Jan 2010 8:11] Felix Frank
I reproduced on the following system: 
$ uname -a 
Linux sles 2.6.27.39-0.3-default #1 SMP 2009-11-23 12:57:38 +0100 x86_64 x86_64 x86_64 GNU/Linux
$ rpm -q mysql
mysql-5.0.67-13.16.1

Queries:
CREATE TABLE HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW ( id INTEGER );
DROP VIEW IF EXISTS `TEST_VIEW`;
CREATE ALGORITHM=UNDEFINED DEFINER=`aeadmin`@`%` SQL SECURITY DEFINER VIEW `TEST_VIEW` AS select
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY000`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY0001`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY2`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0003`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0004`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0005`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0006`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0007`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0008`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY0009`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00010`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00011`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00012`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00013`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00014`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00015`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00016`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00017`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00018`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00019`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00022`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00023`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00024`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00025`,
`HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`.`ID` `DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY_DUMMY00026`
from `HILARIOUSLY_LONG_TABLE_NAME_THAT_SAVES_INPUT_LINES_DURING_VIEW`;

These generate a corrupt binlog. The view is created correctly on the local system, but replication breaks. This is a showstopper in an otherwise running replication setup of mine.

By removing but one character from any of the aliases, one can have the last query generate a valid binlog entry. The length of the table names is known to have an impact, as do other factors (this was originally observed with a view that had less columns, but greater complexity per column). I was unable to derive a satisfactory theory on the exact dependencies, though.
[19 Jan 2010 8:27] Felix Frank
Dump with CREATE VIEW query to break binlogging.

Attachment: long-table-error.sql (application/octet-stream, text), 3.43 KiB.

[26 Jan 2010 9:18] Sveta Smirnova
Thank you for the feedback.

Bug is fixed in version 5.0.89. Please upgrade.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.057 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.