Sample Header Ad - 728x90

mysql table get lock on adding new field

0 votes
3 answers
628 views
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