To query two system variables, you execute this statement and get the results shown:

When you execute the following two statements, only the first one succeeds:
SET @@autocommit=0;
SET @@max_connections=200;
Why does the second statement fail?

• A.max_connections is a derived variable that can only be set indirectly.
• B.max_connections is a read-only variable that cannot be set dynamically.
• C.max_connections must be set at server start in an options file.
• D.max_connections must be set globally instead of locally.

Comment: *

Name: *

Email: *

Verification: *

Multiversion Concurrency Control (MVCC) enables what type of backup?

• A.Hot
• B.Binary
• C.Logical
• D.Incremental

Comment: *

Name: *

Email: *

Verification: *

You are no longer able to log in to an existing MySQL Server because the root password credentials not working. You need to reset the root password to complete various administrative tasks. What are the two major methods that will achieve this? (Choose two.)

• A.Start the MySQL Server in --safe-mode, which only loads the privilege system for changes as data is inaccessible.
• B.Start the MySQL Server with reset-root-password in my.cnf, which will prompt you to enter a new root user password.
• C.Start the MySQL Server with --init-filepointing to SQL that executes an ALTER USER statement to change the root user password.
• D.Start the MySQL Server with --skip-grant-tables and execute SQL, which will update the root password.
• E.Start the MySQL Server with -initialize-insecureto force a password reset procedure on the command line.

Comment: *

Name: *

Email: *

Verification: *

old_alter_tableis disabled as shown.
mysql> SELECT @@old_alter_table;

Consider this statement on a RANGE-partitioned table:
mysql> ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing this statement?

• A.All data in p1 and p3 partitions is removed and the table definition is changed.
• B.Only the first partition (p1) will be dropped because only one partition can be dropped at any time.
• C.All data in p1 and p3 partitions is removed, but the table definition remains unchanged.
• D.It results in a syntax error because you cannot specify more than one partition in the same statement.

Comment: *

Name: *

Email: *

Verification: *

Consider the table people with the definition:

The application uses a query such as:
SELECT * FROM people WHERE YEAR(Birthday) = 1980;
The query is not using an index.
Which two methods can be used to allow the query to use an index?

• A.Change the WHERE clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31.
• B.Execute ANALYZE TABLE to update the index statistics.
• C.Add a functional index for YEAR(Birthday).
• D.Add a generated column calculating YEAR(Birthday) and index that column.
• E.Add FORCE INDEX (Birthday) to the query.

Comment: *

Name: *

Email: *

Verification: *