MySQL Database Multiple Choice Questions on “Updating MySQL”.
1. Which upgrade involves exporting existing data using mysqldump?
a) inplace
b) logical
c) illogical
d) system
Answer: b
Clarification: The in-place upgrade involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.
2. Which upgrade involves shutting down the old MySQL version and replacing the old binaries?
a) inplace
b) logical
c) illogical
d) system
Answer: a
Clarification: The logical upgrade involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version.
3. Slow shutdown can be performed by setting innodb_fast_shutdown to _____________
a) 0
b) -1
c) 1
d) 2
Answer: a
Clarification: With a slow shutdown InnoDB performs a full purge and change buffer merge before shutting down. This ensures that data files are fully prepared in case of file format differences.
4. A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading.
a) True
b) False
Answer: a
Clarification: A procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table can damage the table.
5. The command to see the warning messages is ______________
a) DISPLAY WARNINGS
b) DISP WARNINGS
c) DISP WARNING
d) SHOW WARNINGS
Answer: d
Clarification: In MySQL, the default value conversions are reported as warnings for INSERT, REPLACE and UPDATE statements. The SHOW WARNINGS command is used after executing one of those statements to see the warning messages.
6. What is the SQL mode to check for divide by zero error?
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) ERROR_DIVIDE_BY_ZERO
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer: b
Clarification: To enable the check for divide by zero errors int all the storage engines, the SQL mode named ‘ERROR_FOR_DIVISION_BY_ZERO’ can be enabled. This is done by using SET sql_mode = ‘mode_name’.
7. The mode used to turn on strict mode and all of the additional restrictions is _____________
a) STRICT_ALL_TABLES
b) ERROR_FOR_DIVISION_BY_ZERO
c) TRADITIONAL
d) ERROR_WHEN_DIVIDE_BY_ZERO
Answer: c
Clarification: The ‘TRADITIONAL’ mode is used to enable the strict mode and all of the additional restrictions. It is done by the command SET sql_mod = ‘TRADITIONAL’; The other SQL modes have other functions.
8. The number of values returned from a stored procedure can be _______________
a) 0
b) 1
c) 2
d) 3
Answer: a
Clarification: In MySQL, unlike the stored functions, the stored procedures cannot return values. They can be used to perform calculations or produce the result sets passed back to the clients.
9. How would a stored function named PI() written in the database ‘sampdb’ be called?
a) PI()
b) sampdb.PI()
c) MySQL.PI()
d) db.PI()
Answer: b
Clarification: When a stored function is defined with the same name as a built in function, the function name should be qualified with the database name when it is invoked. The function ‘PI()’ is built in.
10. The privilege that must be given to the database to create a stored function or procedure is ____________
a) CREATE ROUTINE
b) CREATE METHOD
c) CREATE FUNCTION
d) CREATE PROCEDURE
Answer: a
Clarification: The ‘CREATE ROUTINE’ privilege must be given for the database in order to create a stored function or procedure. Stored functions and procedures always belong to a particular database.