1Z0-883 Online Practice Questions and Answers

Consider the following statement on a RANGE partitioned table:

ALTER TABLE orders DROP PARTITION p1, p3;

What is the outcome of executing the above statement?

A. Only the first partition (p1) will be dropped as only one can be dropped at any time.

B. All data in p1 and p3 partitions are removed, but the table definition remains unchanged.

C. A syntax error will result as you cannot specify more than one partition in the same statement.

D. All data in pi and p3 partitions are removed and the table definition is changed.

You execute the following statement in a Microsoft Windows environment. There are no conflicts in the path name definitions.

C:

\> mysqld install Mysql56 defaults file = C : \my opts.cnf What is the expected outcome?

A.

Mysqld acts as an MSI installer and installs the Mysql 5.6 version, with the c: \my-opts.cnf configuration file.

B.

Mysql is installed as the Windows service name Mysql56, and uses c: \my-opts.cnf as the configuration file

C.

An error message is issued because install is not a valid option for mysqld.

D.

A running Mysql 5.6 installation has its runtime configuration updated with the server variables set in c: \my-opts.cnf.

Assume that you want to know which Mysql Server options were set to custom values. Which two methods would you use to find out?

A. Check the configuration files in the order in which they are read by the Mysql Server and compare them with default values.

B. Check the command-line options provided for the Mysql Server and compare them with default values.

C. Check the output of SHOW GLOBAL VARIABLES and compare it with default values.

D. Query the INFORMATION_SCHEMA.GLOBAL_VARIABLES table and compare the result with default values.

Consider the query:

Mysql> SET @run = 15;

Mysql> EXPLAIN SELECT objective, stage, COUNT (stage) FROM iteminformation

WHERE run=@run AND objective='7.1'

GROUP BY objective,stage

ORDER BY stage;

The iteminformation table has the following indexes; Mysql> SHOW INDEXES FROM iteminformation:

This query is run several times in an application with different values in the WHERE clause in a growing data set.

What is the primary improvement that can be made for this scenario?

A. Execute the run_2 index because it has caused a conflict in the choice of key for this query.

B. Drop the run_2 index because it has caused a conflict in the choice of key for this query.

C. Do not pass a user variable in the WHERE clause because it limits the ability of the optimizer to use indexes.

D. Add an index on the objective column so that is can be used in both the WHERE and GROUP BY operations.

E. Add a composite index on (run,objective,stage) to allow the query to fully utilize an index.

Which three statements are characteristic of the MEMORY storage engine?

A. Each table is represented on disk as an.frm file.

B. Each table has a corresponding.MYI and .MYD file.

C. It can support foreign keys.

D. It cannot contain text or BLOB columns.

E. Table contents are not saved if the server is restarted.

F. It can support transactions

Which three statements describe how the strict SQL mode provides added security?

A. It rejects statements that try to insert out-of-range values

B. It rejects invalid dates.

C. It limits the operations that the server can perform.

D. It rejects queries that produce out-of-range values.

E. It rejects dates with zero day or month values.

A user has deleted the wrong row in a table and you are preparing a point-in-time recovery skipping the DELETE event.

The server is configured with:

You have identified that the DELETE statement to skip has the Global Transaction Identifier (GTID)

`dbbe7da-fe25-11e2-b6c7-0800274aa49e:5 and you replay the binary log with:

Mysqlbinlog exclude-gtides=' dbbe07da-fe25-11e2-b6c7-0800274aa49e:5' binlog.00000.2 | mysql

However all events were skipped instead of just the one deleting the wrong row.

What is the reason for this?

A. Mysqlbinlog ignores arguments to exclude-gtids-it means ignore all events with GTIDs.

B. The server keeps track of which GTIDs have already been executed and skips those.

C. Enforce_gtid_consistency is set to ON.

D. Gtid_mode must be set to AUTO during point in time recoveries.

Which two requirements would lead towards a high availability solution?

A. When uptime is critical

B. When data must be refactored

C. When application concurrency is static

D. When data loss is unacceptable

E. When application is a single point of failure

You adjust a default configuration to the following /etc/my.cnf on a Linux installation:

[mysqld]

Loq-bin

Binrylog_format=ROW

You do not notice the spelling error in binrylog_format and restart your production server.

How does the MySQL server behave with incorrectly spelled options?

A. Mysqld uses internal configuration versioning and reverts to the previous configuration.

B. When using mysql_config_editor for configuration adjustments, it detects incorrect syntax and typing mistakes.

C. The mysqld_safe script skips the unknown variable and starts using the remaining configuration changes.

D. Mysqld prints to the error log about an unknown variable, and then exits.

Which two statements are true about setting the per-thread buffers higher than required?

A. More memory per thread is beneficial in all scenarios.

B. It causes increased overhead due to initial memory allocation.

C. It can affect system stability during peak load times, due to swapping.

D. It requires increasing the thread_cache_size variable.