Results 1 to 5 of 5

Thread: Issue with incremental load from RDS

  1. #1
    tfindlay is offline Junior Member
    Join Date
    Jan 2018
    Posts
    4
    Rep Power
    0

    Issue with incremental load from RDS

    Hi Folks,

    I'd like to report an issue we have with Attunity Replicate. We are using an instance from the AWS Marketplace (/w supplied hourly licence) per:
    Code:
    00003576: 2018-01-19T02:50:08 [AT_GLOBAL       ]I:  Task Server Log (V6.0.0.238 EC2AMAZ-ALOK7HJ Microsoft Windows Server 2012  (build 9200) 64-bit, PID: 2940) started at Fri Jan 19 02:50:08 2018  (at_logger.c:2470)
    00003576: 2018-01-19T02:50:08 [AT_GLOBAL       ]I:  Licensed to AWS Marketplace - Attunity Replicate Hourly, permanent license, sources: (Oracle,SQLServer,MySQL,PostgreSQL,DB2LUW), targets: (Oracle,SQLServer,MySQL,PostgreSQL,Teradata,Redshift,Hadoop), all hosts  (at_logger.c:2473)
    We are attempting to replicate data from a MySQL RDS instance to a Redshift instance. When we setup the job, the full load works fine, it reports finding:
    Code:
    00006920: 2018-01-18T10:34:31 [SOURCE_CAPTURE  ]I:  Set position to initial context 'now'  (mysql_endpoint_capture.c:3080)
    00006920: 2018-01-18T10:34:31 [SOURCE_CAPTURE  ]I:  Setting position in binlog 'mysql-bin-changelog.009678' at 9178452  (mysql_endpoint_capture.c:785)
    at the end of the full load, it notes:
    Code:
    00006920: 2018-01-19T02:45:04 [SOURCE_CAPTURE  ]I:  > ROTATE_EVENT  (mysql_endpoint_capture.c:2959)
    00004872: 2018-01-19T02:46:08 [SORTER          ]I:  Final saved task state. Stream position mysql-bin-changelog.009874Am:143093:-1:143124:42408507220456:mysql-bin-changelog.009874Am:142947, Source id 4802602, next Target id 12401841, confirmed Target id 12400325   (sorter.c:655)
    00004008: 2018-01-19T02:46:12 [TASK_MANAGER    ]I:  Subtask #0 ended  (replicationtask_util.c:937)
    00004008: 2018-01-19T02:46:12 [TASK_MANAGER    ]I:  Task management thread terminated  (replicationtask.c:3105)
    00005016: 2018-01-19T02:46:12 [SERVER          ]I:  Client session (ID 7426) closed  (dispatcher.c:200)
    00005016: 2018-01-19T02:46:12 [UTILITIES       ]I:  The last state is saved to file 'C:\Program Files\Attunity\Replicate\data\tasks\Afterpay/StateManager/ars_saved_state_000001.sts' at Thu, 18 Jan 2018 15:46:11 GMT (1516290371809835)  (statemanager.c:601)
    Notice the additional characters at the end of the binlog filename ? We feel like they could be the root of the problem.

    Upon starting the incremental load, we see:
    Code:
    00000908: 2018-01-19T02:50:09 [SORTER          ]I:  Start the task using saved state. Start source from stream position mysql-bin-changelog.009874Am:143093:-1:143124:42408507220456:mysql-bin-changelog.009874Am:142947 and id 4802602. Confirmed target id is 12400325, next target id is 12401841  (sorter.c:449)
    00003900: 2018-01-19T02:50:09 [SOURCE_CAPTURE  ]I:  Resume TABLE_MAP at file 'mysql-bin-changelog.009874Am', pos '142947'  (mysql_endpoint_capture.c:3173)
    00003900: 2018-01-19T02:50:09 [SOURCE_CAPTURE  ]I:  Setting position in binlog 'mysql-bin-changelog.009874Am' at 142947  (mysql_endpoint_capture.c:785)
    00003900: 2018-01-19T02:50:09 [SOURCE_CAPTURE  ]I:  System var 'binlog_checksum' = 'CRC32'  (mysql_endpoint_capture.c:272)
    00003900: 2018-01-19T02:50:09 [SOURCE_CAPTURE  ]I:  Position was set in binlog 'mysql-bin-changelog.009874Am' at 142947  (mysql_endpoint_capture.c:810)
    00003900: 2018-01-19T02:50:09 [SOURCE_CAPTURE  ]I:  Error 1236 (Could not find first log file name in binary log index file) reading binlog. Try reconnect  (mysql_endpoint_capture.c:999)
    So.... obviously it just fails. We attempted to change to use the official MySQL ODBC drivers, but sadly the AWS Marketplace image isnt licensed for that feature, so we can only use the supplied driver.

    The problem is a little hard to visualize in the text above as the additional characters are in fact unicode I believe. If someone from Attunity can supply an email address I'm happy to send in the full logs.

    FWIW - We have considered modifying the state file with a hex editor to remove the extra data after the filename, but presumably we would be stuck if we stopped/started the process again as the state file would get overwritten with bad data again.

    If anyone has any suggestions of advice, please share.

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    Hello ,

    I try to duplicate the issue, but can not.

    CDC process is running correctly.

    1. could yo check your mysql-bin-changelog.009874Am ,,, what is this special character?

    2. for you MySQL RDS, what are the setting for :
    Binlog_format = ?? (should be = row)
    binlog_checksum = ?? (should be =none)
    binlog_rod_image = ?? (should be =full)

    Notes, i am using the native mySQL connector/ODBC 5.2.7 driver
    Last edited by stevenguyen; 01-22-2018 at 02:22 PM.

  3. #3
    tfindlay is offline Junior Member
    Join Date
    Jan 2018
    Posts
    4
    Rep Power
    0
    Hi Steven,

    Thanks for the reply.

    Quote Originally Posted by stevenguyen View Post
    1. could yo check your mysql-bin-changelog.009874Am ,,, what is this special character?

    I expect the binlog file name should end with the number, as it does at the start of the log, it appears only when after the full load and it then appears in the
    Code:
    sorter
    when it saves the state.

    I have see a range of other random text in there, always 4 bytes, per the example above, see this screen shot from a hex editor, the selected bytes should not be there.
    Name:  Screen Shot 2018-01-23 at 10.09.45 am.png
