We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this)
ALTER TABLE posts_topic
ADD COLUMN hindi_description
longtext NULL, LOCK=NONE;
SHOW FULL PROCESSLIST;
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 6044590 | Waiting on empty queue | NULL |
| 1511667 | admin | 172.31.32.92:52604 | orchestra_cms | Sleep | 1221 | | NULL |
| 1589284 | rdsadmin | localhost | NULL | Sleep | 11 | | NULL |
| 1678479 | admin | 172.31.32.92:47982 | orchestra_cms | Sleep | 8465 | | NULL |
| 1680873 | admin | 172.31.32.92:60246 | orchestra_cms | Query | 286 | Waiting for table metadata lock | ALTER TABLE posts_topic
ADD COLUMN hindi_description_11
longtext NULL, LOCK=NONE |
| 1680907 | admin | 172.31.32.92:51524 | orchestra_cms | Query | 0 | init | SHOW FULL PROCESSLIST |
| 1681014 | admin | 172.31.32.92:51602 | orchestra_cms | Query | 135 | Waiting for table metadata lock | SELECT (1) AS a
FROM posts_posttopic
INNER JOIN posts_topic
ON (posts_posttopic
.topic_id
= posts_topic
.id
) WHERE (NOT (posts_posttopic
.object_status
= 0) AND posts_posttopic
.post_id
= 'kuqDLdvyodbd' AND posts_topic
.name
LIKE 'TOPNEWS') LIMIT 1 |
| 1681019 | admin | 172.31.32.92:43246 | orchestra_cms | Sleep | 135 | | NULL |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 13578859
Purge done for trx's n:o 0;
+---------------+-------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+---------------+-------------+--------+-------------+
| orchxxxxx_xxx | posts_topic | 1 | 0 |
+---------------+-------------+--------+-------------+
CREATE TABLE posts_posttopic
(
id
int NOT NULL AUTO_INCREMENT,
object_status
smallint NOT NULL,
leaning
smallint unsigned DEFAULT NULL,
leaning_direction
smallint unsigned NOT NULL,
created_on
datetime(6) NOT NULL,
post_id
varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
topic_id
varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id
),
KEY posts_posttopic_post_id_73ce25a9_fk_posts_post_id
(post_id
),
KEY posts_posttopic_topic_id_04b92641_fk_posts_topic_id
(topic_id
),
CONSTRAINT posts_posttopic_post_id_73ce25a9_fk_posts_post_id
FOREIGN KEY (post_id
) REFERENCES posts_post
(id
),
CONSTRAINT posts_posttopic_topic_id_04b92641_fk_posts_topic_id
FOREIGN KEY (topic_id
) REFERENCES posts_topic
(id
),
CONSTRAINT posts_posttopic_chk_1
CHECK ((leaning
>= 0)),
CONSTRAINT posts_posttopic_chk_2
CHECK ((leaning_direction
>= 0))
) ENGINE=InnoDB AUTO_INCREMENT=1945751 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE posts_topic
(
object_status
smallint NOT NULL,
id
varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
name
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
post_count
int NOT NULL,
display_name
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
slug
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
image_url
varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
description
longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
is_qualified
tinyint(1) NOT NULL,
created_by
varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
created_on
datetime(6) NOT NULL,
updated_on
datetime(6) NOT NULL,
is_section
int NOT NULL DEFAULT '0',
is_language
smallint NOT NULL DEFAULT '0',
PRIMARY KEY (id
),
KEY posts_topic_slug_8ebc1796
(slug
),
KEY is_section
(is_section
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
If I clone the table posts_topic as posts_topic_copy and add field its take no time. might be issue with foreign key between posts_topic and posts_post_topic table.
Im not sure why but as i killed this processesi its worked
| 1511667 | admin | 172.31.32.92:52604 | orchestra_cms | Sleep | 1221 | | NULL |
| 1589284 | rdsadmin | localhost | NULL | Sleep | 11 | | NULL |
Asked by NARESH KUMAR TELI
(1 rep)
Nov 7, 2022, 06:42 AM
Last activity: Jun 24, 2025, 08:04 PM
Last activity: Jun 24, 2025, 08:04 PM