Your MySQL server was upgraded from an earlier major version. The sales database contains three tables, one of which is the transactions table, which has 4 million rows. You are running low on disk…

Question

Exhibit

Options

• AExecuting SET GLOBAL innodb_row_format=COMPRESSED and then ALTER TABLE
• BExecuting ALTER TABLE transactions will enable you to free up disk space.
• CTruncating the sales and leads table will free up disk space.
• DTruncating the transactions table will free up the most disk space.
• EThe transactions table was created with innodb_file_per_table=OFF.

• A
  60% (18)
• B
  20% (6)
• C
  7% (2)
• D
  13% (4)

Explanation

Option E is correct because the MySQL server was upgraded from an older major version where innodb_file_per_table defaulted to OFF (prior to MySQL 5.6.7). This means the transactions table's data lives inside the shared system tablespace (ibdata1), which is critical context for every other choice.

Option A is correct because combining SET GLOBAL innodb_row_format=COMPRESSED with ALTER TABLE transactions will rebuild the table using InnoDB page compression and - since innodb_file_per_table now defaults to ON post-upgrade - move it into its own smaller .ibd file, genuinely reducing disk usage on the partition.

Option B is wrong because a plain ALTER TABLE without first enabling compression would simply rebuild the table at roughly the same size; no meaningful disk space is reclaimed this way.

Option C is wrong because the sales and leads tables are presumably small compared to the 4M-row transactions table, and truncating them provides negligible disk savings.

Option D is wrong - this is the key trap: because the transactions table lives in ibdata1 (per option E), TRUNCATE marks space as free inside the shared tablespace file but ibdata1 never shrinks, so no OS-level disk space is recovered.

Memory tip: "ibdata1 is a one-way street - data goes in but disk space never comes back." When innodb_file_per_table=OFF, your only path to reclaiming space is to rebuild with compression into a per-table file using ALTER TABLE.

No community discussion yet for this question.