MySQL Interview Questions -4

MySQL Interview Questions -4

Q.How To Shutdown MySQL Server?
 

If you want to shutdown your MySQL server, you can run the "mysqladmin" program in a command window as shown in the following tutorial:
>cd mysqlin
>mysqladmin shutdown

Q.How To Extract a Unit Value from a Date and Time?
If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:
ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;
28
ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;
23
ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;
36

Q.How To Get a List of Columns in an Existing Table?
If you have an existing table, but you don't remember what are the columns used in the table, you can use the "SHOW COLUMNS FROM tableName" command to get a list of all columns of the specified table. You can also use the "DESCRIBE tableName" command, which gives you the same output as "SHOW COLUMNS" command. The following tutorial script shows you a good example:
mysql> SHOW COLUMNS FROM tip;
+-------------+--------------+------+-----+---------+-------
| Field | Type | Null | Key | Default | Extra
+-------------+--------------+------+-----+---------+-------
| id | int(11) | NO | PRI | |
| subject | varchar(80) | NO | | |
| description | varchar(256) | NO | | |
| create_date | date | YES | | NULL |
+-------------+--------------+------+-----+---------+-------
4 rows in set (0.04 sec)

Q.How To Show Table Names with "mysqlshow"?
If you want to show table names with "mysqlshow", you need to specify a database name. The followings tutorial exercise shows you how to get all table names that match a pattern:
If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:
>cd mysqlin
>mysqlshow --verbose mysql time%
Database: mysql Wildcard: time%
+---------------------------+----------+
| Tables | Columns |
+---------------------------+----------+
| time_zone | 2 |
| time_zone_leap_second | 2 |
| time_zone_name | 2 |
| time_zone_transition | 3 |
| time_zone_transition_type | 5 |
+---------------------------+----------+
5 rows in set.

Q.How To Calculate Expressions with SQL Statements
here is no special SQL statements to calculate expressions. But you can use the "SELECT expression FROM DUAL" statement return the calculated value of an expression. "DUAL" is a dummy table in the server. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL;
Hello world!
SELECT (1+2)*3/4 FROM DUAL;
2.2500
SELECT TRUE FROM DUAL;
1
SELECT TRUE AND FALSE FROM DUAL;
0
SELECT TIME(SYSDATE()) FROM DUAL;
21:30:26

Q.How To Include Comments in SQL Statements?
If you want to include comments in a SQL statement, you can first enter "--", then enter your comment until the end of the line. The tutorial exercise below shows you some good examples:
SELECT 'Hello world!' FROM DUAL; -- My first SQL statement!
INSERT INTO links VALUES ('GlobalGuideLine.com'); -- Top rated!
CREATE TABLE faq (
id INTEGER, -- primary key
title VARCHAR(80) -- FAQ title
);

Q.MySQL - Why So Many Open tables?
When you run mysqladmin status, you'll see something like this:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
This can be somewhat perplexing if you only have 6 tables.
MySQL is multithreaded, so it may have many queries on the same table simultaneously. To minimize the problem with two threads having different states on the same file, the table is opened independently by each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.

