Just what is ..SQL and MySQL?

SQL (some people call it "ess Que ell", others insist that it should be pronounce as "sequel") stands for Structured Query Language. SQL is an international standard language for querying databases.

MySQL is, at its root, an SQL server, i.e. a program which accepts requests written in SQL and delivers back some type of answer. The answer may be data, the number of records affected by the query, or it may simply be an "ok, did that".

Of course, you must have some way of communicating with MySQL. This may be done either through a client or through your favorite programming language.

What's so great about MySQL?

MySQL is a great database system for handling very large data sets. Many users report having tables that contain several hundred thousand, or million, records. Hearing of databases of several Gigabytes are not uncommon on the mailing list.

MySQL outperforms many other available systems in querying on large tables. MySQL is also very stable and performs well, even when several hundred people are accessing the same data concurrently. This is one of the reasons MySQL is very popular for web applications. MySQL also scales very well, and runs on anything from small pc's to huge, multi-processor systems.

All that aside, this author thinks that the greatest feature of MySQL is the ability to connect to the same MySQL server, no matter what operating system and what programming language or client program you use. Besides a whole bunch of UNIX-type systems, you can run the MySQL server on OS/2 and Windows (see the supported OS types section of the manual).

That's nice. Now, what's the catch?

MySQL doesn't support transactions. If you don't know what transactions are, chances are you'll never need them. Transactions are most widely used in systems where complex updates of many tables concurrently is important and you must be absolutely sure that all things either go right or don't happen at all. That's why banking systems are heavily dependent on transactions. The rest of us, however, can usually live without 'em.

November 2001 update: The MySQL-Max distribution includes support for the Berkeley DB (BDB) and innoDB table types, both of which have transaction support. The latest word is that BDB tables are now considered "very stable", while innoDB tables "appear to work well". Officially, support for both is "Beta".

MySQL doesn't support relational integrity constraints. To most of us, relational integrity is mostly a convenience that prevent programmers and users from introducing inconsistencies, such as creating orphan records. Usage of relational integrity constraints is often a hazard (personally, I've seen too many things go wrong with a DELETE CASCADE), so the lack of this feature shouldn't upset you terribly, either. For a good explanation of relational integrity (and how to live without it), have a look at the manual entry on foreign keys.

November 2001: In the latest MySQL versions (3.23.44 / 4.0.1), innoDB tables (see note above) now supports relational integrity constraints.

MySQL does not support sub-selects, which you use for complex queries. This is being worked on for one of the upcomming releases of MySQL

The are other things missing from MySQL (most notably: triggers, views, stored procedures), but most are up-and-coming features. Have a look at the Functionality missing from MySQL entry in the manual, for further details.

November 2001 - "Real soon now": Along with a lot of other improvements, sub-selects, stored procedures, triggers and constraints are exptected to appear in version 4.0.1. No release date has been set, but knowing the speed at which MySQL is updated, we might see it before this year is out.

Getting MySQL

While this section describes how to get your hands on MySQL, it does not and never will contain installation and/or compiling instructions. The number of possible errors differ widely along with all the systems used.

Do I have to pay for MySQL?

(Note: the following information is purely of informative nature. As noted in the the disclaimer below, you should check the exact terms on the MySQL web site.)

Where do I obtain MySQL?

You get MySQL in the downloads section of the web site unsurprisingly called http://www.mysql.com/. If you wish to run MySQL on a Mac, you should check out these two pages: SQL and the Macintosh and Mysql for MacOSX Server.

What are all these different download packages?

What programs do i get with the installation?

The manual entry on MySQL programs gives a good overview

Auto_increment blues

Usage of AUTO_INCREMENT columns is probably the source of most FAQs on the MySQL mailing list. Thus, we've decided to allocate a whole chapter for the subject.

What do I INSERT or UPDATE into an AUTO_INCREMENT field?

Nothing.

If you insist on using the INSERT INTO table_name VALUES (...) without specifying the column names, insert NULL or 0.

You may insert positive integers into the AUTO_INCREMENT field, as long as you do not violate the index rules. You may even insert duplicates of the AUTO_INCREMENT field, provided that the index you use is not UNIQUE.

You do not want to insert negative values into the AUTO_INCREMENT field. Trust me on this.

Retrieving the value

To retrieve the number generated in an AUTO_INCREMENT field by an INSERT query, use on of the following methods:

• the mysql_insert_id() API function.

• the query SELECT * FROM tbl_name WHERE auto_col IS NULL

• the SQL function LAST_INSERT_ID()

For all the examples, not notice that no table is specified. It gets this value from your current MySQL connection. The last example can also be used within the next query you execute using the same connection.

Values are reused! (well, sometimes)

When you use ISAM or BDB tables, MySQL will reuse values in the AUTO_INCREMENT column. That is, if you delete one or more rows that contain the max values of the AUTO_INCREMENT column, the counter will start over at MAX(auto_increment_column)+1.

If you use MyISAM or InnoDB tables, MySQL will not reuse the values.

If you wish to reset the AUTO_INCREMENT column of a MyISAM table, issue a DELETE FROM tablename command. This will reset the counters, and they will start over from 1.

If you need to reset the counter on InnoDB tables, you will need to either drop and recreate the table (v. 3.23); use TRUNCATE TABLE (v. 4) or delete all rows from the table, then restart the server.

Can I specify a (negative) starting value?

In latter versions of MySQL, you can specify AUTO_INCREMENT=n as a table option. This will tell MySQL to use n for the initial value. Any new rows added will increment the counter by 1, as usual. This value is lost if you delete all rows from the table.

In older versions, you need to insert a dummy row in your table, if you wish to ensure that the table increments from a certain value

Whichever method you choose, the statment above still holds: do not specify a negative number as a starting value.

What happens on overflow?

If the value of the AUTO_INCREMENT column reaches the maximum allowable number for that field, one of two things can happen:

• If the column is declared with a UNIQUE INDEX, an error will occur and you will not be able to enter more data into the table.

• If the index is non-unique, the max value of the column type will be used over and over.

MySQL won't accept my AUTO_INCREMENT column definition!

In MySQL,

• you can have only one AUTO_INCREMENT column, and

• that column must be indexed. This, in turn, requires that

• the column must be defined as NOT NULL

How do I...

connect to the MySQL server?

It's a common misconception that you use telnet to connect to a remote MySQL server. You can't.

To connect to the server, you need to use a client program written specifically for use with MySQL. One such program is mysql, which is sometimes distributed in a package separate from the web server (see the section on download packages above).

To connect to a remote host, using mysql, use the -h flag on startup, e.g.

$ mysql -h my.isp.com

Several graphical (GUI) and HTML clients are also available:

• mysql.com's own MySQLGUI (several platforms supported),

• phpMyAdmin from phpwizard.net (requires a working installation of PHP on your web server),

• mySQLEasyAdmin from WeDoNet.net (MS-Windows(?)),

• urSQL from urbanresarch.com (MS-Windows),

• Mascon from scibit.com (MS-Windows),

• Java SQLClient from trustice.com. (100% Java SQL client),

• DBTools by Crercio O. da Silva et. al. (MS-Windows)

• MySQL Studio from dba-tools.com (MS-Windows)

And of course, you can use any client which is able to connect through an ODBC client. Personally, I only have experience using phpMyAdmin and MS-Access (through ODBC) as front ends for MySQL. Both work very well, although both have some limitations.

If you already have a client, but cannot seem to reach the server, read the section on Connecting to the server.

reset the root password?

The following procedure will help you reset the root password. Note that while you are doing this, the server is totally open and gives everybody full access to all databases.

• Stop the server

• Restart the server with the command-line option -Sg or --skip-grant-tables

• You can now log in to the server without a password. So can anyone else!

• Reset the root password with the command

mysql -> GRANT ALL PRIVILEGES ON *.* TO "root"@"localhost"
      -> IDENTIFIED BY "the_new_password"

• Exit the client

• Immediately thereafter, shut down and restart the server as usual.

The manual states that you can use FLUSH PRIVILEGES or the command mysqladmin reload frm the command line to make a running MySQL start using the grant tables. The manual also says that changes in grant tables aren't noticed for clients already connected to the server. If any clients have connected to the server while you were doing this, they have full access as long as they keep the connection open. The choice is yours...

move all my data from [other database program] to MySQL?

The fastest way to export/convert any DB to MySQL is probably by using one of the many conversion tools available on the MySQL web site.

Using ODBC

You can use the MyODBC interface to link ODBC-compliant applications to your MySQL server. Get it from the API downloads section at www.mysql.com

An interesting aside (and a cause of FAQs as well): While being the major proponent and supplier of ODBC, Microsoft managed to release Access 2000 without ensuring that it was ODBC compliant. Patches are supposedly available from Microsoft.

move data from one MySQL server to another?

In general, MySQL database files are not compatible between different operating systems. Use the mysqldump program to write all your table definitions and contents into a file, then import that file into the new database.

If you're absolutely certain that both of the MySQL servers are of version 3.23 and that you have used MyISAM tables, the tables are compatible between operating systems. See the manual for further information on table types.

move a column to another position?

If you really need to know the answer to this question, you need to rethink your programming strategy. Read the chapter Relying on table structure below.

Suppose you have a table created by the command CREATE TABLE t (ca int, cc int, cb int); and wish to move column cb in between column ca and cc. This is how you could do it:

mysql> ALTER TABLE t ADD tmp int AFTER ca;
mysql> UPDATE table SET tmp=cb;
mysql> ALTER TABLE table DROP cb;
mysql> ALTER TABLE table CHANGE tmp cb int;

This will create a copy of column cb just after ca, then delete the original column.

Of course, you will have to make sure that all types and options match between the two columns and that you re-create all indexes which you may have to drop when deleting the original column.

In version 4 you can do this in a single command: ALTER TABLE t CHANGE COLUMN cb INT NOT NULL AFTER ca; -- you still need to check that all types and option modifiers are carried along in the move.

modify an ENUM column without having to retype all the possible entries?

Using the mysql client, You can't. In MySQL versions prior to 3.23.20, the easiest way to do it may be to dump the description of the table to a file (using mysqldump), then edit the file to create an ALTER TABLE query which does what you need.

In MySQL version 3.23.20 and above, you can execute

SHOW CREATE TABLE tablename

to see the definition of the table, then cut and paste to alter the table.

Some of the GUI clients available for MySQL greatly facilitate changes in ENUM columns.

use a batch file with MySQL?

From within the MySQL client (v. 3.23.9 and above):

mysql> SOURCE filename

From the command line:

$ mysql < filename

You can also execute commands directly from the command prompt in the following manner:

$ echo "select * from dbname.tablename" | mysql 

remove duplicate records in a table?

The following query will do the trick. Don't forget to specify all columns.

mysql> ALTER IGNORE TABLE table_name 
    -> ADD UNIQUE index_name (
    -> column_a, column_b, ...
    -> );

If you need to re-enter duplicate data, you must of course drop the index again after completing the operation.

find the cause of warnings generated on LOAD DATA INFILE?

Short of importing every single row on its own, finding out which rows cause the error and then inspecting the data, there's not much you can do. MySQL does not give enough detail in its warnings on LOAD DATA.

One person highly regarded on the mailing list (I'm not telling you who) suggests that after importing the data, one can re-export the data using SELECT INTO OUTFILE, then run a diff on the two files. Errr...

February 2002: Paul DuBois, of MySQL book fame, let me see a preview of his load_diag perl script, which analyzes where things go wrong during import. The script itself, which will be part of Paul's upcoming book, will soon released. Head over to Paul's site and see if it's released. If not, you should spend some time reading the pdf-documentation for load_diag anyway. This looks very promising!

find rows in one table that do not have a related row in another table?

This is done using a LEFT JOIN where the right side of the join has a NULL value:

mysql> SELECT column_c
    -> FROM table_a LEFT JOIN table_b
    -> ON table_a.column_c = table_b.column_d
    -> WHERE table_b.column_d IS NULL

This will select all the values in column_c of table_a which have no corresponding values in column_d of table_b.

see exactly what MySQL is doing at any given moment?

The command

$ mysqladmin processlist

will show you what MySQL is doing right now. Starting with version 3.23.07, you can add the --verbose option to the command. This will break the 100-character limit otherwise imposed on the output from the program.

There is also a nifty little Perl utility out there called mysqltop, which shows you what MySQL is doing much like the top command on UNIX. You can get it from http://public.yahoo.com/~jzawodn/mytop/.

speed up batch operations and table restructuring?

Certain operations involving large amounts of data will be slow, if the table uses many indexes. For ISAM tables, you can speed up these operations by running the myisamchk program with the -k0 option, do the update and then running myisamchk again with the -r option. This will disable the indexes for the duration of the operation.

Note that you will get in trouble if you alter data in such a way that indexes cannot be restructured (i.e. inserting non-unique data in fields which are defined to be unique).

If you use MySQL v. 4, you can issue the commands

mysql> ALTER TABLE tablename DISABLE KEYS;

and

mysql> ALTER TABLE tablename ENABLE KEYS;

before and after a batch operation. This effectively does the same as a dropping and re-creating they keys, but you are relieved from remembering exactly how your keys were specified.

use two tables from different databases in the same query?

Using the database-dot-table notation, you can access tables from seperate databases in the same query:

mysql> select * from db1.table1, db2.table2
    -> where field1_in_table1 = field_in_table2

You may have to alias table or column names or use use the full db.table.column syntax, to avoid ambiguity.

delete all the records from a table?

The recommended way is to use the TRUNCATE statement. For version 3.23.28 and the rest of the 3.23 series, you may use:

mysql> TRUNCATE table_name

Starting from version 3.23.33, you should use the following syntax, as it will be kept on through version 4:

mysql> TRUNCATE TABLE table_name

For all versions, including pre-3.23.28, the DELETE command without the optional WHERE clause, i.e.

mysql> DELETE FROM table_name;

will work.

The seeming confusion regarding these statements stems from several issues: The DELETE command should return the number of deleted row, which it doesn't do now, but will do from version 4.0 onward. Thus, TRUNCATE, which is not a standard SQL statement but an Oracle extension, was adopted to do in 4.0 what 3.23 does today. Unfortunately, the first implementation (without the TABLE clause) was not quite right, and the second form had to be adopted as well.

store images?

Before you do it: Read the good practices section on this subject below. If you still insist on going on with this, here are some hints:

• in MySQL 3.23, you can use the LOAD_FILE() function to read a file from the file system, e.g.

mysql> INSERT INTO table (description,image) 
    -> VALUES ("my image",LOAD_FILE('image.jpg'));

create a column of type BOOLEAN?

MySQL does not have a boolean type per se. The usual way to create a boolean value, is to use the ENUM type:

mysql> CREATE TABLE truths 
    -> (answer ENUM('true','false') NOT NULL);

(don't forget the NOT NULL clause, or you end up having not two, but three legal values for the field.)

You can also use the following hack to define a field which can contain only one of two values, namely the empty string ("") or NULL:

mysql> CREATE TABLE truths 
    -> (answer CHAR(0));

The special beauty of the second example is that the data in the field only occupies one bit, as opposed to an ENUM field, which always occupies at least a byte. However, you get to do quite a bit of encoding and decoding of these values on your front end...

back up a MySQL database?

You have several options. First, let's start by pointing out the wrong way of doing it: You definitely do not want to rely on a copy of files of a live database! If the database server is running while you copy, all you get are files which are almost certainly corrupted.

Some of the right ways of doing it include

1. Shut down the server; save a copy of the MySQL data directory; restart the server.

2. Use the mysqldump utility provided with MySQL to make a SQL dump of your data to disk files. This is a fairly slow process, but very safe. You end up with a text dump of your data, and even if parts of your backup are corrupt, you stand a good chance of recovering a lot of your data. SQL files are also the only sure way you've got, in case you need to restore your data to a server running on another OS than the one from which the data originially came.

3. Use SELECT INTO OUTFILE commands to copy data to comma-separated files. This has the same advantages as method (2), and takes up less space as only the data is stored. On the other hand, you also loose the table and index description, making it harder to re-create your finely tuned database structure, etc.

4. Manually do a LOCK TABLES and FLUSH TABLES, take a copy of the data files and then UNLOCK TABLES.

5. Use the mysqlhotcopy utility to create a copy of the MySQL data directory. This program essentially goes through the steps in (4).

Using log files

You might consider an incremental backup approach, if your databases are very large, heavily used and/or uptime is absolutely critical. First, use one of the methods outlined above to create complete backups at regular intervals. Then run MySQL with logging enabled. The log files give you a complete command history, which you can use against the last complete backup, to re-create the database up to just the moment before it crashed.

The advantages include not having to lock the server down for long periods of time and losing only very little work in case of a system crash; the disadvantages are that log files tend to become very large in a short time and that you must save your log files locally. This is significant, in cases where a hard disk or CPU crashes so completely that data cannot be recovered. In these cases you must go back to the full backup.

Note that you should use binary logs for this. While the update log may perform the same function, this facility likely to be removed from MySQL in the not too distant future.

retrieve a description of a database object?

The definition of all objects (databases, tables, fields, ...) which you create in MySQL, can be retrieved using either SHOW or DESCRIBE

The mysqldump utility is also useful for getting database and table descriptions (use it with the --no-data switch)

create an autonumber field / field with sequential numbers?

Use the AUTO_INCREMENT option on a field of type INTEGER.

Then, read this FAQs chapter on auto_increment do's and dont's.

make MySQL return records in reverse order?

By adding the keyword DESC to the ORDER BY clause:

mysql> SELECT * FROM table_name 
    -> ORDER BY column_name DESC

make MySQL return records in a random order?

For MySQL v. 3.23, use the following query to randomize the order in which MySQL returns rows:

mysql> SELECT * FROM table_name 
    -> ORDER BY RAND()

For earlier versions, you can use this slightly less effective method:

mysql> SELECT col1, col2, ..., rand()+0 AS ord
    -> FROM table_name
    -> ORDER BY ord;

Both of these methods are inefficient for large data sets, as MySQL must compute a random number for every single row in the table. The following method is faster than both of the other methods, but there's a catch. Read on...

Assuming that you have an unique integer ID column (possibly the primary key, defined as AUTO_INCREMENT, you first

mysql> SELECT max(ID) as m FROM tablename

Calcluate r, a random number, 0 <= r < m. Next, select the data using a query like

mysql> select * from tablename
    -> where id>r
    -> order by ID
    -> limit 1;

This method is very fast, and will always select a record; However, if you want "perfect randomness", it also assumes that there are no holes in the sequence of IDs. In many cases this won't matter. But if you are to draw lottery winners in this manner, beware that as soon as a single ID is missing, one ticket will have twice the chance of being selected than any other!

insert or update data in multiple tables in a single command?

You can't. Period. End of discussion.

November 2001: The times, they are a'changing... With version 4 coming at us, this is one thing that is going to change in the near future. Version 4.0.0 allows for multi-table deletes; 4.0.2 will be allowing for multi-table updates.

copy a table?

In v. 3.23, you can use the command

mysql> CREATE TABLE copy 
    -> SELECT * FROM original

If you only need to copy the table structure (i.e. without inserting any data), use the command

mysql> CREATE TABLE copy 
    -> SELECT * FROM original
    -> WHERE 1=2

And, finally, if you wish to copy the table to a completely different server, you can use the programs mysql and mysqldump in this elegant manner:

$ mysqldump -u from_username_from -pfrom_password -h from_host 
    from_db from_table | mysql -u to_user -pto_pass -h to_host to_db_name

tell MySQL to return only parts of a result set?

To retrieve only the first n records of a result set, use the LIMIT clause in your query, i.e. SELECT * FROM table_name LIMIT n.

To retrieve the next n rows from the result set, use the LIMIT clause again, this time with an extra comma: SELECT * from table_name LIMIT m, n. This tells MySQL to retrieve the n records starting from record number m in the result set.

rename a table?

If you're using MySQL ver. 3.23.23 and above, use the SQL command:

mysql> RENAME TABLE old_table_name TO new_table_name;

If you're using an earlier version, use the following SQL command:

mysql> ALTER TABLE old_table_name
    -> RENAME AS new_table_name;

rename a column?

The basic syntax is:

mysql> ALTER TABLE table_name 
    -> CHANGE old_column_name 
    -> new_column_name old_create_definition

To get the exact old_create_definition, you can issue the statement SHOW CREATE table_name (version 3.23.20 and upwards)

rename a database?

If all the tables in the database are of type ISAM and/or MyISAM, you can rename a database simply by shutting down the server, renaming the directory containing that database and restarting the server.

Why does MySQL...

die for no particular reason?

There is, of course, no single answer for this question. In almost all cases, however, the answer is "MySQL ran out of resources". Typical resources that you should check, include:

• Disk space. Surprisingly often, DB managers don't realize just how much data they've stuffed onto their disks.

• Do you have enough file descriptors? A database providing services for many concurrent clients can run out of file descriptors pretty fast.

• Is your kernel providing enough processes? For much the same reasons, the MySQL deamon may exhaust your OS's pool of available processes. You may need to update your kernel to allow a greater number of processes.

If you have checked all of the above and still can't figure out what's wrong, try reading this page in the MySQL documentation.

give an "access denied" error?

If you specify your password on the command line, (which, by the way, is a Bad Idea™) i.e.

$ mysql -u username -pMyPassword

don't forget that the password must follow immediately after "-p" as shown. A common error is to put a space between "-p" and the password.

If this is not the cause of the error, you have some further reading to do: First, read this FAQ's chapters on finding the server

If you're the administrator of you MySQL installation, also read the chapter on managaging users.

If you're still stuck: Well, the manual lists some 20 different causes of Access denied errors. At least one of these apply to you.

return "0 rows affected" when i just deleted all of the gazillion-billion rows in my table?

When you issue a DELETE FROM table_name; command without specifying a WHERE clause, MySQL simply re-creates the table without ever looking at the number of records in the table (or, for that matter, even opening the table). This is why you get "0 rows affected".

seem to return smaller number of affected rows than were matched in an UPDATE?

When using the UPDATE command, MySQL will only return the number of rows that were actually changed. If you "update" a field to its previous value, this doesn't count in the affected_rows return value.

Newer versions of MySQL report the number of matched rows, as well as the number of affected rows. This gives a better indication of what is going on.

What is the maximum...

Much of the information below has been harvested from the mailing list. You can find more data on MySQLs limits on the crash-me pages at www.mysql.com, or by inspecting the MySQL variables using the show variables command.

table size?

The maximum table size is limited by the MySQL version, the OS and in particular the filesystem. In most cases, this effectively comes out to: "The size of your hard drive." Many Linux systems impose a 2GB limit on files.

MySQL v. 3.23 in particular, can be compiled to enable large tables which are only limited by the filesystem (unless, of course, your drive is larger than 800 million terabytes.)

length of a query?

The total length of the query string is limited by the variable max_allowed_packet (use the show variables command to see it). The size of this variable is 1Mb by default, but can be increased to 16Mb in v. 3.23.

Internally, the MySQL server can handle constant strings in WHERE clauses up to the full size of TEXT and BLOB fields (1,048,540 characters).

You can have some 85,000 elements in the WHERE clause, and nest parantheseses more than 1,200 levels deep.

number of users?

The maximum number of simultaneous connections your database can handle is limited by the variable max_connections (use the show variables command to see it).

Note that you cannot set this value indiscriminately: Your OS too enforces some limits on the number of network connections and/or threads that can be active at any one time.

number of databases and tables?

The only limit on the number of databases and tables is how many files you may be able to store in a directory.

When you create a database, MYSQL creates a directory in which to store the tables. Thus, the number of databases is limited by the number of subdirectories the OS can handle for the MySQL data directory.

For ISAM and MyISAM tables, each table in a database consists of 3 files. Therefore, the maximum number of tables you may define is limited by the number of files your operating system allows in a single directory, divided by 3. Other table types generally use only one file per table. Some temporary data will also be written to this directory, e.g. during table restructuring.

That said, some common sense is in order when you plan your database layout: Making the OS search through thousands of directory entries whenever a query is performed, is a bad idea.

number of columns in a table?

If you really need to know the answer to this question, it's about time you reconsider your database schema. You should have a really good reason to create tables with more than 30 or so columns.

The answer is: at least 2000 columns

amount of data I can store in a row?

As for the previous question: If you really need to know this, you should reconsider your database/table layot.

The collected size of all columns containing simple datatypes (numbers, CHAR/VARCHAR, ENUM, etc.), is limited to about 65,000 bytes. The size of TEXTs and BLOBs only count toward this limit with 1-4 bytes per column.

number of indexes for a table?

You can have 16 indexes in a table (32 in a MyISAM table)

number of fields in an index?

You can specify up to 16 field names in an index.

The maximum length of the index (the length of all index values combined) is 250 bytes (500 for MyISAM tables; however, no single part may be greater than 255 bytes).

size of a table, index or field name?

64 characters.

Aliases can have a length upwards of 500 characters.

Can't find the server?

One of the most frequently asked questions is "Why can't I connect to the server?

Missing mysql.sock

(note: The following section on sockets does not pertain to Windows installations.)

When running both the client and the server on the same machine, they will communicate through a socket, rather than make use of the much slower TCP/IP protocol. Socket communication is done through a socket file.

The socket file must exist, for communication to take place. Upon startup, the server creates the socket file and when the server shuts down, the socket file is removed. Before and between these two events, several things may happen which prevent the use of the socket.

• The server and the client must agree on where the socket file is located. On most installations, this location is /tmp/mysql.sock. On some installations, the configuration file my.cnf specifies another location. In this case, my.cnf must contain at least the following lines:

  [mysqld]
  socket=/path/to/mysql.sock
  user=mysql # or whatever username the server is run as
  
  [client]
  socket=/path/to/mysql.sock
  

  Make sure that my.cnf is world readable, and that the user running mysql (the user 'mysql' in the example) owns all directories and files associated with the server.

• If the socket file isn't where you thought it should be, it may be that the server has shut down, thus deleting the socket file. If this is the case, simply restart the server to re-create the socket file.

• If the server is indeed running, someone or something may have deleted the socket file after the server created it. On some systems, there is a process (cronjob) which at odd intervals traverses and removes "old" files in the /tmp directory. If this is the case, you should configure MySQL to place the socket file somewhere else, then restart the server.

Connecting from remote hosts / using ODBC

Before connecting to the MySQL server from a remote machine, you must first create a user who is allowed to connect from other hosts. None of the initial users defined after an install (root and test) have remote access.

Wrong password

If MySQL has just been installed and the user tables just set up, the only user (of any value) you can log on as is root. And root has no password, so don't try to specify one when connecting.

If all else fails, run the script mysql_install_db. This will re-create the user tables in MySQL and reset the root password to nothing.

After you have run mysql_install_db, use the command

$ mysqladmin -u root password the_new_password

Wrong port or networking skipped

If you're trying to connect to the mysql server from another machine and can't connect to the server at all (i.e. you're told that your client "can't connect", not "Access denied"), you must check whether the TCP port number has been set up as expected. Make a local connection to the MySQL server (i.e. log in to MySQL from the same machine as the server is running on), then issue the following command and note its output

mysql> show variables like 'port';

If the port is 3306 (the default port for MySQL), then the server is waiting for connections on that port. Check your client program to see whether this too is trying to connect to port 3306. If you're using the mysql program, the port number may be specified on the command line (using either the -P or --port= switch). It may also have been specified in the [client] section of the my.cnf file(s) you're using.

If the port number is neither 0 (zero) nor 3306, the server is waiting for connections, although on a non-standard port. You must either restart the server (removing any command line switches or entries in my.cnf file(s)) which affect the port number, or set your client program to reflect this non-standard port number.

If the port number is 0 (zero), networking for the server has been disabled either through use of the --skip-networking switch or through a similar line in the my.cnf file(s). Correct these entries and restart the server.

Oh, and if networking is indeed enabled and you still cannot get through, don't forget to check your firewall rules.

Managing users

The system used by MySQL for granting access to the data is fairly complex to learn. Simply put: the more you wish to restrict a user, the more complex commands you need to write. Unfortunately, this encourages system administrators to give much too broad access to too many users. It's important that you understand how the privilege system works.

The following sections will give you a brief overview of many of the simpler commands and their implications. Make sure you read and understand the manual's chapter on the access privilege system, before you put a server to production use or connect it to a network.

The commands

All privileges are granted and revoked with the commands GRANT and REVOKE. The privileges with encrypted passwords are stored in tables in the mysql database. You should never try to fiddle with these tables; GRANT and REVOKE are much safer to use.

Whenever you use the GRANT and REVOKE commands, specify the username as 'user'@'domain.com', i.e. place quotes around both the username and the domain.

Adding new users

If the user name does not already exist when you issue a GRANT command, the user name automatically created.

The privileges

At the time of writing, MySQL defines 14 different privileges that the administrator may hand down to users. They are: SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP, GRANT and REFERENCES, which allows the user to perform said operations on tables. CREATE, DROP, GRANT and REFERENCES also affect opreations on databases.

RELOAD, SHUTDOWN and PROCESS all pertain to system administration, and the last, FILE, restricts the user's access to files readable and writable by MySQL.

Users and hosts

All MySQL users are identified by a username, and the host from which they connect to the database. Note: the MySQL users defined are in no way related to usernames on your network.

To see the users and hosts currently defined on the server, use the following command:

mysql> select user, host from mysql.user;

If your server version is newer than 3.23.3, you can use the command SHOW GRANTS FOR user_a to see the privileges for the user user_a

Initial users

At installation time, two users are defined:

• root, who is granted all privileges on all databases and tables.

• An anonymous user on localhost, which has all privileges on all databases having a name of TEST or a name starting with TEST_.

A couple of details which should worry the security minded administrator:

• The root user has no password set on initial installation. Make sure you set one right away!

• While the anonymous user can only access the database TEST and its derivatives, it does mean that anyone, logged in on localhost can access your database, and create tables (files) in the database directory, making the machine vulnerable to a DoS attack. You should revoke all privileges from the anonymous user.

On thursday, January 13th 2000, the MySQL team issued the following warning:

"We recently found out that MySQL 3.22.x and MySQL 3.23.x has a bug in the GRANT handling; Because of this bug anyone with GRANT privilege can change the password for anyone else (like root) and thus get access to any MySQL database. Note that by default the test user has grant privileges set and using this one can change the root password for root at localhost."

The bug is present in all 3.22 version up to and including 3.22.29 and all 3.23 versions up to and including v. 3.23.8. Yet another reason to get rid of the anonymous user!

Understanding how it works

It's very important, that you understand how MySQL access control works. If you don't, you're in for some nasty surprises. Consider the following two statements:

mysql> grant select on abc.* to user_a;

mysql> revoke all privileges on *.* from user_a;

Most people would read this as:

1. user_a is granted the privilege of using "select from..." on tables in database abc.

2. Then, all privileges are revoked from user_a, so now he can do nothing at all in the database.

But these assumptions are wrong! user_a would still retain his privileges on database abc!

The reason for this is a bit obscure. To understand why, first realize that although you use the same commands (GRANT and REVOKE) to handle all privileges, the privileges for the server, databases, tables and columns are different beasts entirely.

Whenever you make changes in access privileges, the user / host / password combination that you give may update in the table mysql.user. This happens, if the user / host combination does not yet exist, or if you change the password. Depending on the GRANT statement you execute, other tables may be updated:

• "GRANT ... ON *.* ..." only stores the information in mysql.user.

• "GRANT ... ON db_name.* ..." stores the information in the table mysql.db.

• "GRANT ... ON db_name.tbl_name ..." stores the information in the table mysql.table_privs.

• "GRANTs with column specifications, i.e. "GRANT SELECT (column_a, column_b) ON db_name.tbl_name ..." stores the results in both mysql.table_privs and mysql.column_privs.

Thus, each GRANT statement only affects a few of the access privilege tables.

Note that the REVOKE command never deletes the user from the mysql.user table. At the most, all privileges are marked as revoked.

When is a user granted access?

A user is granted access to the database server, whenever the username / hostname / password combo exists in the table mysql.user. Even if all grants have been revoked from that user, she will still be able to log on to the server, SHOW DATABASES, etc.

Whenever the user attempts an operation, that operation is permitted if either of the following is true:

• her column privileges say she can do so;

• her table (db_name.tbl_name) privileges say she can do so;

• her database (db_name.*) and host privileges say she can do so;

• her global privileges (*.*) say she can do so.

Pitfalls

This should be your checklist, whenever you update user permissions:

• In GRANT and REVOKE, quote both username and domain name. I.e. use the form 'user'@'domain.com', where quotation marks appear around the user name as well as around the domain name or IP adress.

• Always set a password for users. Make it a rule to always use the IDENTIFIED BY clause in any GRANT commands you issue. Users created without IDENTIFIED BY can log in without specifying a password!

• If you do not specify a host, this is the same as creating an account which is allowed to log in from anywhere on the network.

• Some changes (namely global privileges and password changes) are not noted by the server until the client drops the connection and re-connects.

• The currently selected database matters! If you do not specify a database in the grant statement, the current database is assumed.

• If you revoke all privileges for a user, chances are that the user definition should be eradicated from the database. Do this by inspecting the table mysql.user, and delete any records pertaining to that user. This is the only time I can think of, when you should meddle directly with the privilege tables.

  mysql> delete from user where User='user_a'
      -> and host='%';
  
  mysql> flush privileges;
  

  Don't forget that FLUSH PRIVILEGES! Otherwise, your changes won't take effect until you restart the server.

Using date and time values

Using dates in MySQL is sometimes confusing for people moving from other DB systems. When using dates in MySQL,

• Always include the time value, if the field you're searching on is of type TIMESTAMP or DATETIME. Using a partial date or a date without a time (e.g. "2000-08-14" instead of "2000-08-14 00:00:00") will get you nowhere.

• Always use 4-digit years

using timestamps

You can have more than one column of type TIMESTAMP defined in a table, but only one will be updated when a row changes. And besides... what would be the point?

Case sensitivity in MySQL

database objects

Here's the quick rundown: Column names and column aliases are always case insensitive; database and table names may be considered case insensitive, depending on the underlying OS, but you must use the same case if you refer to one of these more than once in a statement; table aliases are always case sensitive.

All in all, you should always refer to database objects using consistent case, to ensure portability and minimizing risks.

string comparisons

If any part of a comparison is case sensitive, i.e. the field you use, the entire evaluation is done in a case sensitive manner.

If case is important when searching for data in a table, create CHAR and VARCHAR fields using the BINARY keyword. For larger text objects, use BLOB fields for case-sensitive comparisons, and TEXT fields for case-insensitive comparisons.

Note that case sensitive searches and functions are significantly faster than their case-insensitive counterparts. This is very important in large data sets.

Beginning with version 3.23.00, you can force a query into using case sensitive searches, by using the BINARY cast:

mysql> SELECT * FROM table_name
    -> WHERE BINARY column_name = "searchstring";

Good practices

This section is not a FAQ as such. It is a compilation of some of the spinoff discussions that crop up on the mailing list every now and then, and sums up the experiences of many developers.

Storing images and other binary data

Relying on table structure (not!)

You should never, ever, rely on table structure when you do INSERTs, i.e.

mysql> INSERT INTO my_table 
    -> VALUES (val1, val2, ..., valn);

The reason is simple: Once you have a single statement like this floating around in your code, the table structure is forever locked. If you delete or add even a single column, the statement will not work.

Always specify the fields that you wish to insert data into, e.g.

mysql> INSERT INTO my_table (col1, col2, ..., coln)
    -> VALUES (val1, val2, ..., valn);

For much the same reasons, you should always specify the columns you wish to retrieve in a SELECT statement, i.e. make it a rule not to use the * operator. This will help you keep down traffic between the server and client, you keep down the number of side-effects resulting from table restructuring, and maybe most importantly: it helps in documenting your code.

You will hate doing this when you first create your database. However, 2 weeks later when your boss comes up with a "nifty idea", you will give yourself a nice pat on the back gloating at your foresightfulness...

Error codes and messages

This is a short run-down of many of the error-codes and messages you may come upon while using MySQL. Note that, as of. v. 3.23.29a, the MySQL distribution includes a nifty tool called perror. From your command prompt, simply type

$ perror <errorcode>

and see the resulting message.

28:

You ran out of disk space. Delete unnecesary files, or move the MySQL data files to another partition.

127:

Some data or, more likely, the index is corrupt. Do a REPAIR TABLE <tablename> to fix the problems.

1045: Access denied for user: ...

You forgot to specify a password, or you used the wrong one. Use the -u and -p options when starting the client. (use mysql --help to see how)

1046: No Database Selected

You must use the command USE database_name or the mysql_select_db() function to select a database to use.

1062: Duplicate entry 'x' for key n

You have attempted to insert or update data that would break the integrity of a UNIQUE index (most likely the primary key). You must redefine either the data you're trying to insert, or the indexes which you have imposed on the table.

This error is very often caused by an AUTO_INCREMENT integer field which was defined as a TINYINT. If this is the case, redefine the field to be an INT (using ALTER TABLE) instead. Yoy might also want to read up on the section on AUTO_INCREMENT usage.

1064: Syntax error

You wrote something that doesn't match the syntax definitions in the manual. When you have checked everything else, here's something to consider: A common error is to put a space between a function name and its opening parenthesis '('. Another common error is to use a reserved word out of its normal context.

2000: parse error near ...

see explanation for 1064: Syntax error

2002: Can't connect to local MySQL server

The MySQL socket file (usually, /tmp/mysql.sock) is missing. See the notes on connecting to the server.

 

---

Back                                                                                                                                                  Home Page