1Z0-874 Online Practice Questions and Answers

Which of the following statements are required to create a key cache of 4 MB, assign the MyISAM table world. City to it and preload the index?

A. mysql> SET GLOBAL city_cache.key_buffer_size = 4194304;mysql> CACHE INDEX world.City IN city_cache;mysql> LOAD INDEX INTO CACHE world.City;

B. mysql> ALTER TABLE world.city KEY_CACHE = 4194304;

C. mysql> CREATE CACHE FOR world.City SIZE = 4194304;

D. It is not possible to create a key cache for a specific MyISAM table, only the global key cache can be used.

Which of the following best describes why InnoDB tables should always have primary keys and why they should be short?

A. Because InnoDB uses primary keys to locate tables, and shorter keys make quicker lookups.

B. Because InnoDB uses primary keys to locate table rows, and shorter keys make quicker lookups.

C. Because InnoDB stores pointers in a log to all the primary keys and shorter keys make this log smaller.

The account 'ulf'@'localhost' has previously been created with the statement GRANT ALL PRIVILEGES ON *.* TO 'ulf'@'localhost' IDENTIFIED BY 'w01f'

This account should no longer be used, so you issue the following command:

REVOKE ALL PRIVILEGES ON *.* FROM 'ulf'@'localhost'

After executing this statement, you inspect the server and find that no clients are connected using that account. However, the next day you notice that the account 'ulf'@'localhost' is indeed connected to the server.

Why is it still possible for the account 'ulf'@'localhost' to connect to the server?

A. Specifying GRANT ALL PRIVILEGES ON *.* effectively created an administrator account, which cannot be disabled through the REVOKE statement

B. The REVOKE statement used does not remove the USAGE privilege from the account, allowing new connections with that account.

C. The REVOKE command was not followed by a FLUSH PRIVILEGES command, so the account was never actually disabled

D. The ON *.* clause should not be specified when disabling an account

Which of the following are true regarding the usage of the IDENTIFIED BY clause in the GRANT statement used to create a new user account?

A. There is no IDENTIFIED BY clause for the GRANT statement

B. The IDENTIFIED BY clause is always optional

C. The IDENTIFIED BY clause is always mandatory

D. The IDENTIFIED BY clause may be mandatory depending on the current SQL mode

E. The IDENTIFIED BY clause specifies the password to be used with the account

F. The IDENTIFIED BY clause specifies an alternate user name for the account

Which of the following best describes the purpose of the max_allowed_packet parmeter, and when it is set?

A. The max_allowed_packet parameter is set per-connection.

B. The max_allowed_packet parameter is global and is only set by shutting down the server.

C. The max_allowed_packet parameter is used to control the buffer that is used for communication between the server and the client.

D. The max_allowed_packet parameter is used to control the size of the dataset that is sent to the client.

Does max_connections have an affect on the number of slaves that may connect to a master?

A. No, because slaves are not really clients.

B. Yes, because each slave is effectively a connected user.

C. No, because slaves do not use the same type of connection as a regular client.

D. Yes, because if the server has too many connections, it may slow the slaves down.

The user 'joe' connecting from the host example.com has full access to all databases on the server:

mysql> SHOW GRANTS FOR 'joe'@'example.com';

+----------------------------------------------------+

| Grants for [email protected] |

+----------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'joe'@'example.com' |

+----------------------------------------------------+

1 row in set (0.00 sec)

You need to restrict the user from accessing the mysql database. Will the following statement do this?

REVOKE ALL PRIVILEGES ON mysql.* FROM 'joe'@'example.com'

A. Yes

B. No

C. The answer cannot be determined from the information provided.

To prevent users from seeing the password used for FEDERATED tables, you should...

A. not grant them privileges to execute SHOW CREATE TABLE for this table.

B. not grant them privileges to execute SHOW CONNECTION for this table. C. not grant them privileges to execute SHOW TABLE STATUS for this table.

C. not grant them privileges to execute SHOW CONNECTION STRING for this table.

Consider the following:

ALTER TABLE City ROW_FORMAT = FIXED

What would be the effect of this statement?

A. Nothing as this is not a valid statement.

B. The table City is changed to a fixed row format.

C. The columns in the table City are changed from VARCHAR to CHAR.

Which of the following best describes the purpose of the join buffer, and when should you increase the size of it?

A. The join buffer is used to process joins.

B. The join buffer is set per-connection.

C. The join buffer is global and can only be changed by restarting the server.

D. The value should be increased if the client performs a lot of SELECT operations.

E. The join buffer is used to process sorts when complex joins are being performed.

F. The value should be increased if the client has the tendency to perform complex joins.