• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

My SQL Interview Questions Answers

Question - 41 : - What Have We Used MySQL For?

Answer - 41 : - During MySQL initial development, the features of MySQL were made to fit our largest customer. They handle data warehousing for a couple of the biggest retailers in Sweden. From all stores, we get weekly summaries of all bonus card transactions, and we are expected to provide useful information for the store owners to help them find how their advertisement campaigns are affecting their customers. The data is quite huge (about 7 million summary transactions per month), and we have data for 4-10 years that we need to present to the users. We got weekly requests from the customers that they want to get 'instant' access to new reports from this data. We solved this by storing all information per month in compressed 'transaction' tables. We have a set of simple macros (script) that generates summary tables grouped by different criteria (product group, customer id, store ...) from the transaction tables. The reports are Web pages that are dynamically generated by a small Perl script that parses a Web page, executes the SQL statements in it, and inserts the results. We would have used PHP or mod_perl instead but they were not available at that time. For graphical data we wrote a simple tool in C that can produce GIFs based on the result of a SQL query (with some processing of the result). This is also dynamically executed from the Perl script that parses the HTML files. In most cases a new report can simply be done by copying an existing script and modifying the SQL query in it. In some cases, we will need to add more fields to an existing summary table or generate a new one, but this is also quite simple, as we keep all transactions tables on disk. (Currently we have at least 50G of transactions tables and 200G of other customer data.) We also let our customers access the summary tables directly with ODBC so that the advanced users can themselves experiment with the data. We haven't had any problems handling this with quite modest Sun Ultra SPARCstation (2x200 Mhz). We recently upgraded one of our servers to a 2 CPU 400 Mhz UltraSPARC, and we are now planning to start handling transactions on the product level, which would mean a ten-fold increase of data. We think we can keep up with this by just adding mo

Question - 42 : - how to set up complete replication on your current MySQL server?

Answer - 42 : - Below is a quick description of how to set up complete replication on your current MySQL server. It assumes you want to replicate all your databases and have not configured replication before. You will need to shutdown your master server briefly to complete the steops outlined below. Make sure you have a recent version of MySQL installed on the master and slave(s). Use Version 3.23.29 or higher. Previous releases used a different binary log format and had bugs which have been fixed in newer releases. Please, do not report bugs until you have verified that the problem is present in the latest release. Set up special a replication user on the master with the FILE privilege and permission to connect from all the slaves. If the user is only doing replication (which is recommended), you don't need to grant any additional privileges. For example, to create a user named repl which can access your master from any host, you might use this command: GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY ''; Shut down MySQL on the master. mysqladmin -u root -p<password> shutdown Snapshot all the data on your master server. The easiest way to do this (on Unix) is to simply use tar to produce an archvie of your entrie data directory. The exact data directory location depends on your installation. tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir Windows users can use WinZip or similar software to create an archive of the data directory. In my.cnf on the master add log-bin and server-id=unique number to the [mysqld] section and restart it. It is very important that the id of the slave is different from the id of the master. Think of server-id as something similar to the IP address - it uniquely identifies the server instance in the comminity of replication partners. [mysqld] log-bin server-id=1 Restart MySQL on the master. Add the following to my.cnf on the slave(s): master-host=<hostname of the master> master-user=<replication user name> master-password=<replication user password> master-port=<TCP/IP port for master> server-id=<some unique number between 2 and 2^32-1> replacing the values in <> with what is re

Question - 43 : - Disk Issues ?

