This error occurs when you attempt to execute partition management operations (ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION) on a table that was not created with partitioning enabled. The fix depends on whether you need to add partitioning to the table or remove the partition statement from your script.
The "ERROR 1505: Partition management on non-partitioned table" error indicates that MySQL received a partition management command directed at a table that lacks partitioning. This error is raised when you try to execute any ALTER TABLE statement with partition-specific operations on a regular, non-partitioned table. MySQL returns this error when: - **ADD PARTITION**: Attempting to add a new partition to a table without a PARTITION BY clause in its definition - **DROP PARTITION**: Trying to drop a partition from a non-partitioned table - **REORGANIZE PARTITION**: Attempting to reorganize or coalesce partitions when the table has none - **Other partition operations**: Any maintenance commands like ANALYZE PARTITION or CHECK PARTITION on tables without partitions The error typically appears in automation scripts, migration tools, or maintenance jobs that assume a table is partitioned without first verifying its actual structure. The solution involves either adding partitioning to the table if needed, or removing/conditionalizing the partition statement if the table should remain non-partitioned.
First, verify whether the table is partitioned by querying the INFORMATION_SCHEMA:
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;If the query returns no results (except a single NULL entry), the table is not partitioned.
Example output for non-partitioned table:
TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | SUBPARTITION_METHOD
your_table | NULL | NULL | NULLExample output for partitioned table:
TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | SUBPARTITION_METHOD
users | p0 | RANGE | NULL
users | p1 | RANGE | NULL
users | pmax | RANGE | NULLUse SHOW CREATE TABLE to see the full table definition:
SHOW CREATE TABLE your_tableGNon-partitioned table example:
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATETIME,
amount DECIMAL(10,2)
) ENGINE=InnoDB;Partitioned table example:
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
);The absence of a PARTITION BY clause confirms the table is not partitioned.
If you need to partition the table, use ALTER TABLE with the PARTITION BY clause. This requires a full table lock.
For RANGE partitioning by date:
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
);For RANGE partitioning by numeric column:
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
);For HASH partitioning (simpler, distributes evenly):
ALTER TABLE your_table
PARTITION BY HASH(id)
PARTITIONS 8;Important considerations:
- The table will be locked during partitioning conversion (can take minutes for large tables)
- Plan this during a maintenance window for production databases
- The primary key (if composite) must include the partitioning column
- Test on a copy of the table first
Monitor partitioning progress:
-- Check if the ALTER TABLE is still running
SHOW PROCESSLIST;
-- Estimate time remaining
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID = <your_query_id>;If the table should remain non-partitioned, remove or comment out the partition management statement from your script.
Identify the problematic statement:
Look for ALTER TABLE statements with these clauses:
- ALTER TABLE ... ADD PARTITION ...
- ALTER TABLE ... DROP PARTITION ...
- ALTER TABLE ... REORGANIZE PARTITION ...
- ALTER TABLE ... COALESCE PARTITION ...
- ALTER TABLE ... ANALYZE PARTITION ...
Remove or conditionally execute:
Option 1: Delete the statement entirely
-- DELETE THIS LINE (or comment it out):
-- ALTER TABLE your_table ADD PARTITION (PARTITION p_new VALUES LESS THAN (5000));Option 2: Make it conditional (better for multi-environment scripts)
-- 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;Option 3: Version-specific conditionals in migration tools:
For tools like Flyway or Liquibase, use separate migration files:
migrations/
├── V1__create_orders_table.sql # Non-partitioned
├── V2__partition_orders_conditional.sql # Only if needed
└── schema-validation.sql # Verifies environmentFor maintenance jobs that run partition operations monthly, add checks to ensure the table is actually partitioned:
In Python (scheduled job):
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")In Bash (cron job):
#!/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"Before applying the fix to production, test on a development or staging environment:
Test partitioning operation safely:
# 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;"Test conditional script:
# 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;"For large tables, limit the impact:
-- 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 changesMySQL Partitioning Overview:
Partitioning allows large tables to be divided into smaller, more manageable pieces while maintaining a single logical table interface. Common partitioning schemes:
- RANGE: Partition by numeric ranges or dates (e.g., partitions by year, month)
- LIST: Partition by discrete values (e.g., partition by region or status)
- HASH: Auto-distribute rows based on hash function (even distribution, less control)
- KEY: Similar to HASH but uses MySQL's internal key hash function
Benefits of partitioning:
- Query optimization: Queries can skip entire partitions (partition pruning)
- Easier maintenance: Drop old partitions instead of DELETE operations
- Improved performance for range queries on large tables
Limitations:
- Foreign keys not supported across partitions
- All unique keys must include the partition column
- Cannot directly convert back: REMOVE PARTITIONING requires full table rebuild
- Adds operational complexity
When NOT to partition:
- Tables < 1GB (performance gains negligible)
- Tables without range-based queries (WHERE on partition column)
- When development environment doesn't match production schema
- Without proper automation for partition maintenance
Common ERROR 1505 scenarios:
1. Development → Production migration: Dev has partitioned tables, production doesn't. Migration scripts fail.
- Solution: Check target environment schema before running partition operations
2. Generic maintenance scripts: Cron jobs run partition operations on all tables.
- Solution: Add INFORMATION_SCHEMA checks as shown above
3. Copy-paste errors: Schema.sql defines partitions, but production table predates this.
- Solution: Version control all DDL and audit schema differences quarterly
4. Automation framework assumptions: ORMs or migration tools assume partitioning.
- Solution: Configure environment-specific settings or disable partition operations for non-partitioned tables
Monitoring partition health:
-- Check partition distribution
SELECT
PARTITION_NAME,
PARTITION_EXPRESSION,
PARTITION_DESCRIPTION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders'
ORDER BY PARTITION_ORDINAL_POSITION;
-- Identify unbalanced partitions
SELECT
PARTITION_NAME,
TABLE_ROWS,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders'
AND PARTITION_NAME IS NOT NULL
ORDER BY TABLE_ROWS DESC;Performance impact of partitioning errors:
A failed partition operation doesn't corrupt the table, but repeated failures in a maintenance loop can cause:
- Disk space issues from failed temporary tables
- Application errors if maintenance is part of deployment
- Log file bloat from repeated error attempts
Always monitor error logs and alert on ERROR 1505 in production environments.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions