1Z0-883 Online Practice Questions and Answers

You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are notified that users are getting the following error:

mysql> CALL film_in_stock (40, 2, @count);

ERROR 1449 (HY000): The user specified as a definer (`bon'@'localhost') does not exist

How would you identify all stored procedures that pose the same problem?

A. Execute SELECT * FROM mysql.routines WHERE DEFINER='bob@localhost';.

B. Execute SHOW ROUTINES WHERE DEFINER='bob@localhost'.

C. Execute SELECT * FROM INFORMATION_SCHEMA. ROUTINES WHERE DEFINER='bob@localhost';.

D. Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER='bob' and HOST=' localhost';.

E. Examine the Mysql error log for other ERROR 1449 messages.

A Mysql Server has been running an existing application successfully for six months.

The my.cnf is adjusted to contain the following additional configuration:

[mysqld]

Default-authentication-plugin=sha256_password

The Mysql Server is restarted without error.

What effect will the new configuration have in existing accounts?

A. They will have their passwords updated on start-up to sha256_password format.

B. They will have to change their password the next time they login to the server.

C. They are not affected by this configuration change.

D. They all connect via the secure sha256_password algorithm without any configuration change.

A Mysql instance is running on a dedicated server. Developers access the server from the same network subnet. Users access the database through an application that is running on a separate server in a DMZ.

Which two will optimize the security of this setup?

A. Disabling connections from named pipes or socket files (depending on the operating system of the server)

B. Running the server with skip-networking specified

C. Limiting logins to originate from the application server or the server's subnet

D. Starting the server with bind- address=0.0.0.0 specified

E. Installing Mysql on the application server, and running the database and application on the same server

F. Enabling and using SSL for connections to the Mysql database

The following commands are available in the Linux binary distributions of Mysql:

Mysqld

Mysqld_safe

Mysql.server

What is the correct description of each of these commands?

A. Mysqld is the server. Mysqld_safe is a shell script that invokes mysqld. Mysql.server is a wrapper for mysql_safe.

B. Mysqld is a shell script that starts mysql.server. Mysqld_safe causes the server to start up in data recovery mode. Mysql.server is the server.

C. Mysqld is the server. Mysqld_safe causes the server to start up in data recovery mode. Mysql.server is a wrapper for mysqld_safe.

D. Mysql, mysqld.safe, and mysql.server reside in different locations but are all symlinked to the same script.

Which statement is true about using Microsoft Windows Cluster as a platform for Mysql?

A. It is provided by means of IP- level disk replication.

B. It is shared-nothing architecture.

C. It implements High Availability by using the .NET Connector's load balancing capabilities.

D. It relies on the shared disk architecture being visible to both servers.

You have enabled the Slow Query Log for a short period.

When you process the Slow Query Log, you receive the following snip of output:

Count: 100 Time=0 .22a (22s) Lock=0.00s (0s) Rows=0.0 (0), root[root] @localhost CREATE TABLE

`t1' (id serial,id0 varchar(N) unique key,intcaoll INT (N) ,intco12 INT(N) ,intco13 INT(N) ,intco14 INT

(N) ,intco15 INT(N) ,charcol1 VARVHAR(N) ,charcol2 VARCHAR(N) charcol3 VARCHAR (N) ,charcol4 VARVHAR(N) ,charcol5 VARCHAR(N) charcol6 VARCHAR (N) ,charcol7 VARVHAR(N) ,charcol8 VARCHAR(N) charcol9 VARCHAR (N) .charcol 10 VACHAR (N) )

Count: 64000 Time-0.02s (1213s) Lock=0.00s (6s) Rows=1.0 (64000), root [root]@ localhost SELECT intocl1, intco12, intco13, intco14, intco15, intco16,intco17, intco18 ,intcol9, intcol10, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6 ,charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s'

Count: 1 Time=0.02s (0s) Lock=0.00s (0s) Rows=1.0 (1) agent [agent] @localhost SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv FROM mysql.user WHERE CONCAT (user, `s', host) = CURRENT_USER () Count: 48000 Time=0.02s (778s) Lock=0.00 (3s) Rows=1.0 (48000), root [root]@localhost SELECT intocl1,intcol2,intcol3, intcol4, intcol5, charcol1, charcol2, charcol3 ,charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s'

You want to tune the query such that it provides the greatest overall time savings.

Which query will accomplish this?

A. CHEATE TABLE `t1' (id serial, id0 varchar (N) unique key, intcol1 INT (N) ,intcol2 INT (N), intcol3 INT

(N) ,intcol4 INT(N), intcol5 INT(N), charool1 VARCHAR (N)

,charcol2 VARCHAR (N), charcol3 VARCHAR(N), charcol4 VARCHAR(N), charcol5 VARCHAR (N)

,charcol6 VARCHAR (N), charcol7 VARCHAR(N), charcol8 VARCHAR(N), charcol9 VARCHAR (N)

,charcol10 VARCHAR (N);

B. SELECT intcol1, intcol2, intcol3, intcol4, intcol5, intcol6, intcol7, intcol8, intcol9, Intcol10, intcol11, intcol12, intcol13, intcol14, intcol15, intcol16, intcol17, intcol18, intcol19, charcol10 FROM t1 WHERE id = `s';

C. SELECT Select_priv, Repl_client_priv, Show_db_priv, Super_priv, Process_priv FROM mysql.user WHERE CONCAT (user,'s', host) = CURRENT_USER();

D. SELECT intcol1, intcol2, intcol3, intcol4, intcol5, charcol1, charcol2, charcol3, charcol4, charcol5, charcol6, charcol7, charcol8, charcol9, charcol10 FROM t1 WHERE id = `s';

Review the definition of the phone_list view.

CHEATE OR REPLACE ALGORITHM=MERGE DEFINER= `root'@localhost' SQL SECURITY DEFINER VIEW `phone_list' AS SELECT

e . id as id

`e . first_name AS `first_name'

`e . last_name AS `last_name'

`coalesce ( ph1.phone_no, `--') AS `office_no'

`coalesce (ph2 .phone_no, '--`) AS `cell_no'

FROM employees e

LEFT JOIN employee_phone ph1

ON ph1.emp_id = e.id AND ph1.type = `office'

LEFT JOIN employee_phone ph2

ON ph2 .emp_id = e.id AND ph2 .type = `mobile'

The tables employees and employee_phone are InnoDB tables; all columns are used in this view.

The contents of the phone_list view are as follows:

Mysql> select * from phone_list;

1 row in set (0.00 sec) Which method can you use to change the cell_no value to `555-8888' for John Doe?

A. DELETE FROM phone_list WHERE first_name= `John' and last_name= `Doe'; INSERT INTO phone_list (first_name, last_name, office_no, cell_no) VALUES (`John' , `Doe' , `x1234' , `555-8888);

B. INSERT INTO employee_phone (emp_id, phone_no, type) VALUES (1, `555- 8888','mobile');

C. UPDATE phone_list SET cell_name `555-8888' WHERE first_name= `John' and last_name= `Doe';

D. UPDATE employee_phone SET phone_no= `555-8888' where emp_id=1;

Which two options describe how MySQL Server allocates memory?

A. Each thread allocates memory from a global pool.

B. Global memory resources are allocated at server startup.

C. Thread memory is pre-allocated up to thread_cache_size for performance.

D. Each connection may have its own per-thread memory allocations.