Answer - 43 : - As mentioned before, disks seeks are a big performance bottleneck. This problems gets more and more apparent when the data starts to grow so large that effective caching becomes impossible. For large databases, where you access data more or less randomly, you can be sure that you will need at least one disk seek to read and a couple of disk seeks to write things. To minimize this problem, use disks with low seek times. Increase the number of available disk spindles (and thereby reduce the seek overhead) by either symlink files to different disks or striping the disks. Using symbolic links This means that you symlink the index and/or data file(s) from the normal data directory to another disk (that may also be striped). This makes both the seek and read times better (if the disks are not used for other things). Striping Striping means that you have many disks and put the first block on the first disk, the second block on the second disk, and the Nth on the (N mod number_of_disks) disk, and so on. This means if your normal data size is less than the stripe size (or perfectly aligned) you will get much better performance. Note that striping is very dependent on the OS and stripe-size. So benchmark your application with different stripe-sizes. Note that the speed difference for striping is very dependent on the parameters. Depending on how you set the striping parameters and number of disks you may get a difference in orders of magnitude. Note that you have to choose to optimize for random or sequential access. For reliability you may want to use RAID 0+1 (striping + mirroring), but in this case you will need 2*N drives to hold N drives of data. This is probably the best option if you have the money for it! You may, however, also have to invest in some volume-management software to handle it efficiently. A good option is to have semi-important data (that can be regenerated) on RAID 0 disk while storing really important data (like host information and logs) on a RAID 0+1 or RAID N disk. RAID N can be a problem if you have many writes because of the time to update the parity bits. You may also set the parameters for the file system that the database uses. One easy change is to mount the file system with the noatime option. That makes it skip the updating of the last access

Question - 44 : - What happens if a table has one column defined as TIMESTAMP?

Answer - 44 : - That field gets the current timestamp whenever the row gets altered.

Question - 45 : - Using Symbolic Links for Databases and Tables?

Answer - 45 : - You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space. If MySQL notices that a table is symbolically linked, it will resolve the symlink and use the table it points to instead. This works on all systems that support the realpath() call (at least Linux and Solaris support realpath())! On systems that don't support realpath(), you should not access the table through the real path and through the symlink at the same time! If you do, the table will be inconsistent after any update. MySQL doesn't that you link one directory to multiple databases. Replacing a database directory with a symbolic link will work fine as long as you don't make a symbolic link between databases. Suppose you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1: shell> cd /path/to/datadir shell> ln -s db1 db2 Now, for any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be problems. If you really need this, you must change the following code in `mysys/mf_format.c': if (flag & 32 || (!lstat(to,&stat_buff) && S_ISLNK(stat_buff.st_mode))) to if (1) On Windows you can use internal symbolic links to directories by compiling MySQL with -DUSE_SYMDIR. This allows you to put different databases on different disks.

Question - 46 : - Reasons NOT to Use Foreign Keys constraints ?

Answer - 46 : - There are so many problems with foreign key constraints that we don't know where to start: Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied, and removed. The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY constraint checks are useless because you usually insert records in the right tables in the right order, anyway. There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables. You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or from a backup). If you use foreign key constraints you can't dump and restore tables unless you do so in a very specific order. It's very easy to do ``allowed'' circular definitions that make the tables impossible to re-create each table with a single create statement, even if the definition works and is usable. It's very easy to overlook FOREIGN KEY ... ON DELETE rules when one codes an application. It's not unusual that one loses a lot of important information just because a wrong or misused ON DELETE rule. The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applicatons. MySQL will soon store FOREIGN KEY definitions so that a client can ask for and receive an answer about how the original connection was made. The current `.frm' file format does not have any place for it. At a later stage we will implement the foreign key constraints for application that can't easily be coded to avoid them.

Question - 47 : - How MySQL Locks Tables ?

