mysql: enum values are sometimes string, sometimes int

[pankajmyt]

Peerdb -> latest version
clickhouse -> latest version

clickhouse table that got created
CREATE TABLE myt.myt_twilio_sms
(
    `id` Int32,
   ....
   `message_sent_by` LowCardinality(String),
....
    `_peerdb_synced_at` DateTime64(9) DEFAULT now64(),
    `_peerdb_is_deleted` Int8,
    `_peerdb_version` Int64
)
ENGINE = ReplacingMergeTree(_peerdb_version)
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192

mysql table

CREATE TABLE `twilio_sms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `uuid` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message_sent_by` enum('sms','whatsapp') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'sms',
  ....
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
) ENGINE=InnoDB AUTO_INCREMENT=2435567 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

over a synced data for 1 month. on 15 days message_sent_by value is 'whatsapp' then it become 2 for couple of days. Then again its whatsapp.

---

Will like it to be consistent to run queries, and will prefer string.

[pankajmyt]

mysql --version
mysql Ver 8.0.41 for Linux on x86_64 (MySQL Community Server - GPL)

[serprex]

this is driven by binlog_row_metadata = full in cdc. in qrep we always receive the string info

[pankajmyt]

noted. though when its doing the initial snapshot. I believe it pulls from table. At that time the enum should stay string. This is happening during initial snapshot.

[ilidemi]

Current status: binlog_row_metadata = full is enforced in 8.x, so the values are strings in snapshot in CDC. After #4057, for 5.x we consistently insert them as integers.