Q.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 table locking if you explicitely lock the table with LOCK TABLES or execute a command that will modify every row in the table, like ALTER TABLE.
In MySQL Version 3.23.7 and above, you can insert rows into MyISAM tables at the same time other threads are reading from the table. Note that currently this only works if there are no holes after deleted rows in the table at the time the insert is made.
Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must first get exclusive access. During the update, all other threads that want to access this particular table will wait until the update is ready.
As updates on tables normally are considered to be more important than SELECT, all statements that update a table have higher priority than statements that retrieve information from a table. This should ensure that updates are not 'starved' because one issues a lot of heavy queries against a specific table. (You can change this by using LOW_PRIORITY with the statement that does the update or HIGH_PRIORITY with the SELECT statement.)
Starting from MySQL Version 3.23.7 one can use the max_write_lock_count variable to force MySQL to temporary give all SELECT statements, that wait for a table, a higher priority after a specific number of inserts on a table.
Table locking is, however, not very good under the following senario:
A client issues a SELECT that takes a long time to run.
Another client then issues an UPDATE on a used table. This client will wait until the SELECT is finished.
Another client issues another SELECT statement on the same table. As UPDATE has higher priority than SELECT, this SELECT will wait for the UPDATE to finish. It will also wait for the first SELECT to finish!
A thread is waiting for something like full disk, in which case all threads that wants to access the problem table will also be put in a waiting state until more disk space is made available.
Some possible solutions to this problem are:
Try to get the SELECT statements to run faster. You may have to create some summary tables to do this.
Start mysqld with --low-priority-updates. This will give all statements that update (modify) a table lower priority than a SELECT statement. In this case the last SELECT statement in the previous scenario would execute before the INSERT statement. You can give a specific INSERT, UPDATE, or DELETE statement lower priority with the LOW_PRIORITY attribute.
Start mysqld with a low value for max_write_lock_count to give READ locks after a certain number of WRITE locks.
You can specify that all updates from a specific thread should be done with low priority by using the SQL command: SET SQL_LOW_PRIORITY_UPDATES=1.
You can specify that a specific SELECT is very important with the HIGH_PRIORITY attribute.
If you have problems with INSERT combined with SELECT, switch to use the new MyISAM tables as these support concurrent SELECTs and INSERTs.
If you mainly mix INSERT and SELECT statements, the DELAYED attribute to INSERT will probably solve your problems.
If you have problems with SELECT and DELETE, the LIMIT option to DELETE may help.

Q.MySQL - Speed of SELECT Queries ?
In general, when you want to make a slow SELECT ... WHERE faster, the first thing to check is whether or not you can add an index.
All references between different tables should usually be done with indexes.
You can use the EXPLAIN command to determine which indexes are used for a SELECT.
Some general tips:
To help MySQL optimize queries better, run myisamchk --analyze on a table after it has been loaded with relevant data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1, of course.). MySQL will use this to decide which index to choose when you connect two tables with 'a non-constant expression'. You can check the result from the analyze run by doing SHOW INDEX FROM table_name and examining the Cardinality column.
To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster. Note, however, that this sorting isn't written optimally and will take a long time for a large table!

Q.How MySQL Optimizes LIMIT ?
In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:
If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.

Q.MySQL - Using Your Own Benchmarks
You should definately benchmark your application and database to find out where the bottlenecks are. By fixing it (or by replacing the bottleneck with a 'dummy module') you can then easily identify the next bottleneck (and so on). Even if the overall performance for your application is sufficient, you should at least make a plan for each bottleneck, and decide how to solve it if someday you really need the extra performance.
For an example of portable benchmark programs, look at the MySQL benchmark suite.
You can take any program from this suite and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you.
It is very common that some problems only occur when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In every one of these cases so far, it has been problems with basic design (table scans are NOT good at high load) or OS/Library issues. Most of this would be a LOT easier to fix if the systems were not already in production.
To avoid problems like this, you should put some effort into benchmarking your whole application under the worst possible load! You can use Sasha's recent hack for this - super-smack. As the name suggests, it can bring your system down to its knees if you ask it, so make sure to use it only on your development systems.

