1

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 <mysql_session_id>

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

1 Answer 1

3

Before version 8.0, DDL statements (such as ALTER) were performed without transactional ACID. (That is, autocommit and BEGIN..COMMIT were not honored.)

CREATE TABLE tmp LIKE real;
ALTER tmp as per your statement
INSERT INTO tmp SELECT * FROM real;
RENAME TABLE real TO old, tmp TO real;
DROP TABLE old;

If the client went away or the system crashed before the RENAME, the process terminated. (Often the "tmp" was left on disk.)

The RENAME is fast and essentially atomic, so once it gets through that, the action is finished.

(There are some locks that I did not list.)

MySQL 8.0 does the same, but with BEGIN and COMMIT around those statements, plus has new code to be able to ROLLBACK.

Back to your question... Effectively, the old code had nearly the effect of the new code in that killing the client probably happened before the RENAME, hence it looked like it was rolled back.

Note also that the INSERT statement takes the longest amount of time -- because it must copy all the data over, possibly in a different order. (The data is stored in a B+Tree based on the Primary Key.) It will also rebuild all the secondary INDEXes

What version are you running? Perhaps you are running 8.0 and your ALTER had really finished, but it took time for the locks to get in place. This might explain the delay in DESC showing it. (I don't think any Oracle developers are watching this forum, so you may not get a definitive answer here. You could post a bug at bugs.mysql.com in hopes of getting an answer.)

1
  • $mysql -V mysql Ver 8.0.35-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu)) ` Jan 1 at 7:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.