Search code examples
mysqlreplicationmysqlbinlog

Do row-level binlog entries get recorded in MySQL when a non-null column with a default gets added

I wanted to verify the following behavior I noticed with MySQL row-based replication in case there was just something peculiar with our setup or configuration. With row-based replication turned on, and given the following table named pets:

| id |   name    |    species   |
|----|-----------|--------------|
| 1  |   max     |    canine    |   
| 2  |   spike   |    canine    |
| 3  |   bell    |    feline    |

Any updates, deletes, or inserts are recorded in the binlog. However, if I were to add a non-null column with a default value, e.g.

ALTER TABLE `pets` 
ADD COLUMN `sex` varchar(7) NOT NULL DEFAULT "unknown" AFTER `species`;

The records are updated like so:

| id |   name    |    species   |  sex
|----|-----------|--------------|--------
| 1  |   max     |    canine    |  unknown
| 2  |   spike   |    canine    |  unknown
| 3  |   bell    |    feline    |  unknown

The behavior I initially expected was that an update would be recorded for each row (since each row undergoes change), and these updates would appear in the binlog. However, it actually appears that no row-level events are being written to the binlog at all when the new column and default values are added.

Anyways, the questions I have are:

  1. Is this behavior expected, or is this indicative of some issue with our setup (or my observational skills)?
  2. Is this behavior configurable in any way?

Any information, links, resources, etc will be greatly appreciated.

Thanks,

Solution

  • As mysql documentation on binlog format setting says (emphasis is mine):

    With the binary log format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

    To be honest, your train of thoughts did not seem logical to me, replicating such operations through updates just seemed completely inefficient to me. I know that some complex ddl-dml statements may be partially be replicated through a series of insert / updates, but this does not apply here.