MS SQL Interview Questions and Answers

Answer - 41 : - Information about user privileges is stored in the user, db, host, tables_priv, and columns_priv tables in the mysql database (that is, in the database named mysql). The names used in this manual to refer to the privileges provided by MySQL are shown below, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies: Privilege Column Context select Select_priv tables insert Insert_priv tables update Update_priv tables delete Delete_priv tables index Index_priv tables alter Alter_priv tables create Create_priv databases, tables, or indexes drop Drop_priv databases or tables grant Grant_priv databases or tables references References_priv databases or tables reload Reload_priv server administration shutdown Shutdown_priv server administration process Process_priv server administration file File_priv file access on server The select, insert, update, and delete privileges allow you to perform operations on rows in existing tables in a database. SELECT statements require the select privilege only if they actually retrieve rows from a table. You can execute certain SELECT statements even without permission to access any of the databases on the server. For example, you could use the mysql client as a simple calculator: mysql> SELECT 1+1; mysql> SELECT PI()*2; The index privilege allows you to create or drop (remove) indexes. The alter privilege allows you to use ALTER TABLE. The create and drop privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables. Note that if you grant the drop privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored! The grant privilege allows you to give to other users those privileges you yourself possess. The file privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is

Answer - 42 : - When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether or not you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests. Your identity is based on two pieces of information: The host from which you connect Your MySQL user name Identity checking is performed using the three user table scope fields (Host, User, and Password). The server accepts the connection only if a user table entry matches your hostname and user name, and you supply the correct password. Values in the user table scope fields may be specified as follows: A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host. You can use the wild-card characters `%' and `_' in the Host field. A Host value of '%' matches any hostname. A blank Host value is equivalent to '%'. Note that these values match any host that can create a connection to your server! As of MySQL Version 3.23, for Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example: GRANT ALL PRIVILEGES on db.* to david@'192.58.197.0/255.255.255.0'; This will allow everyone to connect from an IP where the following is true: user_ip & netmask = host_ip. In the above example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect to the MySQL server. Wild-card characters are not allowed in the User field, but you can specify a blank value, which matches any name. If the user table entry that matches an incoming connection has a blank user name, the user is considered to be the anonymous user (the user with no name), rather than the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2). The Password field can be blank. This does not mean that any password matches, it means the user must connect without specifying a password. Non-blank Password values represent encrypted passwords. MySQL does not

Answer - 43 : - When mysqld starts, all grant table contents are read into memory and become effective at that point. Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference! When the server notices that the grant tables have been changed, existing client connections are affected as follows: Table and column privilege changes take effect with the client's next request. Database privilege changes take effect at the next USE db_name command. Global privilege changes and password changes take effect the next time the client connects.

