Sample Header Ad - 728x90

Why does creating an index on MySQL(InnoDB) and then crashing deliberately in between not result in a rollback?

1 vote
1 answer
81 views
I created this table USERS having `10 million records
mysql> desc users;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | NO   |     | NULL    |       |
| name   | varchar(255) | NO   |     | NULL    |       |
| email  | varchar(255) | NO   |     | NULL    |       |
| gender | varchar(10)  | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
Now I have 2 terminal sessions, session_1, and session_2 In session_1, I ran this command
mysql> alter table users add  primary key(id);
While this alter command in session_1 was still in progress, In session 2, I kill the mysql client using
kill -9
When I restart the mysql client, I issue the desc USERS once more, I don't see any Primary Key on id column, but after a minute or so, I see that the primary key is there on id column.
mysql> desc users;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | NO   | PRI | NULL    |       |
| name   | varchar(255) | NO   |     | NULL    |       |
| email  | varchar(255) | NO   |     | NULL    |       |
| gender | varchar(10)  | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
I have also tried with set autocommit=0
Asked by catsarethebest (13 rep)
Dec 31, 2023, 08:49 AM
Last activity: Dec 31, 2023, 06:35 PM