When you encounter the error `1231: Cannot update partition` in MySQL, it likely indicates an issue with the partitioning scheme or constraints on the partition that prevent an update operation. Here are actionable steps you can take immediately to investigate and possibly resolve the issue:
SHOW CREATE TABLE yourtablename;
CHECK TABLE yourtablename FOR UPGRADE PARTITION;
SELECT partitionname, tablerows, datalength, indexlength, data_free
FROM information_schema.partitions
WHERE tableschema = 'yourdatabasename' AND tablename = 'yourtablename';
SELECT partitionname, tablename, partitionordinalposition, partitionmethod, partitionexpression, tablerows, partitiondescription
FROM information_schema.partitions
WHERE tableschema = 'yourdatabasename' AND tablename = 'yourtablename' AND partitionname = 'yourpartition_name';
ALTER TABLE yourtablename REORGANIZE PARTITION partitiontoreorganize INTO (
PARTITION newpartitionname VALUES LESS THAN (your_value),
PARTITION otherpartitionname VALUES LESS THAN MAXVALUE
);
tail -f /path/to/mysql/log/error.log
Proceed with the investigation in the order that best aligns with your recent activities or changes to the database that could have led to this error.
Let Dr. Droid create custom investigation plans for your infrastructure.
Book Demo
