Skip to content

When the order of fields in the data table is changed, the binlog log is confused. #905

Open
@ccke

Description

@ccke

Hello, when I perform the following steps:

  1. Start canal and monitor table A
  2. Change the field order of table A
  3. Debug binlog to change data and find that data confusion occasionally occurs.

I'm wondering what could be the reason for this? What needs to be done?

Activity

ccke

ccke commented on Aug 30, 2024

@ccke
Author

Table schema:
3fda74ce-9df4-40b4-8e06-aa48d1dd0c62

Canal table columns:
366b28a7-d91a-42ba-b446-99baec775cf5

Canal row values:
7be93867-e4d2-4907-898f-da2a61c76c99

lance6716

lance6716 commented on Aug 30, 2024

@lance6716
Collaborator

Change the field order of table A

This is done by a DDL? I think you can check if canal has captured this DDL and invalid the internal table structure. I'm not a user of canal interface, but I think it should be caused by table structure mismatch.

equnchen

equnchen commented on Dec 20, 2024

@equnchen
Contributor

I reproduced the problem.
1.DDL: change the field order => insert => revert the field order => insert
image
2.Start canal. After call Canal.StartFromGTID(), we will get the newest table schema,but the binlog data can old, results in mismatch of filed.
image

At present, what we can do is to minimize the canal's master-slave delay,ensure that there is less dirty data in the binlog.

dfradehubs

dfradehubs commented on Apr 3, 2025

@dfradehubs

Hello there!

Any news about this? We are getting also these errors in our tool https://github.com/freepik-company/binwatch.

When an alter table occurs, the index of e.Table.Columns and e.Rows[] are not in the same order 😓 .

Thanks!
Daniel.

dfradehubs

dfradehubs commented on Apr 3, 2025

@dfradehubs

Okay, it seems the issue occurs when you're trying to read binlogs older than the moment the schema change took place. This happens because Canal directly reads the current schema when it's unable to read it from the binlogs (since it doesn't have that data, from what I understood). Let me know if I'm correct; thank you very much! :)

lance6716

lance6716 commented on Apr 3, 2025

@lance6716
Collaborator

Okay, it seems the issue occurs when you're trying to read binlogs older than the moment the schema change took place. This happens because Canal directly reads the current schema when it's unable to read it from the binlogs (since it doesn't have that data, from what I understood). Let me know if I'm correct; thank you very much! :)

I think so. It's hard to get a history table structure in binlog.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions

      When the order of fields in the data table is changed, the binlog log is confused. · Issue #905 · go-mysql-org/go-mysql