Q.MySQL - Design Choices
MySQL keeps row data and index data in separate files. Many (almost all) other databases mix row and index data in the same file. We believe that the MySQL choice is better for a very wide range of modern systems.
Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This will cause a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general purpose databases.
The more common case is that the index and data are stored together (like in Oracle/Sybase et al). In this case you will find the row information at the leaf page of the index. The good thing with this layout is that it, in many cases, depending on how well the index is cached, saves a disk read. The bad things with this layout are:
Table scanning is much slower because you have to read through the indexes to get at the data.
You can't use only the index table to retrieve data for a query.
You lose a lot of space, as you must duplicate indexes from the nodes (as you can't store the row in the nodes).
Deletes will degenerate the table over time (as indexes in nodes are usually not updated on delete).
It's harder to cache ONLY the index data.

Q.What Is "mysqld"?
"mysqld" is MySQL server daemon program which runs quietly in background on your computer system. Invoking "mysqld" will start the MySQL server on your system. Terminating "mysqld" will shutdown the MySQL server. Here is a tutorial example of invoking "mysqld" with the "--console" option:
>cd mysqlin
>mysqld --console
... 21:52:54 InnoDB: Started; log sequence number 0 43655
... 21:52:54 [Note] mysqld: ready for connections.
Version: '5.0.24-community' socket: '' port: 3306
MySQL Community Edition (GPL)
The returning message indicates that "mysqld" running now, and your MySQL server is ready to take client connections. To know about "mysqld", read other parts of this FAQ collection.

Q.How Many Ways to Get the Current Time?
There are 8 ways to get the current time:
SELECT NOW() FROM DUAL;
2006-07-01 10:02:41
SELECT CURRENT_TIME() FROM DUAL;
10:02:58
SELECT SYSDATE() FROM DUAL;
2006-07-01 10:03:21
mysql> SELECT CURRENT_TIMESTAMP() FROM DUAL;
2006-07-01 10:04:03
SELECT LOCALTIME() FROM DUAL;
2006-07-01 10:07:37
mysql> SELECT LOCALTIMESTAMP() FROM DUAL;
2006-07-01 10:08:08
mysql> SELECT UTC_TIME() FROM DUAL;
14:09:22
mysql> SELECT UTC_TIMESTAMP() FROM DUAL;
2006-07-01 14:09:49

Q.How To Escape Special Characters in SQL statements?
There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:
The escape character () needs to be escaped as ().
The single quote (') needs to be escaped as (') or ('') in single-quote quoted strings.
The double quote (") needs to be escaped as (") or ("") in double-quote quoted strings.
The wild card character for a single character (_) needs to be escaped as (_).
The wild card character for multiple characters (%) needs to be escaped as (%).
The tab character needs to be escaped as ( ).
The new line character needs to be escaped as ( ).
The carriage return character needs to be escaped as ( ).
Here are some examples of how to include special characters:
SELECT 'It''s Sunday!' FROM DUAL;
It's Sunday!
SELECT 'Allo, C'est moi.' FROM DUAL;
Allo, C'est moi.
SELECT 'Mon Tue Wed Thu Fri' FROM DUAL;
Mon Tue Wed Thu Fri

Q.What Are NULL Values?
NULL is a special value that represents no value. Here are basic rules about NULL values:
NULL presents no value.
NULL is not the same as an empty string ''.
NULL is not the same as a zero value 0.
NULL can be used as any data type.
NULL should not be used in any comparison options.
NULL has its own equality operator "IS".
NULL has its own not-equality operator "IS NOT".
The tutorial exercise shows you some interesting examples:
SELECT 0 IS NULL FROM DUAL;
0
SELECT 0 IS NOT NULL FROM DUAL;
1
SELECT '' IS NULL FROM DUAL;
0
SELECT '' IS NOT NULL FROM DUAL;
1
SELECT NULL IS NULL FROM DUAL;
1
SELECT NULL IS NOT NULL FROM DUAL;
0

Q.How To Use "mysql" to Run SQL Statements?
f you want to run SQL statement to your server with "mysql", you need to start "mysql" and enter your SQL statement at the "mysql" prompt. Here is a good tutorial exercise that shows you how to run two SQL statements with "mysql":
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4 to server version: 5.0.24
mysql> CREATE TABLE links (name VARCHAR(80));
Query OK, 0 rows affected (0.10 sec)
mysql> INSERT INTO links VALUES ('www.GlobalGuideLine.com');
Query OK, 1 row affected (0.02 sec)
mysql> quit;
Bye

Q.What Happens If You No CREATE Privilege in a Database?
In order to create tables in a database, your user account must have the CREATE privilege for that database. Otherwise you will get an error as shown in the following tutorial exercise:
>cd mysqlin
>mysql -u guest -ppub
mysql> use ggl;
Database changed
mysql> CREATE TABLE test (id integer);
ERROR 1142 (42000): CREATE command denied to user
'guest'@'localhost' for table 'test'
If you get this error, you need to see the DBA to obtain the CREATE privilege.

Q.How To Dump a Table to a File with "mysqldump"?
If you want to dump all rows in a table from the server to a file, you can use "mysqldump" with the "-f fileName" option as show in the following tutorial exercise:
>cd mysqlin
>mysqldump -u root -r emplinks.txt test links
>type emplinks.txt
>type emplinks.txt | more
-- MySQL dump 10.10
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.24-community
...
The dump file contains SQL statements that you can use to restore the table and its data content.

Q.How To Delete an Existing Column in a Table?
If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing column:
mysql> ALTER TABLE tip DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tip;
+----+-------------+-------------------------+--------+
| id | subject | description | author |
+----+-------------+-------------------------+--------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | NULL |
+----+-------------+-------------------------+--------+
1 row in set (0.00 sec)
As you can see the column "create_date" is gone.

Q.How To Show All Tables with "mysql"?
If you want to see all the tables in a database, you run the non-SQL command "SHOW TABLES" at the "mysql" prompt. See the following tutorial exercise for example:
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 5.0.24
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.01 sec)
The output shows you that there is only one table in the "test" database.

