How to fix ERROR 1507: Error in list of partitions in MySQL

SHOW CREATE TABLE your_table_name\G

SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table_name'
ORDER BY PARTITION_ORDINAL_POSITION;

-- WRONG: partition might not exist
ALTER TABLE sales DROP PARTITION p_2024_old;

-- RIGHT: verify first, then drop
ALTER TABLE sales DROP PARTITION p2024;

-- Combine old partitions into new ones
ALTER TABLE sales REORGANIZE PARTITION p2022, p2023
INTO (
  PARTITION p202201 VALUES LESS THAN (202203),
  PARTITION p202204 VALUES LESS THAN (202301)
);

-- Optimize specific partitions
ALTER TABLE sales OPTIMIZE PARTITION p2024, p2025;

ALTER TABLE sales DROP PARTITION IF EXISTS p_2024_old;

SELECT VERSION();

#!/bin/bash
PARTITION_TO_DROP="p_2024_old"
DB="mydb"
TABLE="sales"

# Check if partition exists
EXISTS=$(mysql -N -e "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA='$DB'
    AND TABLE_NAME='$TABLE'
    AND PARTITION_NAME='$PARTITION_TO_DROP'
")

if [ "$EXISTS" -gt 0 ]; then
  echo "Partition exists, dropping..."
  mysql -e "ALTER TABLE $DB.$TABLE DROP PARTITION $PARTITION_TO_DROP"
  if [ $? -eq 0 ]; then
    echo "Successfully dropped partition $PARTITION_TO_DROP"
  else
    echo "ERROR: Failed to drop partition"
    exit 1
  fi
else
  echo "Partition $PARTITION_TO_DROP does not exist, skipping"
fi

import pymysql

def drop_partition_if_exists(db_conn, database, table, partition_name):
    cursor = db_conn.cursor()

    # Check if partition exists
    cursor.execute("""
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = %s
          AND TABLE_NAME = %s
          AND PARTITION_NAME = %s
    """, (database, table, partition_name))

    exists = cursor.fetchone()[0] > 0

    if exists:
        try:
            cursor.execute(f"ALTER TABLE {database}.{table} DROP PARTITION {partition_name}")
            db_conn.commit()
            print(f"Successfully dropped partition {partition_name}")
        except pymysql.Error as e:
            print(f"ERROR: {e}")
            db_conn.rollback()
            raise
    else:
        print(f"Partition {partition_name} does not exist, skipping")

    cursor.close()

-- Wrong: generates p2024_13 but partitions are p202413
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'p%Y_%m');

-- Right: format matches actual partitions
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), 'p%Y%m');

#!/bin/bash
# Drop yesterday's partition after archiving data

PARTITION_DATE=$(date -d 'yesterday' +%Y%m%d)
PARTITION_NAME="p_${PARTITION_DATE}"

# Verify this matches the actual partition scheme
echo "Attempting to drop partition: $PARTITION_NAME"

# Query to confirm it exists
mysql -N -e "
  SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME='sales' AND PARTITION_NAME='$PARTITION_NAME'
"

# Only drop if confirmed to exist
if [ $? -eq 0 ]; then
  mysql -e "ALTER TABLE mydb.sales DROP PARTITION $PARTITION_NAME"
fi

SHOW CREATE TABLE sales\G

SHOW CREATE TABLE sales\G

SHOW SLAVE STATUS\G

mysqlbinlog --start-position=<pos> /var/log/mysql/mysql-bin.000001 | grep -A5 "ALTER TABLE.*PARTITION"