Examine the statement which executes successfully: SET sql_mode=' NO_ENGINE_SUBSTITTJTION' ; You try to create a table with a storage engine that is not available. What will happen?

Question

Options

• AAn error occurs and the create table statement fails.
• BThe server will create the table but it will be unusable until the specified storage engine is
• CThe server will create the table but report an error when the first attempt to insert a row is
• DThe server will create the table using the default storage engine.

• A
  74% (14)
• B
  5% (1)
• C
  5% (1)
• D
  16% (3)

Explanation

Option A is correct because NO_ENGINE_SUBSTITUTION is a strict SQL mode that instructs MySQL to refuse the operation entirely if the requested storage engine is unavailable - the CREATE TABLE statement fails immediately with an error rather than silently proceeding.

Option D describes the opposite behavior: silent engine substitution is exactly what happens when NO_ENGINE_SUBSTITUTION is not enabled - MySQL quietly swaps in the default engine (InnoDB) without warning. Options B and C describe fictional half-states that MySQL doesn't actually produce; MySQL either creates the table successfully or rejects the statement outright - it never creates a broken or insert-blocked table as a result of a missing engine.

Memory tip: Read the mode name literally - NO_ENGINE_SUBSTITUTION means "do NOT substitute the engine," so if the engine is missing, MySQL does nothing and throws an error rather than substituting a fallback.

No community discussion yet for this question.