Q.How To Create a New Table by Selecting Rows from Another Table in MySQL ?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:
mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',
'Visit www.GlobalGuideLine.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)
mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tipBackup;

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

| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+
1 row in set (0.00 sec)
As you can see, this SQL script created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

Q.What Is "mysqlimport"?
"mysqlimport" - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables. Here is a sample commands supported by "mysqlimport":
"mysqlimport databaseName fileName" - Imports the data from the specified file to the specified database. The data will be loaded into the table who's name matches the specified file name.

Q.How To Include Character Strings in SQL statements?
If you want to include character strings in your SQL statements, you need to quote them in one of the following formats:
Using single quotes. For example 'GlobalGuideLine.com'.
Using double quotes. For example "ggl Center".
Using single quotes prefixed with N for NATIONAL characters (same as UTF8 characters). For example N'Allo, Francois.'.
Using single quotes prefixed with _utf8 for UTF8 characters. For example _utf8'Allo, Francois.'.

Q.MySQL Design Limitations/Tradeoffs
Because MySQL uses extremely fast table locking (multiple readers / single writers) the biggest remaining problem is a mix of a steady stream of inserts and slow selects on the same table.
We believe that for a huge number of systems the extremely fast performance in other cases make this choice a win. This case is usually also possible to solve by having multiple copies of the table, but it takes more effort and hardware.
We are also working on some extensions to solve this problem for some common application niches.

Q.MySQL - Get Your Data as Small as Possible
One of the most basic optimization is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will be used. Indexing also takes less resources if done on smaller columns.
MySQL supports a lot of different table types and row formats. Choosing the right table format may give you a big performance gain.
You can get better performance on a table and minimize storage space using the techniques listed below:
Use the most efficient (smallest) types possible. MySQL has many specialized types that save disk space and memory.
Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use it. Just avoid having it on all columns by default.
If you don't have any variable-length columns (VARCHAR, TEXT, or BLOB columns), a fixed-size record format is used. This is faster but unfortunately may waste some space.
The primary index of a table should be as short as possible. This makes identification of one row easy and efficient. For each table, you have to decide which storage/index method to use.
Only create the indexes that you really need. Indexes are good for retrieval but bad when you need to store things fast. If you mostly access a table by searching on a combination of columns, make an index on them. The first index part should be the most used column. If you are ALWAYS using many columns, you should use the column with more duplicates first to get better compression of the index.
If it's very likely that a column has a unique prefix on the first number of characters, it's better to only index this prefix. MySQL supports an index on a part of a character column. Shorter indexes are faster not only because they take less disk space but also because they will give you more hits in the index cache and thus fewer disk seeks.
In some circumstances it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic format table and it is pos

Q.How MySQL uses DNS ?
When a new threads connects to mysqld, mysqld will span a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.
If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.
You can disable DNS host lookup by starting mysqld with --skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.
You can disable the hostname cache with --skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.
If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with --skip-networking.

Q.MySQL - Using Symbolic Links for Databases and Tables
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.

Q.How can I use replication to provide redundancy/high availability?
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.

Q.MySQL - Replication Implementation Overview
MySQL replication is based on the server keeping track of all changes to your database (updates, deletes, etc) in the binary log. and the slave server(s) reading the saved queries from the master server's binary log so that the slave can execute the same queries on its copy of the data.
It is very important to realize that the binary log is simply a record starting from a fixed point in time (the moment you enable binary logging). Any slaves which you set up will need copies of all the data from your master as it existed the moment that you enabled binary logging on the master. If you start your slaves with data that doesn't agree with what was on the master when the binary log was started, your slaves may fail.
A future version (4.0) of MySQL will remove the need to keep a (possibly large) snapshot of data for new slaves that you might wish to set up through the live backup functionality with no locking required. However, at this time, it is necessary to block all writes either with a global read lock or by shutting down the master while taking a snapshot.
Once a slave is properly configured and running, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect every master-connect-retry seconds until it is able to reconnect and resume listening for updates.
Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up-to-date at any given time.

Q.MySQL - Startup options to mysqld which concerns security ?
The following mysqld options affect networking security:
secure
IP numbers returned by the gethostbyname() system call are checked to make sure they resolve back to the original hostname. This makes it harder for someone on the outside to get access by pretending to be another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL Version 3.21 because sometimes it takes a long time to perform backward resolutions. MySQL Version 3.22 caches hostnames and has this option enabled by default.
skip-grant-tables
This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin flush-privileges or mysqladmin reload.)
skip-name-resolve
Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.
skip-networking
Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets. This option is unsuitable for systems that use MIT-pthreads, because the MIT-pthreads package doesn't support Unix sockets.
skip-show-database
SHOW DATABASE command doesn't return anything.
safe-show-database
SHOW DATABASE only returns databases for which the user have some kind of privilege.

Q.MySQL - Reasons NOT to Use Foreign Keys constraints
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.

Q.MySQL - Foreign Keys
Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:
SELECT * from table1,table2 where table1.id = table2.id;
The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors' CREATE TABLE commands; it doesn't do anything. The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order. MySQL only supports these clauses because some applications require them to exist (regardless of whether or not they work).
In MySQL, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.
In the near future we will extend the FOREIGN KEY implementation so that at least the information will be saved in the table specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement the foreign key constraints for application that can't easily be coded to avoid them.

Q.How To Show All Tables with "mysql"?
If you want to see all the tables in a database, you run the non-SQL command "SHOW TABLES" at the "mysql" prompt. See the following tutorial exercise for example:
>cd mysqlin
>mysql -u root test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 14 to server version: 5.0.24
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| links |
+----------------+
1 row in set (0.01 sec)
The output shows you that there is only one table in the "test" database.

Q.What Is "mysqlimport"?
"mysqlimport" - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables. Here is a sample commands supported by "mysqlimport":
"mysqlimport databaseName fileName" - Imports the data from the specified file to the specified database. The data will be loaded into the table who's name matches the specified file name.

Q.How To Create a New Table by Selecting Rows from Another Table in MySQL ?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the "CREATE TABLE ... SELECT" statement. The tutorial script below gives you a good example:
mysql> INSERT INTO tip VALUES (1, 'Learn MySQL',
'Visit www.GlobalGuideLine.com','2006-07-01');
Query OK, 1 row affected (0.62 sec)
mysql> CREATE TABLE tipBackup SELECT * FROM tip;
Query OK, 1 row affected (0.49 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject | description | create_date |
+----+-------------+-------------------------+-------------+
| 1 | Learn MySQL | Visit www.GlobalGuideLine.com | 2006-07-01 |
+----+-------------+-------------------------+-------------+
1 row in set (0.00 sec)
As you can see, this SQL script created a table called "tipBackup" using the same column definitions as the "tip" table and copied all data rows into "tipBackup".

Q.How To Include Character Strings in SQL statements?
If you want to include character strings in your SQL statements, you need to quote them in one of the following formats:
Using single quotes. For example 'GlobalGuideLine.com'.
Using double quotes. For example "ggl Center".
Using single quotes prefixed with N for NATIONAL characters (same as UTF8 characters). For example N'Allo, Francois.'.
Using single quotes prefixed with _utf8 for UTF8 characters. For example _utf8'Allo, Francois.'.

Q.MySQL - Stored Procedures and Triggers
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep reissuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.
The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even queries for which they are not needed.

Q.MySQL - SELECT INTO TABLE
 

MySQL doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing.
Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT to solve your problem.

0 comments:

Post a Comment