Answer - 47 : - You can find a discussion about different locking methods in the appendix. All locking in MySQL is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order. The locking method MySQL uses for WRITE locks works as follows: If there are no locks on the table, put a write lock on it. Otherwise, put the lock request in the write lock queue. The locking method MySQL uses for READ locks works as follows: If there are no write locks on the table, put a read lock on it. Otherwise, put the lock request in the read lock queue. When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue. This means that if you have many updates on a table, SELECT statements will wait until there are no more updates. To work around this for the case where you want to do many INSERT and SELECT operations on a table, you can insert rows in a temporary table and update the real table with the records from the temporary table once in a while. This can be done with the following code: mysql> LOCK TABLES real_table WRITE, insert_table WRITE; mysql> insert into real_table select * from insert_table; mysql> TRUNCATE TABLE insert_table; mysql> UNLOCK TABLES; You can use the LOW_PRIORITY options with INSERT if you want to prioritize retrieval in some specific cases. You could also change the locking code in `mysys/thr_lock.c' to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications. MySQL - Table Locking Issues The table locking code in MySQL is deadlock free. MySQL uses table locking (instead of row locking or column locking) on all table types, except BDB tables, to achieve a very high lock speed. For large tables, table locking is MUCH better than row locking for most applications, but there are, of course, some pitfalls. For BDB tables, MySQL only uses ta

Question - 48 : - What’s the difference between Unix timestamps and MySQL timestamps?

Answer - 48 : - Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

Question - 49 : - How to Get MySQL binary distributions?

Answer - 49 : - MySQL binary distributions are provided as compressed tar archives and have names like `mysql-VERSION-OS.tar.gz', where VERSION is a number (for example, 3.21.15), and OS indicates the type of operating system for which the distribution is intended (for example, pc-linux-gnu-i586). Add a user and group for mysqld to run as: shell> groupadd mysql shell> useradd -g mysql mysql These commands add the mysql group and the mysql user. The syntax for useradd and groupadd may differ slightly on different Unixes. They may also be called adduser and addgroup. You may wish to call the user and group something else instead of mysql. Change into the intended installation directory: shell> cd /usr/local> Unpack the distribution and create the installation directory: shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s mysql-VERSION-OS mysql The first command creates a directory named `mysql-VERSION-OS'. The second command makes a symbolic link to that directory. This lets you refer more easily to the installation directory as `/usr/local/mysql'. Change into the installation directory: shell> cd mysql You will find several files and subdirectories in the mysql directory. The most important for installation purposes are the `bin' and `scripts' subdirectories. `bin' This directory contains client programs and the server You should add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly. `scripts' This directory contains the mysql_install_db script used to initialize the server access permissions. If you would like to use mysqlaccess and have the MySQL distribution in some nonstandard place, you must change the location where mysqlaccess expects to find the mysql client. Edit the `bin/mysqlaccess' script at approximately line 18. Search for a line that looks like this: $MYSQL = '/usr/local/bin/mysql'; # path to mysql executable Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, you will get a Broken pipe error when you run mysqlaccess. Create the MySQL grant tables (necessary only if you haven't installed MySQL before): shell>

Question - 50 : - Troubleshooting Replication?

Answer - 50 : - If you have followed the instructions, and your replication setup is not working, first elliminate the user error factor by checking the following: Is the master logging to the binary log? Check with SHOW MASTER STATUS. If it is, Position will be non-zero. If not, verify that you have given the master log-bin option and have set server-id. Is the slave running? Check with SHOW SLAVE STATUS. The answer is found in Slave_running column. If not, verify slave options and check the error log for messages. If the slave is running, did it establish connection with the master? Do SHOW PROCESSLIST, find the thread with system user value in User column and none in the Host column, and check the State column. If it says connecting to master, verify the privileges for the replication user on the master, master host name, your DNS setup, whether the master is actually running, whether it is reachable from the slave, and if all that seems ok, read the error logs. If the slave was running, but then stopped, look at SHOW SLAVE STATUS output andcheck the error logs. It usually happens when some query that succeeded on the master fails on the slave. This should never happen if you have taken a proper snapshot of the master, and never modify the data on the slave outside of the slave thread. If it does, it is a bug, read below on how to report it. If a query on that succeeded on the master refuses to run on the slave, and a full database resync ( the proper thing to do ) does not seem feasible, try the following: First see if there is some stray record in the way. Understand how it got there, then delete it and run SLAVE START If the above does not work or does not apply, try to understand if it would be safe to make the update manually ( if needed) and then ignore the next query from the master. If you have decided you can skip the next query, do SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START; to skip a query that does not use auto_increment, last_insert_id or timestamp, or SET SQL_SLAVE_SKIP_COUNTER=2; SLAVE START; otherwise If you are sure the slave started out perfectly in sync with the master, and no one has updated the tables involved outside of slave thread, report the bug, so you will not have to do the above tricks again. Make sure you are not running into an old bug by upgrading to the most recent version. If all else fails, read the error logs. If they are bi

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners