MySQL 8.0 - Problems with Update Query on Partitioned Table Using Row-based Replication
I'm deploying to production and I've been banging my head against this for hours... I've been banging my head against this for hours. I've spent hours debugging this and I've looked through the documentation and I'm still confused about I've looked through the documentation and I'm still confused about I’m working with an scenario when attempting to update records in a partitioned table in MySQL 8.0 while using row-based replication. The table is partitioned by date, and I want to update the status of records based on a specific condition. However, I'm working with the behavior `behavior Code: 1366. Incorrect integer value: 'NULL' for column 'status' at row 1` when executing my update query. Here’s the SQL I’m using: ```sql UPDATE my_table SET status = NULL WHERE created_at < NOW() - INTERVAL 30 DAY; ``` I’ve double-checked the data type of the `status` column, which is defined as `INT`. When I try to set the `status` to NULL, I receive the aforementioned behavior. I expected it to update those rows without any issues. To troubleshoot, I tried modifying the column to allow NULL values by executing the following: ```sql ALTER TABLE my_table MODIFY status INT NULL; ``` However, I still received the same behavior after this change. Additionally, I checked if the row-based replication could be affecting this behavior, but my replication setup seems fine. I also validated that there are indeed records that satisfy the `WHERE` condition. The current MySQL configuration has the following relevant settings: ```sql SHOW VARIABLES LIKE 'binlog_format'; ``` This returns `ROW`, confirming that I’m indeed using row-based replication. Has anyone encountered a similar scenario, or could there be something related to partitioning or replication configuration that I might be missing? Any insights would be greatly appreciated! My development environment is Ubuntu. What's the best practice here? My team is using Sql for this desktop app. How would you solve this? My development environment is CentOS. Any examples would be super helpful. I'm working in a Ubuntu 22.04 environment. Any pointers in the right direction? Hoping someone can shed some light on this.