Answer - 44 : - If you encounter Access denied errors when you try to connect to the MySQL server, the list below indicates some courses of action you can take to correct the problem: The server should let you connect without error. You should also make sure you have a file `user.MYD' in the MySQL database directory. Ordinarily, this is `PATH/var/mysql/user.MYD', where PATH is the pathname to the MySQL installation root. After a fresh installation, you should connect to the server and set up your users and their access permissions: shell> mysql -u root mysql The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the root password is something you should do while you're setting up your other MySQL users. If you try to connect as root and get this error: Access denied for user: '@unknown' to database mysql this means that you don't have an entry in the user table with a User column value of 'root' and that mysqld cannot resolve the hostname for your client. In this case, you must restart the server with the --skip-grant-tables option and edit your `/etc/hosts' or `\windows\hosts' file to add an entry for your host. If you updated an existing MySQL installation from a version earlier than Version 3.22.11 to Version 3.22.11 or later, did you run the mysql_fix_privilege_tables script? If not, do so. The structure of the grant tables changed with MySQL Version 3.22.11 when the GRANT statement became functional. If you can't get your password to work, remember that you must use the PASSWORD() function if you set the password with the INSERT, UPDATE, or SET PASSWORD statements. The PASSWORD() function is unnecessary if you specify the password using the GRANT ... INDENTIFIED BY statement or the mysqladmin password command. localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly. However, connections to localhost do not work if you are running on a system that uses MIT-pthreads (localhost connections are made using Unix sockets, which are not supported by MIT-pthreads). To avoid this problem on such systems, you should use the --host option to name the server host explicitly. This will make a TCP/IP connection to the mysqld serve

Answer - 45 : - Below is an explanation of what is supported and what is not: Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID, and TIMESTAMP values. RAND() in updates does not replicate properly. Use RAND(some_non_rand_expr) if you are replicating updates with RAND(). You can, for example, use UNIX_TIMESTAMP() for the argument to RAND(). LOAD DATA INFILE will be handled properly as long as the file still resides on the master server at the time of update propagation. LOAD LOCAL DATA INFILE will be skipped. Update queries that use user variables are not replication-safe (yet). Temporary tables starting in 3.23.29 are replicated properly with the exception of the case when you shut down slave server ( not just slave thread), you have some temporary tables open, and the are used in subsequent updates. To deal with this problem, to shut down the slave, do SLAVE STOP, then check Slave_open_temp_tables variable to see if it is 0, then issue mysqladmin shutdown. If the number is not 0, restart the slave thread with SLAVE START and see if you have better luck next time. There will be a cleaner solution, but it has to wait until version 4.0. In earlier versions temporary tables are not being replicated properly - we recommend that you either upgrade, or execute SET SQL_LOG_BIN=0 on your clients before all queries with temp tables. MySQL only supports one master and many slaves. We will in 4.x add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce 'agent' processes to help doing load balancing by sending select queries to different slaves. Starting in Version 3.23.26, it is safe to connect servers in a circular master-slave relationship with log-slave-updates enabled. Note, however, that many queries will not work right in this kind of setup unless your client code is written to take care of the potential problems that can happen from updates that occur in different sequence on different servers. Note that the log format has changed in Version 3.23.26 so that pre-3.23.26 slaves will not be able to read it. If the query on the slave gets an error, the slave thread will terminate, and a message will appear in the .err file. You should then connect to the slave manually, fix the cause of the error (for example, non-existent table), and then run SLAVE START sql command (available starting i

Answer - 46 : - Binlog_Dump is a continuous process that is handled by the server in the following way: Catch up on the updates. Once there are no more updates left, go into pthread_cond_wait(), from which we can be awakened either by an update or a kill. On wake up, check the reason. If we are not supposed to die, continue the Binlog_dump loop. If there is some fatal error, such as detecting a dead client, terminate the loop. So if the slave thread stops on the slave, the corresponding Binlog_Dump thread on the master will not notice it until after at least one update to the master (or a kill), which is needed to wake it up from pthread_cond_wait(). In the meantime, the slave could have opened another connection, which resulted in another Binlog_Dump thread. The above problem should not be present in Version 3.23.26 and later versions. In Version 3.23.26 we added server-id to each replication server, and now all the old zombie threads are killed on the master when a new replication thread connects from the same slave

Answer - 47 : - If you are upgrading pre-3.23.26 versions, you should just lock the master tables, let the slave catch up, then run FLUSH MASTER on the master, and FLUSH SLAVE on the slave to reset the logs, then restart new versions of the master and the slave. Note that the slave can stay down for some time - since the master is logging all the updates, the slave will be able to catch up once it is up and can connect. After 3.23.26, we have locked the replication protocol for modifications, so you can upgrade masters and slave on the fly to a newer 3.23 version and you can have different versions of MySQL running on the slave and the master, as long as they are both newer than 3.23.26.

Answer - 48 : - You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write-for-all-tables to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed.

Answer - 49 : - With the currently available features, you would have to set up a master and a slave (or several slaves), and write a script that will monitor the master to see if it is up, and instruct your applications and the slaves of the master change in case of failure. Some suggestions: To tell a slave to change the master use the CHANGE MASTER TO command. A good way to keep your applications informed where the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS. You should run your slaves with the log-bin option and without log-slave-updates. This way the slave will be ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO on the other slaves. It will also help you catch spurious updates that may happen because of misconfiguration of the slave (ideally, you want to configure access rights so that no client can update the slave, except for the slave thread) combined with the bugs in your client programs (they should never update the slave directly). We are currently working on intergrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools.

Answer - 50 : - Optimization is a complicated task because it ultimately requires understanding of the whole system. While it may be possible to do some local optimizations with small knowledge of your system/application, the more optimal you want your system to become the more you will have to know about it. So this chapter will try to explain and give some examples of different ways to optimize MySQL. But remember that there are always some (increasingly harder) additional ways to make the system even faster.