MySQL 8.0 Database Administrator — Question 29

Examine these statements, which execute successfully:
TRUNCATE test;
BEGIN;
INSERT INTO test(id, name) VALUES(1, “Hello”);
ROLLBACK;
SELECT id FROM test;
Which three storage engines would return a nonempty recordset for the test table when executing the statements? (Choose three.)

Answer options

• A. NDB
• B. ARCHIVE
• C. InnoDB
• D. BLACKHOLE
• E. MEMORY
• F. MyISAM

Correct answer: B, E, F

Explanation

The ARCHIVE, MEMORY, and MyISAM storage engines allow for the TRUNCATE command to remove all rows while still enabling the SELECT statement to return results when there are no active transactions affecting the table. In contrast, InnoDB would not return any records due to its transactional nature, and BLACKHOLE does not store any data, thus returning an empty set.