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.

ROW-based replication has stopped working. You investigate the error log file and find the following entries:

2013-08-27 14:15:47 9056 [ERROR] Slave SQL: Could not execute Delete_rows event on table test.t1; Can't find record in `t1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log 56_master-bin. 000003, end_log_pos 851, Error_code: 1032 2013-08-27 14:15:47 9056 [warning] Slave: Can't find record in `t1' Error_code: 1032 2013-08-27 14:15:47 9056 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log `56_master- bin. 000003' position 684

Why did you receive this error?

A. The slave SQL thread does not have DELETE privileges to execute on test.t1 table.s

B. The table definition on the slave -litters from the master.

C. Multi-threaded replication slaves can have temporary errors occurring for cross database updates.

D. The slave SQL thread attempted to remove a row from the test.t1 table, but the row did not exist.

A database exists as a read-intensive server that is operating with query_cachek_type = DEMAND.

The database is refreshed periodically, but the resultset size of the queries does not fluctuate.

Note the following details about this environment:

A web application uses a limited set of queries.

The Query Cache hit rate is high.

All resultsets fit into the Query Cache.

All queries are configured to use the Query Cache successfully.

The response times for queries have recently started to increase. The cause for this has correctly been identified as the increase in the number of concurrent users accessing the web service.

Based solely on the information provided, what is the most likely cause for this slowdown at the database level?

A. The Query Cache is pruning queries due to an increased number of requests.

B. Query_cache_min_res_unit has been exceeded, leading to an increased performance overhead due to additional memory block lookups.

C. Mutex contention on the Query Cache is forcing the queries to take longer due to its single- threaded nature.

D. The average resultset of a query is increasing due to an increase in the number of users requiring SQL statement execution.

You install a copy of Mysql 5.6.13 on a brand new Linux server by using RPM packages. The server starts

successfully as verified by the following commands:

$ pidof mysqld

3132

$tail - n2 /var/lib.mysql/hostname.err

2013-08-18 08:18:38 3132 [Note] /usr/sbin/mysqld: ready for connections.

Version: ‘5.6.13-enterprise-commercial-advaced’ socket: ‘/tmp/mysql.sock’ port;

3306 Mysql Enterprise Server – Advanced Edition (Commercial)

You attempt to log in as the root user with the following command:

$mysql –u root

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Which statement is true about this scenario?

A. The RPM installation script sets a default password of password for new installations.

B. The local root user must log in with a blank password initially: mysql u root p.

C. New security measures mean that the mysql_secure_installation script must be run first on all new installations.

D. The mysql_install_bd post-installation script used random-password.

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.

User A issues the command:

LOCK TABLES pets READ;

Which command can User B execute against the pets table?

A. UPDATE pets...

B. SELECT....FROM pets

C. INSERT INTO pets...

D. ALTER TABLE pets...

Consider the following table:

REATE TABLE ‘game’ (

‘id’ int (10) unsigned NOT NULL AUTO_INCREMENT,

‘keyword’ varchar (45) DEFAULT NULL,

‘date’ datetime NOT NULL,

PRIMARY KEY (‘id’ , ‘date’),

UNIQUE KEY ‘keyword_idx’ (‘keyword’ , ‘date’)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (TO_DAYS (date) ) (

PARTITION g201301 VALUES LESS THAN (TO_DAYS (‘2013-01-01 00:00:00’) ),

PARTITION g201302 VALUES LESS THAN (TO_DAYS (‘2013-02-01 00:00:00’) ),

PARTITION g201303 VALUES LESS THAN (TO_DAYS (‘2013-03-01 00:00:00’) ),

PARTITION g201304 VALUES LESS THAN (TO_DAYS (‘2013-04-01 00:00:00’) ),

PARTITION gMORES VALUES LESS THAN (MAXVALUE) );

Which method should used to add a new g201305 partition to the table?

A. ALTER TABLE games REORGANIZE PARTITION (gMORES) INTO g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

B. ALTER TABLE games ADD PARTITION g201350 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') );

C. ALTER TABLE games COALESCE PARTITION (gMORES) INTO g01305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

D. ALTER TABLE games SPLIT PARTITION (gMORES) INTO g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

E. ALTHER TABLE games DROP PATITION gMORES, ADD PARTITION g201305 VALUES LESS THAN (TO_DAYS (`2013-05-01 00:00:00') ), gMORES VALUES LESS THAN (MAXVALUE) );

You attempt to connect to a Mysql Server by using the mysql program. However, you receive the following notice:

ERROR 2059 (HY000): Authentication plugin `mysql_clear_password' connot be loaded: plugin not enabled

What would you run to fix the issue?

A. The mysql client with the ignore-password-hashing option

B. The mysql_secure_installation script to update server security settings

C. The mysql client with the enable-cleartext-plugin option

D. The mysql_upgrade script

E. The install plugin command for the mysql_cleartext_password plugin

You want to create a temporary table named OLD_INVENTORY in the OLD_INVENTORY database on the master server. This table is not to be replicated to the slave server.

Which two changes would ensure that the temporary table does not propagate to the slave?

A. Use the replicate-do-db, -- replicate-do-table, or replicate-wild-do-table option with the value equal to OLD_INVENTORY.

B. Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table.

C. Stop SQL_THREAD on the slave until you have finished using the OLD_INVENTORY temporary table.

D. Set binlog_format=MIXED with the replicate-ignore-temp-table option.

E. Use the replicate-ignore-table option with the value equal to OLD_INENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table.

Consider the MySQL Enterprise Audit plugin.

On attempting to start the MySQL service after a crash, notice the following error:

[ERROR] Plugin `audit_log' init function returned error.

In the audit log file, you notice the final entry:

...

TIMESTAMP="2013-07-09T02:12:35"

NAME="Connect"

CONNECTION_ID="98"

STATUS="0"

USER="Kate"

PRIV_USER="kate"

OS_LOGIN=""

HOST="localhost"

DB=""/>

What action should you take to fix the error and allow the service to start?

A. Re-install the audit plugin.

B. Execute the command FLUSH LOGS.

C. Execute the command SET GLOBAL audit_log_fiush= ON.

D. Move or rename the existing audit.log file.