29. Emoji in reviews fail to save
Reviews containing emoji fail with 'Incorrect string value' in MySQL. The column charset is utf8. What is the fix?
- A.Strip all non-English characters first
- B.Use utf8mb4; MySQL's utf8 has only 3 bytes
- C.Store reviews as BLOBs and decode on read
- D.Change the column's charset to latin1
Show answer
Answer: B. Use utf8mb4; MySQL's utf8 has only 3 bytes
In MySQL, utf8 is an alias for utf8mb3, which only covers characters up to 3 bytes. Emoji and some rare scripts need 4 bytes. Convert the database, tables, columns and connection to utf8mb4. latin1 cannot store Arabic or Hindi at all.
30. InnoDB gap locks
In MySQL InnoDB at the default REPEATABLE READ level, session A runs SELECT * FROM t WHERE id BETWEEN 10 AND 20 FOR UPDATE inside a transaction. Session B tries INSERT INTO t (id) VALUES (15), and no row with id 15 exists. What happens?
- A.The insert succeeds at once because id 15 was not locked
- B.The insert waits, because InnoDB took next-key (gap) locks on the range to prevent phantoms
- C.The insert succeeds but session A gets a dirty read error
- D.MySQL raises a duplicate key error
Show answer
Answer: B. The insert waits, because InnoDB took next-key (gap) locks on the range to prevent phantoms
For locking reads on a range, InnoDB REPEATABLE READ uses next-key locks, which lock index records and the gaps between them. This blocks inserts into the range so the locking read cannot see phantom rows. It is a common, surprising source of lock waits and deadlocks; READ COMMITTED mostly disables gap locking.
31. Faster commits with a durability cost
A team sets innodb_flush_log_at_trx_commit = 2 to speed up writes. What is the real risk?
- A.Data can be lost if mysqld itself crashes, but never on a power failure
- B.Transactions are no longer atomic
- C.Up to about one second of committed transactions can be lost if the OS crashes or power fails, because the log is written to the OS cache at commit but flushed to disk only once a second
- D.There is no risk; it only changes logging verbosity
Show answer
Answer: C. Up to about one second of committed transactions can be lost if the OS crashes or power fails, because the log is written to the OS cache at commit but flushed to disk only once a second
With value 1 the redo log is written and fsynced at every commit, giving full durability. With 2 it is written to the OS at commit, so a mysqld crash loses nothing, but an OS crash or power loss can lose about the last second. With 0 even a mysqld crash can lose data. Pair the choice with sync_binlog when replication consistency matters.
32. Where old row versions are kept
How do PostgreSQL and MySQL InnoDB differ in storing old row versions for MVCC?
- A.Both keep old versions only in the WAL or redo log
- B.PostgreSQL keeps them in the table; InnoDB updates in place and keeps undo
- C.PostgreSQL uses undo logs; InnoDB writes new rows in the table
- D.Neither keeps old versions; both lock rows for every read
Show answer
Answer: B. PostgreSQL keeps them in the table; InnoDB updates in place and keeps undo
PostgreSQL writes a new tuple per update and leaves the old one in the heap until VACUUM removes it, which is why bloat happens. InnoDB changes the row in place and writes the old version to undo logs, which purge cleans. Both give readers snapshots without read locks.
33. MySQL ignores an index on phone
In MySQL, phone is a VARCHAR column with an index. The query below does a full table scan. Why?
SELECT * FROM customers WHERE phone = 9876543210;
- A.Comparing text to a number converts every row
- B.VARCHAR columns cannot have B-tree indexes
- C.The index is only used with ORDER BY
- D.Numbers must be compared with LIKE
Show answer
Answer: A. Comparing text to a number converts every row
When a string column is compared with a numeric literal, MySQL converts the column value of every row to a number, so the index cannot be used and the results can match odd values like '9876543210abc'. Quote the value: WHERE phone = '9876543210'.
34. Top queries without a slow log
The MySQL slow log is off and you cannot restart. Where can you see which statements use the most total time since startup?
- A.information_schema.TABLES
- B.SHOW PROCESSLIST
- C.mysql.user
- D.The sys.statement_analysis view
Show answer
Answer: D. The sys.statement_analysis view
performance_schema aggregates statement digests with counts, total latency and rows examined, and the sys schema shows them in readable views such as statement_analysis. SHOW PROCESSLIST shows only what is running now. information_schema.TABLES has table sizes, not query costs.
35. Old replication commands fail on 8.4
A failover script runs CHANGE MASTER TO and SHOW SLAVE STATUS against MySQL 8.4 and gets syntax errors. Why?
- A.They were removed; use the SOURCE/REPLICA forms
- B.Replication is not supported on MySQL 8.4
- C.They now need the SUPER privilege first
- D.They work only over the X Protocol port
Show answer
Answer: A. They were removed; use the SOURCE/REPLICA forms
MySQL 8.0.22 introduced CHANGE REPLICATION SOURCE TO, SHOW REPLICA STATUS and START REPLICA, and 8.4 removed the old MASTER and SLAVE forms. Scripts and monitoring checks must be updated before upgrading. Output column names changed too, such as Source_Host.
36. SELECT and SELECT FOR UPDATE disagree
In MySQL InnoDB at Repeatable Read, a transaction reads stock = 10 with a plain SELECT. Another transaction commits stock = 4. The first then runs SELECT stock ... FOR UPDATE. What does it return?
- A.10, because the snapshot is fixed for it
- B.An error, as its snapshot is too old
- C.4, as locking reads see the latest commit
- D.NULL, since the row changed
Show answer
Answer: C. 4, as locking reads see the latest commit
In InnoDB, plain SELECTs use the transaction's consistent snapshot, but locking reads (FOR UPDATE, FOR SHARE) and UPDATE/DELETE read and lock the latest committed version. So the same transaction can see 10 and then 4. Base writes on the locking read, not the earlier snapshot.
37. One UPDATE blocks the whole table
In MySQL InnoDB at Repeatable Read, UPDATE orders SET flag = 1 WHERE note = 'x' runs where note has no index. Other sessions now cannot update any order row. Why?
- A.MySQL takes a table lock for every UPDATE
- B.The binary log blocks updates until it rotates
- C.Unindexed columns turn on SERIALIZABLE mode
- D.InnoDB locks every row it scans, which is the whole table
Show answer
Answer: D. InnoDB locks every row it scans, which is the whole table
InnoDB sets locks on the index records it examines, not just the rows that match. Without an index on note, it scans the clustered index and locks every row plus the gaps. Adding an index on the filter column limits locks to matching rows.
38. An idle MySQL session blocks ALTER
A tool connects to MySQL with autocommit = 0, runs one SELECT on orders and sits idle. Later ALTER TABLE orders hangs with "Waiting for table metadata lock". Why?
- A.SELECTs take a table write lock in MySQL
- B.Its open transaction holds a metadata lock
- C.ALTER TABLE always waits for idle sessions
- D.The binary log is full
Show answer
Answer: B. Its open transaction holds a metadata lock
With autocommit off, the SELECT started a transaction, and InnoDB keeps a shared metadata lock on every table used until it ends. ALTER needs an exclusive metadata lock, so it waits, and new queries queue behind it. Find the session in performance_schema.metadata_locks and end it.
39. Deadlock from two concurrent inserts
In MySQL at Repeatable Read, two sessions each run SELECT ... FOR UPDATE for a missing id in the same gap, then each INSERT that id range. Both block and one is killed as a deadlock victim. Why?
- A.Each holds a gap lock that blocks the other's insert
- B.Inserts always deadlock in MySQL
- C.FOR UPDATE on a missing row takes a table lock
- D.The auto-increment counter deadlocked
Show answer
Answer: A. Each holds a gap lock that blocks the other's insert
A locking read that finds no row still locks the gap. Gap locks from two sessions are compatible, but an insert needs an insert intention lock that conflicts with the other's gap lock, so each waits on the other. Use INSERT ... ON DUPLICATE KEY UPDATE or a unique key with retry instead.
40. REPLACE INTO removed child rows
In MySQL, a sync job runs REPLACE INTO customers (...) for existing customers. Afterwards their orders, linked with ON DELETE CASCADE, are gone. Why?
- A.REPLACE truncates the whole table first
- B.Cascades run on every UPDATE in MySQL
- C.REPLACE turns off all foreign key checks
- D.REPLACE deletes the old row and inserts anew
Show answer
Answer: D. REPLACE deletes the old row and inserts anew
When a key already exists, REPLACE deletes the old row and inserts a new one, so delete triggers and ON DELETE CASCADE fire. That wipes children and can also change auto-increment IDs. INSERT ... ON DUPLICATE KEY UPDATE changes the row in place instead.