Views: 450
Size:  29.8 KB

    Quote Originally Posted by stevenguyen View Post
    2. for you MySQL RDS, what are the setting for :
    Binlog_format = ?? (should be = row)
    binlog_checksum = ?? (should be =none)
    binlog_rod_image = ?? (should be =full)
    Settings as follows:
    Code:
    binlog_format = ROW
    binlog_checksum = CRC32
    binlog_row_image = FULL
    The documentation (Attunity_Replicate.pdf) Setup & User Guide, page 162 suggests binlog_checksum could be either None or CRC32.

    Notes, i am using the native mySQL connector/ODBC 5.2.7 driver[/QUOTE]
    FWIW - We are using the supplied driver:
    Code:
    00003416:  2018-01-19T02:50:08 [METADATA_MANAGE ]I:  Driver 'MySQL ODBC 5.3 Unicode  Driver' is installed and will be used  (mysql_endpoint_imp.c:578)
    00003416: 2018-01-19T02:50:08 [METADATA_MANAGE ]I:  ODBC additional properties = '(null)'  (mysql_endpoint_imp.c:644)
    00003416:  2018-01-19T02:50:08 [METADATA_MANAGE ]I:  Connecting to MySQL through  ODBC connection string: DRIVER={MySQL ODBC 5.3 Unicode  Driver};SERVER=prod-rds-paylater-replica-db.ckd9apsfgq3q.ap-southeast-2.rds.amazonaws.com;port=3306;UID=attunity;PWD=***;DB=;initstmt=SET  time_zone='+00:00';Option=74448896;  (mysql_endpoint_imp.c:727)
    We are using terraform to create/destory the RDS instance, can share the scripts with you if that helps.

  4. #4
    tfindlay is offline Junior Member
    Join Date
    Jan 2018
    Posts
    4
    Rep Power
    0
    Hi Folks,

    Just a followup - I recreated our RDS instance in AWS and this time set the binlog_checksum parameter to None.

    I created a small mock table and put a few rows in it, it seems to work great. I was able to stop/start replication and it all works as expected.
    resource "aws_db_parameter_group" "rds_slave_instance_settings" {
    name = "${terraform.workspace}-${var.database_name}-parameter-group"
    family = "mysql5.6"
    parameter {
    name = "binlog_checksum"
    value = "NONE"
    }
    }

    Tomorrow I will do a larger load of production-like data (~200Gb) and see how that goes. Hopefully it was this simple.

  5. #5
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    299
    Rep Power
    6
    thank you for posting the update.

    notes in MYSQL RDS

    https://www.attunity.com/products/cl...es-amazon-ec2/


    Name:  mysqlrds.JPG
Views: 342
Size:  60.4 KB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •