How to fix ERROR 1505: Partition management on non-partitioned table in MySQL

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, SUBPARTITION_METHOD
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table'
AND PARTITION_NAME IS NOT NULL;

TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | SUBPARTITION_METHOD
your_table | NULL           | NULL             | NULL

TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | SUBPARTITION_METHOD
users      | p0             | RANGE            | NULL
users      | p1             | RANGE            | NULL
users      | pmax           | RANGE            | NULL

SHOW CREATE TABLE your_tableG

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATETIME,
  amount DECIMAL(10,2)
) ENGINE=InnoDB;

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATETIME,
  amount DECIMAL(10,2)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

ALTER TABLE your_table
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

ALTER TABLE user_logs
PARTITION BY RANGE (user_id) (
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (3000),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

ALTER TABLE your_table
PARTITION BY HASH(id)
PARTITIONS 8;

-- Check if the ALTER TABLE is still running
SHOW PROCESSLIST;

-- Estimate time remaining
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = <your_query_id>;

-- DELETE THIS LINE (or comment it out):
-- ALTER TABLE your_table ADD PARTITION (PARTITION p_new VALUES LESS THAN (5000));

-- Only run partition operations on tables that have partitions
SET @has_partitions = (
  SELECT COUNT(*) > 0
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'your_table'
  AND PARTITION_NAME IS NOT NULL
);

IF @has_partitions THEN
  ALTER TABLE your_table ADD PARTITION (PARTITION p_new VALUES LESS THAN (5000));
END IF;

migrations/
├── V1__create_orders_table.sql           # Non-partitioned
├── V2__partition_orders_conditional.sql  # Only if needed
└── schema-validation.sql                 # Verifies environment

import mysql.connector

def add_monthly_partition():
    conn = mysql.connector.connect(
        host="localhost",
        user="root",
        password="password",
        database="mydb"
    )
    cursor = conn.cursor()

    # Check if table is partitioned
    cursor.execute("""
        SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'my_table'
        AND PARTITION_NAME IS NOT NULL
    """)

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

    if not is_partitioned:
        print("Table is not partitioned. Skipping partition operation.")
        cursor.close()
        conn.close()
        return

    # Safe to add partition
    cursor.execute("""
        ALTER TABLE my_table
        ADD PARTITION (PARTITION p_new VALUES LESS THAN (9999))
    """)

    conn.commit()
    cursor.close()
    conn.close()
    print("Partition added successfully")

#!/bin/bash

MYSQL_USER="root"
MYSQL_PASS="password"
DB_NAME="mydb"
TABLE_NAME="orders"

# Check if table is partitioned
IS_PARTITIONED=$(mysql -u$MYSQL_USER -p$MYSQL_PASS $DB_NAME -e "
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = '$TABLE_NAME'
  AND PARTITION_NAME IS NOT NULL
" 2>/dev/null | tail -1)

if [ "$IS_PARTITIONED" -eq 0 ]; then
  echo "$(date): Table $TABLE_NAME is not partitioned. Skipping."
  exit 0
fi

# Add partition
mysql -u$MYSQL_USER -p$MYSQL_PASS $DB_NAME -e "
  ALTER TABLE $TABLE_NAME
  ADD PARTITION (PARTITION p_$(date +%Y%m) VALUES LESS THAN (5000))
"

echo "$(date): Partition added to $TABLE_NAME"

# Create a test copy of the table
mysql -u root -p mydb -e "CREATE TABLE test_orders LIKE orders;"

# Apply partitioning to the test table
mysql -u root -p mydb -e "
  ALTER TABLE test_orders
  PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
  );
"

# Verify it worked
mysql -u root -p mydb -e "
  SELECT TABLE_NAME, PARTITION_NAME
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'test_orders'
  AND PARTITION_NAME IS NOT NULL;
"

# If successful, apply to production
# If failed, drop test table and investigate
mysql -u root -p mydb -e "DROP TABLE test_orders;"

# First run with --dry-run or in a transaction
mysql -u root -p mydb -e "START TRANSACTION;"
# Run your migration/maintenance script here
# Check results with SELECT queries
# If OK: COMMIT; If not: ROLLBACK;
mysql -u root -p mydb -e "ROLLBACK;"

-- Check table size before partitioning
SELECT
  TABLE_NAME,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS size_gb,
  TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- For tables > 1GB, consider reducing lock time
-- with ALGORITHM=COPY for older MySQL versions
-- or using gh-ost for online schema changes