Preface

This is the Reference Manual for all releases of the MySQL Database
System through version 4.1.16. It is applicable for older
versions of the MySQL software (such as 3.23 or 4.0-production)
because functional changes are indicated with reference to a version
number.
For later MySQL releases, see the appropriately-numbered edition of
this manual.

The MySQL® software delivers a very fast, multi-threaded,
multi-user, and robust SQL (Structured Query Language) database
server. MySQL Server is intended for mission-critical, heavy-load
production systems as well as for embedding into mass-deployed
software. MySQL is a registered trademark of MySQL AB.

If you have found a sensitive security bug in MySQL Server, please
let us know immediately by sending an email message to
<security@mysql.com>.

1.1. About This Manual

This is the Reference Manual for all releases of the MySQL
Database System from version 3.23 through release
4.1.16. It is also applicable for versions of the MySQL
software previous to 4.1 (such as 3.23 or 4.0)
because functional changes are indicated with reference to version
numbers.
For later MySQL releases, see the appropriately-numbered edition
of this manual.

Because this manual serves as a reference, it does not provide
general instruction on SQL or relational database concepts. It
also does not teach you how to use your operating system or
command-line interpreter.

The MySQL Database Software is under constant development, and the
Reference Manual is updated frequently as well. The most recent
version of the manual is available online in searchable form at
http://dev.mysql.com/doc/. Other formats also are available
there, including HTML, PDF, and Windows CHM versions.

The Reference Manual source files are written in DocBook XML
format. The HTML version and other formats are produced
automatically, primarily using the DocBook XSL stylesheets. For
information about DocBook, see http://docbook.org/

If you have any suggestions concerning additions or corrections to
this manual, please send them to the documentation team at
<docs@mysql.com>.

This manual was originally written by David Axmark and Michael
“Monty” Widenius. It is maintained by the MySQL
Documentation Team, consisting of Paul DuBois, Stefan Hinz, Mike
Hillyer, and Jon Stephens. For the many other contributors, see
Appendix C, Credits.

The copyright to this manual is owned by the Swedish company MySQL
AB. MySQL® and the MySQL logo are registered trademarks of
MySQL AB. Other trademarks and registered trademarks referred to
in this manual are the property of their respective owners, and
are used for identification purposes only.

1.2. Conventions Used in This Manual

This manual uses certain typographical conventions:

Text in this style is used for SQL
statements; database, table, and column names; program listings
and source code; and environment variables. Example: “To
reload the grant tables, use the FLUSH
PRIVILEGES statement”.

Text in this style indicates input that
you type in examples.

Text in this style indicates the names of
executable programs and scripts, examples being
mysql (the MySQL command line client program)
and mysqld (the MySQL server executable).

Text in this style is used for
variable input for which you should substitute a value of your
own choosing.

Filenames and directory names are written like this: “The
global my.cnf file is located in the
/etc directory”.

Character sequences are written like this: “To specify a
wildcard, use the ‘%’
character”.

Text in this style is used for emphasis.

Text in this style is used in
table headings and to convey especially strong emphasis.

When commands are shown that are meant to be executed from within a
particular program, the prompt shown preceding the command indicates
which command to use. For example, shell>
indicates a command that you execute from your login shell, and
mysql> indicates a statement that you execute
from the mysql client program:

shell> type a shell command here
mysql> type a mysql statement here

The “shell” is your command interpreter. On Unix, this
is typically a program such as sh,
csh, or bash. On Windows, the
equivalent program is command.com or
cmd.exe, typically run in a console window.

When you enter a command or statement shown in an example, do not
type the prompt shown in the example.

Database, table, and column names must often be substituted into
statements. To indicate that such substitution is necessary, this
manual uses db_name,
tbl_name, and
col_name. For example, you might see a
statement like this:

mysql> SELECT col_name FROM db_name.tbl_name;

This means that if you were to enter a similar statement, you would
supply your own database, table, and column names, perhaps like
this:

mysql> SELECT author_name FROM biblio_db.author_list;

SQL keywords are not case sensitive and may be written in any
lettercase. This manual uses uppercase.

In syntax descriptions, square brackets
(‘[’ and
‘]’) indicate optional words or
clauses. For example, in the following statement, IF
EXISTS is optional:

DROP TABLE [IF EXISTS] tbl_name

When a syntax element consists of a number of alternatives, the
alternatives are separated by vertical bars
(‘|’). When one member from a set of
choices may be chosen, the alternatives are
listed within square brackets (‘[’
and ‘]’):

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)

When one member from a set of choices must be
chosen, the alternatives are listed within braces
(‘{’ and
‘}’):

{DESCRIBE | DESC} tbl_name [col_name | wild]

An ellipsis (…) indicates the omission of
a section of a statement, typically to provide a shorter version of
more complex syntax. For example, INSERT …
SELECT is shorthand for the form of
INSERT statement that is followed by a
SELECT statement.

An ellipsis can also indicate that the preceding syntax element of a
statement may be repeated. In the following example, multiple
reset_option values may be given, with
each of those after the first preceded by commas:

RESET reset_option [,reset_option] ...

Commands for setting shell variables are shown using Bourne shell
syntax. For example, the sequence to set the CC
environment variable and run the configure
command looks like this in Bourne shell syntax:

shell> CC=gcc ./configure

If you are using csh or tcsh,
you must issue commands somewhat differently:

shell> setenv CC gcc
shell> ./configure

1.3. Overview of MySQL AB

MySQL AB is the company of the MySQL founders and main developers.
MySQL AB was originally established in Sweden by David Axmark, Allan
Larsson, and Michael “Monty” Widenius.

By the way, the “AB” part of the company name is the
acronym for the Swedish “aktiebolag,” or “stock
company.” It translates to “MySQL, Inc.” In
fact, MySQL, Inc. and MySQL GmbH are examples of MySQL AB
subsidiaries. They are located in the United States and Germany,
respectively.

1.4. Overview of the MySQL Database Management System

MySQL, the most popular Open Source SQL database management system,
is developed, distributed, and supported by MySQL AB. MySQL AB is a
commercial company, founded by the MySQL developers. It is a second
generation Open Source company that unites Open Source values and
methodology with a successful business model.

The MySQL Web site (http://www.mysql.com/) provides
the latest information about MySQL software and MySQL AB.

MySQL is a database management system.

A database is a structured collection of data. It may be
anything from a simple shopping list to a picture gallery or the
vast amounts of information in a corporate network. To add,
access, and process data stored in a computer database, you need
a database management system such as MySQL Server. Since
computers are very good at handling large amounts of data,
database management systems play a central role in computing, as
standalone utilities or as parts of other applications.

MySQL is a relational database management system.

A relational database stores data in separate tables rather than
putting all the data in one big storeroom. This adds speed and
flexibility. The SQL part of “MySQL” stands for
“Structured Query Language.” SQL is the most common
standardized language used to access databases and is defined by
the ANSI/ISO SQL Standard. The SQL standard has been evolving
since 1986 and several versions exist. In this manual,
“SQL-92” refers to the standard released in 1992,
“SQL:1999” refers to the standard released in 1999,
and “SQL:2003” refers to the current version of the
standard. We use the phrase “the SQL standard” to
mean the current version of the SQL Standard at any time.

MySQL software is Open Source.

Open Source means that it is possible for anyone to use and
modify the software. Anybody can download the MySQL software
from the Internet and use it without paying anything. If you
wish, you may study the source code and change it to suit your
needs. The MySQL software uses the GPL (GNU General Public
License), http://www.fsf.org/licenses/, to define
what you may and may not do with the software in different
situations. If you feel uncomfortable with the GPL or need to
embed MySQL code into a commercial application, you can buy a
commercially licensed version from us. See the MySQL Licensing
Overview for more information
(http://www.mysql.com/company/legal/licensing/).

The MySQL Database Server is very fast, reliable, and easy to
use.

If that is what you are looking for, you should give it a try.
MySQL Server also has a practical set of features developed in
close cooperation with our users. You can find a performance
comparison of MySQL Server with other database managers on our
benchmark page. See Section 7.1.4, “The MySQL Benchmark Suite”.

MySQL Server was originally developed to handle large databases
much faster than existing solutions and has been successfully
used in highly demanding production environments for several
years. Although under constant development, MySQL Server today
offers a rich and useful set of functions. Its connectivity,
speed, and security make MySQL Server highly suited for
accessing databases on the Internet.

MySQL Server works in client/server or embedded systems.

The MySQL Database Software is a client/server system that
consists of a multi-threaded SQL server that supports different
backends, several different client programs and libraries,
administrative tools, and a wide range of application
programming interfaces (APIs).

We also provide MySQL Server as an embedded multi-threaded
library that you can link into your application to get a
smaller, faster, easier-to-manage standalone product.

A large amount of contributed MySQL software is available.

It is very likely that your favorite application or language
supports the MySQL Database Server.

The official way to pronounce “MySQL” is “My Ess
Que Ell” (not “my sequel”), but we don't mind if
you pronounce it as “my sequel” or in some other
localized way.

1.4.1. History of MySQL

We started out with the intention of using the
mSQL database system to connect to our tables
using our own fast low-level (ISAM) routines. However, after some
testing, we came to the conclusion that mSQL
was not fast enough or flexible enough for our needs. This
resulted in a new SQL interface to our database but with almost
the same API interface as mSQL. This API was
designed to allow third-party code that was written for use with
mSQL to be ported easily for use with MySQL.

The derivation of the name MySQL is not clear. Our base directory
and a large number of our libraries and tools have had the prefix
“my” for well over 10 years. However, co-founder
Monty Widenius's daughter is also named My. Which of the two gave
its name to MySQL is still a mystery, even for us.

The name of the MySQL Dolphin (our logo) is “Sakila,”
which was chosen by the founders of MySQL AB from a huge list of
names suggested by users in our “Name the Dolphin”
contest. The winning name was submitted by Ambrose Twebaze, an
Open Source software developer from Swaziland, Africa. According
to Ambrose, the feminine name Sakila has its roots in SiSwati, the
local language of Swaziland. Sakila is also the name of a town in
Arusha, Tanzania, near Ambrose's country of origin, Uganda.

1.4.2. The Main Features of MySQL

The following list describes some of the important characteristics
of the MySQL Database Software. See also
Section 1.6, “MySQL Development Roadmap”, for more information about current and
upcoming features.

The server is available as a separate program for use in a
client/server networked environment. It is also available as a
library that can be embedded (linked) into standalone
applications. Such applications can be used in isolation or in
environments where no network is available.

Full support for SQL GROUP BY and
ORDER BY clauses. Support for group
functions (COUNT(), COUNT(DISTINCT
...), AVG(),
STD(), SUM(),
MAX(), MIN(), and
GROUP_CONCAT()).

Support for LEFT OUTER JOIN and
RIGHT OUTER JOIN with both standard SQL and
ODBC syntax.

Support for aliases on tables and columns as required by
standard SQL.

DELETE, INSERT,
REPLACE, and UPDATE
return the number of rows that were changed (affected). It is
possible to return the number of rows matched instead by
setting a flag when connecting to the server.

The MySQL-specific SHOW command can be used
to retrieve information about databases, database engines,
tables, and indexes.

The EXPLAIN command can be used to
determine how the optimizer resolves a query.

Function names do not clash with table or column names. For
example, ABS is a valid column name. The
only restriction is that for a function call, no spaces are
allowed between the function name and the
‘(’ that follows it. See
Section 9.6, “Treatment of Reserved Words in MySQL”.

You can mix tables from different databases in the same query
(as of MySQL 3.22).

Security:

A privilege and password system that is very flexible and
secure, and that allows host-based verification. Passwords are
secure because all password traffic is encrypted when you
connect to a server.

Scalability and Limits:

Handles large databases. We use MySQL Server with databases
that contain 50 million records. We also know of users who use
MySQL Server with 60,000 tables and about 5,000,000,000 rows.

Up to 64 indexes per table are allowed (32 before MySQL
4.1.2). Each index may consist of 1 to 16 columns or parts of
columns. The maximum index width is 1000 bytes (500 before
MySQL 4.1.2). An index may use a prefix of a column for
CHAR, VARCHAR,
BLOB, or TEXT column
types.

Connectivity:

Clients can connect to the MySQL server using TCP/IP sockets
on any platform. On Windows systems in the NT family (NT,
2000, XP, or 2003), clients can connect using named pipes. On
Unix systems, clients can connect using Unix domain socket
files.

In MySQL versions 4.1 and higher, Windows servers also support
shared-memory connections if started with the
--shared-memory option. Clients can connect
through shared memory by using the
--protocol=memory option.

The Connector/ODBC (MyODBC) interface provides MySQL support
for client programs that use ODBC (Open Database Connectivity)
connections. For example, you can use MS Access to connect to
your MySQL server. Clients can be run on Windows or Unix.
MyODBC source is available. All ODBC 2.5 functions are
supported, as are many others. See
Chapter 19, Connectors.

The Connector/J interface provides MySQL support for Java
client programs that use JDBC connections. Clients can be run
on Windows or Unix. Connector/J source is available. See
Chapter 19, Connectors.

Full support for several different character sets, including
latin1 (cp1252), german,
big5, ujis, and more.
For example, the Scandinavian characters
‘å’,
‘ä’ and
‘ö’ are allowed in table and
column names. Unicode support is available as of MySQL 4.1.

All data is saved in the chosen character set. All comparisons
for normal string columns are case-insensitive.

Sorting is done according to the chosen character set (using
Swedish collation by default). It is possible to change this
when the MySQL server is started. To see an example of very
advanced sorting, look at the Czech sorting code. MySQL Server
supports many different character sets that can be specified
at compile time and runtime.

Clients and Tools:

MySQL Server has built-in support for SQL statements to check,
optimize, and repair tables. These statements are available
from the command line through the
mysqlcheck client. MySQL also includes
myisamchk, a very fast command-line utility
for performing these operations on MyISAM
tables. See Chapter 5, Database Administration.

All MySQL programs can be invoked with the
--help or -? options to
obtain online assistance.

1.4.3. MySQL Stability

This section addresses the questions, “How stable
is MySQL Server?” and, “Can I
depend on MySQL Server in this project?” We will
try to clarify these issues and answer some important questions
that concern many potential users. The information in this section
is based on data gathered from the mailing lists, which are very
active in identifying problems as well as reporting types of use.

The original code stems back to the early 1980s. It provides a
stable code base, and the ISAM table format
used by the original storage engine remains backward-compatible.
At TcX, the predecessor of MySQL AB, MySQL code has worked in
projects since mid-1996, without any problems. When the MySQL
Database Software initially was released to a wider public, our
new users quickly found some pieces of untested code. Each new
release since then has had fewer portability problems, even though
each new release has also had many new features.

Each release of the MySQL Server has been usable. Problems have
occurred only when users try code from the “gray
zones.” Naturally, new users don't know what the gray zones
are; this section therefore attempts to document those areas that
are currently known. The descriptions mostly deal with Versions
3.23 and later of MySQL Server. All known and reported bugs are
fixed in the latest version, with the exception of those listed in
the bugs section, which are design-related. See
Section A.8, “Known Issues in MySQL”.

The MySQL Server design is multi-layered with independent modules.
Some of the newer modules are listed here with an indication of
how well-tested each of them is:

Replication (Stable)

Large groups of servers using replication are in production
use, with good results. Work on enhanced replication features
is continuing.

InnoDB tables (Stable)

The InnoDB transactional storage engine has
been stable since version 3.23.49. InnoDB
is being used in large, heavy-load production systems.

BDB tables (Stable)

The Berkeley DB code is very stable, but we
are still improving the BDB transactional
storage engine interface in MySQL Server.

Full-text searches (Stable)

Full-text searching is widely used. Important feature
enhancements were added in MySQL 4.0 and 4.1.

MyODBC 3.51 (Stable)

MyODBC 3.51 uses ODBC SDK 3.51 and is in
wide production use. Some issues brought up appear to be
application-related and independent of the ODBC driver or
underlying database server.

1.4.4. How Large MySQL Tables Can Be

MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the
MyISAM storage engine in MySQL 3.23, the
maximum table size was increased to 65536 terabytes
(2567 – 1 bytes). With this
larger allowed table size, the maximum effective table size for
MySQL databases is usually determined by operating system
constraints on file sizes, not by MySQL internal limits.

The InnoDB storage engine maintains
InnoDB tables within a tablespace that can be
created from several files. This allows a table to exceed the
maximum individual file size. The tablespace can include raw disk
partitions, which allows extremely large tables. The maximum
tablespace size is 64TB.

The following table lists some examples of operating system
file-size limits. This is only a rough guide and is not intended
to be definitive. For the most up-to-date information, be sure to
check the documentation specific to your operating system.

Operating System

File-size Limit

Linux 2.2-Intel 32-bit

2GB (LFS: 4GB)

Linux 2.4+

(using ext3 filesystem) 4TB

Solaris 9/10

16TB

NetWare w/NSS filesystem

8TB

Win32 w/ FAT/FAT32

2GB/4GB

Win32 w/ NTFS

2TB (possibly larger)

MacOS X w/ HFS+

2TB

On Linux 2.2, you can get MyISAM tables larger
than 2GB in size by using the Large File Support (LFS) patch for
the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS
to get support for big files (up to 2TB). Most current Linux
distributions are based on kernel 2.4 and include all the required
LFS patches. With JFS and XFS, petabyte and larger files are
possible on Linux. However, the maximum available file size still
depends on several factors, one of them being the filesystem used
to store MySQL tables.

Windows users please note: FAT and VFAT (FAT32) are
not considered suitable for production use
with MySQL. Use NTFS instead.

By default, MySQL creates MyISAM tables with an
internal structure that allows a maximum size of about 4GB. You
can check the maximum table size for a MyISAM
table with the SHOW TABLE STATUS statement or
with myisamchk -dv
tbl_name. See
Section 13.5.4, “SHOW Syntax”.

If you need a MyISAM table that is larger than
4GB and your operating system supports large files, the
CREATE TABLE statement supports
AVG_ROW_LENGTH and MAX_ROWS
options. See Section 13.1.5, “CREATE TABLE Syntax”. You can also change
these options with ALTER TABLE to increase a
table's maximum allowable size after the table has been created.
See Section 13.1.2, “ALTER TABLE Syntax”.

Other ways to work around file-size limits for
MyISAM tables are as follows:

1.4.5. Year 2000 Compliance

The MySQL Server itself has no problems with Year 2000 (Y2K)
compliance:

MySQL Server uses Unix time functions that handle dates into
the year 2037 for
TIMESTAMP values. For
DATE and DATETIME
values, dates through the year 9999 are
accepted.

All MySQL date functions are implemented in one source file,
sql/time.cc, and are coded very carefully
to be year 2000-safe.

In MySQL, the YEAR column type can store
the years 0 and 1901 to
2155 in one byte and display them using two
or four digits. All two-digit years are considered to be in
the range 1970 to 2069,
which means that if you store 01 in a
YEAR column, MySQL Server treats it as
2001.

The following simple demonstration illustrates that MySQL Server
has no problems with DATE or
DATETIME values through the year 9999, and no
problems with TIMESTAMP values until after the
year 2030:

The final two TIMESTAMP column values are zero
because the year values (2040,
9999) exceed the TIMESTAMP
maximum. The TIMESTAMP data type, which is used
to store the current time, supports values that range from
'1970-01-01 00:00:00' to '2030-01-01
00:00:00' on 32-bit machines (signed value). On 64-bit
machines, TIMESTAMP handles values up to
2106 (unsigned value).

Although MySQL Server itself is Y2K-safe, you may run into
problems if you use it with applications that are not Y2K-safe.
For example, many old applications store or manipulate years using
two-digit values (which are ambiguous) rather than four-digit
values. This problem may be compounded by applications that use
values such as 00 or 99 as
“missing” value indicators. Unfortunately, these
problems may be difficult to fix because different applications
may be written by different programmers, each of whom may use a
different set of conventions and date-handling functions.

Thus, even though MySQL Server has no Y2K problems, it
is the application's responsibility to provide unambiguous
input. See Section 11.3.4, “Y2K Issues and Date Types”, for MySQL
Server's rules for dealing with ambiguous date input data that
contains two-digit year values.

MaxDB is a heavy-duty enterprise database. The database management
system is SAP-certified.

MaxDB is the new name of a database management system formerly
called SAP DB. In 2003 SAP AG and MySQL AB joined a partnership and
re-branded the database system to MaxDB. The development of MaxDB
has continued since then as it was done before—through the SAP
developer team.

MySQL AB cooperates closely with the MaxDB team at SAP around
delivering improvements to the MaxDB product. Joint efforts include
development of new native drivers to enable more efficient usage of
MaxDB in the Open Source community, and improvement of documentation
to expand the MaxDB user base. Interoperability features between
MySQL and MaxDB database also are seen as important. For example,
the new MaxDB Synchronization Manager supports data synchronization
from MaxDB to MySQL.

The MaxDB database management system does not share a common
code-base with the MySQL database management system. The MaxDB and
MySQL database management systems are independent products provided
by MySQL AB.

MySQL AB offers a complete portfolio of Professional Services for
MaxDB.

1.5.1. What is MaxDB?

MaxDB is an ANSI SQL-92 (entry level) compliant relational
database management system (RDBMS) from SAP AG, that is delivered
by MySQL AB as well. MaxDB fulfills the needs for enterprise
usage: safety, scalability, high concurrency, and performance. It
runs on all major operating systems. Over the years it has proven
able to run SAP R/3 and terabytes of data in 24×7 operation.

The database development started in 1977 as a research project at
the Technical University of Berlin. In the early 1980s it became a
database product that subsequently was owned by Nixdorf, Siemens
Nixdorf, Software AG, and today by SAP AG. Along the way, it has
been named VDN, Reflex, Supra 2, DDB/4, Entire SQL-DB-Server, and
ADABAS D. In 1997, SAP took over the software from software AG and
renamed it to SAP DB. Since October 2000, SAP DB sources
additionally were released as Open Source under the GNU General
Public License (see Appendix J, GNU General Public License).

In 2003, SAP AG and MySQL AB formed a partnership and re-branded
the database system to MaxDB.

1.5.2. History of MaxDB

The history of MaxDB goes back to SAP DB, SAP AG's DBMS. That is,
MaxDB is a re-branded and enhanced version of SAP DB. For many
years, MaxDB has been used for small, medium, and large
installations of the mySAP Business Suite and other demanding SQL
applications requiring an enterprise-class DBMS with regard to the
number of users, the transactional workload, and the size of the
database.

SAP DB was meant to provide an alternative to third-party database
systems such as Oracle, Microsoft SQL Server, and DB2 by IBM. In
October 2000, SAP AG released SAP DB under the GNU GPL license
(see Appendix J, GNU General Public License), thus making it Open Source
software.

Today, MaxDB is used in about 3,500 SAP customer installations
worldwide. Moreover, the majority of all DBMS installations on
Unix and Linux within SAP’s IT department rely on MaxDB. MaxDB
is tuned toward heavy-duty online transaction processing (OLTP)
with several thousand users and database sizes ranging from
several hundred GB to multiple TB.

In 2003, SAP and MySQL concluded a partnership and development
cooperation agreement. As a result, SAP's database system SAP DB
has been delivered under the name of MaxDB by MySQL since the
release of version 7.5 (November 2003).

Version 7.5 of MaxDB is a direct advancement of the SAP DB 7.4
code base. Therefore, the MaxDB software version 7.5 can be used
as a direct upgrade of previous SAP DB versions starting 7.2.04
and higher.

The former SAP DB development team at SAP AG is responsible, now
as before, for developing and supporting MaxDB. MySQL AB
cooperates closely with the MaxDB team at SAP around delivering
improvements to the MaxDB product, see Section 1.5, “Overview of the MaxDB Database Management System”.
Both SAP AG and MySQL AB handle the sale and distribution of
MaxDB. The advancement of MaxDB and the MySQL Server leverages
synergies that benefit both product lines.

MaxDB is subjected to SAP AG's complete quality assurance process
before it is shipped with SAP solutions or provided as a download
from the MySQL site.

1.5.3. Features of MaxDB

MaxDB is a heavy-duty, SAP-certified Open Source database for OLTP
and OLAP usage which offers high reliability, availability,
scalability, and a very comprehensive feature set. It is targeted
for large mySAP Business Suite environments and other applications
that require maximum enterprise-level database functionality and
complements the MySQL database server.

MaxDB operates as a client/server product. It was developed to
meet the needs of installations in OLTP and Data
Warehouse/OLAP/Decision Support scenarios and offers these
benefits:

1.5.4. Licensing and Support

MaxDB can be used under the same licenses available for the other
products distributed by MySQL AB. Thus, MaxDB is available under
the GNU General Public License, and a commercial license. For more
information on licensing, see
http://www.mysql.com/company/legal/licensing/.

MySQL AB offers MaxDB technical support to non-SAP customers.
MaxDB support is available on various levels (Basic, Silver, and
Gold), which expand from unlimited email/web-support to 24×7
phone support for business critical systems.

MySQL AB also offers Licenses and Support for MaxDB when used with
SAP Applications, like SAP NetWeaver and mySAP Business Suite. For
more information on licenses and support for your needs, please
contact MySQL AB. (See
http://www.mysql.com/company/contact/.)

Consulting and training services are available. MySQL gives
classes on MaxDB at regular intervals. See
http://www.mysql.com/training/ for a list of
classes.

1.5.5. Feature Differences Between MaxDB and MySQL

MaxDB is MySQL AB's SAP-certified database. The MaxDB database
server complements the MySQL AB product portfolio. Some MaxDB
features are not available on the MySQL database management server
and vice versa.

The following list summarizes the main differences between MaxDB
and MySQL; it is not complete.

MaxDB runs as a client/server system. MySQL can run as a
client/server system or as an embedded system.

MaxDB is distributed with user interfaces that are text-based,
graphical, or Web-based. MySQL is distributed with text-based
user interfaces only; graphical user interfaces (MySQL Query
Browser, MySQL Administrator) are shipped separately from the
main distributions. Web-based user interfaces for MySQL are
offered by third parties.

MaxDB supports a number of programming interfaces that also
are supported by MySQL. For developing with MaxDB, the MaxDB
ODBC Driver, SQL Database Connectivity (SQLDBC), JDBC Driver,
Perl and Python modules and a MaxDB PHP extension, which
provides access to MySQL MaxDB databases using PHP, are
available. Third Party Programming Interfaces: Support for OLE
DB, ADO, DAO, RDO and .NET through ODBC. MaxDB supports
embedded SQL with C/C++.

MaxDB includes administrative features that MySQL does not
have: job scheduling by time, event, and alert, and sending
messages to a database administrator on alert thresholds.

1.5.6. Interoperability Features Between MaxDB and MySQL

MaxDB and MySQL are independent database management servers. The
interoperation of the systems is possible in a way that the
systems can exchange their data. To exchange data between MaxDB
and MySQL, you can use the import and export tools of the systems
or the MaxDB Synchronization Manager. The import and export tools
can be used to transfer data in an infrequent, manual fashion. The
MaxDB Synchronization Manager offers faster, automatic data
transfer capabilities.

The MaxDB Loader can be used to export data and object
definitions. The Loader can export data using MaxDB internal,
binary formats and text formats (CSV). Data exported from MaxDB in
text formats can be imported into MySQL using the
mysqlimport client program. To export MySQL
data, you can use either mysqldump to create
INSERT statements or SELECT ... INTO
OUTFILE to create a text file (CSV). Use the MaxDB
Loader to import the data files generated by MySQL.

Object definitions can be exchanged between the systems using
MaxDB Loader and the MySQL tool mysqldump. As
the SQL dialects of both systems differ slightly and MaxDB has
features currently not supported by MySQL like SQL constraints, we
recommend to hand-tune the definition files. The
mysqldump tool offers an option
--compatible=maxdb to produce output that is
compatible to MaxDB to make porting easier.

The MaxDB Synchronization Manager is available as part of MaxDB
7.6. The Synchronization Manager supports creation of asynchronous
replication scenarios between several MaxDB instances. However,
interoperability features also are planned, so that the
Synchronization Manager supports replication to and from a MySQL
server.

In the first release, the Synchronization Manager supports
inserting data into MySQL. This means that initially only
replication from MaxDB to MySQL is supported. In the course of
2005, exporting of data from a MySQL server to the Synchronization
Manager will be added, thus adding support for MySQL to MaxDB
replication scenarios.

1.5.7. MaxDB-Related Links

The main page for MaxDB information is
http://www.mysql.com/products/maxdb, which provides
details about the features of the MaxDB database management
systems and has pointers to available documentation.

The MySQL Reference Manual does not contain any MaxDB
documentation other than the introduction given in this section.
MaxDB has its own documentation, which is called the MaxDB library
and is available at
http://dev.mysql.com/doc/maxdb/index.html.

MySQL AB runs a community mailing list on MaxDB; see
http://lists.mysql.com/maxdb. The list shows a
vivid community discussion. Many of the core developers contribute
to it. Product announcements are sent to the list.

A Web forum on MaxDB is available at
http://forums.mysql.com/. The forum focuses on
MaxDB questions not related to SAP applications.

1.6. MySQL Development Roadmap

This section provides a snapshot of the MySQL development roadmap,
including major features implemented in or planned for various
MySQL releases. The following sections provide information for
each release series.

The current production release series is MySQL 5.0, which was
declared stable for production use as of MySQL 5.0.15, released in
October 2005. The previous production release series was MySQL
4.1, which was declared stable for production use as of MySQL
4.1.7, released in October 2004. “Production status”
means that future 5.0 and 4.1 development is limited only to
bugfixes. For the older MySQL 4.0 and 3.23 series, only critical
bugfixes are made.

Active MySQL development currently is taking place in the MySQL
5.0 and 5.1 release series; and new features are being added only
to the latter.

1.6.1.1. Features Available in MySQL 4.0

MySQL 4.0 further increased the speed of MySQL Server
in a number of areas, such as bulk
INSERT statements, searching on
packed indexes, full-text searching (using
FULLTEXT indexes), and
COUNT(DISTINCT).

Introduction of Embedded MySQL Server

The Embedded Server library added in this release can
easily be used to create standalone and embedded
applications. The embedded server provides an
alternative to using MySQL in a client/server
environment. See
Section 1.6.1.2, “The Embedded MySQL Server”.

InnoDB storage engine as standard

The InnoDB storage engine began to
be offered as a standard feature of the MySQL server.
This provided full support for ACID transactions,
foreign keys with cascading UPDATE
and DELETE, and row-level locking
as standard features. See Chapter 15, The InnoDB Storage Engine.

New functionality

The enhanced FULLTEXT search
capabilities of MySQL Server 4.0 enabled
FULLTEXT indexing of large text
masses with both binary and natural-language searching
logic. It became possible to customize minimal word
length and define your own stop word lists in most
human languages, enabling a broader class of
applications to be built with MySQL Server. See
Section 12.7, “Full-Text Search Functions”.

Standards compliance, portability, and migration

MySQL Server added support for the
UNION statement, a standard SQL
feature.

Features to simplify migration from other database
systems to MySQL Server include TRUNCATE
TABLE (as in Oracle)
.

Internationalization

German-speaking users should note that MySQL 4.0 added
support for a new character set,
latin1_de, which ensures that words
with umlauts are sorted in the same order as in German
telephone books.

Usability enhancements

As of version 4.0, most mysqld
parameters (startup options) can be set without taking
down the server. This is a convenient feature for
database administrators. See
Section 13.5.3, “SET Syntax”.

Multiple-table DELETE and
UPDATE statements were added.

On Windows, symbolic link handling at the database
level was enabled by default. On Unix, the
MyISAM storage engine added support
for symbolic linking at the table level (and not just
the database level as before).

The addition of the
SQL_CALC_FOUND_ROWS and
FOUND_ROWS() functions made it
possible to find out the number of rows a
SELECT query that includes a
LIMIT clause would have returned
without that clause.

1.6.1.2. The Embedded MySQL Server

The libmysqld embedded server library made
MySQL Server suitable for a wider range of applications. Using
this library, developers can embed MySQL Server into various
applications and electronics devices, where the end user has
no knowledge of there actually being an underlying database.
Embedded MySQL Server is ideal for use in Internet appliances,
public kiosks, turnkey hardware/software combination units,
high performance Internet servers, self-contained databases
distributed on CD-ROM, and so on.

Support for a number of additional storage engines was
implemented in the MySQL 4.1 release series:

The EXAMPLE storage engine is
a “stub” engine that serves as an
example in the MySQL source code for writing new
storage engines, and is primarily of interest to
developers. See
Section 14.5, “The EXAMPLE Storage Engine”.

NDB Cluster is the storage
engine used by MySQL Cluster to implement tables
that are partitioned over many computers. See
Chapter 16, MySQL Cluster.

Note: These engine
were implemented at different points in the
development of MySQL 4.1. Please see the indicated
sections for particulars in each case.

Standards compliance, portability,
and migration:

The enhanced client/server protocol available
beginning with MySQL 4.1.1 provides the ability to
pass multiple warnings to the client, rather than only
a single result, making it much easier to track
problems that occur in operations such as bulk data
loading.

To support applications that require the use of local
languages, the MySQL software added extensive Unicode
support through the utf8 and
ucs2 character sets.

Definition of character sets by column, table, and
database. This allows for a high degree of flexibility
in application design, particularly for multi-language
Web sites. See Chapter 10, Character Set Support.

Per-connection time zones support, allowing individual
clients to select their own time zones when necessary.

Usability enhancements:

The addition of a server-based HELP
command that can be used to get help information for
SQL statements. This information is always applicable
to the particular server version being used. Because
this information is available by issuing an SQL
statement, any client can access it. For example, the
help command of the
mysql command-line client has been
modified to have this capability.

The syntax INSERT ... ON DUPLICATE KEY UPDATE
... was implemented. This allows you to
update an existing row if the insert would have caused
a duplicate value for a primary or unique index. See
Section 13.2.4, “INSERT Syntax”.

1.6.3. What's New in MySQL 5.0

The following features are implemented in MySQL 5.0.

BIT Data
Type: Can be used to store numbers in binary
notation.

Cursors: Elementary support
for server-side cursors.

Data Dictionary (Information
Schema): The introduction of the
INFORMATION_SCHEMA database in MySQL 5.0
provided a standards-compliant means for accessing the MySQL
Server's metadata, that is, data about the databases
(schemas) on the server and the objects which they contain.

Instance Manager: Can be
used to start and stop the MySQL Server, even from a remote
host.

Precision Math: MySQL 5.0
introduced stricter criteria for acceptance or rejection of
data, and implemented a new library for fixed-point
arithmetic. These contributed to a much higher degree of
accuracy for mathematical operations and greater control
over invalid values.

Stored Routines: Support
for named stored procedures and stored functions was
implemented in MySQL 5.0.

Strict Mode and Standard Error
Handling: MySQL 5.0 added a strict mode where by
it follows standard SQL in a number of ways in which it did
not previously. Support for standard SQLSTATE error messages
was also implemented.

Triggers: MySQL 5.0 added
limited support for triggers.

VARCHAR Data
Type: The maximum effective length of a
VARCHAR column was increased to 65,532
bytes, and stripping of trailing whitespace was eliminated.

1.7.1. MySQL Mailing Lists

This section introduces the MySQL mailing lists and provides
guidelines as to how the lists should be used. When you subscribe
to a mailing list, you receive all postings to the list as email
messages. You can also send your own questions and answers to the
list.

To subscribe to or unsubscribe from any of the mailing lists
described in this section, visit
http://lists.mysql.com/. For most of them, you can
select the regular version of the list where you get individual
messages, or a digest version where you get one large message per
day.

Please do not send messages about subscribing
or unsubscribing to any of the mailing lists, because such
messages are distributed automatically to thousands of other
users.

Your local site may have many subscribers to a MySQL mailing list.
If so, the site may have a local mailing list, so that messages
sent from lists.mysql.com to your site are
propagated to the local list. In such cases, please contact your
system administrator to be added to or dropped from the local
MySQL list.

If you wish to have traffic for a mailing list go to a separate
mailbox in your mail program, set up a filter based on the message
headers. You can use either the List-ID: or
Delivered-To: headers to identify list
messages.

The MySQL mailing lists are as follows:

announce

This list is for announcements of new versions of MySQL and
related programs. This is a low-volume list to which all MySQL
users should subscribe.

mysql

This is the main list for general MySQL discussion. Please
note that some topics are better discussed on the
more-specialized lists. If you post to the wrong list, you may
not get an answer.

bugs

This list is for people who want to stay informed about issues
reported since the last release of MySQL or who want to be
actively involved in the process of bug hunting and fixing.
See Section 1.8, “How to Report Bugs or Problems”.

internals

This list is for people who work on the MySQL code. This is
also the forum for discussions on MySQL development and for
posting patches.

mysqldoc

This list is for people who work on the MySQL documentation:
people from MySQL AB, translators, and other community
members.

benchmarks

This list is for anyone interested in performance issues.
Discussions concentrate on database performance (not limited
to MySQL), but also include broader categories such as
performance of the kernel, filesystem, disk system, and so on.

packagers

This list is for discussions on packaging and distributing
MySQL. This is the forum used by distribution maintainers to
exchange ideas on packaging MySQL and on ensuring that MySQL
looks and feels as similar as possible on all supported
platforms and operating systems.

java

This list is for discussions about the MySQL server and Java.
It is mostly used to discuss JDBC drivers such as MySQL
Connector/J.

win32

This list is for all topics concerning the MySQL software on
Microsoft operating systems, such as Windows 9x, Me, NT, 2000,
XP, and 2003.

myodbc

This list is for all topics concerning connecting to the MySQL
server with ODBC.

gui-tools

This list is for all topics concerning MySQL graphical user
interface tools such as MySQL Administrator
and MySQL Query Browser.

cluster

This list is for discussion of MySQL Cluster.

dotnet

This list is for discussion of the MySQL server and the .NET
platform. It is mostly related to MySQL Connector/Net.

plusplus

This list is for all topics concerning programming with the
C++ API for MySQL.

perl

This list is for all topics concerning Perl support for MySQL
with DBD::mysql.

If you're unable to get an answer to your questions from a MySQL
mailing list or forum, one option is to purchase support from
MySQL AB. This puts you in direct contact with MySQL developers.

The following table shows some MySQL mailing lists in languages
other than English. These lists are not operated by MySQL AB.

1.7.1.1. Guidelines for Using the Mailing Lists

When you answer a question sent to a mailing list, if you
consider your answer to have broad interest, you may want to
post it to the list instead of replying directly to the
individual who asked. Try to make your answer general enough
that people other than the original poster may benefit from it.
When you post to the list, please make sure that your answer is
not a duplication of a previous answer.

Try to summarize the essential part of the question in your
reply. Don't feel obliged to quote the entire original message.

When answers are sent to you individually and not to the mailing
list, it is considered good etiquette to summarize the answers
and send the summary to the mailing list so that others may have
the benefit of responses you received that helped you solve your
problem.

1.7.2. MySQL Community Support at the MySQL Forums

The forums at http://forums.mysql.com are an
important community resource. Many forums are available, grouped
into these general categories:

Migration

MySQL Usage

MySQL Connectors

Programming Languages

Tools

3rd-Party Applications

Storage Engines

MySQL Technology

SQL Standards

Business

1.7.3. MySQL Community Support on Internet Relay Chat (IRC)

In addition to the various MySQL mailing lists and forums, you can
find experienced community people on Internet Relay Chat (IRC).
These are the best networks/channels currently known to us:

#mysql is primarily for MySQL questions,
but other database and general SQL questions are welcome.
Questions about PHP, Perl, or C in combination with MySQL are
also common.

If you are looking for IRC client software to connect to an IRC
network, take a look at xChat
(http://www.xchat.org/). X-Chat (GPL licensed) is
available for Unix as well as for Windows platforms (a free
Windows build of X-Chat is available at
http://www.silverex.org/download/).

1.8. How to Report Bugs or Problems

Before posting a bug report about a problem, please try to verify
that it is a bug and that it has not been reported already:

Start by searching the MySQL online manual at
http://dev.mysql.com/doc/. We try to keep the manual up to
date by updating it frequently with solutions to newly found
problems. The change history
(http://dev.mysql.com/doc/mysql/en/news.html) can be
particularly useful since it is quite possible that a newer
version contains a solution to your problem.

If you get a parse error for a SQL statement, please check your
syntax closely. If you can't find something wrong with it, it's
extremely likely that your current version of MySQL Server
doesn't support the syntax you are using. If you are using the
current version and the manual doesn't cover the syntax that you
are using, MySQL Server doesn't support your statement. In this
case, your options are to implement the syntax yourself or email
<licensing@mysql.com> and ask for an offer to
implement it.

If the manual covers the syntax you are using, but you have an
older version of MySQL Server, you should check the MySQL change
history to see when the syntax was implemented. In this case,
you have the option of upgrading to a newer version of MySQL
Server.

If you can't find an answer in the manual, the bugs database, or the
mailing list archives, check with your local MySQL expert. If you
still can't find an answer to your question, please use the
following guidelines for reporting the bug.

The normal way to report bugs is to visit
http://bugs.mysql.com/, which is the address for our
bugs database. This database is public and can be browsed and
searched by anyone. If you log in to the system, you can enter new
reports. If you have no Web access, you can generate a bug report by
using the mysqlbug script described at the end of
this section.

All bugs posted in the bugs database at
http://bugs.mysql.com/ are corrected or documented in
the next MySQL release. If only minor code changes are needed to
correct a problem, we may also post a patch that fixes the problem.

If you have found a sensitive security bug in MySQL, you can send
email to <security@mysql.com>.

Writing a good bug report takes patience, but doing it right the
first time saves time both for us and for yourself. A good bug
report, containing a full test case for the bug, makes it very
likely that we will fix the bug in the next release. This section
helps you write your report correctly so that you don't waste your
time doing things that may not help us much or at all. Please read
this section carefully and make sure that all the information
described here is included in your report.

Preferably, you should test the problem using the latest production
or development version of MySQL Server before posting. Anyone should
be able to repeat the bug by just using mysql test <
script_file on your test case or by running the shell or
Perl script that you include in the bug report. Any bug that we are
able to repeat has a high chance of being fixed in the next MySQL
release.

It is most helpful when a good description of the problem is
included in the bug report. That is, give a good example of
everything you did that led to the problem and describe, in exact
detail, the problem itself. The best reports are those that include
a full example showing how to reproduce the bug or problem. See
Section E.1.6, “Making a Test Case If You Experience Table Corruption”.

Remember that it is possible for us to respond to a report
containing too much information, but not to one containing too
little. People often omit facts because they think they know the
cause of a problem and assume that some details don't matter. A good
principle to follow is that if you are in doubt about stating
something, state it. It is faster and less troublesome to write a
couple more lines in your report than to wait longer for the answer
if we must ask you to provide information that was missing from the
initial report.

The most common errors made in bug reports are (a) not including the
version number of the MySQL distribution that you use, and (b) not
fully describing the platform on which the MySQL server is installed
(including the platform type and version number). These are highly
relevant pieces of information, and in 99 cases out of 100, the bug
report is useless without them. Very often we get questions like,
“Why doesn't this work for me?” Then we find that the
feature requested wasn't implemented in that MySQL version, or that
a bug described in a report has been fixed in newer MySQL versions.
Errors often are platform-dependent. In such cases, it is next to
impossible for us to fix anything without knowing the operating
system and the version number of the platform.

If you compiled MySQL from source, remember also to provide
information about your compiler if it is related to the problem.
Often people find bugs in compilers and think the problem is
MySQL-related. Most compilers are under development all the time and
become better version by version. To determine whether your problem
depends on your compiler, we need to know what compiler you used.
Note that every compiling problem should be regarded as a bug and
reported accordingly.

If a program produces an error message, it is very important to
include the message in your report. If we try to search for
something from the archives, it is better that the error message
reported exactly matches the one that the program produces. (Even
the lettercase should be observed.) It is best to copy and paste the
entire error message into your report. You should never try to
reproduce the message from memory.

If your report includes long query output lines from test cases that
you run with the mysql command-line tool, you can
make the output more readable by using the
--vertical option or the \G
statement terminator. The EXPLAIN SELECT example
later in this section demonstrates the use of \G.

Please include the following information in your report:

The version number of the MySQL distribution you are using (for
example, MySQL 5.0.19). You can find out which version you are
running by executing mysqladmin version. The
mysqladmin program can be found in the
bin directory under your MySQL installation
directory.

The manufacturer and model of the machine on which you
experience the problem.

The operating system name and version. If you work with Windows,
you can usually get the name and version number by
double-clicking your My Computer icon and pulling down the
“Help/About Windows” menu. For most Unix-like
operating systems, you can get this information by executing the
command uname -a.

Sometimes the amount of memory (real and virtual) is relevant.
If in doubt, include these values.

If you are using a source distribution of the MySQL software,
include the name and version number of the compiler that you
used. If you have a binary distribution, include the
distribution name.

If the problem occurs during compilation, include the exact
error messages and also a few lines of context around the
offending code in the file where the error occurs.

If a database table is related to the problem, include the
output from the SHOW CREATE TABLE
db_name.tbl_name
statement in the bug report. This is a very easy way to get the
definition of any table in a database. The information helps us
create a situation matching the one that you have experienced.

For performance-related bugs or problems with
SELECT statements, you should always include
the output of EXPLAIN SELECT ..., and at
least the number of rows that the SELECT
statement produces. You should also include the output from
SHOW CREATE TABLE
tbl_name for each table
that is involved. The more information you provide about your
situation, the more likely it is that someone can help you.

The following is an example of a very good bug report. The
statements are run using the mysql
command-line tool. Note the use of the \G
statement terminator for statements that would otherwise provide
very long output lines that are difficult to read.

mysql> SHOW VARIABLES;
mysql> SHOW COLUMNS FROM ...\G<output from SHOW COLUMNS>
mysql> EXPLAIN SELECT ...\G<output from EXPLAIN>
mysql> FLUSH STATUS;
mysql> SELECT ...;<A short version of the output from SELECT,
including the time taken to run the query>
mysql> SHOW STATUS;<output from SHOW STATUS>

If a bug or problem occurs while running
mysqld, try to provide an input script that
reproduces the anomaly. This script should include any necessary
source files. The more closely the script can reproduce your
situation, the better. If you can make a reproducible test case,
you should upload it to be attached to the bug report.

If you can't provide a script, you should at least include the
output from mysqladmin variables extended-status
processlist in your report to provide some information
on how your system is performing.

If you can't produce a test case with only a few rows, or if the
test table is too big to be included in the bug report (more
than 10 rows), you should dump your tables using
mysqldump and create a
README file that describes your problem.
Create a compressed archive of your files using
tar and gzip or
zip, and use FTP to transfer the archive to
ftp://ftp.mysql.com/pub/mysql/upload/. Then enter
the problem into our bugs database at
http://bugs.mysql.com/.

If you believe that the MySQL server produces a strange result
from a statement, include not only the result, but also your
opinion of what the result should be, and an explanation
describing the basis for your opinion.

When you provide an example of the problem, it's better to use
the table names, variable names, and so forth that exist in your
actual situation than to come up with new names. The problem
could be related to the name of a table or variable. These cases
are rare, perhaps, but it is better to be safe than sorry. After
all, it should be easier for you to provide an example that uses
your actual situation, and it is by all means better for us. If
you have data that you don't want to be visible to others in the
bug report, you can use FTP to transfer it to
ftp://ftp.mysql.com/pub/mysql/upload/. If the
information is really top secret and you don't want to show it
even to us, go ahead and provide an example using other names,
but please regard this as the last choice.

Include all the options given to the relevant programs, if
possible. For example, indicate the options that you use when
you start the mysqld server, as well as the
options that you use to run any MySQL client programs. The
options to programs such as mysqld and
mysql, and to the
configure script, are often key to resolving
problems and are very relevant. It is never a bad idea to
include them. If your problem involves a program written in a
language such as Perl or PHP, please include the language
processor's version number, as well as the version for any
modules that the program uses. For example, if you have a Perl
script that uses the DBI and
DBD::mysql modules, include the version
numbers for Perl, DBI, and
DBD::mysql.

If your question is related to the privilege system, please
include the output of mysqlaccess, the output
of mysqladmin reload, and all the error
messages you get when trying to connect. When you test your
privileges, you should first run mysqlaccess.
After this, execute mysqladmin reload version
and try to connect with the program that gives you trouble.
mysqlaccess can be found in the
bin directory under your MySQL installation
directory.

If you have a patch for a bug, do include it. But don't assume
that the patch is all we need, or that we can use it, if you
don't provide some necessary information such as test cases
showing the bug that your patch fixes. We might find problems
with your patch or we might not understand it at all. If so, we
can't use it.

If we can't verify the exact purpose of the patch, we won't use
it. Test cases help us here. Show that the patch handles all the
situations that may occur. If we find a borderline case (even a
rare one) where the patch won't work, it may be useless.

Guesses about what the bug is, why it occurs, or what it depends
on are usually wrong. Even the MySQL team can't guess such
things without first using a debugger to determine the real
cause of a bug.

Indicate in your bug report that you have checked the reference
manual and mail archive so that others know you have tried to
solve the problem yourself.

If the problem is that your data appears corrupt or you get
errors when you access a particular table, you should first
check your tables and then try to repair them with
CHECK TABLE and REPAIR
TABLE or with myisamchk. See
Chapter 5, Database Administration.

If you are running Windows, please verify the value of
lower_case_table_names using the
SHOW VARIABLES LIKE 'lower_case_table_names'
command.

If you often get corrupted tables, you should try to find out
when and why this happens. In this case, the error log in the
MySQL data directory may contain some information about what
happened. (This is the file with the .err
suffix in the name.) See Section 5.10.1, “The Error Log”. Please
include any relevant information from this file in your bug
report. Normally mysqld should
never crash a table if nothing killed it in
the middle of an update. If you can find the cause of
mysqld dying, it's much easier for us to
provide you with a fix for the problem. See
Section A.1, “How to Determine What Is Causing a Problem”.

If possible, download and install the most recent version of
MySQL Server and check whether it solves your problem. All
versions of the MySQL software are thoroughly tested and should
work without problems. We believe in making everything as
backward-compatible as possible, and you should be able to
switch MySQL versions without difficulty. See
Section 2.1.2, “Choosing Which MySQL Distribution to Install”.

If you are a support customer, please cross-post the bug report to
<mysql-support@mysql.com> for higher-priority
treatment, as well as to the appropriate mailing list to see whether
someone else has experienced (and perhaps solved) the problem.

If you have no Web access and cannot report a bug by visiting
http://bugs.mysql.com/, you can use the
mysqlbug script to generate a bug report (or a
report about any problem). mysqlbug helps you
generate a report by determining much of the following information
automatically, but if something important is missing, please include
it with your message. mysqlbug can be found in
the scripts directory (source distribution) and
in the bin directory under your MySQL
installation directory (binary distribution).

This section describes how MySQL relates to the ANSI/ISO SQL
standards. MySQL Server has many extensions to the SQL standard,
and here you can find out what they are and how to use them. You
can also find information about functionality missing from MySQL
Server, and how to work around some of the differences.

The SQL standard has been evolving since 1986 and several versions
exist. In this manual, “SQL-92” refers to the
standard released in 1992, “SQL:1999” refers to the
standard released in 1999, and “SQL:2003” refers to
the current version of the standard. We use the phrase “the
SQL standard” or “standard SQL” to mean the
current version of the SQL Standard at any time.

One of our main goals with the product is to continue to work
toward compliance with the SQL standard, but without sacrificing
speed or reliability. We are not afraid to add extensions to SQL
or support for non-SQL features if this greatly increases the
usability of MySQL Server for a large segment of our user base.
The HANDLER interface is an example of this
strategy. See Section 13.2.3, “HANDLER Syntax”.

We continue to support transactional and non-transactional
databases to satisfy both mission-critical 24/7 usage and heavy
Web or logging usage.

MySQL Server was originally designed to work with medium-sized
databases (10-100 million rows, or about 100MB per table) on small
computer systems. Today MySQL Server handles terabyte-sized
databases, but the code can also be compiled in a reduced version
suitable for hand-held and embedded devices. The compact design of
the MySQL server makes development in both directions possible
without any conflicts in the source tree.

In MySQL 4.1.2 in later, high-availability database clustering is
supported by the NDBCluster storage engine. See
Chapter 16, MySQL Cluster.

XML support is to be implemented in a future version of the
database server.

1.9.1. What Standards MySQL Follows

Our aim is to support the full ANSI/ISO SQL standard, but
without making concessions to speed and quality of the code.

ODBC levels 0-3.51.

1.9.2. Selecting SQL Modes

The MySQL server can operate in different SQL modes, and can
apply these modes differentially for different clients. This
capability allows applications to tailor server operation to
their own requirements.

SQL modes control aspects of server operation such as what SQL
syntax MySQL should support and what kind of data validation
checks it should perform. This makes it easier to use MySQL in
different environments and to use MySQL together with other
database servers.

You can set the default SQL mode by starting
mysqld with the
--sql-mode="mode_value"
option. Beginning with MySQL 4.1, you can also change the mode
at runtime by setting the sql_mode system
variable with a SET [SESSION|GLOBAL]
sql_mode='mode_value'
statement.

Note that running the server in ANSI mode with
--ansi is not quite the same as setting the SQL
mode to 'ANSI'. The --ansi
option affects the SQL mode and also sets the transaction
isolation level. Setting the SQL mode to
'ANSI' has no effect on the isolation level.

1.9.4. MySQL Extensions to Standard SQL

MySQL Server supports some extensions that you probably won't
find in other SQL DBMSs. Be warned that if you use them, your
code won't be portable to other SQL servers. In some cases, you
can write code that includes MySQL extensions, but is still
portable, by using comments of the following form:

/*! MySQL-specific code */

In this case, MySQL Server parses and executes the code within
the comment as it would any other SQL statement, but other SQL
servers will ignore the extensions. For example, MySQL Server
recognizes the STRAIGHT_JOIN keyword in the
following statement, but other servers will not:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

If you add a version number after the
‘!’ character, the syntax within
the comment is executed only if the MySQL version is greater
than or equal to the specified version number. The
TEMPORARY keyword in the following comment is
executed only by servers from MySQL 3.23.02 or higher:

CREATE /*!32302 TEMPORARY */ TABLE t (a INT);

The following descriptions list MySQL extensions, organized by
category.

Organization of data on disk

MySQL Server maps each database to a directory under the
MySQL data directory, and maps tables within a database to
filenames in the database directory. This has a few
implications:

You can use standard system commands to back up, rename,
move, delete, and copy tables that are managed by the
MyISAM or ISAM
storage engines. For example, it is possible to rename a
MyISAM table by renaming the
.MYD, .MYI,
and .frm files to which the table
corresponds. (Nevertheless, it is preferable to use
RENAME TABLE or ALTER TABLE
… RENAME and let the server rename the
files.)

By default, strings can be enclosed by either
‘"’ or
‘'’, not just by
‘'’. (If the
ANSI_QUOTES SQL mode is enabled,
strings can be enclosed only by
‘'’ and the server
interprets strings enclosed by
‘"’ as identifiers.)

Use of ‘\’ as an escape
character in strings.

In SQL statements, you can access tables from different
databases with the
db_name.tbl_name syntax. Some
SQL servers provide the same functionality but call this
User space. MySQL Server doesn't
support tablespaces such as used in statements like
this: CREATE TABLE ralph.my_table...IN
my_tablespace.

To make it easier for users who migrate from other SQL
environments, MySQL Server supports aliases for many
functions. For example, all string functions support
both standard SQL syntax and ODBC syntax.

MySQL Server understands the || and
&& operators to mean logical
OR and AND, as in the C programming language. In MySQL
Server, || and OR
are synonyms, as are && and
AND. Because of this nice syntax,
MySQL Server doesn't support the standard SQL
|| operator for string concatenation;
use CONCAT() instead. Because
CONCAT() takes any number of
arguments, it's easy to convert use of the
|| operator to MySQL Server.

Use of COUNT(DISTINCT
value_list) where
value_list has more than one
element.

String comparisons are case-insensitive by default, with
sort ordering determined by the current character set
(cp1252 Latin1 by default). If you don't like this, you
should declare your columns with the
BINARY attribute or use the
BINARY cast, which causes comparisons
to be done using the underlying character code values
rather then a lexical ordering.

The % operator is a synonym for
MOD(). That is,
N %
M is equivalent to
MOD(N,M).
% is supported for C programmers and
for compatibility with PostgreSQL.

The =, <>,
<=,<,
>=,>,
<<,
>>,
<=>, AND,
OR, or LIKE
operators may be used in expressions in the output
column list (to the left of the FROM)
in SELECT statements. For example:

1.9.5.1. Subquery Support

MySQL 4.1 and up supports subqueries and derived tables. A
“subquery” is a SELECT
statement nested within another statement. A “derived
table” (an unnamed view) is a subquery in the
FROM clause of another statement. See
Section 13.2.8, “Subquery Syntax”.

Because MySQL Server supports both paradigms, you can decide
whether your applications are best served by the speed of
atomic operations or the use of transactional features. This
choice can be made on a per-table basis.

As noted, the trade-off for transactional versus
non-transactional table types lies mostly in performance.
Transactional tables have significantly higher memory and disk
space requirements, and more CPU overhead. On the other hand,
transactional table types such as InnoDB
also offer many significant features. MySQL Server's modular
design allows the concurrent use of different storage engines
to suit different requirements and deliver optimum performance
in all situations.

But how do you use the features of MySQL Server to maintain
rigorous integrity even with the non-transactional
MyISAM tables, and how do these features
compare with the transactional table types?

If your applications are written in a way that is
dependent on being able to call
ROLLBACK rather than
COMMIT in critical situations,
transactions are more convenient. Transactions also ensure
that unfinished updates or corrupting activities are not
committed to the database; the server is given the
opportunity to do an automatic rollback and your database
is saved.

If you use non-transactional tables, MySQL Server in
almost all cases allows you to resolve potential problems
by including simple checks before updates and by running
simple scripts that check the databases for
inconsistencies and automatically repair or warn if such
an inconsistency occurs. Note that just by using the MySQL
log or even adding one extra log, you can normally fix
tables perfectly with no data integrity loss.

More often than not, critical transactional updates can be
rewritten to be atomic. Generally speaking, all integrity
problems that transactions solve can be done with
LOCK TABLES or atomic updates, ensuring
that there are no automatic aborts from the server, which
is a common problem with transactional database systems.

To be safe with MySQL Server, whether or not you use
transactional tables, you only need to have backups and
have binary logging turned on. When that is true, you can
recover from any situation that you could with any other
transactional database system. It is always good to have
backups, regardless of which database system you use.

The transactional paradigm has its benefits and its drawbacks.
Many users and application developers depend on the ease with
which they can code around problems where an abort appears to
be necessary, or is necessary. However, even if you are new to
the atomic operations paradigm, or more familiar with
transactions, do consider the speed benefit that
non-transactional tables can offer on the order of three to
five times the speed of the fastest and most optimally tuned
transactional tables.

In situations where integrity is of highest importance, MySQL
Server offers transaction-level reliability and integrity even
for non-transactional tables. If you lock tables with
LOCK TABLES, all updates stall until
integrity checks are made. If you obtain a READ
LOCAL lock (as opposed to a write lock) for a table
that allows concurrent inserts at the end of the table, reads
are allowed, as are inserts by other clients. The newly
inserted records are not be seen by the client that has the
read lock until it releases the lock. With INSERT
DELAYED, you can write inserts that go into a local
queue until the locks are released, without having the client
wait for the insert to complete. See
Section 13.2.4.2, “INSERT DELAYED Syntax”.

“Atomic,” in the sense that we mean it, is
nothing magical. It only means that you can be sure that while
each specific update is running, no other user can interfere
with it, and there can never be an automatic rollback (which
can happen with transactional tables if you are not very
careful). MySQL Server also guarantees that there are no dirty
reads.

Following are some techniques for working with
non-transactional tables:

Loops that need transactions normally can be coded with
the help of LOCK TABLES, and you don't
need cursors to update records on the fly.

To avoid using ROLLBACK, you can employ
the following strategy:

Use LOCK TABLES to lock all the
tables you want to access.

Test the conditions that must be true before
performing the update.

Update if the conditions are satisfied.

Use UNLOCK TABLES to release your
locks.

This is usually a much faster method than using
transactions with possible rollbacks, although not always.
The only situation this solution doesn't handle is when
someone kills the threads in the middle of an update. In
that case, all locks are released but some of the updates
may not have been executed.

You can also use functions to update records in a single
operation. You can get a very efficient application by
using the following techniques:

Modify columns relative to their current value.

Update only those columns that actually have changed.

For example, when we are updating customer information, we
update only the customer data that has changed and test
only that none of the changed data, or data that depends
on the changed data, has changed compared to the original
row. The test for changed data is done with the
WHERE clause in the
UPDATE statement. If the record wasn't
updated, we give the client a message: “Some of the
data you have changed has been changed by another
user.” Then we show the old row versus the new row
in a window so that the user can decide which version of
the customer record to use.

This gives us something that is similar to column locking
but is actually even better because we only update some of
the columns, using values that are relative to their
current values. This means that typical
UPDATE statements look something like
these:

This is very efficient and works even if another client
has changed the values in the pay_back
or money_owed_to_us columns.

In many cases, users have wanted LOCK
TABLES or ROLLBACK for the
purpose of managing unique identifiers. This can be
handled much more efficiently without locking or rolling
back by using an AUTO_INCREMENT column
and either the LAST_INSERT_ID() SQL
function or the mysql_insert_id() C API
function. See Section 12.9.3, “Information Functions”, and
Section 18.2.3.35, “mysql_insert_id()”.

You can generally code around the need for row-level
locking. Some situations really do need it, and
InnoDB tables support row-level
locking. Otherwise, with MyISAM tables,
you can use a flag column in the table and do something
like the following:

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

MySQL returns 1 for the number of
affected rows if the row was found and
row_flag wasn't 1 in
the original row. You can think of this as though MySQL
Server changed the preceding statement to:

UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;

1.9.5.4. Stored Routines and Triggers

Stored procedures and functions are implemented beginning with
MySQL 5.0.

Basic trigger functionality is implemented beginning with
MySQL 5.0.2, with further development planned for MySQL 5.1.

1.9.5.5. Foreign Keys

For storage engines other than InnoDB,
MySQL Server parses the FOREIGN KEY syntax
in CREATE TABLE statements, but does not
use or store it. In the future, the implementation will be
extended to store this information in the table specification
file so that it may be retrieved by
mysqldump and ODBC. At a later stage,
foreign key constraints will be implemented for
MyISAM tables as well.

Assuming proper design of the relationships, foreign key
constraints make it more difficult for a programmer to
introduce an inconsistency into the database.

Centralized checking of constraints by the database server
makes it unnecessary to perform these checks on the
application side. This eliminates the possibility that
different applications may not all check the constraints
in the same way.

Using cascading updates and deletes can simplify the
application code.

Do keep in mind that these benefits come at the cost of
additional overhead for the database server to perform the
necessary checks. Additional checking by the server affects
performance, which for some applications may be sufficiently
undesirable as to be avoided if possible. (Some major
commercial applications have coded the foreign key logic at
the application level for this reason.)

MySQL gives database developers the choice of which approach
to use. If you don't need foreign keys and want to avoid the
overhead associated with enforcing referential integrity, you
can choose another storage engine instead, such as
MyISAM. (For example, the
MyISAM storage engine offers very fast
performance for applications that perform only
INSERT and SELECT
operations. In this case, the table has no holes in the middle
and the inserts can be performed concurrently with retrievals.
See Section 7.3.2, “Table Locking Issues”.)

If you choose not to take advantage of referential integrity
checks, keep the following considerations in mind:

In the absence of server-side foreign key relationship
checking, the application itself must handle relationship
issues. For example, it must take care to insert rows into
tables in the proper order, and to avoid creating orphaned
child records. It must also be able to recover from errors
that occur in the middle of multiple-record insert
operations.

If ON DELETE is the only referential
integrity capability an application needs, you can achieve
a similar effect as of MySQL Server 4.0 by using
multiple-table DELETE statements to
delete rows from many tables with a single statement. See
Section 13.2.1, “DELETE Syntax”.

A workaround for the lack of ON DELETE
is to add the appropriate DELETE
statements to your application when you delete records
from a table that has a foreign key. In practice, this is
often as quick as using foreign keys and is more portable.

Be aware that the use of foreign keys can sometimes lead to
problems:

Foreign key support addresses many referential integrity
issues, but it is still necessary to design key
relationships carefully to avoid circular rules or
incorrect combinations of cascading deletes.

It is not uncommon for a DBA to create a topology of
relationships that makes it difficult to restore
individual tables from a backup. (MySQL alleviates this
difficulty by allowing you to temporarily disable foreign
key checks when reloading a table that depends on other
tables. See
Section 15.7.4, “FOREIGN KEY Constraints”. As of
MySQL 4.1.1, mysqldump generates dump
files that take advantage of this capability automatically
when they are reloaded.)

Note that foreign keys in SQL are used to check and enforce
referential integrity, not to join tables. If you want to get
results from multiple tables from a SELECT
statement, you do this by performing a join between them:

1.9.5.6. Views

Views are useful for allowing users to access a set of
relations (tables) as if it were a single table, and limiting
their access to just that. Views can also be used to restrict
access to rows (a subset of a particular table). For access
control to columns, you can also use the sophisticated
privilege system in MySQL Server. See
Section 5.6, “The MySQL Access Privilege System”.

In designing an implementation of views, our ambitious goal,
as much as is possible within the confines of SQL, has been
full compliance with “Codd's Rule #6” for
relational database systems: “All views that are
theoretically updatable, should in practice also be
updatable.”

1.9.5.7. '--' as the Start of a Comment

Standard SQL uses the C syntax /* this is a comment
*/ for comments, and MySQL Server supports this
syntax as well. MySQL also support extensions to this syntax
that allow MySQL-specific SQL to be embedded in the comment,
as described in Section 9.5, “Comment Syntax”.

Standard SQL uses ‘--’ as a
start-comment sequence. MySQL Server uses
‘#’ as the start comment
character. MySQL Server 3.23.3 and up also supports a variant
of the ‘--’ comment style. That
is, the ‘--’ start-comment
sequence must be followed by a space (or by a control
character such as a newline). The space is required to prevent
problems with automatically generated SQL queries that use
constructs such as the following, where we automatically
insert the value of the payment for
!payment!:

UPDATE account SET credit=credit-!payment!

Consider about what happens if payment has
a negative value such as -1:

UPDATE account SET credit=credit--1

credit--1 is a legal expression in SQL, but
‘--’ is interpreted as the
start of a comment, part of the expression is discarded. The
result is a statement that has a completely different meaning
than intended:

UPDATE account SET credit=credit

The statement produces no change in value at all! This
illustrates that allowing comments to start with
‘--’ can have serious
consequences.

Using our implementation of require a following space for
‘--’ to be recognized as a
start-comment sequence in MySQL Server 3.23.3 and up,
credit--1 is actually safe.

Another safe feature is that the mysql
command-line client ignores lines that start with
‘--’.

The following information is relevant only if you are running
a MySQL version earlier than 3.23.3:

If you have an SQL script in a text file that contains
‘--’ comments, you should use
the replace utility as follows to convert
the comments to use ‘#’
characters before executing the script:

1.9.6. How MySQL Deals with Constraints

MySQL allows you to work both with transactional tables that
allow rollback and with non-transactional tables that do not.
Because of this, constraint handling is a bit different in MySQL
than in other DBMSs. We must handle the case when you have
inserted or updated a lot of rows in a non-transactional table
for which changes cannot be rolled back when an error occurs.

The basic philosophy is that MySQL Server tries to produce an
error for anything that it can detect while parsing a statement
to be executed, and tries to recover from any errors that occur
while executing the statement. We do this in most cases.

The options MySQL has when an error occurs are to stop the
statement in the middle or to recover as well as possible from
the problem and continue. By default, the server follows the
latter course. This means, for example, that the server may
coerce illegal values to the closest legal values.

The following sections describe how MySQL Server handles
different types of constraints.

1.9.6.1. PRIMARY KEY and UNIQUE Index Constraints

Normally, an error occurs when you try to
INSERT or UPDATE a row
that causes a primary key, unique key, or foreign key
violation. If you are using a transactional storage engine
such as InnoDB, MySQL automatically rolls
back the statement. If you are using a non-transactional
storage engine, MySQL stops processing the statement at the
row for which the error occurred and leaves any remaining rows
unprocessed.

1.9.6.2. Constraints on Invalid Data

Through version 4.1, MySQL is forgiving of illegal or improper
data values and coerces them to legal values for data entry.
When you insert an “incorrect” value into a
column, such as a NULL into a NOT
NULL column or a too-large numeric value into a
numeric column, MySQL sets the column to the “best
possible value” instead of producing an error. The
following rules describe in more detail how this works:

If you try to store an out of range value into a numeric
column, MySQL Server instead stores zero, the smallest
possible value, or the largest possible value, whichever
is closest to the invalid value. column.

For strings, MySQL stores either the empty string or as
much of the string as can be stored in the column.

If you try to store a string that doesn't start with a
number into a numeric column, MySQL Server stores 0.

MySQL allows you to store certain incorrect date values
into DATE and
DATETIME columns (such as
'2000-02-31' or
'2000-02-00'). The idea is that it's
not the job of the SQL server to validate dates. If MySQL
can store a date value and retrieve exactly the same
value, MySQL stores it as given. If the date is totally
wrong (outside the server's ability to store it), the
special “zero” date value
'0000-00-00' is stored in the column
instead.

If you try to store NULL into a column
that doesn't take NULL values, an error
occurs for single-row INSERT
statements. For multiple-row INSERT
statements or for INSERT INTO ...
SELECT statements, MySQL Server stores the
implicit default value for the column data type. In
general, this is 0 for numeric types,
the empty string ('') for string types,
and the “zero” value for date and time types.
Implicit default values are discussed in
Section 13.1.5, “CREATE TABLE Syntax”.

If an INSERT statement specifies no
value for a column, MySQL inserts its default value if the
column definition includes an explicit
DEFAULT clause. If the definition has
no such DEFAULT clause, MySQL inserts
the implicit default value for the column data type.

The reason for using the preceding rules is that we can't
check these conditions until the statement has begun
executing. We can't just roll back if we encounter a problem
after updating a few rows, because the storage engine may not
support rollback. The option of terminating the statement is
not that good; in this case, the update would be “half
done,” which is probably the worst possible scenario.
In this case, it's better to “do the best you
can” and then continue as if nothing happened.

ENUM columns always have a default
value. If you specify no default value, then it is
NULL for columns that can have
NULL, otherwise it is the first
enumeration value in the column definition.

If you insert an incorrect value into an
ENUM column or if you force a value
into an ENUM column with
IGNORE, it is set to the reserved
enumeration value of 0, which is
displayed as an empty string in string context.

If you insert an incorrect value into a
SET column, the incorrect value is
ignored. For example, if the column can contain the values
'a', 'b', and
'c', an attempt to assign
'a,x,b,y' results in a value of
'a,b'.

Choose which distribution to
install. Several versions of MySQL are available, and
most are available in several distribution formats. You can
choose from pre-packaged distributions containing binary
(precompiled) programs or source code. When in doubt, use a
binary distribution. We also provide public access to our
current source tree for those who want to see our most recent
developments and help us test new code. To determine which
version and type of distribution you should use, see
Section 2.1.2, “Choosing Which MySQL Distribution to Install”.

Note: If you plan to upgrade an
existing version of MySQL to a newer version rather than
installing MySQL for the first time, see
Section 2.10, “Upgrading MySQL”, for information about upgrade
procedures and about issues that you should consider before
upgrading.

Perform any necessary post-installation
setup. After installing MySQL, read
Section 2.9, “Post-Installation Setup and Testing”. This section contains
important information about making sure the MySQL server is
working properly. It also describes how to secure the initial
MySQL user accounts, which have no
passwords until you assign passwords. The section
applies whether you install MySQL using a binary or source
distribution.

This section contains the information necessary to carry out these
steps. After doing so, you can use the instructions in later
sections of the chapter to install the distribution that you
choose.

2.1.1. Operating Systems Supported by MySQL

This section lists the operating systems on which you can expect
to be able to run MySQL.

We use GNU Autoconf, so it is possible to port MySQL to all
modern systems that have a C++ compiler and a working
implementation of POSIX threads. (Thread support is needed for
the server. To compile only the client code, the only
requirement is a C++ compiler.) We use and develop the software
ourselves primarily on Linux (SuSE and Red Hat), FreeBSD, and
Sun Solaris (versions 8 and 9).

MySQL has been reported to compile successfully on the following
combinations of operating system and thread package. Note that
for many operating systems, native thread support works only in
the latest versions.

Not all platforms are equally well-suited for running MySQL. How
well a certain platform is suited for a high-load
mission-critical MySQL server is determined by the following
factors:

General stability of the thread library. A platform may have
an excellent reputation otherwise, but MySQL is only as
stable as the thread library it calls, even if everything
else is perfect.

The capability of the kernel and the thread library to take
advantage of symmetric multi-processor (SMP) systems. In
other words, when a process creates a thread, it should be
possible for that thread to run on a different CPU than the
original process.

The capability of the kernel and the thread library to run
many threads that acquire and release a mutex over a short
critical region frequently without excessive context
switches. If the implementation of
pthread_mutex_lock() is too anxious to
yield CPU time, this hurts MySQL tremendously. If this issue
is not taken care of, adding extra CPUs actually makes MySQL
slower.

General filesystem stability and performance.

If your tables are big, the ability of the filesystem to
deal with large files at all and to deal with them
efficiently.

Our level of expertise here at MySQL AB with the platform.
If we know a platform well, we enable platform-specific
optimizations and fixes at compile time. We can also provide
advice on configuring your system optimally for MySQL.

The amount of testing we have done internally for similar
configurations.

The number of users that have successfully run MySQL on the
platform in similar configurations. If this number is high,
the chances of encountering platform-specific surprises are
much smaller.

Based on the preceding criteria, the best platforms for running
MySQL at this point are x86 with SuSE Linux using a 2.4 kernel,
and ReiserFS (or any similar Linux distribution) and SPARC with
Solaris (2.7-9). FreeBSD comes third, but we really hope it
joins the top club once the thread library is improved. We also
hope that at some point we is able to include into the top
category all other platforms on which MySQL currently compiles
and runs okay, but not quite with the same level of stability
and performance. This requires some effort on our part in
cooperation with the developers of the operating system and
library components that MySQL depends on. If you are interested
in improving one of those components, are in a position to
influence its development, and need more detailed instructions
on what MySQL needs to run better, send an email message to the
MySQL internals mailing list. See
Section 1.7.1, “MySQL Mailing Lists”.

Please note that the purpose of the preceding comparison is not
to say that one operating system is better or worse than another
in general. We are talking only about choosing an OS for the
specific purpose of running MySQL. With this in mind, the result
of this comparison would be different if we considered more
factors. In some cases, the reason one OS is better than the
other could simply be that we have been able to put more effort
into testing and optimizing for a particular platform. We are
just stating our observations to help you decide which platform
to use for running MySQL.

When preparing to install MySQL, you should decide which version
to use. MySQL development occurs in several release series, and
you can pick the one that best fits your needs. After deciding
which version to install, you can choose a distribution format.
Releases are available in binary or source format.

2.1.2.1. Choosing Which Version of MySQL to Install

The first decision to make is whether you want to use a
production (stable) release or a development release. In the
MySQL development process, multiple release series co-exist,
each at a different stage of maturity:

MySQL 5.0 is the newest development release series and is
under very active development for new features. Alpha
releases have been issued to allow more widespread
testing.

MySQL 4.1 is the current stable (production-quality)
release series. New releases are issued for bugfixes. No
new features are added that could diminish the code
stability.

MySQL 4.0 is the previous stable (production-quality)
release series. New releases are issued for bugfixes. No
new features are added that could diminish the code
stability.

MySQL 3.23 is the old stable (production-quality) release
series. This series is retired, so new releases are issued
only to fix critical bugs.

We do not believe in a complete freeze, as this also leaves
out bugfixes and things that “must be done.”
“Somewhat frozen” means that we may add small
things that “almost surely do not affect anything that's
currently working.” Naturally, relevant bugfixes from
an earlier series propagate to later series.

Normally, if you are beginning to use MySQL for the first time
or trying to port it to some system for which there is no
binary distribution, we recommend going with the production
release series. Currently this is MySQL 4.1. All MySQL
releases, even those from development series, are checked with
the MySQL benchmarks and an extensive test suite before being
issued.

If you are running an old system and want to upgrade, but do
not want to take the chance of having a non-seamless upgrade,
you should upgrade to the latest version in the same release
series you are using (where only the last part of the version
number is newer than yours). We have tried to fix only fatal
bugs and make small, relatively safe changes to that version.

If you want to use new features not present in the production
release series, you can use a version from a development
series. Note that development releases are not as stable as
production releases.

If you want to use the very latest sources containing all
current patches and bugfixes, you can use one of our BitKeeper
repositories. These are not “releases” as such,
but are available as previews of the code on which future
releases are based.

The MySQL naming scheme uses release names that consist of
three numbers and a suffix; for example,
mysql-4.1.2-alpha. The numbers within the
release name are interpreted like this:

The first number (4) is the major
version and also describes the file format. All version 4
releases have the same file format.

The second number (1) is the release
level. Taken together, the major version and release level
constitute the release series number.

The third number (2) is the version
number within the release series. This is incremented for
each new release. Usually you want the latest version for
the series you have chosen.

For each minor update, the last number in the version string
is incremented. When there are major new features or minor
incompatibilities with previous versions, the second number in
the version string is incremented. When the file format
changes, the first number is increased.

Release names also include a suffix to indicates the stability
level of the release. Releases within a series progress
through a set of suffixes to indicate how the stability level
improves. The possible suffixes are:

alpha indicates that the release
contains some large section of new code that hasn't been
100% tested. Known bugs should be documented in the News
section. See Appendix D, MySQL Change History. There are also new
commands and extensions in most alpha releases. Active
development that may involve major code changes can occur
in an alpha release, but everything is tested before
issuing a release.

beta means that we are feature complete
and that all new code has been tested. No major new
features that could cause corruption in old code are
added. There should be no known critical bugs. A version
changes from alpha to beta when there have not been any
reported fatal bugs within an alpha version for at least a
month and we have no plans to add any features that could
make any old command unreliable.

All API's, extern visible structures and columns for SQL
commands will not change during future beta, release
candidate, or production releases.

rc is a release candidate; that is, a
beta that has been around a while and seems to work fine.
Only minor fixes are added. (A release candidate is what
formerly was known as a gamma release.)

If there is no suffix, it means that the version has been
run for a while at many different sites with no reports of
critical repeatable bugs other than platform-specific
bugs. Only critical bugfixes are applied to the release.
This is what we call a production (stable) or `General
Availability' (GA) release.

MySQL uses a naming scheme that is slightly different from
most other products. In general, it is relatively safe to use
any version that has been out for a couple of weeks without
being replaced with a new version within the release series.

All releases of MySQL are run through our standard tests and
benchmarks to ensure that they are relatively safe to use.
Because the standard tests are extended over time to check for
all previously found bugs, the test suite keeps getting
better.

All releases have been tested at least with:

An internal test suite

The mysql-test directory contains an
extensive set of test cases. We run these tests for
virtually every server binary. See
Section 20.1.2, “MySQL Test Suite”, for more information
about this test suite.

Another test is that we use the newest MySQL version in our
internal production environment, on at least one machine. We
have more than 100GB of data to work with.

2.1.2.2. Choosing a Distribution Format

After choosing which version of MySQL to install, you should
decide whether to use a binary distribution or a source
distribution. In most cases, you should probably use a binary
distribution, if one exists for your platform. Binary
distributions are available in native format for many
platforms, such as RPM files for Linux or DMG package
installers for Mac OS X. Distributions also are available as
Zip archives or compressed tar files.

Reasons to choose a binary distribution include the following:

Binary distributions generally are easier to install than
source distributions.

To satisfy different user requirements, we provide two
different binary versions: one compiled with the
non-transactional storage engines (a small, fast binary),
and one configured with the most important extended
options like transaction-safe tables. Both versions are
compiled from the same source distribution. All native
MySQL clients can connect to servers from either MySQL
version.

If you want to use the MySQL-Max RPM,
you must first install the standard
MySQL-server RPM.

Under some circumstances, you may be better off installing
MySQL from a source distribution:

You want to install MySQL at some explicit location. The
standard binary distributions are “ready to
run” at any place, but you may want to have even
more flexibility to place MySQL components where you want.

You want to configure mysqld with some
extra features that are not included in the standard
binary distributions. Here is a list of the most common
extra options that you may want to use:

--with-innodb (default for MySQL 4.0
and up)

--with-berkeley-db (not available on
all platforms)

--with-raid

--with-libwrap

--with-named-z-libs (this is done for
some of the binaries)

--with-debug[=full]

You want to configure mysqld without
some features that are included in the standard binary
distributions. For example, distributions normally are
compiled with support for all character sets. If you want
a smaller MySQL server, you can recompile it with support
for only the character sets you need.

You have a special compiler (such as
pgcc) or want to use compiler options
that are better optimized for your processor. Binary
distributions are compiled with options that should work
on a variety of processors from the same processor family.

You want to use the latest sources from one of the
BitKeeper repositories to have access to all current
bugfixes. For example, if you have found a bug and
reported it to the MySQL development team, the bugfix is
committed to the source repository and you can access it
there. The bugfix does not appear in a release until a
release actually is issued.

You want to read (or modify) the C and C++ code that makes
up MySQL. For this purpose, you should get a source
distribution, because the source code is always the
ultimate manual.

Source distributions contain more tests and examples than
binary distributions.

2.1.2.3. How and When Updates Are Released

MySQL is evolving quite rapidly here at MySQL AB and we want
to share new developments with other MySQL users. We try to
make a release when we have very useful features that others
seem to have a need for.

We also try to help out users who request features that are
easy to implement. We take note of what our licensed users
want to have, and we especially take note of what our support
customers want and try to help them out.

Releases are issued within each series. For each release,
the last number in the version is one more than the
previous release within the same series.

Production (stable) releases are meant to appear about 1-2
times a year. However, if small bugs are found, a release
with only bugfixes is issued.

Working releases/bugfixes to old releases are meant to
appear about every 4-8 weeks.

Binary distributions for some platforms are made by us for
major releases. Other people may make binary distributions
for other systems, but probably less frequently.

We make fixes available as soon as we have identified and
corrected small or non-critical but annoying bugs. The
fixes are available immediately from our public BitKeeper
repositories, and will be included in the next release.

If by any chance a fatal bug is found in a release, we
make a new release as soon as possible. (We would like
other companies to do this, too!)

2.1.2.4. Release Philosophy—No Known Bugs in Releases

We put a lot of time and effort into making our releases
bug-free. We haven't released a single MySQL version with any
known “fatal” repeatable
bugs. (A “fatal” bug is something that crashes
MySQL under normal usage, produces incorrect answers for
normal queries, or has a security problem.)

Our aim is to fix everything that is fixable without risk of
making a stable MySQL version less stable. In certain cases,
this means we can fix an issue in the development versions,
but not in the stable (production) version. Naturally, we
document such issues so that users are aware of them.

Here is a description of how our build process works:

We monitor bugs from our customer support list, the bugs
database at http://bugs.mysql.com/, and the
MySQL external mailing lists.

All reported bugs for live versions are entered into the
bugs database.

When we fix a bug, we always try to make a test case for
it and include it into our test system to ensure that the
bug can never recur without being detected. (About 90% of
all fixed bugs have a test case.)

We create test cases for all new features we add to MySQL.

Before we start to build a new MySQL release, we ensure
that all reported repeatable bugs for the MySQL version
(3.23.x, 4.0.x, and so forth) are fixed. If something is
impossible to fix (due to some internal design decision in
MySQL), we document this in the manual. See
Section A.8, “Known Issues in MySQL”.

We do a build on all platforms for which we support
binaries (15+ platforms) and run our test suite and
benchmark suite on all of them.

We do not publish a binary for a platform for which the
test or benchmark suite fails. If the problem is due to a
general error in the source, we fix it and do the build
plus tests on all systems again from scratch.

The build and test process takes 2-3 days. If we receive a
report regarding a fatal bug during this process (for
example, one that causes a core dump), we fix the problem
and restart the build process.

After publishing the binaries on
http://dev.mysql.com/, we send out an
announcement message to the mysql and
announce mailing lists. See
Section 1.7.1, “MySQL Mailing Lists”. The announcement message
contains a list of all changes to the release and any
known problems with the release. The Known Problems
section in the release notes has been needed for only a
handful of releases.

To quickly give our users access to the latest MySQL
features, we do a new MySQL release every 4-8 weeks.
Source code snapshots are built daily and are available at
http://downloads.mysql.com/snapshots.php.

If, despite our best efforts, we get any bug reports after
the release is done that there was something critically
wrong with the build on a specific platform, we fix it at
once and build a new 'a' release for
that platform. Thanks to our large user base, problems are
found quickly.

Our track record for making stable releases is quite good.
In the last 150 releases, we had to do a new build for
fewer than 10 releases. In three of these cases, the bug
was a faulty glibc library on one of
our build machines that took us a long time to track down.

2.1.2.5. MySQL Binaries Compiled by MySQL AB

As a service of MySQL AB, we provide a set of binary
distributions of MySQL that are compiled on systems at our
site or on systems where supporters of MySQL kindly have given
us access to their machines.

These distributions are generated using the script
Build-tools/Do-compile, which compiles the
source code and creates the binary tar.gz
archive using
scripts/make_binary_distribution.

These binaries are configured and built with the following
compilers and options. This information can also be obtained
by looking at the variables COMP_ENV_INFO
and CONFIGURE_LINE inside the script
bin/mysqlbug of every binary
tar file distribution.

The following binaries are built on third-party systems kindly
provided to MySQL AB by other users. These are provided only
as a courtesy; MySQL AB does not have full control over these
systems, so we can provide only limited support for the
binaries built on them.

The following compile options have been used for binary
packages that MySQL AB provided in the past. These binaries no
longer are being updated, but the compile options are listed
here for reference purposes.

If you want to compile a debug version of MySQL, you should
add --with-debug or
--with-debug=full to the preceding
configure commands and remove any
-fomit-frame-pointer options.

2.1.3. How to Get MySQL

Check the MySQL downloads page
(http://dev.mysql.com/downloads/) for information about the
current version and for downloading instructions. For a complete
up-to-date list of MySQL download mirror sites, see
http://dev.mysql.com/downloads/mirrors.html. There you can
also find information about becoming a MySQL mirror site and how
to report a bad or out-of-date mirror.

2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG

After you have downloaded the MySQL package that suits your
needs and before you attempt to install it, you should make sure
that it is intact and has not been tampered with. MySQL AB
offers three means of integrity checking:

MD5 checksums

Cryptographic signatures using GnuPG, the
GNU Privacy Guard

For RPM packages, the built-in RPM integrity verification
mechanism

The following sections describe how to use these methods.

If you notice that the MD5 checksum or GPG signatures do not
match, first try to download the respective package one more
time, perhaps from another mirror site. If you repeatedly cannot
successfully verify the integrity of the package, please notify
us about such incidents, including the full package name and the
download site you have been using, at
<webmaster@mysql.com> or
<build@mysql.com>. Do not report downloading
problems using the bug-reporting system.

2.1.4.1. Verifying the MD5 Checksum

After you have downloaded a MySQL package, you should make
sure that its MD5 checksum matches the one provided on the
MySQL download pages. Each package has an individual checksum
that you can verify with the following command, where
package_name is the name of the package you
downloaded:

You should verify that the resulting checksum (the string of
hexadecimal digits) matches the one displayed on the download
page immediately below the respective package.

Note: Make sure to verify the
checksum of the archive file
(for example, the .zip or
.tar.gz file) and not of the files that are
contained inside of the archive!

Note that not all operating systems support the
md5sum command. On some, it is simply
called md5 and others do not ship it at
all. On Linux, it is part of the GNU Text
Utilities package, which is available for a wide
range of platforms. You can download the source code from
http://www.gnu.org/software/textutils/ as well.
If you have OpenSSL installed, you can also
use the command openssl md5 package_name
instead. A DOS/Windows implementation of the
md5 command line utility is available from
http://www.fourmilab.ch/md5/. A graphical MD5
checking tool is winMd5Sum, which can be
obtained from
http://www.nullriver.com/index/products/winmd5sum.

2.1.4.2. Signature Checking Using GnuPG

Another method of verifying the integrity and authenticity of
a package is to use cryptographic signatures. This is more
reliable than using MD5 checksums, but requires more work.

Beginning with MySQL 4.0.10 (February 2003), MySQL AB started
signing downloadable packages with GnuPG
(GNU Privacy Guard).
GnuPG is an Open Source alternative to the
very well-known Pretty Good Privacy
(PGP) by Phil Zimmermann. See
http://www.gnupg.org/ for more information
about GnuPG and how to obtain and install
it on your system. Most Linux distributions ship with
GnuPG installed by default. For more
information about OpenPGP, see
http://www.openpgp.org/.

To verify the signature for a specific package, you first need
to obtain a copy of MySQL AB's public GPG build key. You can
download the key from
http://www.keyserver.net/. The key that you
want to obtain is named build@mysql.com.
Alternatively, you can cut and paste the key directly from the
following text:

You can import the build key into your personal public GPG
keyring by using gpg --import. For example,
if you save the key in a file named
mysql_pubkey.asc, the import command
looks like this:

shell> gpg --import mysql_pubkey.asc

See the GPG documentation for more information on how to work
with public keys.

After you have downloaded and imported the public build key,
download your desired MySQL package and the corresponding
signature, which also is available from the download page. The
signature file has the same name as the distribution file with
an .asc extension. For example:

Distribution file

mysql-standard-4.0.17-pc-linux-i686.tar.gz

Signature file

mysql-standard-4.0.17-pc-linux-i686.tar.gz.asc

Make sure that both files are stored in the same directory and
then run the following command to verify the signature for the
distribution file:

Note: If you are using RPM
4.1 and it complains about (GPG) NOT OK (MISSING
KEYS: GPG#5072e1f5), even though you have imported
the MySQL public build key into your own GPG keyring, you need
to import the key into the RPM keyring first. RPM 4.1 no
longer uses your personal GPG keyring (or GPG itself). Rather,
it maintains its own keyring because it is a system-wide
application and a user's GPG public keyring is a user-specific
file. To import the MySQL public key into the RPM keyring,
first obtain the key as described in the previous section.
Then use rpm --import to import the key.
For example, if you have the public key stored in a file named
mysql_pubkey.asc, import it using this
command:

2.1.5. Installation Layouts

This section describes the default layout of the directories
created by installing binary or source distributions provided by
MySQL AB. If you install a distribution provided by another
vendor, some other layout might be used.

On Windows, the default installation directory is
C:\mysql. With MySQL version 4.1.5 and
higher, this has changed to C:\Program
Files\MySQL\MySQL Server 4.1, where 4.1 is the major
version of the installation. The folder has the following
subdirectories:

Directory

Contents of Directory

bin

Client programs and the mysqld server

data

Log files, databases

Docs

Documentation

examples

Example programs and scripts

include

Include (header) files

lib

Libraries

scripts

Utility scripts

share

Error message files

Installations created from Linux RPM distributions result in
files under the following system directories:

Directory

Contents of Directory

/usr/bin

Client programs and scripts

/usr/sbin

The mysqld server

/var/lib/mysql

Log files, databases

/usr/share/doc/packages

Documentation

/usr/include/mysql

Include (header) files

/usr/lib/mysql

Libraries

/usr/share/mysql

Error message and character set files

/usr/share/sql-bench

Benchmarks

On Unix, a tar file binary distribution is
installed by unpacking it at the installation location you
choose (typically /usr/local/mysql) and
creates the following directories in that location:

Directory

Contents of Directory

bin

Client programs and the mysqld server

data

Log files, databases

docs

Documentation, ChangeLog

include

Include (header) files

lib

Libraries

scripts

mysql_install_db

share/mysql

Error message files

sql-bench

Benchmarks

A source distribution is installed after you configure and
compile it. By default, the installation step installs files
under /usr/local, in the following
subdirectories:

Directory

Contents of Directory

bin

Client programs and scripts

include/mysql

Include (header) files

info

Documentation in Info format

lib/mysql

Libraries

libexec

The mysqld server

share/mysql

Error message files

sql-bench

Benchmarks and crash-me test

var

Databases and log files

Within an installation directory, the layout of a source
installation differs from that of a binary installation in the
following ways:

The mysqld server is installed in the
libexec directory rather than in the
bin directory.

The data directory is var rather than
data.

mysql_install_db is installed in the
bin directory rather than in the
scripts directory.

The header file and library directories are
include/mysql and
lib/mysql rather than
include and lib.

You can create your own binary installation from a compiled
source distribution by executing the
scripts/make_binary_distribution script
from the top directory of the source distribution.

2.2. Standard MySQL Installation Using a Binary Distribution

The next several sections cover the installation of MySQL on
platforms where we offer packages using the native packaging
format of the respective platform. (This is also known as
performing a “binary install.”) However, binary
distributions of MySQL are available for many other platforms as
well. See Section 2.7, “Installing MySQL on Other Unix-Like Systems”, for generic
installation instructions for these packages that apply to all
platforms.

A native Windows version of MySQL has been available from MySQL AB
since version 3.21 and represents a sizable percentage of the
daily downloads of MySQL. This section describes the process for
installing MySQL on Windows.

With the release of MySQL 4.1.5, MySQL AB has introduced a new
installer for the Windows version of MySQL, combined with a new
GUI Configuration Wizard. This combination automatically installs
MySQL, creates an option file, starts the server, and secures the
default user accounts.

If you have installed a version of MySQL prior to version 4.1.5,
you must perform the following steps:

Obtain and install the distribution.

Set up an option file if necessary.

Select the server that you want to use.

Start the server.

Assign passwords to the initial MySQL accounts.

This process also must be followed with newer MySQL installations
where the installation package does not include an installer.

MySQL for Windows is available in two distribution formats:

The binary distribution contains a setup program that installs
everything you need so that you can start the server
immediately.

The source distribution contains all the code and support
files for building the executables using the Visual Studio
2003 compiler system.

Generally speaking, you should use the binary distribution. It is
simpler, and you need no additional tools to get MySQL up and
running.

If you need tables with a size larger than 4GB, install
MySQL on an NTFS or newer filesystem. Do not forget to use
MAX_ROWS and
AVG_ROW_LENGTH when you create tables.
See Section 13.1.5, “CREATE TABLE Syntax”.

2.3.2. Choosing An Installation Package

Starting with MySQL version 4.1.5, there are three install
packages to choose from when installing MySQL on Windows. The
Packages are as follows:

The Essentials Package:
This package has a filename similar to
mysql-essential-4.1.13a-win32.msi and
contains the minimum set of files needed to install MySQL on
Windows, including the Configuration Wizard. This package
does not include optional components such as the embedded
server and benchmark suite.

The Complete Package: This
package has a filename similar to
mysql-4.1.13a-win32.zip and contains
all files needed for a complete Windows installation,
including the Configuration Wizard. This package includes
optional components such as the embedded server and
benchmark suite.

The Noinstall Archive: This
package has a filename similar to
mysql-noinstall-4.1.13a-win32.zip and
contains all the files found in the Complete install
package, with the exception of the Configuration Wizard.
This package does not include an automated installer, and
must be manually installed and configured.

2.3.3. Installing MySQL with the Automated Installer

Starting with MySQL 4.1.5, users can use the new MySQL
Installation Wizard and MySQL Configuration Wizard to install
MySQL on Windows. The MySQL Installation Wizard and MySQL
Configuration Wizard are designed to install and configure MySQL
in such a way that new users can immediately get started using
MySQL.

The MySQL Installation Wizard and MySQL Configuration Wizard are
available in the Essentials and Complete install packages, and
are recommended for most standard MySQL installations.
Exceptions include users who need to install multiple instances
of MySQL on a single server and advanced users who want complete
control of server configuration.

2.3.4.1. Introduction

MySQL Installation Wizard is a new installer for the MySQL
server that uses the latest installer technologies for
Microsoft Windows. The MySQL Installation Wizard, in
combination with the MySQL Configuration Wizard, allows a user
to install and configure a MySQL server that is ready for use
immediately after installation.

The MySQL Installation Wizard is the standard installer for
all MySQL server distributions, version 4.1.5 and higher.
Users of previous versions of MySQL need to manually shut down
and remove their existing MySQL installations before
installing MySQL with the MySQL Installation Wizard. See
Section 2.3.4.7, “Upgrading MySQL”, for more
information on upgrading from a previous version.

Microsoft has included an improved version of their Microsoft
Windows Installer (MSI) in the recent versions of Windows.
Using the MSI has become the de-facto standard for application
installations on Windows 2000, Windows XP, and Windows Server
2003. The MySQL Installation Wizard makes use of this
technology to provide a smoother and more flexible
installation progress.

The Microsoft Windows Installer Engine was updated with the
release of Windows XP; those using a previous version of
Windows can reference
this
Microsoft Knowledge Base article for information on
upgrading to the latest version of the Windows Installer
Engine.

Further, Microsoft has introduced the WiX (Windows Installer
XML) tool set recently. It is the first highly acknowledged
Open Source project from Microsoft. We switched to WiX because
it is an Open Source project and it allows us to handle the
complete Windows installation process in a flexible way with
scripts.

Improving the MySQL Installation Wizard depends on the support
and feedback of users like you. If you find that the MySQL
Installation Wizard is lacking some feature important to you,
or if you discover a bug, please use our
MySQL Bug System to
request features or report problems.

2.3.4.2. Downloading and Starting the MySQL Installation Wizard

The MySQL server install packages can be downloaded from
http://dev.mysql.com/downloads/. If the package you
download is contained within a Zip archive, you need to
extract the archive first.

The process for starting the wizard depends on the contents of
the install package you download. If there is a
setup.exe file present, double-click it to
start the install process. If there is a
.msi file present, double-click it to start
the install process.

2.3.4.3. Choosing an Install Type

There are up three installation types available:
Typical, Complete, and
Custom.

The Typical installation type installs the
MySQL server, the mysql command-line
client, and the command-line utilities. The command-line
clients and utilities include mysqldump,
myisamchk, and several other tools to help
you manage the MySQL server.

The Complete installation type installs all
components included in the installation package. The full
installation package includes components such as the embedded
server library, the benchmark suite, support scripts, and
documentation.

The Custom installation type gives you
complete control over which packages you wish to install and
the installation path that is used. See
Section 2.3.4.4, “The Custom Install Dialog”, for
more information on performing a custom install.

If you choose the Typical or
Complete installation types and click the
Next button, you advance to the confirmation screen to confirm
your choices and begin the installation. If you choose the
Custom installation type and click the Next
button, you advance to the custom install dialog, described in
Section 2.3.4.4, “The Custom Install Dialog”.

2.3.4.4. The Custom Install Dialog

If you wish to change the installation path or the specific
components that are installed by the MySQL Installation
Wizard, you should choose the Custom
installation type.

All available components are listed in a tree view on the left
side of the custom install dialog. Components that are not
installed have a red
X
icon, components that are installed have a gray icon. To
change whether a component is installed, click on the
component's icon and choose an new option from the drop-down
list that appears.

You can change the default installation path by clicking the
Change... button to the right of the displayed installation
path.

After choosing your install components and installation path,
click the Next button to advance to the confirmation dialog.

2.3.4.5. The Confirmation Dialog

Once you choose an installation type and optionally choose
your installation components, you advance to the confirmation
dialog. Your installation type and installation path are
displayed for you to review.

To install MySQL if you are satisfied with your settings,
click the Install button. To change your settings, click the
Back button. To exit the MySQL Installation Wizard without
installing MySQL, click the Cancel button.

After installation is complete, you are given the option of
registering with the MySQL web site. Registration gives you
access to post in the MySQL forums at
forums.mysql.com,
along with the ability to report bugs at
bugs.mysql.com and
to subscribe to the newsletter. The final screen of the
installer provides a summary of the installation and gives you
the option to launch the MySQL Configuration Wizard, which you
can use to create a configuration file, install the MySQL
service, and configure security.

2.3.4.6. Changes Made by MySQL Installation Wizard

Once you click the Install button, the MySQL Installation
Wizard begins the installation process and makes certain
changes to your system which are described in the sections
that follow.

Changes to the Registry

The MySQL Installation Wizard creates one Windows registry key
in a typical install situation, located in
HKEY_LOCAL_MACHINE\SOFTWARE\MySQL AB.

The MySQL Installation Wizard creates a key named after the
major version of the server that is being installed, such as
MySQL Server 4.1. It contains two string
values, Location and
Version. The Location
string contains the path to the installation directory. In a
default installation it contains C:\Program
Files\MySQL\MySQL Server 4.1\. The
Version string contains the release number.
For example, for an installation of MySQL Server 4.1.5 the key
contains a value of 4.1.5.

These registry keys are used to help external tools identify
the installed location of the MySQL server, preventing a
complete scan of the hard-disk to determine the installation
path of the MySQL server. The registry keys are not required
to run the server and when using the
noinstall Zip archive the registry keys are
not created.

Changes to the Start Menu

The MySQL Installation Wizard creates a new entry in the
Windows Start menu under a common MySQL menu heading named
after the major version of MySQL that you have installed. For
example, if you install MySQL 4.1, the MySQL Installation
Wizard creates a MySQL Server 4.1 section in the start menu.

The following entries are created within the new Start menu
section:

MySQL Command Line Client: This is a
shortcut to the mysql command-line
client and is configured to connect as the
root user. The shortcut prompts for a
root user password when connecting.

MySQL Server Instance Config Wizard:
This is a shortcut to the MySQL Configuration Wizard. Use
this shortcut to configure a newly installed server, or to
re-configure an existing server.

MySQL Documentation: This is a link to
the MySQL server documentation that is stored locally in
the MySQL server installation directory. This option is
not available when the MySQL server is installed from the
essential installation package.

Changes to the File System

The MySQL Installation Wizard by default installs the MySQL
server to C:\Program
Files\MySQL\MySQL Server
4.1, where
Program Files is the default
location for applications in your system, and
4.1 is the major version of your
MySQL server. This is the new recommended location for the
MySQL server, replacing the previous default location of
c:\mysql.

By default, all MySQL applications are stored in a common
directory at C:\Program
Files\MySQL, where
Program Files is the default
location for applications in your Windows installation. A
typical MySQL installation on a developer machine may look
like this:

This approach makes it easier to manage and maintain all MySQL
applications installed on a particular system.

2.3.4.7. Upgrading MySQL

From MySQL version 4.1.5, the new MySQL Installation Wizard
can perform server upgrades automatically using the upgrade
capabilities of MSI. That means you do not need to remove a
previous installation manually before installing a new
release. The installer automatically shuts down and removes
the previous MySQL service before installing the new version.

Automatic upgrades are only available when upgrading between
installations that have the same major and minor version
numbers. For example, you can upgrade automatically from MySQL
4.1.5 to MySQL 4.1.6, but not from MySQL 4.1 to MySQL 5.0.

If you are upgrading MySQL version 4.1.4 or earlier
to version 4.1.5 or later, you must first manually shut down
and remove the older installation before upgrading. Be sure to
back up your databases before performing such an upgrade, so
that you can restore the databases after the upgrade is
completed. It is always recommended that you back up
your data before performing any
upgrades.

2.3.5.1. Introduction

The MySQL Configuration Wizard helps automate the process of
configuring your server under Windows. The MySQL Configuration
Wizard creates a custom my.ini file by
asking you a series of questions and then applying your
responses to a template to generate a
my.ini file that is tuned to your
installation.

The MySQL Configuration Wizard is included with the MySQL
server starting with MySQL version 4.1.5, but is designed to
work with MySQL servers versions 4.1 and higher. The MySQL
Configuration Wizard is currently available for Windows users
only.

MySQL Configuration Wizard is to a large extent the result of
feedback MySQL AB has received from many users over a period
of several years. However, if you find it is lacking some
feature important to you, or if you discover a bug, please use
our MySQL Bug
System to request features or report problems.

2.3.5.2. Starting the MySQL Configuration Wizard

The MySQL Configuration Wizard is typically launched from the
MySQL Installation Wizard, as the MySQL Installation Wizard
exits. You can also launch the MySQL Configuration Wizard by
clicking the MySQL Server Instance Config Wizard entry in the
MySQL section of the Start menu.

In addition, you can navigate to the bin
directory of your MySQL installation and launch the
MySQLInstanceConfig.exe file directly.

2.3.5.3. Choosing a Maintenance Option

If the MySQL Configuration Wizard detects an existing
my.ini file, you have the option of
either re-configuring your existing server, or removing the
server instance by deleting the my.ini
file and stopping and removing the MySQL service.

To reconfigure an existing server, choose the
Re-configure Instance option and click the
Next button. Your existing my.ini file is
renamed to my
timestamp.ini.bak, where
timestamp is the date and time the
existing my.ini file was created. To
remove the existing server instance, choose the
Remove Instance option and click the Next
button.

If you choose the Remove Instance option,
you advance to a confirmation window. Click the Execute button
and the MySQL Configuration Wizard stops and removes the MySQL
service and deletes the my.ini file. The
server installation and its data folder are
not removed.

If you choose the Re-configure Instance
option, you advance to the Configuration
Type dialog where you can choose the type of
installation you wish to configure.

2.3.5.4. Choosing a Configuration Type

When you start the MySQL Configuration Wizard for a new MySQL
installation, or choose the Re-configure
Instance option for an existing installation, you
advance to the Configuration Type dialog.

There are two configuration types available: Detailed
Configuration and Standard
Configuration. The Standard
Configuration option is intended for new users who
want to get started with MySQL quickly without having to make
a lot of decisions in regards to server configuration. The
Detailed Configuration option is intended
for advanced users who want more fine-grained control of
server configuration.

If you are new to MySQL and need a server configured as a
single-user developer machine the Standard
Configuration should suit your needs. Choosing the
Standard Configuration option causes the
MySQL Configuration Wizard to automatically set all
configuration options with the exception of the
Service Options and Security
Options.

The Standard Configuration sets options
that may be incompatible with systems where there are existing
MySQL installations. If you have an existing MySQL
installation on your system in addition to the installation
you wish to configure, the Detailed
Configuration option is recommended.

2.3.5.5. The Server Type Dialog

There are three different server types available to choose
from, and the server type you choose affects the decisions the
MySQL Configuration Wizard makes with regards to memory, disk,
and processor usage.

Developer Machine: Choose this
option for a typical desktop workstation where MySQL is
intended only for personal use. It is assumed that many
other desktop applications are running. The MySQL server
is configured to use minimal system resources.

Server Machine: Choose this
option for a server machine where the MySQL server is
running alongside other server applications such as FTP,
email, and web servers. The MySQL server is configured to
use a medium portion of the system resources.

Dedicated MySQL Server Machine:
Choose this option for a server machine that is intended
to run only the MySQL server. It is assumed that no other
applications are running. The MySQL server is configured
to use all available system resources.

2.3.5.6. The Database Usage Dialog

The Database Usage dialog allows you to
indicate the storage engines you expect to use when creating
MySQL tables. The option you choose determines whether the
InnoDB storage is available and what
percentage of the server resources are available to
InnoDB.

Multifunctional Database: This
option enables both the InnoDB and
MyISAM storage engines, and divides
resources evenly between the two. This option is
recommended for users who employ both table handlers on a
regular basis.

Transactional Database Only:
This option enables both the InnoDB and
MyISAM storage engines but dedicates
most server resources to the InnoDB
storage engine. This option is recommended for users who
employ InnoDB almost exclusively and
make only minimal use of MyISAM.

Non-Transactional Database
Only: This option disables the
InnoDB storage engine completely, and
dedicates all server resources to the
MyISAM storage engine. This option is
recommended for those who do not wish to use
InnoDB.

2.3.5.7. The InnoDB Tablespace Dialog

Some users may want to locate the InnoDB
tablespace files in a location other than the MySQL server
data directory. Placing the tablespace files in a separate
location can be desirable if your system has available a
storage device availablehas with higher capacity or higher
performance, such as a RAID storage system.

To change the default location for the
InnoDB tablespace files, choose a new drive
from the drop-down list of drive letters and choose a new path
from the drop-down list of paths. To create a custom path,
click the ... button.

If you are modifying the configuration of an existing server,
you must click the Modify button before
you change the path. In this situation you must move existing
tablespace files to the new location manually before starting
the server.

2.3.5.8. The Concurrent Connections Dialog

It is important to set a limit to the number of concurrent
connections to the MySQL server that can be established to
prevent the server from running out of resources. The
Concurrent Connections dialog allows you to
choose the expected usage of your server, and sets the limit
for concurrent connections accordingly. It is also possible to
manually set the concurrent connection limit.

Decision Support (DSS)/OLAP:
Choose this option if the server does not require a large
number of concurrent connections. The maximum number of
connections is set at 100, with an average of 20
concurrent connections assumed.

Online Transaction Processing
(OLTP): Choose this option if the server
requires a large number of concurrent connections. The
maximum number of connections is set at 500.

Manual Setting: Choose this
option to set the maximum number of concurrent connections
to the server manually. Choose the number of concurrent
connections from the drop-down box provided, or type the
maximum number of connections into the drop-down box if
the number you desire is not listed.

2.3.5.9. The Networking and Strict Mode Options Dialog

Use the Networking Options dialog to enable
or disable TCP/IP networking and to configure the port number
that is used to connect to the MySQL server.

TCP/IP networking is enabled by default. To disable TCP/IP
networking, uncheck the box next to the Enable
TCP/IP Networking option.

Port 3306 is used by default. To change the port used to
access MySQL, choose a new port number from the drop-down box
or type a new port number directly into the drop-down box. If
the port number you choose is in use you are prompted to
confirm your choice of port number.

Set the Server SQL Mode to either enable or
disable strict mode. Enabling strict mode (default) will make
MySQL behave more like other database management systems.
If you run applications that rely on
MySQL's old “forgiving” behavior make sure to
either adapt those applications or to disable strict
mode. For more information about strict mode, see
Section 5.2.2, “The Server SQL Mode”.

2.3.5.10. The Character Set Dialog

The MySQL server supports multiple character sets and it is
possible to set a default server character set that is applied
to all tables, columns, and databases unless overridden. Use
the Character Set dialog to change the
default character set of the MySQL server.

Standard Character Set: Choose
this option if you want to use Latin1
as the default server character set.
Latin1 is used for English and many
Western European languages.

Best Support For
Multilingualism: Choose this option if you
want to use UTF8 as the default server
character set. UTF8 can store
characters from many different languages in a single
character set.

Manually Selected Default Character Set /
Collation: Choose this option if you want to
pick the server's default character set manually. Choose
the desired character set from the provided drop-down
list.

2.3.5.11. The Service Options Dialog

On Windows NT based platforms, the MySQL server can be
installed as a service. When installed as a service, the MySQL
server can be started automatically during system startup, and
even restarted automatically by Windows in the event of a
service failure.

The MySQL Configuration Wizard installs the MySQL server as a
service by default, using the service name
MySQL. If you do not wish to install the
service, un-check the box next to the Install As
Windows Service option. You can change the
service name by picking a new service name from the drop-down
box provided or by typing a new service name into the
drop-down box.

To install the MySQL server as a service but not have it
started automatically at startup, un-check the box next to the
Launch the MySQL Server
automatically option.

2.3.5.12. The Security Options Dialog

It is strongly recommended that you set a
root password for your MySQL
server, and the MySQL Configuration Wizard requires
by default that you do so. If you do not wish to set a
root password, un-check thebox next to the
Modify Security Settings option.

To set the root password, type the desired
password into both the New root
password and Confirm
boxes. If you are re-configuring an existing server, you also
need to enter the existing root password
into the Current root password box.

To prevent root logins from across the
network, check the box next to the Root may only
connect from localhost option. This increases
the security of your root account.

To create an anonymous user account, check the box next to the
Create An Anonymous Account option.
Creating an anonymous account can decrease server security and
cause login and permission difficulties and is not
recommended.

2.3.5.13. The Confirmation Dialog

The final dialog in the MySQL Configuration Wizard is the
Confirmation Dialog. To start the
configuration process, click the
Execute button. To return to a previous
dialog, click the Back button. To exit
the MySQL Configuration Wizard without configuring the server,
click the Cancel button.

After you click the Execute button, the
MySQL Configuration Wizard performs a series of tasks with the
progress displayed onscreen as the tasks are performed.

The MySQL Configuration Wizard firsts determines various
configuration file options based on your choices using a
template prepared by MySQL AB developers and engineers. This
template is named my-template.ini and is
located in your server installation directory.

The MySQL Configuration Wizard then writes these options to a
my.ini file. The final location of the
my.ini file is displayed next to the
Write configuration file task.

If you chose to create a service for the MySQL server the
MySQL Configuration Wizard creates and starts the service. If
you are re-configuring an existing service, the MySQL
Configuration Wizard restarts the service to apply your
configuration changes.

If you chose to set a root password, the
MySQL Configuration Wizard connects to the server, sets your
new root password and applies any other
security settings you may have selected.

After the MySQL Configuration Wizard has completed its tasks,
a summary is shown. Click the Finish button to exit the MySQL
Configuration Wizard.

2.3.5.14. The Location of the my.ini File

In MySQL installations prior to version 4.1.5 it was customary
to name the server configuration file
my.cnf or my.ini and
locate the file either at c:\my.cnf or
c:\Windows\my.ini.

The new MySQL Configuration Wizard places the
my.ini file in the installation directory
of the MySQL server. This helps associate configuration files
with particular server instances.

To ensure that the MySQL server knows where to look for the
my.ini file, an argument similar to this
is passed to the MySQL server as part of the service
installation: --defaults-file="C:\Program
Files\MySQL\MySQL Server 4.1\my.ini",
where C:\Program Files\MySQL\MySQL Server
4.1 is replaced with the installation path to
the MySQL Server.

The --defaults-file instructs the MySQL
server to read the specified file for configuration options.

2.3.5.15. Editing the my.ini File

To modify the my.ini file, open it with a
text editor and make any necessary changes. You can also
modify the server configuration with the
MySQL
Administrator utility.

MySQL clients and utilities such as the
mysql command-line client and
mysqldump are not able to locate the
my.ini file located in the server
installation directory. To configure the client and utility
applications, create a new my.ini file in
the c:\Windows directory.

2.3.6. Installing MySQL from a Noinstall Zip Archive

Users who are installing from the Noinstall package, or who are
installing a version of MySQL prior to 4.1.5 can use the
instructions in this section to manually install MySQL. If you
are installing a version prior to 4.1.5 with an install package
that includes a Setup program, substitute running the Setup
program for extracting the archive.

2.3.7. Extracting the Install Archive

If you are using a Windows NT-based operating system such as
Windows NT, Windows 2000, Windows XP, or Windows Server
2003, make sure that you are logged in as a user with
administrator privileges.

Choose an installation location. Traditionally the MySQL
server is installed at C:\mysql, and
the new MySQL Installation Wizard installs MySQL to
C:\Program Files\MySQL. If you do not
install MySQL in C:\mysql, you must
specify the path to the install directory during startup or
in an option file. See
Section 2.3.8, “Creating an Option File”.

Extract the install archive to the chosen installation
location using your preferred Zip archive tool. Some tools
may extract the archive to a folder within your chosen
installation location. If this occurs you can move the
contents of the subfolder into the chosen installation
location.

2.3.8. Creating an Option File

If you need to specify startup options when you run the server,
you can indicate them on the command line or place them in an
option file. For options that are used every time the server
starts, you may find it most convenient to use an option file to
specify your MySQL configuration. This is particularly true
under the following circumstances:

The installation or data directory locations differ from the
default locations (C:\mysql and
C:\mysql\data).

You need to tune the server settings. For example, to use
the InnoDB transactional tables in MySQL
3.23, you must manually add some extra lines to the option
file, as described in
Section 15.4, “InnoDB Configuration”. (As of MySQL 4.0,
InnoDB creates its data files and log
files in the data directory by default. This means you need
not configure InnoDB explicitly. You may
still do so if you wish, and an option file is also useful
in this case.)

When the MySQL server starts on Windows, it looks for options in
two files: the my.ini file in the Windows
directory, and the C:\my.cnf file. The
Windows directory typically is named something like
C:\WINDOWS or
C:\WINNT. You can determine its exact
location from the value of the WINDIR
environment variable using the following command:

C:\> echo %WINDIR%

MySQL looks for options first in the my.ini
file, and then in the my.cnf file. However,
to avoid confusion, it is best if you use only one file. If your
PC uses a boot loader where the C: drive is
not the boot drive, your only option is to use the
my.ini file. Whichever option file you use,
it must be a plain text file.

You can also make use of the example option files included with
your MySQL distribution. Look in your installation directory for
files such as my-small.cnf,
my-medium.cnf,
my-large.cnf, and so on, which you can
rename and copy to the appropriate location for use as a base
configuration file.

An option file can be created and modified with any text editor,
such as the Notepad program. For example, if
MySQL is installed in E:\mysql and the data
directory is E:\mydata\data, you can create
the option file and set up a [mysqld] section
to specify values for the basedir and
datadir parameters:

[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=E:/mydata/data

Note that Windows pathnames are specified in option files using
forward slashes rather than backslashes. If you do use
backslashes, you must double them:

[mysqld]
# set basedir to your installation path
basedir=E:\\mysql
# set datadir to the location of your data directory
datadir=E:\\mydata\\data

On Windows, the MySQL installer places the data directory
directly under the directory where you install MySQL. If you
would like to use a data directory in a different location, you
should copy the entire contents of the data
directory to the new location. For example, by default, the
installer places MySQL in C:\mysql and the
data directory in C:\mysql\data. If you
want to use E:\mydata as the data
directory, you must do two things:

Move the data directory from
C:\mysql\data to
E:\mydata.

Use a --datadir option to specify the new
data directory location each time you start the server.

2.3.9. Selecting a MySQL Server type

Starting with MySQL 3.23.38, the Windows distribution includes
both the normal and the MySQL-Max server binaries.

Up through the early releases of MySQL 4.1, the servers included
in Windows distributions are named like this:

Binary

Description

mysqld

Compiled with full debugging and automatic memory allocation checking,
and InnoDB and BDB
tables.

mysqld-opt

Optimized binary. From version 4.0 on, InnoDB is
enabled. Before 4.0, this server includes no
transactional table support.

mysqld-nt

Optimized binary for Windows NT, 2000, and XP with support for named
pipes.

mysqld-max

Optimized binary with support for InnoDB and
BDB tables.

mysqld-max-nt

Like mysqld-max, but compiled with support for named
pipes.

We have found that the server with the most generic name
(mysqld) is the one that many users are
likely to choose by default. However, that is also the server
that results in the highest memory and CPU use due to the
inclusion of full debugging support. The server named
mysqld-opt is a better general-use server
choice to make instead if you do not need debugging support and
do not want the maximal feature set offered by the
-max servers or named pipe support offered by
the -nt servers.

To make it less likely that the debugging server would be chosen
inadvertently, some name changes were made from MySQL 4.1.2 to
4.1.4: mysqld has been renamed to
mysqld-debug and
mysqld-opt has been renamed to
mysqld. Thus, the server that includes
debugging support indicates that in its name, and the server
named mysqld is an efficient default choice.
The other servers still have their same names. The resulting
servers are named like this:

Binary

Description

mysqld-debug

Compiled with full debugging and automatic memory allocation checking,
and InnoDB and BDB
tables.

mysqld

Optimized binary with InnoDB support.

mysqld-nt

Optimized binary for Windows NT, 2000, and XP with support for named
pipes.

mysqld-max

Optimized binary with support for InnoDB and
BDB tables.

mysqld-max-nt

Like mysqld-max, but compiled with support for named
pipes.

The name changes were not both instituted at the same time. If
you have MySQL 4.1.2 or 4.1.3, it might be that you have a
server named mysqld-debug but not one named
mysqld. In this case, you should have a
server mysqld-opt, which you should choose as
your default server unless you need maximal features, named
pipes, or debugging support.

All of the preceding binaries are optimized for modern Intel
processors, but should work on any Intel i386-class or higher
processor.

As of MySQL 4.0, all Windows servers have support for symbolic
linking of database directories. Before MySQL 4.0, only the
debugging and Max server versions include this feature.

MySQL supports TCP/IP on all Windows platforms. The
mysqld-nt and mysql-max-nt
servers support named pipes on Windows NT, 2000, XP, and 2003.
However, the default is to use TCP/IP regardless of the
platform. (Named pipes are slower than TCP/IP in many Windows
configurations.)

Use of named pipes is subject to these conditions:

Starting from MySQL 3.23.50, named pipes are enabled only if
you start the server with the
--enable-named-pipe option. It is necessary
to use this option explicitly because some users have
experienced problems shutting down the MySQL server when
named pipes were used.

Named pipe connections are allowed only by the
mysqld-nt or
mysqld-max-nt servers, and only if the
server is run on a version of Windows that supports named
pipes (NT, 2000, XP, 2003).

These servers can be run on Windows 98 or Me, but only if
TCP/IP is installed; named pipe connections cannot be used.

These servers cannot be run on Windows 95.

Note: Most of the examples in
reference manual use mysqld as the server
name. If you choose to use a different server, such as
mysqld-nt, make the appropriate substitutions
in the commands that are shown in the examples.

2.3.10. Starting the Server for the First Time

On Windows 95, 98, or Me, MySQL clients always connect to the
server using TCP/IP. (This allows any machine on your network to
connect to your MySQL server.) Because of this, you must make
sure that TCP/IP support is installed on your machine before
starting MySQL. You can find TCP/IP on your Windows CD-ROM.

Note that if you are using an old Windows 95 release (for
example, OSR2), it is likely that you have an old Winsock
package; MySQL requires Winsock 2. You can get the newest
Winsock from http://www.microsoft.com/. Windows
98 has the new Winsock 2 library, so it is unnecessary to update
the library.

On NT-based systems such as Windows NT, 2000, XP, or 2003,
clients have two options. They can use TCP/IP, or they can use a
named pipe if the server supports named pipe connections. For
MySQL to work with TCP/IP on Windows NT 4, you must install
service pack 3 (or newer).

In MySQL versions 4.1 and higher, Windows servers also support
shared-memory connections if started with the
--shared-memory option. Clients can connect
through shared memory by using the
--protocol=memory option.

This section gives a general overview of starting the MySQL
server. The following sections provide more specific information
for starting the MySQL server from the command line or as a
Windows service.

The examples in these sections assume that MySQL is installed
under the default location of C:\mysql.
Adjust the pathnames shown in the examples if you have MySQL
installed in a different location.

Testing is best done from a command prompt in a console window
(a “DOS window”). This way you can have the server
display status messages in the window where they are easy to
see. If something is wrong with your configuration, these
messages make it easier for you to identify and fix any
problems.

To start the server, enter this command:

C:\> C:\mysql\bin\mysqld --console

For servers that include InnoDB support, you
should see the following messages as the server starts:

InnoDB: The first specified datafile c:\ibdata\ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file c:\ibdata\ibdata1 size to 209715200
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 31457280
InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created
InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 31457280
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: creating foreign key constraint system tables
InnoDB: foreign key constraint system tables created
011024 10:58:25 InnoDB: Started

When the server finishes its startup sequence, you should see
something like this, which indicates that the server is ready to
service client connections:

2.3.11. Starting MySQL from the Windows Command Line

The MySQL server can be started manually from the command line.
This can be done on any version of Windows.

To start the mysqld server from the command
line, you should start a console window (a “DOS
window”) and enter this command:

C:\> C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld

The path used in the preceding example may vary depending on the
install location of MySQL on your system.

On non-NT versions of Windows, this starts
mysqld in the background. That is, after the
server starts, you should see another command prompt. If you
start the server this way on Windows NT, 2000, XP, or 2003, the
server runs in the foreground and no command prompt appears
until the server exits. Because of this, you should open another
console window to run client programs while the server is
running.

This invokes the MySQL administrative utility
mysqladmin to connect to the server and tell
it to shut down. The command connects as the MySQL
root user, which is the default
administrative account in the MySQL grant system. Note that
users in the MySQL grant system are wholly independent from any
login users under Windows.

If mysqld does not start, check the error log
to see whether the server wrote any messages there to indicate
the cause of the problem. The error log is located in the
C:\mysql\data directory. It is the file
with a suffix of .err. You can also try to
start the server as mysqld --console; in this
case, you may get some useful information on the screen that may
help solve the problem.

The last option is to start mysqld with
--standalone --debug. In this case,
mysqld writes a log file
C:\mysqld.trace that should contain the
reason why mysqld does not start. See
Section E.1.2, “Creating Trace Files”.

Use mysqld --verbose --help to display all
the options that mysqld understands. (Prior
to MySQL 4.1, omit the --verbose option.)

2.3.12. Starting MySQL as a Windows Service

On the NT family (Windows NT, 2000, XP, 2003), the recommended
way to run MySQL is to install it as a Windows service. With the
MySQL server installed as a service, Windows starts and stops it
server automatically when Windows starts and stops. A MySQL
server installed as a service can also be controlled from the
command line using NET commands, or with the
graphical Services utility.

The Services utility (the Windows
Service Control Manager) can be found in the
Windows Control Panel (under
Administrative Tools on Windows 2000,
XP, and Server 2003). It is advisable to close the
Services utility while performing server
installation or removal operations from this command line. This
prevents some odd errors.

Before installing MySQL as a Windows service, you should first
stop the current server if it is running by using the following
command:

C:\> C:\mysql\bin\mysqladmin -u root shutdown

This invokes the MySQL administrative utility
mysqladmin to connect to the server and tell
it to shut down. The command connects as the MySQL
root user, which is the default
administrative account in the MySQL grant system. Note that
users in the MySQL grant system are wholly independent from any
login users under Windows.

Install the server as a service using this command:

C:\> mysqld --install

If you have problems installing mysqld as a
service using just the server name, try installing it using its
full pathname. For example:

C:\> C:\mysql\bin\mysqld --install

The service-installation command does not start the server.
Instructions for that are given later in this section.

Before MySQL 4.0.2, no command-line arguments can be given
following the --install option. MySQL 4.0.2 and
up offers limited support for additional arguments:

You can specify a service name immediately following the
--install option. The default service name
is MySQL.

As of MySQL 4.0.3, if a service name is given, it can be
followed by a single option. By convention, this should be
--defaults-file=file_name
to specify the name of an option file from which the server
should read options when it starts.

It is possible to use a single option other than
--defaults-file, but this is discouraged.
--defaults-file is more flexible because it
enables you to specify multiple startup options for the
server by placing them in the named option file. Also, in
MySQL 5.0, use of an option different from
--defaults-file is not supported until
5.0.3.

As of MySQL 5.0.1, you can also specify a
--local-service option following the
service name. This causes the server to run using the
LocalService Windows account that has
limited system privileges. This account is available only
for Windows XP or newer. If both
--defaults-file and
--local-service are given following the
service name, they can be in any order.

For a MySQL server that is installed as a Windows service, the
following rules determine the service name and option files that
the server uses:

If the service-installation command specifies no service
name or the default service name (MySQL)
following the --install option, the server
uses the a service name of MySQL and
reads options from the [mysqld] group in
the standard option files.

If the service-installation command specifies a service name
other than MySQL following the
--install option, the server uses that
service name. It reads options from the group that has the
same name as the service, and reads options from the
standard option files.

As of MySQL 4.0.17, the server also reads options from the
[mysqld] group from the standard option
files. This allows you to use the
[mysqld] group for options that should be
used by all MySQL services, and an option group with the
same name as a service for use by the server installed with
that service name.

If the service-installation command specifies a
--defaults-file option after the service
name, the server reads options only from the
[mysqld] group of the named file and
ignores the standard option files.

Here, the default service name (MySQL) is
given after the --install option. If no
--defaults-file option had been given, this
command would have the effect of causing the server to read the
[mysqld] group from the standard option
files. However, because the --defaults-file
option is present, the server reads options from the
[mysqld] option group, but only from the
named file.

You can also specify options in Start
parameters in the Windows
Services utility before you start the MySQL
service.

Note: Prior to MySQL 4.0.17, a
server installed as a Windows service has problems starting if
its pathname or the service name contains spaces. For this
reason, with older versions, avoid installing MySQL in a
directory such as C:\Program Files or using
a service name containing spaces.

Once a MySQL server has been installed as a service, Windows
starts the service automatically whenever Windows starts. The
service also can be started immediately from the
Services utility, or by using the command
NET START MySQL. The NET
command is not case sensitive.

When run as a service, mysqld has no access
to a console window, so no messages can be seen there. If
mysqld does not start, check the error log to
see whether the server wrote any messages there to indicate the
cause of the problem. The error log is located in the MySQL data
directory (for example, C:\mysql\data). It
is the file with a suffix of .err.

When a MySQL server has been installed as a service, and the
service is running, Windows stops the service automatically when
Windows shuts down. The server also can be stopped manually by
using the Services utility, the command
NET STOP MySQL, or the command
mysqladmin shutdown.

From MySQL 3.23.44 on, you have the choice of installing the
server as a Manual service if you do not
wish the service to be started automatically during the boot
process. To do this, use the --install-manual
option rather than the --install option:

C:\> C:\mysql\bin\mysqld --install-manual

To remove a server that is installed as a service, first stop it
if it is running. Then use the --remove option
to remove it:

C:\> C:\mysql\bin\mysqld --remove

For MySQL versions older than 3.23.49, one problem with
automatic MySQL service shutdown is that Windows waited only for
a few seconds for the shutdown to complete, and then killed the
database server process if the time limit was exceeded. This had
the potential to cause problems. (For example, the
InnoDB storage engine would have to perform
crash recovery at the next startup.) Starting from MySQL
3.23.49, Windows waits longer for the MySQL server shutdown to
complete. If you notice this still is not enough for your
installation, it is safest not to run the MySQL server as a
service. Instead, start it from the command-line prompt, and
stop it with mysqladmin shutdown.

This change to tell Windows to wait longer when stopping the
MySQL server works for Windows 2000 and XP. It does not work for
Windows NT, where Windows waits only 20 seconds for a service to
shut down, and after that kills the service process. You can
increase this default by opening the Registry
Editor
(\winnt\system32\regedt32.exe) and editing
the value of WaitToKillServiceTimeout at
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
in the Registry tree. Specify the new larger value in
milliseconds. For example, the value 120000
tells Windows NT to wait up to 120 seconds.

If mysqld is slow to respond to TCP/IP
connections from client programs on Windows 9x/Me, there is
probably a problem with your DNS. In this case, start
mysqld with the
--skip-name-resolve option and use only
localhost and IP numbers in the
Host column of the MySQL grant tables.

You can force a MySQL client to use a named pipe connection
rather than TCP/IP by specifying the --pipe
option or by specifying . (period) as the
host name. Use the --socket option to specify
the name of the pipe. As of MySQL 4.1, you can use the
--protocol=PIPE option instead.

Compiled with the Cygnus GNU compiler and libraries, which offers
readline editing.
mysqlc was intended for use primarily
with Windows 9x/Me. It does not support the updated
authentication protocol used beginning with MySQL 4.1,
and is not supported in MySQL 4.1 and above. Beginning
with MySQL 4.1.8, it is no longer included in MySQL
Windows distributions.

To use mysqlc, you must have a copy of the
cygwinb19.dll library installed somewhere
that mysqlc can find it. If your distribution
does not have the cygwinb19.dll library in
the bin directory under the base directory
of your MySQL installation, look for it in the
lib directory and copy it to your Windows
system directory (\Windows\system or a
similar place).

2.3.14. Troubleshooting a MySQL Installation Under Windows

When installing and running MySQL for the first time, you may
encounter certain errors that prevent the MySQL server from
starting. The purpose of this section is to help you diagnose
and correct some of these errors.

Your first resource when troubleshooting server issues is the
error log. The MySQL server uses the error log to record
information relevant to the error that is preventing the server
from starting. The error log is located in the data directory
specified in your my.ini file. The default
data directory location is C:\mysql\data.
See Section 5.10.1, “The Error Log”.

Another source of information regarding possible errors is the
console messages displayed when the MySQL service is starting.
Use the NET START mysql command from the
command line after installing mysqld as a
service to see any error messages regarding the starting of the
MySQL server as a service. See
Section 2.3.12, “Starting MySQL as a Windows Service”.

The following are examples of some of the more common error
messages you may encounter when installing MySQL and starting
the server for the first time:

System error 1067 has occurred.
Fatal error: Can't open privilege tables: Table 'mysql.host' does not exist

These messages occur when the MySQL server cannot find the
mysql privileges database or other
critical files. This error is often encountered when the
MySQL base or data directories are installed in different
locations than the default locations
(C:\mysql and
C:\mysql\data, respectively).

One situation when this may occur is when MySQL is upgraded
and installed to a new location, but the configuration file
is not updated to reflect the new install location. In
addition there may be old and new configuration files that
conflict, be sure to delete or rename any old configuration
files when upgrading MySQL.

If you have installed MySQL to a directory other than
C:\mysql you need to ensure that the
MySQL server is aware of this through the use of a
configuration (my.ini) file. The
my.ini file needs to be located in your
Windows directory, typically located at
C:\WINNT or
C:\WINDOWS. You can determine its exact
location from the value of the WINDIR
environment variable by issuing the following command from
the command prompt:

C:\> echo %WINDIR%

An option file can be created and modified with any text
editor, such as the Notepad program. For
example, if MySQL is installed in
E:\mysql and the data directory is
D:\MySQLdata, you can create the option
file and set up a [mysqld] section to
specify values for the basedir and
datadir parameters:

[mysqld]
# set basedir to your installation path
basedir=E:/mysql
# set datadir to the location of your data directory
datadir=D:/MySQLdata

Note that Windows pathnames are specified in option files
using forward slashes rather than backslashes. If you do use
backslashes, you must double them:

This error is encountered when you re-install or upgrade
MySQL without first stopping and removing the existing MySQL
service and install MySQL using the MySQL Configuration
Wizard. This happens because when the Configuration Wizard
tries to install the service it finds an existing service
with the same name.

One solution to this problem is to choose a service name
other than mysql when using the
configuration wizard. This will allow the new service to be
installed correctly, but leaves the outdated service in
place. While this is harmless it is best to remove old
services that are no longer in use.

To permanently remove the old mysql service, execute the
following command as a user with administrative privileges,
on the command-line:

If the server is installed as a service, stop the service
with the following command from the command prompt:

C:\> NET STOP MySQL

If you are not running the MySQL server as a service, use
the following command to stop the server:

C:\> C:\mysql\bin\mysqladmin -u root shutdown

When upgrading to MySQL 4.1.5 or higher from a previous
version, or when upgrading from a version of MySQL installed
from a Zip archive to a version of MySQL installed with the
MySQL Installation Wizard, you must manually remove the
previous installation and MySQL service (if the server is
installed as a service).

To remove the MySQL service, use the following command:

C:\> C:\mysql\bin\mysqld --remove

If you do not remove the existing
service, the MySQL Installation Wizard may fail to properly
install the new MySQL service.

If you are installing MySQL from a Zip archive, extract the
archive. You may either overwrite your existing MySQL
installation (usually located at
C:\mysql), or install it into a
different directory, such as C:\mysql4.
Overwriting the existing installation is recommended.

Restart the server. For example, use NET START
MySQL if you run MySQL as a service, or invoke
mysqld directly otherwise.

2.3.16. MySQL on Windows Compared to MySQL on Unix

MySQL for Windows has proven itself to be very stable. The
Windows version of MySQL has the same features as the
corresponding Unix version, with the following exceptions:

Windows 95 and threads

Windows 95 leaks about 200 bytes of main memory for each
thread creation. Each connection in MySQL creates a new
thread, so you should not run mysqld for
an extended time on Windows 95 if your server handles many
connections! Other versions of Windows do not suffer from
this bug.

Limited number of ports

Windows systems have about 4,000 ports available for client
connections, and after a connection on a port closes, it
takes two to four minutes before the port can be reused. In
situations where clients connect to and disconnect from the
server at a high rate, it is possible for all available
ports to be used up before closed ports become available
again. If this happens, the MySQL server appears to be
unresponsive even though it is running. Note that ports may
be used by other applications running on the machine as
well, in which case the number of ports available to MySQL
is lower.

MySQL depends on the pread() and
pwrite() calls to be able to mix
INSERT and SELECT.
Currently we use mutexes to emulate
pread()/pwrite(). We
will, in the long run, replace the file level interface with
a virtual interface so that we can use the
readfile()/writefile()
interface on NT, 2000, and XP to get more speed. The current
implementation limits the number of open files MySQL can use
to 2,048 (1,024 before MySQL 4.0.19), which means that you
cannot run as many concurrent threads on NT, 2000, XP, and
2003 as on Unix.

Blocking read

MySQL uses a blocking read for each connection, which has
the following implications if named pipe connections are
enabled:

A connection is not disconnected automatically after
eight hours, as happens with the Unix version of MySQL.

If a connection hangs, it is impossible to break it
without killing MySQL.

mysqladmin kill does not work on a
sleeping connection.

mysqladmin shutdown cannot abort as
long as there are sleeping connections.

We plan to fix this problem when our Windows developers have
figured out a workaround.

ALTER
TABLE

While you are executing an ALTER TABLE
statement, the table is locked from being used by other
threads. This has to do with the fact that on Windows, you
cannot delete a file that is in use by another thread. In
the future, we may find some way to work around this
problem.

DROP
TABLE

DROP TABLE on a table that is in use by a
MERGE table does not work on Windows
because the MERGE handler does the table
mapping hidden from the upper layer of MySQL. Because
Windows does not allow you to drop files that are open, you
first must flush all MERGE tables (with
FLUSH TABLES) or drop the
MERGE table before dropping the table. We
will fix this at the same time we introduce views.

DATA DIRECTORY and
INDEX DIRECTORY

The DATA DIRECTORY and INDEX
DIRECTORY options for CREATE
TABLE are ignored on Windows, because Windows does
not support symbolic links. These options also are ignored
on systems that have a non-functional
realpath() call.

DROP
DATABASE

You cannot drop a database that is in use by a thread.

Killing MySQL from the Task
Manager

You cannot kill MySQL from the Task Manager or with the
shutdown utility in Windows 95. You must stop it with
mysqladmin shutdown or the NET
STOP ... command.

Case-insensitive names

Filenames are not case sensitive on Windows, so MySQL
database and table names are also not case sensitive on
Windows. The only restriction is that database and table
names must be specified using the same case throughout a
given statement. See
Section 9.2.2, “Identifier Case Sensitivity”.

The ‘\’
pathname separator character

Pathname components in Windows are separated by the
‘\’ character, which is also
the escape character in MySQL. If you are using
LOAD DATA INFILE or SELECT ...
INTO OUTFILE, use Unix-style filenames with
‘/’ characters:

The latter command also can be used to reliably read in any
SQL file that may contain binary data.

Access denied for
user error

If you attempt to run a MySQL client program to connect to a
server running on the same machine, but get the error
Access denied for user
'some-user'@'unknown' to database
'mysql', this means that MySQL cannot resolve your
hostname properly.

To fix this, you should create a file named
\windows\hosts containing the following
information:

127.0.0.1 localhost

Here are some open issues for anyone who might want to help us
improve MySQL on Windows:

Add macros to use the faster thread-safe increment/decrement
methods provided by Windows.

2.4. Installing MySQL on Linux

The recommended way to install MySQL on Linux is by using the RPM
packages. The MySQL RPMs are currently built on a SuSE Linux 7.3
system, but should work on most versions of Linux that support
rpm and use glibc. To obtain
RPM packages, see Section 2.1.3, “How to Get MySQL”.

MySQL AB does provide some platform-specific RPMs; the difference
between a platform-specific RPM and a generic RPM is that the
platform-specific RPMs are built on the targeted platform and are
linked dynamically whereas the generic RPM is linked statically
with LinuxThreads.

Note: RPM distributions of MySQL
often are provided by other vendors. Be aware that they may differ
in features and capabilities from those built by MySQL AB, and
that the instructions in this manual do not necessarily apply to
installing them. The vendor's instructions should be consulted
instead.

In most cases, you only need to install the
MySQL-server and
MySQL-client packages to get a functional MySQL
installation. The other packages are not required for a standard
installation. If you want to run a MySQL-Max server that has
additional capabilities, you should also install the
MySQL-Max RPM. However, you should do so only
after installing the
MySQL-server RPM. See
Section 5.1.2, “The mysqld-max Extended MySQL Server”.

If you get a dependency failure when trying to install the MySQL
4.0 packages (for example, “error: removing these
packages would break dependencies: libmysqlclient.so.10 is needed
by ...”), you should also install the package
MySQL-shared-compat, which includes both the
shared libraries for backward compatibility
(libmysqlclient.so.12 for MySQL 4.0 and
libmysqlclient.so.10 for MySQL 3.23).

Many Linux distributions still ship with MySQL 3.23 and they
usually link applications dynamically to save disk space. If these
shared libraries are in a separate package (for example,
MySQL-shared), it is sufficient to simply leave
this package installed and just upgrade the MySQL server and
client packages (which are statically linked and do not depend on
the shared libraries). For distributions that include the shared
libraries in the same package as the MySQL server (for example,
Red Hat Linux), you could either install our 3.23
MySQL-shared RPM, or use the
MySQL-shared-compat package instead.

The following RPM packages are available:

MySQL-server-VERSION.i386.rpm

The MySQL server. You need this unless you only want to
connect to a MySQL server running on another machine. Note:
Server RPM files were called
MySQL-VERSION.i386.rpm
before MySQL 4.0.10. That is, they did not have
-server in the name.

MySQL-Max-VERSION.i386.rpm

The MySQL-Max server. This server has additional capabilities
that the one provided in the MySQL-server
RPM does not. You must install the
MySQL-server RPM first, because the
MySQL-Max RPM depends on it.

MySQL-client-VERSION.i386.rpm

The standard MySQL client programs. You probably always want
to install this package.

MySQL-bench-VERSION.i386.rpm

Tests and benchmarks. Requires Perl and the
DBD::mysql module.

MySQL-devel-VERSION.i386.rpm

The libraries and include files that are needed if you want to
compile other MySQL clients, such as the Perl modules.

MySQL-shared-VERSION.i386.rpm

This package contains the shared libraries
(libmysqlclient.so*) that certain languages
and applications need to dynamically load and use MySQL.

MySQL-shared-compat-VERSION.i386.rpm

This package includes the shared libraries for both MySQL 3.23
and MySQL 4.0. Install this package instead of
MySQL-shared if you have applications
installed that are dynamically linked against MySQL 3.23 but
you want to upgrade to MySQL 4.0 without breaking the library
dependencies. This package has been available since MySQL
4.0.13.

MySQL-embedded-VERSION.i386.rpm

The embedded MySQL server library (from MySQL 4.0).

MySQL-VERSION.src.rpm

This contains the source code for all of the previous
packages. It can also be used to rebuild the RPMs on other
architectures (for example, Alpha or SPARC).

To see all files in an RPM package (for example, a
MySQL-server RPM), run:

The server RPM places data under the
/var/lib/mysql directory. The RPM also
creates a login account for a user named mysql
(if one does not exist) to use for running the MySQL server, and
creates the appropriate entries in
/etc/init.d/ to start the server
automatically at boot time. (This means that if you have performed
a previous installation and have made changes to its startup
script, you may want to make a copy of the script so that you do
not lose it when you install a newer RPM.) See
Section 2.9.2.2, “Starting and Stopping MySQL Automatically”, for more information on how
MySQL can be started automatically on system startup.

If you want to install the MySQL RPM on older Linux distributions
that do not support initialization scripts in
/etc/init.d (directly or via a symlink), you
should create a symbolic link that points to the location where
your initialization scripts actually are installed. For example,
if that location is /etc/rc.d/init.d, use
these commands before installing the RPM to create
/etc/init.d as a symbolic link that points
there:

shell> cd /etc
shell> ln -s rc.d/init.d .

However, all current major Linux distributions should support the
new directory layout that uses /etc/init.d,
because it is required for LSB (Linux Standard Base) compliance.

If the RPM files that you install include
MySQL-server, the mysqld
server should be up and running after installation. You should be
able to start using MySQL.

2.5. Installing MySQL on Mac OS X

Beginning with MySQL 4.0.11, you can install MySQL on Mac OS X
10.2.x (“Jaguar”) and up using a Mac OS X binary
package in PKG format instead of the binary tarball distribution.
Please note that older versions of Mac OS X (for example, 10.1.x)
are not supported by this package.

The package is located inside a disk image
(.dmg) file that you first need to mount by
double-clicking its icon in the Finder. It should then mount the
image and display its contents.

Note: Before proceeding with the
installation, be sure to shut down all running MySQL server
instances by either using the MySQL Manager Application (on Mac OS
X Server) or via mysqladmin shutdown on the
command line.

To install the MySQL PKG file, double-click on the package icon.
This launches the Mac OS X Package Installer, which guides you
through the installation of MySQL.

Due to a bug in the Mac OS X package installer, you may see this
error message in the destination disk selection dialog:

You cannot install this software on this disk. (null)

If this error occurs, simply click the Go
Back button once to return to the previous screen.
Then click Continue to advance to the
destination disk selection again, and you should be able to choose
the destination disk correctly. We have reported this bug to Apple
and it is investigating this problem.

The Mac OS X PKG of MySQL installs itself into
/usr/local/mysql-VERSION
and also installs a symbolic link,
/usr/local/mysql, pointing to the new
location. If a directory named
/usr/local/mysql exists, it is renamed to
/usr/local/mysql.bak first. In addition, the
installer creates the grant tables in the mysql
database by executing mysql_install_db after
the installation.

The installation layout is similar to that of a
tar file binary distribution; all MySQL
binaries are located in the directory
/usr/local/mysql/bin. The MySQL socket file
is created as /tmp/mysql.sock by default. See
Section 2.1.5, “Installation Layouts”.

MySQL installation requires a Mac OS X user account named
mysql. A user account with this name should
exist by default on Mac OS X 10.2 and up.

If you are running Mac OS X Server, you have a version of MySQL
installed. The versions of MySQL that ship with Mac OS X Server
versions are shown in the following table:

Mac OS X Server Version

MySQL Version

10.2-10.2.2

3.23.51

10.2.3-10.2.6

3.23.53

10.3

4.0.14

10.3.2

4.0.16

10.4.0

4.1.10a

This manual section covers the installation of the official MySQL
Mac OS X PKG only. Make sure to read Apple's help information
about installing MySQL: Run the Help View
application, select Mac OS X Server
help, search for “MySQL”, and read the item entitled
“Installing MySQL”.

For pre-installed versions of MySQL on Mac OS X Server, note
especially that you should start mysqld with
safe_mysqld instead of
mysqld_safe if MySQL is older than version 4.0.

If you previously used Marc Liyanage's MySQL packages for Mac OS X
from http://www.entropy.ch, you can simply follow
the update instructions for packages using the binary installation
layout as given on his pages.

If you are upgrading from Marc's 3.23.xx versions or from the Mac
OS X Server version of MySQL to the official MySQL PKG, you also
need to convert the existing MySQL privilege tables to the current
format, because some new security privileges have been added. See
Section 2.10.3, “Upgrading the Grant Tables”.

If you would like to automatically start up MySQL during system
startup, you also need to install the MySQL Startup Item. Starting
with MySQL 4.0.15, it is part of the Mac OS X installation disk
images as a separate installation package. Simply double-click the
MySQLStartupItem.pkg icon and follow the
instructions to install it.

Note that the Startup Item need be installed only once! There is
no need to install it each time you upgrade the MySQL package
later.

The Startup Item is installed into
/Library/StartupItems/MySQLCOM. (Before MySQL
4.1.2, the location was
/Library/StartupItems/MySQL, but that
collided with the MySQL Startup Item installed by Mac OS X
Server.) Startup Item installation adds a variable
MYSQLCOM=-YES- to the system configuration file
/etc/hostconfig. If you would like to disable
the automatic startup of MySQL, simply change this variable to
MYSQLCOM=-NO-.

On Mac OS X Server, the default MySQL installation uses the
variable MYSQL in the
/etc/hostconfig file. The MySQL AB Startup
Item installer disables this variable by setting it to
MYSQL=-NO-. This avoids boot time conflicts
with the MYSQLCOM variable used by the MySQL AB
Startup Item. However, it does not shut down a running MySQL
server. You should do that yourself.

After the installation, you can start up MySQL by running the
following commands in a terminal window. You must have
administrator privileges to perform this task.

Even better, add /usr/local/mysql/bin to your
PATH environment variable. For example, add the
following line to your $HOME/.tcshrc file if
your shell is tcsh:

setenv PATH ${PATH}:/usr/local/mysql/bin

If no .tcshrc file exists in your home
directory, create it with a text editor.

If you are upgrading an existing installation, please note that
installing a new MySQL PKG does not remove the directory of an
older installation. Unfortunately, the Mac OS X Installer does not
yet offer the functionality required to properly upgrade
previously installed packages.

To use your existing databases with the new installation, you will
need to copy the contents of the old data directory to the new
data directory. Make sure that neither the old server nor the new
one is running when you do this. After you have copied over the
MySQL database files from the previous installation and have
successfully started the new server, you should consider removing
the old installation files to save disk space. Additionally, you
should also remove older versions of the Package Receipt
directories located in
/Library/Receipts/mysql-VERSION.pkg.

2.6. Installing MySQL on NetWare

Porting MySQL to NetWare was an effort spearheaded by Novell.
Novell customers should be pleased to note that NetWare 6.5 ships
with bundled MySQL binaries, complete with an automatic commercial
use license for all servers running that version of NetWare.

MySQL for NetWare is compiled using a combination of
Metrowerks CodeWarrior for NetWare and special
cross-compilation versions of the GNU autotools.

The system must meet Novell's minimum requirements to run the
respective version of NetWare.

MySQL data, as well as the binaries themselves, must be
installed on an NSS volume; traditional volumes are not
supported.

To install MySQL for NetWare, use the following procedure:

If you are upgrading from a prior installation, stop the MySQL
server. This is done from the server console, using the
following command:

SERVER: mysqladmin -u root shutdown

Log on to the target server from a client machine with access
to the location where you are installing MySQL.

Extract the binary package Zip file onto the server. Be sure
to allow the paths in the Zip file to be used. It is safe to
simply extract the file to SYS:\.

If you are upgrading from a prior installation, you may need
to copy the data directory (for example,
SYS:MYSQL\DATA), as well as
my.cnf, if you have customized it. You
can then delete the old copy of MySQL.

You might want to rename the directory to something more
consistent and easy to use. The examples iin this manual use
SYS:MYSQL to refer to the installation
directory.

Note that MySQL installation on NetWare does not detect if a
version of MySQL is already installed outside the NetWare
release. Therefore, if you have installed the latest MySQL
version from the Web (for example, MySQL 4.1 or later) in
SYS:\MYSQL, you must rename the folder
before upgrading the NetWare server; otherwise, files in
SYS:\MySQL are overwritten by the MySQL
version present in NetWare Support Pack.

At the server console, add a search path for the directory
containing the MySQL NLMs. For example:

SERVER: SEARCH ADD SYS:MYSQL\BIN

Initialize the data directory and the grant tables, if needed,
by executing mysql_install_db at the server
console.

Start the MySQL server using mysqld_safe at
the server console.

To finish the installation, you should also add the following
commands to autoexec.ncf. For example, if
your MySQL installation is in SYS:MYSQL
and you want MySQL to start automatically, you could add these
lines:

It is also necessary to use CHECK TABLE and
REPAIR TABLE instead of
myisamchk, because
myisamchk makes use of external locking.
External locking is known to have problems on NetWare 6.0; the
problem has been eliminated in NetWare 6.5.

mysqld_safe on NetWare provides a screen
presence. When you unload (shut down) the
mysqld_safe NLM, the screen does not by
default go away. Instead, it prompts for user input:

*<NLM has terminated; Press any key to close the screen>*

If you want NetWare to close the screen automatically instead,
use the --autoclose option to
mysqld_safe. For example:

MySQL tar file binary distributions have names
of the form
mysql-VERSION-OS.tar.gz,
where VERSION is a
number (for example, 4.0.17), and
OS indicates the type of operating
system for which the distribution is intended (for example,
pc-linux-i686).

You need the following tools to install a MySQL
tar file binary distribution:

GNU gunzip to uncompress the distribution.

A reasonable tar to unpack the
distribution. GNU tar is known to work.
Some operating systems come with a pre-installed version of
tar that is known to have problems. For
example, Mac OS X tar and Sun
tar are known to have problems with long
filenames. On Mac OS X, you can use the pre-installed
gnutar program. On other systems with a
deficient tar, you should install GNU
tar first.

A more detailed version of the preceding description for
installing a binary distribution follows:

Add a login 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 versions of Unix. They may also
be called adduser and
addgroup.

You might want to call the user and group something else
instead of mysql. If so, substitute the
appropriate name in the following steps.

Pick the directory under which you want to unpack the
distribution, and change location into it. In the following
example, we unpack the distribution under
/usr/local. (The instructions, therefore,
assume that you have permission to create files and
directories in /usr/local. If that
directory is protected, you need to perform the installation
as root.)

shell> cd /usr/local

Obtain a distribution file from one of the sites listed in
Section 2.1.3, “How to Get MySQL”. For a given release, binary
distributions for all platforms are built from the same MySQL
source distribution.

Unpack the distribution, which creates the installation
directory. Then create a symbolic link to that directory:

The tar command creates a directory named
mysql-VERSION-OS.
The ln command makes a symbolic link to
that directory. This lets you refer more easily to the
installation directory as
/usr/local/mysql.

With GNU tar, no separate invocation of
gunzip is necessary. You can replace the
first line with the following alternative command to
uncompress and extract the distribution:

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz

Change location into the installation directory:

shell> cd mysql

You can 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. See
Appendix F, Environment Variables.

scripts

This directory contains the
mysql_install_db script used to
initialize the mysql database
containing the grant tables that store the server access
permissions.

If you have not installed MySQL before, you must create the
MySQL grant tables:

shell> scripts/mysql_install_db --user=mysql

If you run the command as root, you should
use the --user option as shown. The value of
the option should be the name of the login account that you
created in the first step to use for running the server. If
you run the command while logged in as that user, you can omit
the --user option.

Note that for MySQL versions older than 3.22.10,
mysql_install_db left the server running
after creating the grant tables. This is no longer true; you
need to restart the server after performing the remaining
steps in this procedure.

Change the ownership of program binaries to
root and ownership of the data directory to
the user that you run mysqld as. Assuming
that you are located in the installation directory
(/usr/local/mysql), the commands look
like this:

The first command changes the owner attribute of the files to
the root user. The second changes the owner
attribute of the data directory to the
mysql user. The third changes the group
attribute to the mysql group.

If you would like MySQL to start automatically when you boot
your machine, you can copy
support-files/mysql.server to the
location where your system has its startup files. More
information can be found in the
mysql.server script itself, and in
Section 2.9.2.2, “Starting and Stopping MySQL Automatically”.

If you would like to use mysqlaccess and
have the MySQL distribution in some non-standard location, 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, a Broken pipe error
will occur when you run mysqlaccess.

After everything has been unpacked and installed, you should test
your distribution.

You can start the MySQL server with the following command:

shell> bin/mysqld_safe --user=mysql &

For versions of MySQL older than 4.0, substitute
bin/safe_mysqld for
bin/mysqld_safe in the command.

Before you proceed with the source installation, check first to
see whether our binary is available for your platform and whether
it works for you. We put a lot of effort into making sure that our
binaries are built with the best possible options.

MySQL source distributions are provided as compressed
tar archives and have names of the form
mysql-VERSION.tar.gz,
where VERSION is a number like
4.1.13.

You need the following tools to build and install MySQL from
source:

GNU gunzip to uncompress the distribution.

A reasonable tar to unpack the
distribution. GNU tar is known to work.
Some operating systems come with a pre-installed version of
tar that is known to have problems. For
example, Mac OS X tar and Sun
tar are known to have problems with long
filenames. On Mac OS X, you can use the pre-installed
gnutar program. On other systems with a
deficient tar, you should install GNU
tar first.

A working ANSI C++ compiler. gcc 2.95.2 or
later, egcs 1.0.2 or later or egcs
2.91.66, SGI C++, and SunPro C++ are some of the
compilers that are known to work. libg++ is
not needed when using gcc.
gcc 2.7.x has a bug that makes it
impossible to compile some perfectly legal C++ files, such as
sql/sql_base.cc. If you have only
gcc 2.7.x, you must upgrade your
gcc to be able to compile MySQL.
gcc 2.8.1 is also known to have problems on
some platforms, so it should be avoided if a new compiler
exists for the platform.

gcc 2.95.2 or later is recommended when
compiling MySQL 3.23.x.

A good make program. GNU
make is always recommended and is sometimes
required. If you have problems, we recommend trying GNU
make 3.75 or newer.

If you are using a version of gcc recent enough
to understand the -fno-exceptions option, it is
very important that you use this option.
Otherwise, you may compile a binary that crashes randomly. We also
recommend that you also use -felide-constructors
and -fno-rtti as well. When in doubt, do the
following:

If configure fails and you are going to
send mail to a MySQL mailing list to ask for assistance,
please include any lines from
config.log that you think can help
solve the problem. Also include the last couple of lines of
output from configure. To file a bug
report, please use the instructions at
Section 1.8, “How to Report Bugs or Problems”.

If you have not installed MySQL before, you must create the
MySQL grant tables:

shell> bin/mysql_install_db --user=mysql

If you run the command as root, you
should use the --user option as shown. The
value of the option should be the name of the login account
that you created in the first step to use for running the
server. If you run the command while logged in as that user,
you can omit the --user option.

Note that for MySQL versions older than 3.22.10,
mysql_install_db left the server running
after creating the grant tables. This is no longer true; you
need to restart the server after performing the remaining
steps in this procedure.

Change the ownership of program binaries to
root and ownership of the data directory
to the user that you run mysqld as.
Assuming that you are in the installation directory
(/usr/local/mysql), the commands look
like this:

The first command changes the owner attribute of the files
to the root user. The second changes the
owner attribute of the data directory to the
mysql user. The third changes the group
attribute to the mysql group.

2.8.2. Typical configure Options

The configure script gives you a great deal
of control over how you configure a MySQL source distribution.
Typically you do this using options on the
configure command line. You can also affect
configure using certain environment
variables. See Appendix F, Environment Variables. For a
list of options supported by configure, run
this command:

shell> ./configure --help

Some of the more commonly used configure
options are described here:

To compile just the MySQL client libraries and client
programs and not the server, use the
--without-server option:

shell> ./configure --without-server

If you do not have a C++ compiler, mysql
cannot be compiled (it is the one client program that
requires C++). In this case, you can remove the code in
configure that tests for the C++ compiler
and then run ./configure with the
--without-server option. The compile step
should still try to build mysql, but you
can ignore any warnings about mysql.cc.
(If make stops, try make
-k to tell it to continue with the rest of the
build even if errors occur.)

If you want to build the embedded MySQL library
(libmysqld.a) you should use the
--with-embedded-server option.

If you do not want your log files and database directories
located under /usr/local/var, use a
configure command something like one of
these:

The first command changes the installation prefix so that
everything is installed under
/usr/local/mysql rather than the
default of /usr/local. The second
command preserves the default installation prefix, but
overrides the default location for database directories
(normally /usr/local/var) and changes
it to /usr/local/mysql/data. After you
have compiled MySQL, you can change these options with
option files. See Section 4.3.2, “Using Option Files”.

If you are using Unix and you want the MySQL socket located
somewhere other than the default location (normally in the
directory /tmp or
/var/run), use a
configure command like this:

If you want to compile statically linked programs (for
example, to make a binary distribution, to get more speed,
or to work around problems with some Red Hat Linux
distributions), run configure like this:

If you are using gcc and do not have
libg++ or libstdc++
installed, you can tell configure to use
gcc as your C++ compiler:

shell> CC=gcc CXX=gcc ./configure

When you use gcc as your C++ compiler, it
does not attempt to link in libg++ or
libstdc++. This may be a good idea to do
even if you have these libraries installed, because some
versions of them have caused strange problems for MySQL
users in the past.

The following list indicates some compilers and environment
variable settings that are commonly used with each one.

If the build fails and produces errors about your compiler
or linker not being able to create the shared library
libmysqlclient.so.N
(where N is a version number),
you can work around this problem by giving the
--disable-shared option to
configure. In this case,
configure does not build a shared
libmysqlclient.so.N
library.

By default, MySQL uses the latin1
(cp1252) character set. To change the default set, use the
--with-charset option:

As of MySQL 4.1.1, the default collation may also be
specified. MySQL uses the
latin1_swedish_ci collation. To change
this, use the --with-collation option:

shell> ./configure --with-collation=COLLATION

To change both the character set and the collation, use both
the --with-charset and
--with-collation options. The collation
must be a legal collation for the character set. (Use the
SHOW COLLATION statement to determine
which collations are available for each character set.)

If you want to convert characters between the server and the
client, you should take a look at the SET CHARACTER
SET statement. See Section 13.5.3, “SET Syntax”.

Warning: If you change
character sets after having created any tables, you have to
run myisamchk -r -q
--set-character-set=charset
on every table. Your indexes may be sorted incorrectly
otherwise. (This can happen if you install MySQL, create
some tables, and then reconfigure MySQL to use a different
character set and reinstall it.)

With the configure option
--with-extra-charsets=LIST,
you can define which additional character sets should be
compiled into the server. LIST is
either a list of character set names separated by spaces,
complex to include all character sets
that cannot be dynamically loaded, or all
to include all character sets into the binaries.

If your client programs are using threads, you also must
compile a thread-safe version of the MySQL client library
with the --enable-thread-safe-client
configure option. This creates a
libmysqlclient_r library with which you
should link your threaded applications. See
Section 18.2.15, “How to Make a Threaded Client”.

It is now possible to build MySQL with big table support
using the --with-big-tables option,
beginning with the following MySQL versions:

4.0 series: 4.0.25

4.1 series: 4.1.11

This option causes the variables used to keep table row
counts to be stored using unsigned long
long rather than unsigned long.
What this does is to allow tables to hold up to
approximately 1.844E+19
((232)2)
rows rather than 232 (~4.295E+09)
rows. Previously it was necessary to pass
-DBIG_TABLES to the compiler manually in
order to enable this feature.

2.8.3. Installing from the Development Source Tree

Caution: You should read this
section only if you are interested in helping us test new code.
If you just want to get MySQL up and running on your system, you
should use a standard release distribution (either a binary or
source distribution).

To obtain our most recent development source tree, use these
instructions:

Make sure gcc and
make have been installed under
Cygwin. You can test this by issuing which
gcc and which make
commands. If either one is not installed, run Cygwin's
package manager, select gcc,
make, or both, and install them.

Under Cygwin, perform these steps:

shell> sh bk-client.shar
shell> cd bk_client-1.1

Then edit the Makefile and change
the line that reads $(CC) $(CFLAGS) -o sfio -lz
sfio.c to this:

$(CC) $(CFLAGS) -o sfio sfio.c -lz

Now run the make command and set the
path:

shell> make all
shell> PATH=$PWD:$PATH

After the BitKeeper free client has been installed, first go
to the directory you want to work from, and then use one of
the following commands to make a local copy of the MySQL
version branch of your choice:

To copy the 3.23 branch, use this command:

shell> sfioball -r+ bk://mysql.bkbits.net/mysql-3.23 mysql-3.23

To copy the 4.0 branch, use this command:

shell> sfioball -r+ bk://mysql.bkbits.net/mysql-4.0 mysql-4.0

To copy the 4.1 branch, use this command:

shell> sfioball -r+ bk://mysql.bkbits.net/mysql-4.1 mysql-4.1

Normally, you do not have to build the documentation
yourself, because we provide it in various formats on
http://dev.mysql.com/doc/. The formats you can download
there (HTML, PDF, and so forth) are built on a daily basis,
so you gain little by creating them yourself from the
DocBook XML base format in the mysqldoc
tree. If you would like to copy the documentation
repository, use the following command:

shell> sfioball -r+ bk://mysql.bkbits.net/mysqldoc mysqldoc

In the preceding examples, the source tree is set up in the
mysql-3.23/,
mysql-4.0/, or
mysql-4.1/ subdirectory of your current
directory.

The initial download of the source tree may take a while,
depending on the speed of your connection. Please be
patient.

To update the local copy of a repository, use this command
(the example is for updating the 4.1 branch):

shell> update bk://mysql.bkbits.net/mysql-4.1

You need GNU make,
autoconf 2.58 (or newer),
automake 1.8, libtool
1.5, and m4 to run the next set of
commands. Even though many operating systems come with their
own implementation of make, chances are
high that the compilation fails with strange error messages.
Therefore, it is highly recommended that you use GNU
make (sometimes named
gmake) instead.

Fortunately, a large number of operating systems ship with
the GNU toolchain preinstalled or supply installable
packages of these. They can also be downloaded from the
following locations:

If you are trying to configure MySQL 4.1, you also need GNU
bison 1.75 or later. Older versions of
bison may report this error:

sql_yacc.yy:#####: fatal error: maximum table size (32767) exceeded

Note: The maximum table size is not actually exceeded; the
error is caused by bugs in older versions of
bison.

Versions of MySQL before version 4.1 may also compile with
other yacc implementations (for example,
BSD yacc 91.7.30). For later versions,
GNU bison is required.

The following example shows the typical commands required to
configure a source tree. The first cd
command changes location into the top-level directory of the
tree; replace mysql-5.0 with the
appropriate directory name.

The command lines that change directory into the
innobase and
bdb/dist directories are used to
configure the InnoDB and Berkeley DB
(BDB) storage engines. You can omit these
command lines if you to not require
InnoDB or BDB support.

If you get some strange errors during this stage, verify
that you really have libtool installed.

A collection of our standard configuration scripts is
located in the BUILD subdirectory. You
may find it more convenient to use the
BUILD/compile-pentium-debug script than
the preceding set of shell commands. To compile on a
different architecture, modify the script by removing flags
that are Pentium-specific.

When the build is done, run make install.
Be careful with this on a production machine; the command
may overwrite your live release installation. If you have
another installation of MySQL, we recommend that you run
./configure with different values for the
--prefix, --with-tcp-port,
and --unix-socket-path options than those
used for your production server.

If you have gotten to the make stage and
the distribution does not compile, please report it in our
bugs database at http://bugs.mysql.com/. If
you have installed the latest versions of the required GNU
tools, and they crash trying to process our configuration
files, please report that also. However, if you execute
aclocal and get a command not
found error or a similar problem, do not report
it. Instead, make sure that all the necessary tools are
installed and that your PATH variable is
set correctly so that your shell can find them.

After the initial copying of the repository
(sfioball) to obtain the source tree, you
should update the repository (update)
periodically to get updates.

You can examine the change history for the tree with all the
diffs by viewing the BK/ChangeLog file
in the source tree and looking at the
ChangeSet descriptions listed there. To
examine a particular changeset, you would have to use the
sfioball command to extract two
particular revisions of the source tree, and then use an
external diff command to compare them. If
you see diffs or code that you have a question about, do not
hesitate to send email to the MySQL
internals mailing list. See
Section 1.7.1, “MySQL Mailing Lists”. If you think you have a
better idea on how to do something, send an email message to
the same address with a patch.

The BitKeeper free client is shipped with its source code.
The only documentation available for the free client is the
source code itself.

2.8.4. Dealing with Problems Compiling MySQL

All MySQL programs compile cleanly for us with no warnings on
Solaris or Linux using gcc. On other systems,
warnings may occur due to differences in system include files.
See Section 2.8.5, “MIT-pthreads Notes”, for warnings that may occur
when using MIT-pthreads. For other problems, check the following
list.

The solution to many problems involves reconfiguring. If you do
need to reconfigure, take note of the following:

If configure is run after it has
previously been run, it may use information that was
gathered during its previous invocation. This information is
stored in config.cache. When
configure starts up, it looks for that
file and reads its contents if it exists, on the assumption
that the information is still correct. That assumption is
invalid when you reconfigure.

Each time you run configure, you must run
make again to recompile. However, you may
want to remove old object files from previous builds first
because they were compiled using different configuration
options.

To prevent old configuration information or object files from
being used, run these commands before re-running
configure:

shell> rm config.cache
shell> make clean

Alternatively, you can run make distclean.

The following list describes some of the problems when compiling
MySQL that have been found to occur most often:

If you get errors such as the ones shown here when compiling
sql_yacc.cc, you probably have run out
of memory or swap space:

The problem is that gcc requires a huge
amount of memory to compile sql_yacc.cc
with inline functions. Try running
configure with the
--with-low-memory option:

shell> ./configure --with-low-memory

This option causes -fno-inline to be added
to the compile line if you are using gcc
and -O0 if you are using something else.
You should try the --with-low-memory option
even if you have so much memory and swap space that you
think you cannot possibly have run out. This problem has
been observed to occur even on systems with generous
hardware configurations and the
--with-low-memory option usually fixes it.

By default, configure picks
c++ as the compiler name and GNU
c++ links with -lg++. If
you are using gcc, that behavior can
cause problems during configuration such as this:

You might also observe problems during compilation related
to g++, libg++, or
libstdc++.

One cause of these problems is that you may not have
g++, or you may have
g++ but not libg++, or
libstdc++. Take a look at the
config.log file. It should contain the
exact reason why your C++ compiler did not work. To work
around these problems, you can use gcc as
your C++ compiler. Try setting the environment variable
CXX to "gcc -O3". For
example:

shell> CXX="gcc -O3" ./configure

This works because gcc compiles C++
sources as well as g++ does, but does not
link in libg++ or
libstdc++ by default.

Another way to fix these problems is to install
g++, libg++, and
libstdc++. We would, however, like to
recommend that you not use libg++ or
libstdc++ with MySQL because this only
increases the binary size of mysqld
without giving you any benefits. Some versions of these
libraries have also caused strange problems for MySQL users
in the past.

Using gcc as the C++ compiler is also
required if you want to compile MySQL with RAID
functionality (see Section 13.1.5, “CREATE TABLE Syntax”, for more
info on RAID table type) and you are using GNU
gcc version 3 and above. If you get
errors like those following during the linking stage when
you configure MySQL to compile with the option
--with-raid, try to use
gcc as your C++ compiler by defining the
CXX environment variable:

If your compilation fails with errors such as any of the
following, you must upgrade your version of
make to GNU make:

making all in mit-pthreads
make: Fatal error in reader: Makefile, line 18:
Badly formed macro assignment

Or:

make: file `Makefile' line 18: Must be a separator (:

Or:

pthread.h: No such file or directory

Solaris and FreeBSD are known to have troublesome
make programs.

GNU make 3.75 is known to work.

If you want to define flags to be used by your C or C++
compilers, do so by adding the flags to the
CFLAGS and CXXFLAGS
environment variables. You can also specify the compiler
names this way using CC and
CXX. For example:

If you get errors such as those shown here when compiling
mysqld, configure did
not correctly detect the type of the last argument to
accept(),
getsockname(), or
getpeername():

cxx: Error: mysqld.cc, line 645: In this statement, the referenced
type of the pointer value ''length'' is ''unsigned long'',
which is not compatible with ''int''.
new_sock = accept(sock, (struct sockaddr *)&cAddr, &length);

To fix this, edit the config.h file
(which is generated by configure). Look
for these lines:

/* Define as the base type of the last arg to accept */
#define SOCKET_SIZE_TYPE XXX

Change XXX to size_t
or int, depending on your operating
system. (Note that you have to do this each time you run
configure because
configure regenerates
config.h.)

The sql_yacc.cc file is generated from
sql_yacc.yy. Normally the build process
does not need to create sql_yacc.cc,
because MySQL comes with an pre-generated copy. However, if
you do need to re-create it, you might encounter this error:

"sql_yacc.yy", line xxx fatal: default action causes potential...

This is a sign that your version of yacc
is deficient. You probably need to install
bison (the GNU version of
yacc) and use that instead.

On Debian Linux 3.0, you need to install
gawk instead of the default
mawk if you want to compile MySQL 4.1 or
higher with Berkeley DB support.

If you need to debug mysqld or a MySQL
client, run configure with the
--with-debug option, and then recompile and
link your clients with the new client library. See
Section E.2, “Debugging a MySQL Client”.

If you get a compilation error on Linux (for example, SuSE
Linux 8.1 or Red Hat Linux 7.3) similar to the following
one:

By default, the configure script attempts
to determine the correct number of arguments by using
g++ the GNU C++ compiler. This test
yields wrong results if g++ is not
installed. There are two ways to work around this problem:

Make sure that the GNU C++ g++ is
installed. On some Linux distributions, the required
package is called gpp; on others, it
is named gcc-c++.

Use gcc as your C++ compiler by
setting the CXX environment variable
to gcc:

export CXX="gcc"

Please note that you need to run
configure again afterward.

2.8.5. MIT-pthreads Notes

This section describes some of the issues involved in using
MIT-pthreads.

After downloading, extract this source archive into the top
level of the MySQL source directory. It creates a new
subdirectory named mit-pthreads.

On most systems, you can force MIT-pthreads to be used by
running configure with the
--with-mit-threads option:

shell> ./configure --with-mit-threads

Building in a non-source directory is not supported when
using MIT-pthreads because we want to minimize our changes
to this code.

The checks that determine whether to use MIT-pthreads occur
only during the part of the configuration process that deals
with the server code. If you have configured the
distribution using --without-server to
build only the client code, clients do not know whether
MIT-pthreads is being used and use Unix socket connections
by default. Because Unix socket files do not work under
MIT-pthreads on some platforms, this means you need to use
-h or --host when you run
client programs.

When MySQL is compiled using MIT-pthreads, system locking is
disabled by default for performance reasons. You can tell
the server to use system locking with the
--external-locking option. This is needed
only if you want to be able to run two MySQL servers against
the same data files, which is not recommended.

Sometimes the pthread bind() command
fails to bind to a socket without any error message (at
least on Solaris). The result is that all connections to the
server fail. For example:

The solution to this is to kill the
mysqld server and restart it. This has
happened to us only when we forced the server to shut down
and then restarted it immediately.

With MIT-pthreads, the sleep() system
call is not interruptible with SIGINT
(break). This is only noticeable when you run
mysqladmin --sleep. You must wait for the
sleep() call to terminate before the
interrupt is served and the process stops.

When linking, you may receive warning messages like these
(at least on Solaris); they can be ignored:

2.8.6. Installing MySQL from Source on Windows

These instructions describe how to build MySQL binaries from
source for versions 4.1 and above on Windows. Instructions are
provided for building binaries from a standard source
distribution or from the BitKeeper tree that contains the latest
development source.

Note: The instructions in this
document are strictly for users who want to test MySQL on
Windows from the latest source distribution or from the
BitKeeper tree. For production use, MySQL AB does not advise
using a MySQL server built by yourself from source. Normally, it
is best to use precompiled binary distributions of MySQL that
are built specifically for optimal performance on Windows by
MySQL AB. Instructions for installing a binary distributions are
available at Section 2.3, “Installing MySQL on Windows”.

To build MySQL on Windows from source, you need the following
compiler and resources available on your Windows system:

You will also need a MySQL source distribution for Windows.
There are two ways you can get a source distribution for MySQL
version 4.1 and above:

Obtain a source distribution packaged by MySQL AB for the
particular version of MySQL in which you are interested.
Prepackaged source distributions are available for released
versions of MySQL and can be obtained from
http://dev.mysql.com/downloads/.

You can package a source distribution yourself from the
latest BitKeeper developer source tree. If you plan to do
this, you must create the package on a Unix system and then
transfer it to your Windows system. (The reason for this is
that some of the configuration and build steps require tools
that work only on Unix.) The BitKeeper approach thus
requires:

If you find something not working as expected, or you have
suggestions about ways to improve the current build process on
Windows, please send a message to the win32
mailing list. See Section 1.7.1, “MySQL Mailing Lists”.

2.8.6.1. Building MySQL Using VC++

Note: VC++ workspace files
for MySQL 4.1 and above are compatible with Microsoft Visual
Studio 2003 editions and tested by MySQL AB staff before each
release.

Follow this procedure to build MySQL:

Create a work directory (for example,
C:\workdir).

Unpack the source distribution in the aforementioned
directory using WinZip or other Windows
tool that can read .zip files.

Start Visual Studio.

In the File menu, select
Open Workspace.

Open the mysql.dsw workspace you find
in the work directory.

From the Build menu, select
the Set Active Configuration
menu.

Click over the screen selecting mysqld -
Win32 Debug and click
OK.

Press F7 to begin the build of the debug
server, libraries, and some client applications.

Compile the release versions that you want in the same
way.

Debug versions of the programs and libraries are placed in
the client_debug and
lib_debug directories. Release
versions of the programs and libraries are placed in the
client_release and
lib_release directories. Note that if
you want to build both debug and release versions, you can
select the Build All option
from the Build menu.

Test the server. The server built using the preceding
instructions expects that the MySQL base directory and
data directory are C:\mysql and
C:\mysql\data by default. If you want
to test your server using the source tree root directory
and its data directory as the base directory and data
directory, you need to tell the server their pathnames.
You can either do this on the command line with the
--basedir and --datadir
options, or place appropriate options in an option file
(the my.ini file in your Windows
directory or C:\my.cnf). If you have
an existing data directory elsewhere that you want to use,
you can specify its pathname instead.

Start your server from the
client_release or
client_debug directory, depending on
which server you want to use. The general server startup
instructions are in
Section 2.3, “Installing MySQL on Windows”. You will need to
adapt the instructions appropriately if you want to use a
different base directory or data directory.

When the server is running in standalone fashion or as a
service based on your configuration, try to connect to it
from the mysql interactive command-line
utility that exists in your
client_release or
client_debug directory.

When you are satisfied that the programs you have built are
working correctly, stop the server. Then install MySQL as
follows:

Create the directories where you want to install MySQL.
For example, to install into
C:\mysql, use these commands:

2.8.6.2. Creating a Windows Source Package from the Latest Development Source

To create a Windows source package from the current BitKeeper
source tree, use the following instructions. Please note that
this procedure must be performed on a system running a Unix or
Unix-like operating system. For example, the procedure is
known to work well on Linux.

2.9. Post-Installation Setup and Testing

After installing MySQL, there are some issues you should address.
For example, on Unix, you should initialize the data directory and
create the MySQL grant tables. On all platforms, an important
security concern is that the initial accounts in the grant tables
have no passwords. You should assign passwords to prevent
unauthorized access to the MySQL server. For MySQL 4.1.3 and up,
you can create time zone tables to enable recognition of named
time zones. (Currently, these tables can be populated only on
Unix. This problem will be addressed soon for Windows.)

2.9.1. Windows Post-Installation Procedures

On Windows, the data directory and the grant tables do not have
to be created. MySQL Windows distributions include the grant
tables with a set of preinitialized accounts in the
mysql database under the data directory. You
do not run the mysql_install_db script that
is used on Unix. However, if you did not install MySQL using the
Windows Installation Wizard, you should assign passwords to the
accounts. See
Section 2.3.4.1, “Introduction”. The
procedure for this is given in
Section 2.9.3, “Securing the Initial MySQL Accounts”.

Before setting up passwords, you might want to try running some
client programs to make sure that you can connect to the server
and that it is operating properly. Make sure that the server is
running (see Section 2.3.10, “Starting the Server for the First Time”), and
then issue the following commands to verify that you can
retrieve information from the server. The output should be
similar to what is shown here:

2.9.2. Unix Post-Installation Procedures

After installing MySQL on Unix, you need to initialize the grant
tables, start the server, and make sure that the server works
satisfactorily. You may also wish to arrange for the server to
be started and stopped automatically when your system starts and
stops. You should also assign passwords to the accounts in the
grant tables.

On Unix, the grant tables are set up by the
mysql_install_db program. For some
installation methods, this program is run for you automatically:

If you install MySQL on Linux using RPM distributions, the
server RPM runs mysql_install_db.

If you install MySQL on Mac OS X using a PKG distribution,
the installer runs mysql_install_db.

Otherwise, you will need to run
mysql_install_db yourself.

The following procedure describes how to initialize the grant
tables (if that has not previously been done) and then start the
server. It also suggests some commands that you can use to test
whether the server is accessible and working properly. For
information about starting and stopping the server
automatically, see Section 2.9.2.2, “Starting and Stopping MySQL Automatically”.

In the examples shown here, the server runs under the user ID of
the mysql login account. This assumes that
such an account exists. Either create the account if it does not
exist, or substitute the name of a different existing login
account that you plan to use for running the server.

BASEDIR is likely to be something
like /usr/local/mysql or
/usr/local. The following steps assume
that you are located in this directory.

If necessary, run the mysql_install_db
program to set up the initial MySQL grant tables containing
the privileges that determine how users are allowed to
connect to the server. You will need to do this if you used
a distribution type that does not run the program for you.

Typically, mysql_install_db needs to be
run only the first time you install MySQL, so you can skip
this step if you are upgrading an existing installation,
However, mysql_install_db does not
overwrite any existing privilege tables, so it should be
safe to run in any circumstances.

To initialize the grant tables, use one of the following
commands, depending on whether
mysql_install_db is located in the
bin or scripts
directory:

The mysql_install_db script creates the
data directory, the mysql database that
holds all database privileges, and the
test database that you can use to test
MySQL. The script also creates privilege table entries for
root accounts and anonymous-user
accounts. The accounts have no passwords initially. A
description of their initial privileges is given in
Section 2.9.3, “Securing the Initial MySQL Accounts”. Briefly, these
privileges allow the MySQL root user to
do anything, and allow anybody to create or use databases
with a name of test or starting with
test_.

It is important to make sure that the database directories
and files are owned by the mysql login
account so that the server has read and write access to them
when you run it later. To ensure this, the
--user option should be used as shown if
you run mysql_install_db as
root. Otherwise, you should execute the
script while logged in as mysql, in which
case you can omit the --user option from
the command.

mysql_install_db creates several tables
in the mysql database:
user, db,
host, tables_priv,
columns_priv, func,
and possibly others depending on your version of MySQL.

If you do not want to have the test
database, you can remove it with mysqladmin -u root
drop test after starting the server.

There are some alternatives to running the
mysql_install_db script as it is provided
in the MySQL distribution:

If you want the initial privileges to be different from
the standard defaults, you can modify
mysql_install_db before you run it.
However, a preferable technique is to use
GRANT and REVOKE
to change the privileges after the grant tables have
been set up. In other words, you can run
mysql_install_db, and then use
mysql -u root mysql to connect to the
server as the MySQL root user so that
you can issue the GRANT and
REVOKE statements.

If you want to install MySQL on several machines with
the same privileges, you can put the
GRANT and REVOKE
statements in a file and execute the file as a script
using mysql after running
mysql_install_db. For example:

By doing this, you can avoid having to issue the
statements manually on each machine.

It is possible to re-create the grant tables completely
after they have previously been created. You might want
to do this if you are just learning how to use
GRANT and REVOKE
and have made so many modifications after running
mysql_install_db that you want to
wipe out the tables and start over.

To re-create the grant tables, remove all the
.frm, .MYI,
and .MYD files in the directory
containing the mysql database. (This
is the directory named mysql under
the data directory, which is listed as the
datadir value when you run
mysqld --help.) Then run the
mysql_install_db script again.

Note: For MySQL
versions older than 3.22.10, you should not delete the
.frm files. If you accidentally do
this, you should copy them back into the
mysql directory from your MySQL
distribution before running
mysql_install_db.

You can start mysqld manually using
the --skip-grant-tables option and add
the privilege information yourself using
mysql:

From mysql, manually execute the SQL
commands contained in
mysql_install_db. Make sure that you
run mysqladmin flush-privileges or
mysqladmin reload afterward to tell
the server to reload the grant tables.

Note that by not using
mysql_install_db, you not only have
to populate the grant tables manually, you also have to
create them first.

Start the MySQL server:

shell> bin/mysqld_safe --user=mysql &

For versions of MySQL older than 4.0, substitute
bin/safe_mysqld for
bin/mysqld_safe in this command.

It is important that the MySQL server be run using an
unprivileged (non-root) login account. To
ensure this, the --user option should be
used as shown if you run mysql_safe as
root. Otherwise, you should execute the
script while logged in as mysql, in which
case you can omit the --user option from
the command.

There is a benchmark suite in the
sql-bench directory (under the MySQL
installation directory) that you can use to compare how
MySQL performs on different platforms. The benchmark suite
is written in Perl. It uses the Perl DBI
module to provide a database-independent interface to the
various databases, and some other additional Perl modules
are required to run the benchmark suite. You must have the
following modules installed:

The sql-bench/Results directory
contains the results from many runs against different
databases and platforms. To run all tests, execute these
commands:

shell> cd sql-bench
shell> perl run-all-tests

If you do not have the sql-bench
directory, you probably installed MySQL using RPM files
other than the source RPM. (The source RPM includes the
sql-bench benchmark directory.) In this
case, you must first install the benchmark suite before you
can use it. Beginning with MySQL 3.22, there are separate
benchmark RPM files named
mysql-bench-VERSION-i386.rpm
that contain benchmark code and data.

If you have a source distribution, there are also tests in
its tests subdirectory that you can
run. For example, to run
auto_increment.tst, execute this
command from the top-level directory of your source
distribution:

shell> mysql -vvf test < ./tests/auto_increment.tst

The expected result of the test can be found in the
./tests/auto_increment.res file.

2.9.2.1. Problems Running mysql_install_db

The purpose of the mysql_install_db script
is to generate new MySQL privilege tables. It does not
overwrite existing MySQL privilege tables, and it does not
affect any other data.

If you want to re-create your privilege tables, first stop the
mysqld server if it is running. Then rename
the mysql directory under the data
directory to save it, and then run
mysql_install_db. For example:

This section lists problems you might encounter when you run
mysql_install_db:

mysql_install_db
does not install the grant tables

You may find that mysql_install_db
fails to install the grant tables and terminates after
displaying the following messages:

Starting mysqld daemon with databases from XXXXXX
mysqld ended

In this case, you should examine the error log file very
carefully. The log should be located in the directory
XXXXXX named by the error message,
and should indicate why mysqld did not
start. If you do not understand what happened, include the
log when you post a bug report. See
Section 1.8, “How to Report Bugs or Problems”.

There is a mysqld
process running

This indicates that the server is running, in which case
the grant tables have probably been created. If so, you do
not have to run mysql_install_db at all
because it need be run only once (when you install MySQL
the first time).

Installing a second
mysqld server does not work when one
server is running

This can happen when you have an existing MySQL
installation, but want to put a new installation in a
different location. For example, you might have a
production installation, but you want to create a second
installation for testing purposes. Generally the problem
that occurs when you try to run a second server is that it
tries to use a network interface that is in use by the
first server. In this case, you should see one of the
following error messages:

2.9.2.2. Starting and Stopping MySQL Automatically

Generally, you start the mysqld server in
one of these ways:

By invoking mysqld directly. This works
on any platform.

By running the MySQL server as a Windows service. This can
be done on versions of Windows that support services (such
as NT, 2000, XP, and 2003). The service can be set to
start the server automatically when Windows starts, or as
a manual service that you start on request. For
instructions, see Section 2.3.12, “Starting MySQL as a Windows Service”.

By invoking mysql.server. This script
is used primarily at system startup and shutdown on
systems that use System V-style run directories, where it
usually is installed under the name
mysql. The
mysql.server script starts the server
by invoking mysqld_safe. See
Section 5.1.4, “mysql.server — MySQL Server Startup Script”.

On Mac OS X, you can install a separate MySQL Startup Item
package to enable the automatic startup of MySQL on system
startup. The Startup Item starts the server by invoking
mysql.server. See
Section 2.5, “Installing MySQL on Mac OS X”, for details.

The mysql.server and
mysqld_safe scripts and the Mac OS X
Startup Item can be used to start the server manually, or
automatically at system startup time.
mysql.server and the Startup Item also can
be used to stop the server.

To start or stop the server manually using the
mysql.server script, invoke it with
start or stop arguments:

shell> mysql.server start
shell> mysql.server stop

Before mysql.server starts the server, it
changes location to the MySQL installation directory, and then
invokes mysqld_safe. If you want the server
to run as some specific user, add an appropriate
user option to the
[mysqld] group of the
/etc/my.cnf option file, as shown later
in this section. (It is possible that you will need to edit
mysql.server if you've installed a binary
distribution of MySQL in a non-standard location. Modify it to
cd into the proper directory before it runs
mysqld_safe. If you do this, your modified
version of mysql.server may be overwritten
if you upgrade MySQL in the future, so you should make a copy
of your edited version that you can reinstall.)

mysql.server stop brings down the server by
sending a signal to it. You can also stop the server manually
by executing mysqladmin shutdown.

To start and stop MySQL automatically on your server, you need
to add start and stop commands to the appropriate places in
your /etc/rc* files.

If you use the Linux server RPM package
(MySQL-server-VERSION.rpm),
the mysql.server script is installed in the
/etc/init.d directory with the name
mysql. You need not install it manually.
See Section 2.4, “Installing MySQL on Linux”, for more information on the
Linux RPM packages.

Some vendors provide RPM packages that install a startup
script under a different name such as
mysqld.

If you install MySQL from a source distribution or using a
binary distribution format that does not install
mysql.server automatically, you can install
it manually. The script can be found in the
support-files directory under the MySQL
installation directory or in a MySQL source tree.

To install mysql.server manually, copy it
to the /etc/init.d directory with the
name mysql, and then make it executable. Do
this by changing location into the appropriate directory where
mysql.server is located and executing these
commands:

Older Red Hat systems use the
/etc/rc.d/init.d directory rather than
/etc/init.d. Adjust the preceding
commands accordingly. Alternatively, first create
/etc/init.d as a symbolic link that
points to /etc/rc.d/init.d:

shell> cd /etc
shell> ln -s rc.d/init.d .

After installing the script, the commands needed to activate
it to run at system startup depend on your operating system.
On Linux, you can use chkconfig:

shell> chkconfig --add mysql

On some Linux systems, the following command also seems to be
necessary to fully enable the mysql script:

shell> chkconfig --level 345 mysql on

On FreeBSD, startup scripts generally should go in
/usr/local/etc/rc.d/. The
rc(8) manual page states that scripts in
this directory are executed only if their basename matches the
*.sh shell filename pattern. Any other
files or directories present within the directory are silently
ignored. In other words, on FreeBSD, you should install the
mysql.server script as
/usr/local/etc/rc.d/mysql.server.sh to
enable automatic startup.

As an alternative to the preceding setup, some operating
systems also use /etc/rc.local or
/etc/init.d/boot.local to start
additional services on startup. To start up MySQL using this
method, you could append a command like the one following to
the appropriate startup file:

/bin/sh -c 'cd /usr/local/mysql; ./bin/mysqld_safe --user=mysql &'

For other systems, consult your operating system documentation
to see how to install startup scripts.

You can add options for mysql.server in a
global /etc/my.cnf file. A typical
/etc/my.cnf file might look like this:

The mysql.server script understands the
following options: basedir,
datadir, and pid-file.
If specified, they must be placed in an
option file, not on the command line.
mysql.server understands only
start and stop as
command-line arguments.

The following table shows which option groups the server and
each startup script read from option files:

Script

Option Groups

mysqld

[mysqld], [server],
[mysqld-major-version]

mysql.server

[mysqld], [mysql.server]

mysqld_safe

[mysqld], [server],
[mysqld_safe]

[mysqld-major-version] means that groups
with names like [mysqld-4.0],
[mysqld-4.1], and
[mysqld-5.0] are read by servers having
versions 4.0.x, 4.1.x, 5.0.x, and so forth. This feature was
added in MySQL 4.0.14. It can be used to specify options that
can be read only by servers within a given release series.

For backward compatibility, mysql.server
also reads the [mysql_server] group and
mysqld_safe also reads the
[safe_mysqld] group. However, you should
update your option files to use the
[mysql.server] and
[mysqld_safe] groups instead when you begin
using MySQL 4.0 or later.

2.9.2.3. Starting and Troubleshooting the MySQL Server

If you have problems starting the server, here are some things
you can try:

Specify any special options needed by the storage engines
you are using.

Make sure that the server knows where to find the data
directory.

Make sure the server can use the data directory. The
ownership and permissions of the data directory and its
contents must be set such that the server can access and
modify them.

Check the error log to see why the server does not start.

Verify that the network interfaces the server wants to use
are available.

Some storage engines have options that control their behavior.
You can create a my.cnf file and set
startup options for the engines you plan to use. If you are
going to use storage engines that support transactional tables
(InnoDB, BDB), be sure
that you have them configured the way you want before starting
the server:

If you are using InnoDB tables, refer
to the InnoDB-specific startup options.
In MySQL 3.23, you must configure
InnoDB explicitly or the server fails
to start. From MySQL 4.0 on, InnoDB
uses default values for its configuration options if you
specify none. See Section 15.4, “InnoDB Configuration”.

When the mysqld server starts, it changes
location to the data directory. This is where it expects to
find databases and where it expects to write log files. On
Unix, the server also writes the pid (process ID) file in the
data directory.

The data directory location is hardwired in when the server is
compiled. This is where the server looks for the data
directory by default. If the data directory is located
somewhere else on your system, the server does not work
properly. You can find out what the default path settings are
by invoking mysqld with the
--verbose and --help
options. (Prior to MySQL 4.1, omit the
--verbose option.)

If the defaults do not match the MySQL installation layout on
your system, you can override them by specifying options on
the command line to mysqld or
mysqld_safe. You can also list the options
in an option file.

To specify the location of the data directory explicitly, use
the --datadir option. However, normally you
can tell mysqld the location of the base
directory under which MySQL is installed and it looks for the
data directory there. You can do this with the
--basedir option.

To check the effect of specifying path options, invoke
mysqld with those options followed by the
--verbose and --help
options. For example, if you change location into the
directory where mysqld is installed, and
then run the following command, it shows the effect of
starting the server with a base directory of
/usr/local:

shell> ./mysqld --basedir=/usr/local --verbose --help

You can specify other options such as
--datadir as well, but note that
--verbose and --help must be
the last options. (Prior to MySQL 4.1, omit the
--verbose option.)

Once you determine the path settings you want, start the
server without --verbose and
--help.

If mysqld is currently running, you can
find out what path settings it is using by executing this
command:

shell> mysqladmin variables

Or:

shell> mysqladmin -h host_name variables

host_name is the name of the MySQL
server host.

If you get Errcode 13 (which means
Permission denied) when starting
mysqld, this means that the access
privileges of the data directory or its contents do not allow
the server access. In this case, you must change the
permissions for the files and directories involved so that the
server has the right to use them. You can also start the
server as root, but this can raise security
issues and thus should be avoided.

On Unix, change location into the data directory and check the
ownership of the data directory and its contents to make sure
the server has access. For example, if the data directory is
/usr/local/mysql/var, use this command:

shell> ls -la /usr/local/mysql/var

If the data directory or its files or subdirectories are not
owned by the account that you use for running the server,
change their ownership to that account:

If the server fails to start up correctly, check the error log
file to see if you can find out why. Log files are located in
the data directory (typically
C:\mysql\data on Windows,
/usr/local/mysql/data for a Unix binary
distribution, and /usr/local/var for a
Unix source distribution). Look in the data directory for
files with names of the form
host_name.err
and
host_name.log,
where host_name is the name of your
server host. (Older servers on Windows use
mysql.err as the error log name.) Then
check the last few lines of these files. On Unix, you can use
tail to display the last few lines:

shell> tail host_name.err
shell> tail host_name.log

The error log contains information that indicates why the
server couldn't start. For example, you might see something
like this in the log:

This means that you did not start mysqld
with the --bdb-no-recover option and Berkeley
DB found something wrong with its own log files when it tried
to recover your databases. To be able to continue, you should
move away the old Berkeley DB log files from the database
directory to some other place, where you can later examine
them. The BDB log files are named in
sequence beginning with log.0000000001,
where the number increases over time.

If you are running mysqld with
BDB table support and
mysqld dumps core at startup, this could be
due to problems with the BDB recovery log.
In this case, you can try starting mysqld
with --bdb-no-recover. If that helps, you
should remove all BDB log files from the
data directory and try starting mysqld
again without the --bdb-no-recover option.

If either of the following errors occur, it means that some
other program (perhaps another mysqld
server) is using the TCP/IP port or Unix socket file that
mysqld is trying to use:

If no other server is running, try to execute the command
telnet your-host-nametcp-ip-port-number. (The
default MySQL port number is 3306.) Then press
Enter a couple of times. If you do not get an
error message like telnet: Unable to connect to
remote host: Connection refused, some other program
is using the TCP/IP port that mysqld is
trying to use. You will need to track down what program this
is and disable it, or else to tell mysqld
to listen on a different port with the --port
option. In this case, you also need to specify the port number
for client programs when connecting to the server via TCP/IP.

Another reason the port might be inaccessible is that you have
a firewall running that blocks connections to it. If so,
modify the firewall settings to allow access to the port.

If the server starts but you cannot connect to it, you should
make sure that you have an entry in
/etc/hosts that looks like this:

127.0.0.1 localhost

This problem occurs only on systems that do not have a working
thread library and for which MySQL must be configured to use
MIT-pthreads.

The grant tables define the initial MySQL user accounts and
their access privileges. These accounts are set up as follows:

Accounts are created with the username
root. These are superuser accounts that
can do anything. The initial root account
passwords are empty, so anyone can connect to the MySQL
server as rootwithout a
password and be granted all privileges.

On Windows, prior to MySQL 4.1.10, two
root accounts are created; one of
these is for connecting from the local host and the
other allows connections from any host. Beginning with
MySQL 4.1.10, the Windows installer creates only one
root account, which can connect from
the local machine only.

On Unix, both root accounts are for
connections from the local host. Connections must be
made from the local host by specifying a hostname of
localhost for one account, or the
actual hostname or IP number for the other.

Two anonymous-user accounts are created, each with an empty
username. The anonymous accounts have no passwords, so
anyone can use them to connect to the MySQL server.

On Windows, one anonymous account is for connections
from the local host. It has all privileges, just like
the root accounts. The other is for
connections from any host and has all privileges for the
test database or other databases with
names that start with test.

On Unix, both anonymous accounts are for connections
from the local host. Connections must be made from the
local host by specifying a hostname of
localhost for one account, or the
actual hostname or IP number for the other. These
accounts have all privileges for the
test database or other databases with
names that start with test_.

As noted, none of the initial accounts have passwords. This
means that your MySQL installation is unprotected until you do
something about it:

If you want to prevent clients from connecting as anonymous
users without a password, you should either assign passwords
to the anonymous accounts or else remove them.

You should assign passwords to the MySQL
root accounts.

The following instructions describe how to set up passwords for
the initial MySQL accounts, first for the anonymous accounts and
then for the root accounts. Replace
newpwd in the examples with the
actual password that you want to use. The instructions also
cover how to remove the anonymous accounts, should you prefer
not to allow anonymous access at all.

You might want to defer setting the passwords until later, so
that you do not need to specify them while you perform
additional setup or testing. However, be sure to set them before
using your installation for any real production work.

To assign passwords to the anonymous accounts, you can use
either SET PASSWORD or
UPDATE. In both cases, be sure to encrypt the
password using the PASSWORD() function.

In the second SET PASSWORD statement, replace
host_name with the name of the server
host. This is the name that is specified in the
Host column of the
non-localhost record for
root in the user table. If
you do not know what hostname this is, issue the following
statement before using SET PASSWORD:

mysql> SELECT Host, User FROM mysql.user;

Look for the record that has root in the
User column and something other than
localhost in the Host
column. Then use that Host value in the
second SET PASSWORD statement.

The other way to assign passwords to the anonymous accounts is
by using UPDATE to modify the
user table directly. Connect to the server as
root and issue an UPDATE
statement that assigns a value to the
Password column of the appropriate
user table records. The procedure is the same
for Windows and Unix. The following UPDATE
statement assigns a password to both anonymous accounts at once:

After you update the passwords in the user
table directly using UPDATE, you must tell
the server to re-read the grant tables with FLUSH
PRIVILEGES. Otherwise, the change goes unnoticed until
you restart the server.

If you prefer to remove the anonymous accounts instead, do so as
follows:

These commands apply both to Windows and to Unix. In the second
command, replace host_name with the
name of the server host. The double quotes around the password
are not always necessary, but you should use them if the
password contains spaces or other characters that are special to
your command interpreter.

If you are using a server from a very old
version of MySQL, the mysqladmin commands to
set the password fail with the message parse error near
'SET password'. The solution to this problem is to
upgrade the server to a newer version of MySQL.

You can also use UPDATE to modify the
user table directly. The following
UPDATE statement assigns a password to both
root accounts at once:

After the passwords have been set, you must supply the
appropriate password whenever you connect to the server. For
example, if you want to use mysqladmin to
shut down the server, you can do so using this command:

2.10. Upgrading MySQL

As a general rule, we recommend that when upgrading from one
release series to another, you should go to the next series rather
than skipping a series. For example, if you currently are running
MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL
4.0 rather than to 4.1 or 5.0.

The following items form a checklist of things you should do
whenever you perform an upgrade:

Read the upgrading section for the release series to which you
are upgrading. Read the change notes as well. These provide
information about new features you can use.

An upgrade may involve changes to the grant tables that are
stored in the mysql database. Occasionally
new columns or tables are added to support new features. To
take advantage of these features, be sure that your grant
tables are up to date. The upgrade procedure is described in
Section 2.10.3, “Upgrading the Grant Tables”.

If you install a MySQL-Max distribution that includes a server
named mysqld-max, and then upgrade later to
a non-Max version of MySQL, mysqld_safe
still attempts to run the old mysqld-max
server. If you perform such an upgrade, you should manually
remove the old mysqld-max server to ensure
that mysqld_safe runs the new
mysqld server.

You can always move the MySQL format files and data files between
different versions on the same architecture as long as you stay
within versions for the same release series of MySQL. The current
production release series is 4.1. If you change the character set
when running MySQL, you must run myisamchk -r -q
--set-character-set=charset
on all MyISAM tables. Otherwise, your indexes
may not be ordered correctly, because changing the character set
may also change the sort order.

Normally you can upgrade MySQL to a newer MySQL version without
having to do any changes to your tables. Please confirm if the
upgrade notes to the particular version you are upgrading to tell
you anything about this. If there would be any incompatibilities
you can use mysqldump to dump your tables
before upgrading. After upgrading, reload the dump file using
mysql or mysqlimport to
re-create your tables.

If you are cautious about using new versions, you can always
rename your old mysqld before installing a
newer one. For example, if you are using MySQL 4.0.18 and want to
upgrade to 4.1.1, rename your current server from
mysqld to mysqld-4.0.18. If
your new mysqld then does something unexpected,
you can simply shut it down and restart with your old
mysqld.

If, after an upgrade, you experience problems with recompiled
client programs, such as Commands out of sync
or unexpected core dumps, you probably have used old header or
library files when compiling your programs. In this case, you
should check the date for your mysql.h file
and libmysqlclient.a library to verify that
they are from the new MySQL distribution. If not, recompile your
programs with the new headers and libraries.

If problems occur, such as that the new mysqld
server does not want to start or that you cannot connect without a
password, verify that you do not have some old
my.cnf file from your previous installation.
You can check this with the --print-defaults
option (for example, mysqld --print-defaults).
If this displays anything other than the program name, you have an
active my.cnf file that affects server or
client operation.

It is a good idea to rebuild and reinstall the Perl
DBD::mysql module whenever you install a new
release of MySQL. The same applies to other MySQL interfaces as
well, such as the PHP mysql and
mysqli extensions or the Python
MySQLdb module.

2.10.1. Upgrading from MySQL 4.0 to 4.1

Note: It is good practice to
back up your data before installing any new version of software.
Although MySQL worked very hard to ensure a high level of
quality, you should protect your data by making a backup. MySQL
generally recommends that you dump and reload your tables from
any previous version to upgrade to 4.1.

In general, you should do the following when upgrading to MySQL
4.1 from 4.0:

Check the items in the change list found later in this
section to see whether any of them might affect your
applications. Note particularly any that are marked
Incompatible change; these
result in incompatibilities with earlier versions of MySQL
and you should consider the implications of these
incompatibilities before you upgrade.
Note particularly the items under “Server
Changes” that related to changes in character set
support.

After upgrading, update the grant tables to obtain the new
longer Password column that is needed for
more secure handling of passwords. The procedure uses
mysql_fix_privilege_tables and is
described in Section 2.10.3, “Upgrading the Grant Tables”. If
you do not do this, MySQL does not use the new more secure
protocol to authenticate. Implications of the
password-handling change for applications are given later in
this section.

The Berkeley DB table handler is updated to DB 4.1 (from
3.2) which has a new log format. If you have to downgrade
back to 4.0 you must use mysqldump to
dump your BDB tables in text format and
delete all log.XXXXXXXXXX files before
you start MySQL 4.0 and reload the data.

Several visible behaviors have changed between MySQL 4.0 and
MySQL 4.1 to fix some critical bugs and make MySQL more
compatible with standard SQL. These changes may affect your
applications.

Some of the 4.1 behaviors can be tested in 4.0 before performing
a full upgrade to 4.1. We have added to later MySQL 4.0 releases
(from 4.0.12 on) a --new startup option for
mysqld. See Section 5.2.1, “mysqld Command-Line Options”.

This option gives you the 4.1 behavior for the most critical
changes. You can also enable these behaviors for a given client
connection with the SET @@new=1 command, or
turn them off if they are on with SET
@@new=0.

If you believe that some of the 4.1 changes affect you, we
recommend that before upgrading to 4.1, you download the latest
MySQL 4.0 version and run it with the --new
option by adding the following to your config file:

[mysqld-4.0]
new

That way you can test the new behaviors in 4.0 to make sure that
your applications work with them. This helps you have a smooth,
painless transition when you perform a full upgrade to 4.1
later. Putting the --new option in the
[mysqld-4.0] option group ensures that you do
not accidentally later run the 4.1 version with the
--new option.

The following lists describe changes that may affect
applications and that you should watch out for when upgrading to
version 4.1.

Server Changes:

The most notable change is that character set support has been
improved. The server supports multiple character sets, and all
tables and non-binary string columns (CHAR,
VARCHAR, and TEXT) have a
character set. See Chapter 10, Character Set Support. Binary string
columns (BINARY,
VARBINARY, and BLOB)
contain strings of bytes and do not have a character set.
Note: This change in character
set support results in the potential for table damage if you do
not upgrade properly, so consider carefully the
incompatibilities noted here.

Incompatible change: There
are conditions under which you should rebuild tables. In
general, to rebuild a table, dump it with
mysqldump and reload the dump file. Some
items in the following list indicate alternatives means for
rebuilding.

If you have created or used InnoDB
tables with TIMESTAMP columns in
MySQL versions 4.1.0 to 4.1.3, you must rebuild those
tables when you upgrade to MySQL 4.1.4 or later. The
storage format in those MySQL versions for
TIMESTAMP columns was incorrect. If
you upgrade from MySQL 4.0 to 4.1.4 or later, no rebuild
of tables with TIMESTAMP columns is
needed.

Starting from MySQL 4.1.3, InnoDB
uses the same character set comparison functions as
MySQL for non-latin1_swedish_ci
character strings that are not
BINARY. This changes the sorting
order of space and characters with a code < ASCII(32)
in those character sets. For
latin1_swedish_ci character strings
and BINARY strings,
InnoDB uses its own pad-spaces-at-end
comparison method, which stays unchanged. Note that
latin1_swedish_ci is the default
collation order for latin1 in 4.0. If
you have an InnoDB table created with
MySQL 4.1.2 or earlier, with an index on a
non-latin1_swedish_ci character set
and collation order column that is not
BINARY (in the case of 4.1.0 and
4.1.1, with any character set and collation), and that
column may contain characters with a code <
ASCII(32), you should do ALTER TABLE
or OPTIMIZE TABLE on it to regenerate
the index, after upgrading to MySQL 4.1.3 or later. You
can also rebuild the table from a dump.

MyISAM tables also have to be rebuilt
or repaired in these cases. You can use
mysqldump to dump them in 4.0 and
then reload them in 4.1. An alternative is to use
OPTIMIZE TABLE after upgrading, but
this must be done before any
updates are made in 4.1.

If you have used column prefix indexes on UTF8 columns
or other multi-byte character set columns in MySQL 4.1.0
to 4.1.5, you must rebuild the tables when you upgrade
to MySQL 4.1.6 or later.

If you have used accent characters (characters with byte
values of 128 to 255) in database names, table names,
constraint names, or column names in versions of MySQL
earlier than 4.1, you cannot upgrade to MySQL 4.1
directly, because 4.1 uses UTF8 to store metadata names.
Use RENAME TABLE to overcome this if
the accent character is in the table name or the
database name, or rebuild the table.

String comparison works according to SQL standard:
Instead of stripping end spaces before comparison, we
now extend the shorter string with spaces. The problem
with this is that now 'a' > 'a\t',
which it was not before. If you have any tables where
you have an indexed CHAR,
VARCHAR or TEXT
column in which the last character in the index may be
less than ASCII(32), you should use
REPAIR TABLE or
mysqlcheck to ensure that the table
is correct.

MyISAM tables now use an improved
checksum algorithm in MySQL 4.1. If you have
MyISAM tables with live checksum
enabled (you used CHECKSUM=1 in
CREATE TABLE or ALTER
TABLE), these tables appear to be corrupted
following an upgrade. Use REPAIR
TABLE to recalculate the checksum for each
such table.

For single-byte character sets, this change makes no
difference. However, if you upgrade to MySQL 4.1 and
configure the server to use a multi-byte character set, the
apparent length of character columns changes. Suppose that a
4.0 table contains a CHAR(8) column used
to store ujis characters. Eight bytes can
store from two to four ujis characters.
If you upgrade to 4.1 and configure the server to use
ujis as its default character set, the
server interprets character column lengths based on the
maximum size of a ujis character, which
is three bytes. The number of three-byte characters that fit
in eight bytes is two. Consequently, if you use
SHOW CREATE TABLE to view the table
definition, MySQL displays CHAR(2). You
can retrieve existing data from the table, but you can only
store new values containing up to two characters. To correct
this issue, use ALTER TABLE to change the
column definition. For example:

ALTER TABLE tbl_name MODIFY col_name CHAR(8);

Warning: Incompatible
change: As of MySQL 4.1.2, handling of the
FLOAT and DOUBLE
floating-point data types is more strict to follow standard
SQL. For example, a data type of
FLOAT(3,1) stores a maximum value of
99.9. Before 4.1.2, the server allowed larger numbers to be
stored. That is, it stored a value such as 100.0 as 100.0.
As of 4.1.2, the server clips 100.0 to the maximum allowable
value of 99.9. If you have tables that were created before
MySQL 4.1.2 and that contain floating-point data not
strictly legal for the column type, you should alter the
data types of those columns. For example:

ALTER TABLE tbl_name MODIFY col_name FLOAT(4,1);

Incompatible change: In
connection with the support for per-connection time zones in
MySQL 4.1.3, the timezone system variable
was renamed to system_time_zone.

Important note: MySQL 4.1
stores table names and column names in
UTF8. If you have table names or column
names that use characters outside of the standard 7-bit
US-ASCII range, you may have to do a
mysqldump of your tables in MySQL 4.0 and
restore them after upgrading to MySQL 4.1. The symptom for
this problem is that you get a table not
found error when trying to access your tables. In
this case, you should be able to downgrade back to MySQL 4.0
and access your data.

Important note: If you
upgrade to MySQL 4.1.1 or higher, it is difficult to
downgrade back to 4.0 or 4.1.0. That is because, for earlier
versions, InnoDB is not aware of multiple
tablespaces.

All tables and non-binary string columns
(CHAR, VARCHAR, and
TEXT) have a character set. See
Chapter 10, Character Set Support. Binary string columns
(BINARY, VARBINARY,
and BLOB) contain strings of bytes and do
not have a character set.

Character set information is displayed by SHOW
CREATE TABLE and mysqldump.
(MySQL versions 4.0.6 and above can read the new dump files;
older versions cannot.) This change should not affect
applications that use only one character set.

If you were using columns with the CHAR
BINARY or VARCHAR BINARY data
types in MySQL 4.0, these were treated as binary strings. To
have them treated as binary strings in MySQL 4.1, you should
convert them to the BINARY and
VARBINARY data types, respectively.

The table definition format used in
.frm files has changed slightly in 4.1.
MySQL 4.0 versions from 4.0.11 on can read the new
.frm format directly, but older
versions cannot. If you need to move tables from 4.1 to a
version earlier than 4.0.11, you should use
mysqldump. See
Section 8.8, “mysqldump — A Database Backup Program”.

Windows servers support connections from local clients using
shared memory if run with the
--shared-memory option. If you are running
multiple servers this way on the same Windows machine, you
should use a different
--shared-memory-base-name option for each
server.

As of MySQL 4.1.10a, the server by default no longer loads
user-defined functions unless they have at least one
auxiliary symbol defined in addition to the main function
symbol. This behavior can be overridden with the
--allow-suspicious-udfs option. See
Section 20.2.4.6, “User-Defined Function Security Precautions”.

Client Changes:

mysqldump has the --opt
and --quote-names options enabled by
default. You can turn these off using
--skip-opt and
--skip-quote-names.

SQL Changes:

Incompatible change: In
MySQL 4.1, string comparison works according to the SQL
standard: Instead of stripping end spaces before comparison,
the shorter string is extended using spaces. This means that
'a' > 'a\t', which it was not
previously. If you have any tables containing an indexed
CHAR, VARCHAR or
TEXT column in which the last character
in the index may be less than ASCII(32),
you should use REPAIR TABLE or
mysqlcheck to ensure that the table is
correct.

When running the server with --new, if you
want to have a TIMESTAMP column returned
as a number (as MySQL 4.0 does by default), you should add
+0 when you retrieve it:

mysql> SELECT ts_col + 0 FROM tbl_name;

Display widths for TIMESTAMP columns are
no longer supported in MySQL 4.1. For example, if you
declare a column as TIMESTAMP(10), the
(10) is ignored.

Incompatible change: Binary
values such as 0xFFDF are assumed to be
strings instead of numbers. This fixes some problems with
character sets where it is convenient to input a string as a
binary value. With this change, you should use
CAST() if you want to compare binary
values numerically as integers:

If you do not use CAST(), a lexical
string comparison is made instead:

mysql> SELECT 0xFEFF < 0xFF;
-> 1

Using binary items in a numeric context or comparing them
using the = operator should work as
before. (The --new option can be used from
4.0.13 on to make a 4.0 server behave as 4.1 in this
respect.)

Incompatible change: Before
MySQL 4.1.1, the statement parser was less strict and its
string-to-date conversion would ignore everything up to the
first digit. As a result, invalid statements such as the
following were accepted:

INSERT INTO t (datetime_col) VALUES ('stuff 2005-02-11 10:17:01');

As of MySQL 4.1.1, the parser is stricter and treats the
string as an invalid date, so the preceding statement
results in a warning.

Incompatible change: In
MySQL 4.1.2, the Type column in the
output from SHOW TABLE STATUS was renamed
to Engine. This affects applications that
identify output columns by name rather than by position.

When using multiple-table DELETE
statements, you should use the alias of the tables from
which you want to delete, not the actual table name. For
example, instead of doing this:

DELETE test FROM test AS t1, test2 WHERE ...

Do this:

DELETE t1 FROM test AS t1, test2 WHERE ...

This corrects a problem that was present in MySQL 4.0.

For functions that produce a DATE,
DATETIME, or TIME
value, the result returned to the client is fixed up to have
a temporal type. For example, in MySQL 4.1, you obtain the
following:

mysql> SELECT CAST('2001-1-1' AS DATETIME);
-> '2001-01-01 00:00:00'

In MySQL 4.0, the result of the stement is different:

mysql> SELECT CAST('2001-1-1' AS DATETIME);
-> '2001-01-01'

DEFAULT values no longer can be specified
for AUTO_INCREMENT columns. (In 4.0, a
DEFAULT value is silently ignored; in
4.1, an error occurs.)

LIMIT no longer accepts negative
arguments. Use some large number (maximum
18446744073709551615) instead of -1.

SERIALIZE is no longer a valid mode value
for the sql_mode variable. You should use
SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE instead. SERIALIZE
is no longer valid for the --sql-mode
option for mysqld, either. Use
--transaction-isolation=SERIALIZABLE
instead.

C API Changes:

Incompatible change: The
mysql_shutdown() C API function has an
extra parameter as of MySQL 4.1.3:
SHUTDOWN-level. You should convert any
mysql_shutdown(X)
call you have in your application to
mysql_shutdown(X,SHUTDOWN_DEFAULT).
Any third-party API that links against the C API library
must be modified to account for this change or it will not
compile.

Some C API calls such as
mysql_real_query() return
1 on error, not -1. You
may have to change some old applications if they use
constructs like this:

The password hashing mechanism changed in 4.1 to provide better
security; this may cause compatibility problems if you have
clients using the client library from 4.0 or earlier. (It is
very likely that you have 4.0 clients in situations where
clients connect from remote hosts that have not yet upgraded to
4.1.) The following list indicates some possible upgrade
strategies. They represent various tradeoffs between the goals
of compatibility with old clients and security.

Only upgrade the client to use 4.1 client libraries (not the
server). No behavior changes (except the return value of
some API calls), but you cannot use any of the new features
provided by the 4.1 client/server protocol, either. (MySQL
4.1 has an extended client/server protocol that offers such
features as prepared statements and multiple result sets.)
See Section 18.2.4, “C API Prepared Statements”.

Upgrade to 4.1 and run the
mysql_fix_privilege_tables script to
widen the Password column in the
user table so that it can hold long
password hashes. However — to provide backward
compatibility allowing pre-4.1 clients to continue
connecting to their short-hash accounts — run the
server with the --old-passwords option.
Eventually, when all your clients are upgraded to 4.1, you
can stop using the --old-passwords server
option. You can also change the passwords for your MySQL
accounts to use the new more secure format. A 4.1
installation using only the improved authentication protocol
is the most secure one.

A new startup option named
innodb_table_locks was added that causes
LOCK TABLE to also acquire InnoDB table
locks. This option is enabled by default. This can cause
deadlocks in applications that use
AUTOCOMMIT=1 and LOCK
TABLES. If you application encounters deadlocks
after upgrading, you may need to add
innodb_table_locks=0 to your
my.cnf file.

A new startup option named
innodb_table_locks was added that causes
LOCK TABLE to also acquire InnoDB table
locks. This option is enabled by default. This can cause
deadlocks in applications that use
AUTOCOMMIT=1 and LOCK
TABLES. If you application encounters deadlocks
after upgrading, you may need to add
innodb_table_locks=0 to your
my.cnf file.

2.10.2. Upgrading from MySQL 3.23 to 4.0

In general, you should do the following when upgrading to MySQL
4.0 from 3.23:

Check the items in the change list found later in this
section to see whether any of them might affect your
applications. Note particularly any that are marked
Incompatible change; these
result in incompatibilities with earlier versions of MySQL.

Edit any MySQL startup scripts or option files so that they
do not use any of the options described as deprecated later
in this section.

Convert your old ISAM tables to
MyISAM format. One way to do this is with
the mysql_convert_table_format script.
(This is a Perl script; it requires that
DBI be installed.) To convert all of the
tables in a given database, use this command:

shell> mysql_convert_table_format database db_name

Note that the above command should be used only if
all tables in the database are
ISAM or MyISAM tables.
To avoid converting tables of other types to
MyISAM, you can explicitly list the names
of the ISAM tables following the database
name on the command line.

Individual tables can be changed to
MyISAM by using the following
ALTER TABLE statement for each table to
be converted:

mysql> ALTER TABLE tbl_name TYPE=MyISAM;

If you are not sure of the table type for a given table, use
this statement:

mysql> SHOW TABLE STATUS LIKE 'tbl_name';

Ensure that you do not have any MySQL clients that use
shared libraries (like the Perl
DBD::mysql module). If you do, you should
recompile them, because the data structures used in
libmysqlclient.so have changed. The
same applies to other MySQL interfaces such as the Python
MySQLdb module.

MySQL 4.0 works even if you do not perform the preceding
actions, but you cannot use the new security privileges in MySQL
4.0 and you may run into problems when upgrading later to MySQL
4.1 or newer. The ISAM file format still
works in MySQL 4.0, but is deprecated and is not compiled in by
default as of MySQL 4.1. MyISAM tables should
be used instead.

Old clients should work with a MySQL 4.0 server without any
problems.

Even if you perform the preceding actions, you can still
downgrade to MySQL 3.23.52 or newer if you run into problems
with the MySQL 4.0 series. In this case, you must use
mysqldump to dump any tables that use
full-text indexes and reload the dump file into the 3.23 server.
This is necessary because 4.0 uses an improved format for
full-text indexing that is not backward-compatible.

The following lists describe changes that may affect
applications and that you should watch out for when upgrading to
version 4.0.

Server Changes:

As of MySQL 4.0.24, the server by default no longer loads
user-defined functions unless they have at least one
auxiliary symbol defined in addition to the main function
symbol. This behavior can be overridden with the
--allow-suspicious-udfs option. See
Section 20.2.4.6, “User-Defined Function Security Precautions”.

In order for these new privileges to work, you must update
the grant tables. The procedure for this is described in
Section 2.10.3, “Upgrading the Grant Tables”. Until you do this,
all accounts have the SHOW DATABASES,
CREATE TEMPORARY TABLES, and
LOCK TABLES privileges.
SUPER and EXECUTE
privileges take their value from PROCESS.
REPLICATION SLAVE and
REPLICATION CLIENT take their values from
FILE.

If you have any scripts that create new MySQL user accounts,
you may want to change them to use the new privileges. If
you are not using GRANT commands in the
scripts, this is a good time to change your scripts to use
GRANT instead of modifying the grant
tables directly.

If you get Access denied errors for new
users in version 4.0.2 and up, you should check whether you
need some of the new grants that you did not need before. In
particular, you need REPLICATION SLAVE
(instead of FILE) for new slave servers.

safe_mysqld has been renamed to
mysqld_safe. For backward compatibility,
binary distributions will for some time include
safe_mysqld as a symlink to
mysqld_safe.

InnoDB support is included by default in
binary distributions. If you build MySQL from source,
InnoDB is configured in by default. If
you do not use InnoDB and want to save
memory when running a server that has
InnoDB support enabled, use the
--skip-innodb server startup option. To
compile MySQL without InnoDB support, run
configure with the
--without-innodb option.

Values for the startup parameters
myisam_max_extra_sort_file_size and
myisam_max_extra_sort_file_size are given
in bytes (prior to 4.0.3,they were given in megabytes).

mysqld has the option
--temp-pool enabled by default because this
gives better performance with some operating systems (most
notably Linux).

The mysqld startup options
--skip-locking and
--enable-locking were renamed to
--skip-external-locking and
--external-locking.

External system locking of
MyISAM/ISAM files is
turned off by default. You can turn this on with
--external-locking. (However, this is never
needed for most users.)

The following startup variables and options were renamed:

Name in 3.23

Name in 4.0 (and above)

myisam_bulk_insert_tree_size

bulk_insert_buffer_size

query_cache_startup_type

query_cache_type

record_buffer

read_buffer_size

record_rnd_buffer

read_rnd_buffer_size

sort_buffer

sort_buffer_size

warnings

log-warnings

--err-log

--log-error (for mysqld_safe)

The startup options record_buffer,
sort_buffer, and
warnings still work in MySQL 4.0 but are
deprecated.

Note: When you use
subtraction between integer values where one is of type
UNSIGNED, the result is unsigned. In
other words, before upgrading to MySQL 4.0, you should check
your application for cases in which you are subtracting a
value from an unsigned entity and want a negative answer or
subtracting an unsigned value from an integer column. You
can disable this behavior by using the
--sql-mode=NO_UNSIGNED_SUBTRACTION option
when starting mysqld. See
Section 5.2.2, “The Server SQL Mode”.

You should use integers to store values in
BIGINT columns (instead of using strings
as in MySQL 3.23). Using strings still works, but using
integers is more efficient.

In MySQL 3.23, INSERT INTO ... SELECT
always had IGNORE enabled. As of 4.0.1,
MySQL stops (and possibly rolls back) by default in case of
an error unless you specify IGNORE.

You should use TRUNCATE TABLE when you
want to delete all rows from a table and you do not need to
obtain a count of the number of rows that were deleted.
(DELETE FROM
tbl_name returns a row
count in 4.0 and does not reset the
AUTO_INCREMENT counter, and
TRUNCATE TABLE is faster.)

You get an error if you have an active transaction or
LOCK TABLES statement when trying to
execute TRUNCATE TABLE or DROP
DATABASE.

To use MATCH ... AGAINST (... IN BOOLEAN
MODE) full-text searches, you must rebuild
existing table indexes using REPAIR TABLE
tbl_name USE_FRM. If
you attempt a boolean full-text search without rebuilding
the indexes in this manner, the search returns incorrect
results. See Section 12.7.5, “Fine-Tuning MySQL Full-Text Search”.

LOCATE() and INSTR()
are case sensitive if one of the arguments is a binary
string. Otherwise they are case insensitive.

STRCMP() uses the current character set
when performing comparisons. This makes the default
comparison behavior not case sensitive unless one or both of
the operands are binary strings.

HEX(str)
returns the characters in str
converted to hexadecimal. If you want to convert a number to
hexadecimal, you should ensure that you call
HEX() with a numeric argument.

RAND(seed) returns a different random
number series in 4.0 than in 3.23; this was done to further
differentiate RAND(seed) and
RAND(seed+1).

The default type returned by IFNULL(A,B)
is set to be the more “general” of the types of
A and B. (The
general-to-specific order is string,
REAL, INTEGER).

C API Changes:

The old C API functions mysql_drop_db(),
mysql_create_db(), and
mysql_connect() are no longer supported
in MySQL 4.0 unless MySQL is compiled with
CFLAGS=-DUSE_OLD_FUNCTIONS. It is
preferable to change client programs to use the new 4.0 API
instead.

In the MYSQL_FIELD structure,
length and max_length
have changed from unsigned int to
unsigned long. This should not cause any
problems, except that they may generate warning messages
when used as arguments in the printf()
class of functions.

If you want to recompile the Perl
DBD::mysql module, use a recent version.
Version 2.9003 is recommended. Versions older than 1.2218
should not be used because they use the deprecated
mysql_drop_db() call.

2.10.3. Upgrading the Grant Tables

Some releases introduce changes to the structure of the grant
tables (the tables in the mysql database) to
add new privileges or features. To make sure that your grant
tables are current when you update to a new version of MySQL,
you should run the mysql_fix_privilege_tables
script to update your grant tables as well. The procedure for
doing this is described at
Section 5.3, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

2.10.4. Copying MySQL Databases to Another Machine

If you are using MySQL 3.23 or later, you can copy the
.frm, .MYI, and
.MYD files for MyISAM
tables between different architectures that support the same
floating-point format. (MySQL takes care of any byte-swapping
issues.) See Section 14.1, “The MyISAM Storage Engine”.

The MySQL ISAM data and index files
(.ISD and *.ISM,
respectively) are dependent upon the architecture and, in some
cases, the operating system. If you want to move applications to
another machine having a different architecture or operating
system than that of the current machine, you should not try to
move a database by simply copying the files to the other
machine. Use mysqldump instead.

By default, mysqldump creates a file
containing SQL statements. You can then transfer the file to the
other machine and use it as input to the
mysql client.

Try mysqldump --help to see what options are
available. If you are moving the data to a newer version of
MySQL, you should use mysqldump --opt to take
advantage of any optimizations that result in a dump file that
is smaller and can be processed faster.

The easiest (although not the fastest) way to move a database
between two machines is to run the following commands on the
machine on which the database is located:

You can also store the result in a file, and then transfer the
file to the target machine and load the file into the database
there. For example, you can dump a database to a file on the
source machine like this:

shell> mysqldump --quick db_name | gzip > db_name.contents.gz

(The file created in this example is compressed.) Transfer the
file containing the database contents to the target machine and
run these commands there:

You can also use mysqldump and
mysqlimport to transfer the database. For
very large tables, this is much faster than simply using
mysqldump. In the following commands,
DUMPDIR represents the full pathname of the
directory you use to store the output from
mysqldump.

First, create the directory for the output files and dump the
database:

shell> mkdir DUMPDIR
shell> mysqldump --tab=DUMPDIRdb_name

Then transfer the files in the
DUMPDIR directory to a directory on
the target machine and load the files into MySQL there:

Also, do not forget to copy the mysql
database because that is where the user,
db, and host grant tables
are stored. You might have to run commands as the MySQL
root user on the new machine until you have
the mysql database in place.

After you import the mysql database on the
new machine, execute mysqladmin
flush-privileges so that the server reloads the grant
table information.

2.11. Downgrading MySQL

This section describes what you should do if you are downgrading
to an older MySQL version in the unlikely case that the previous
version worked better than the new one.

If you are downgrading within the same release series (for
example, from 4.0.20 to 4.0.19) the general rule is that you
merely need to install the new binaries on top of the old ones.
There is no need to do anything with the databases. As always,
however, it is always a good idea to make a backup.

The following items form a checklist of things you should do
whenever you perform a downgrade:

Read the upgrading section for the release series from which
you are downgrading to be sure that it does not have any
features you really need. Section 2.10, “Upgrading MySQL”.

If there is a downgrading section for that version, please
read it, too!

You can always move the MySQL format files and data files between
different versions on the same architecture as long as you stay
within versions for the same release series of MySQL. The current
production release series is 4.1.

If you downgrade from one release series to another, there may be
incompatibilities in table storage formats. In this case, you can
use mysqldump to dump your tables before
downgrading. After downgrading, reload the dump file using
mysql or mysqlimport to
re-create your tables. See Section 2.10.4, “Copying MySQL Databases to Another Machine”,
for examples.

The normal symptom of a downward-incompatible table format change
when you downgrade is that you cannot open tables. In that case,
use the following procedure:

Stop the older MySQL server that you are trying to downgrade
to.

Restart the newer MySQL server you are trying to downgrade
from.

Dump any tables that were inaccessible to the older server by
using mysqldump to create a dump file.

Stop the newer MySQL server and restart the older one.

Reload the dump file into the older server. Your tables should
be accessible.

2.11.1. Downgrading to 4.0

The table format in 4.1 changed to include more and new
character set information. Because of this, you must use
mysqldump to dump any tables you have created
with the newer MySQL server. For example, if all the tables in a
particular database need to be dumped to be reverted back to
MySQL 4.0 format, use this command:

Then stop the newer server, restart the older server, and read
in the dump file:

shell> mysql db_name < dump_file

In the special case that you are downgrading
MyISAM tables, no special treatment is
necessary if all columns in the tables contain only numeric
columns or string columns (CHAR,
VARCHAR, TEXT, and so
forth) that contain only latin1 data. Your
4.1 tables should be directly usable with a 4.0 server.

If you used the mysql_fix_privilege_tables
script to upgrade the grant tables, you can either use the
preceding method to convert them to back to MySQL 4.0 or do the
following in MySQL 4.1 (or above):

ALTER TABLE mysql.user CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE mysql.db CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE mysql.host CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE mysql.tables_priv CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE mysql.columns_priv CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE mysql.func CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;

This section discusses issues that have been found to occur on
Linux. The first few subsections describe general operating
system-related issues, problems that can occur when using binary
or source distributions, and post-installation issues. The
remaining subsections discuss problems that occur with Linux on
specific platforms.

Note that most of these problems occur on older versions of
Linux. If you are running a recent version, you may see none of
them.

2.12.1.1. Linux Operating System Notes

MySQL needs at least Linux version 2.0.

Warning: We have seen some
strange problems with Linux 2.2.14 and MySQL on SMP systems.
Some MySQL users have also reported that they have encountered
serious stability problems using MySQL with kernel 2.2.14. If
you are using this kernel, you should upgrade to 2.2.19 (or
newer) or to a 2.4 or 2.6 kernel. If you have a multiple-CPU
machine, you should seriously consider using 2.4 or 2.6
because it gives you a significant speed boost. Your system
should also be more stable.

When using LinuxThreads, you should see a minimum of three
mysqld processes running. These are in fact
threads. There is one thread for the LinuxThreads manager, one
thread to handle connections, and one thread to handle alarms
and signals.

2.12.1.2. Linux Binary Distribution Notes

The Linux-Intel binary and RPM releases of MySQL are
configured for the highest possible speed. We are always
trying to use the fastest stable compiler available.

The binary release is linked with -static,
which means you do not normally need to worry about which
version of the system libraries you have. You need not install
LinuxThreads, either. A program linked with
-static is slightly larger than a dynamically
linked program, but also slightly faster (3-5%). However, one
problem with a statically linked program is that you cannot
use user-defined functions (UDFs). If you are going to write
or use UDFs (this is something for C or C++ programmers only),
you must compile MySQL yourself using dynamic linking.

A known issue with binary distributions is that on older Linux
systems that use libc (such as Red Hat 4.x
or Slackware), you get some non-fatal problems with hostname
resolution. If your system uses libc rather
than glibc2, you probably will encounter
some difficulties with hostname resolution and
getpwnam(). This happens because
glibc unfortunately depends on some
external libraries to implement hostname resolution and
getpwent(), even when compiled with
-static. These problems manifest themselves
in two ways:

You may see the following error message when you run
mysql_install_db:

Sorry, the host 'xxxx' could not be looked up

You can deal with this by executing
mysql_install_db --force, which does
not execute the resolveip test in
mysql_install_db. The downside is that
you cannot use hostnames in the grant tables: Except for
localhost, you must use IP numbers
instead. If you are using an old version of MySQL that
does not support --force, you must
manually remove the resolveip test in
mysql_install_db using a text editor.

You also may see the following error when you try to run
mysqld with the --user
option:

getpwnam: No such file or directory

To work around this, start mysqld by
using the su command rather than by
specifying the --user option. This causes
the system itself to change the user ID of the
mysqld process so that
mysqld need not do so.

Another solution, which solves both problems, is to not use a
binary distribution. Get a MySQL source distribution (in RPM
or tar.gz format) and install that instead.

On some Linux 2.2 versions, you may get the error
Resource temporarily unavailable when
clients make a lot of new connections to a
mysqld server over TCP/IP. The problem is
that Linux has a delay between the time that you close a
TCP/IP socket and the time that the system actually frees it.
There is room for only a finite number of TCP/IP slots, so you
encounter the resource-unavailable error if clients attempt
too many new TCP/IP connections during a short time. For
example, you may see the error when you run the MySQL
test-connect benchmark over TCP/IP.

We have inquired about this problem a few times on different
Linux mailing lists but have never been able to find a
suitable resolution. The only known “fix” is for
the clients to use persistent connections, or, if you are
running the database server and clients on the same machine,
to use Unix socket file connections rather than TCP/IP
connections.

2.12.1.3. Linux Source Distribution Notes

The following notes regarding glibc apply
only to the situation when you build MySQL yourself. If you
are running Linux on an x86 machine, in most cases it is much
better for you to just use our binary. We link our binaries
against the best patched version of glibc
we can find and with the best compiler options, in an attempt
to make it suitable for a high-load server. For a typical
user, even for setups with many concurrent connections or
tables exceeding the 2GB limit, our binary is the best choice
in most cases. After reading the following text, if you are in
doubt about what to do, try our binary first to see whether it
meets your needs. If you discover that it is not good enough,
you may want to try your own build. In that case, we would
appreciate a note about it so that we can build a better
binary next time.

MySQL uses LinuxThreads on Linux. If you are using an old
Linux version that does not have glibc2,
you must install LinuxThreads before trying to compile MySQL.
You can obtain LinuxThreads at
http://dev.mysql.com/downloads/os-linux.html.

Note that glibc versions before and
including version 2.1.1 have a fatal bug in
pthread_mutex_timedwait() handling, which
is used when you issue INSERT DELAYED
statements. We recommend that you not use INSERT
DELAYED before upgrading glibc.

Note that Linux kernel and the LinuxThread library can by
default have only 1,024 threads. If you plan to have more than
1,000 concurrent connections, you need to make some changes to
LinuxThreads:

Increase PTHREAD_THREADS_MAX in
sysdeps/unix/sysv/linux/bits/local_lim.h
to 4096 and decrease STACK_SIZE in
linuxthreads/internals.h to 256KB.
The paths are relative to the root of
glibc. (Note that MySQL is not stable
with around 600-1000 connections if
STACK_SIZE is the default of 2MB.)

Recompile LinuxThreads to produce a new
libpthread.a library, and relink
MySQL against it.

There is another issue that greatly hurts MySQL performance,
especially on SMP systems. The mutex implementation in
LinuxThreads in glibc 2.1 is very bad for
programs with many threads that hold the mutex only for a
short time. This produces a paradoxical result: If you link
MySQL against an unmodified LinuxThreads, removing processors
from an SMP actually improves MySQL performance in many cases.
We have made a patch available for glibc
2.1.3 to correct this behavior
(http://www.mysql.com/Downloads/Linux/linuxthreads-2.1-patch).

With glibc 2.2.2, MySQL 3.23.36 uses the
adaptive mutex, which is much better than even the patched one
in glibc 2.1.3. Be warned, however, that
under some conditions, the current mutex code in
glibc 2.2.2 overspins, which hurts MySQL
performance. The likelihood that this condition occurs can be
reduced by renicing the mysqld process to
the highest priority. We have also been able to correct the
overspin behavior with a patch, available at
http://www.mysql.com/Downloads/Linux/linuxthreads-2.2.2.patch.
It combines the correction of overspin, maximum number of
threads, and stack spacing all in one. You need to apply it in
the linuxthreads directory with
patch -p0
</tmp/linuxthreads-2.2.2.patch. We hope it is
included in some form in future releases of
glibc 2.2. In any case, if you link against
glibc 2.2.2, you still need to correct
STACK_SIZE and
PTHREAD_THREADS_MAX. We hope that the
defaults is corrected to some more acceptable values for
high-load MySQL setup in the future, so that the commands
needed to produce your own build can be reduced to
./configure; make; make install.

We recommend that you use these patches to build a special
static version of libpthread.a and use it
only for statically linking against MySQL. We know that the
patches are safe for MySQL and significantly improve its
performance, but we cannot say anything about other
applications. If you link other applications that require
LinuxThreads against the patched static version of the
library, or build a patched shared version and install it on
your system, you do so at your own risk.

If you experience any strange problems during the installation
of MySQL, or with some common utilities hanging, it is very
likely that they are either library or compiler related. If
this is the case, using our binary resolves them.

If you link your own MySQL client programs, you may see the
following error at runtime:

ld.so.1: fatal: libmysqlclient.so.#:
open failed: No such file or directory

This problem can be avoided by one of the following methods:

Link clients with the
-Wl,r/full/path/to/libmysqlclient.so flag
rather than with -Lpath).

Copy libmysqclient.so to
/usr/lib.

Add the pathname of the directory where
libmysqlclient.so is located to the
LD_RUN_PATH environment variable before
running your client.

If you are using the Fujitsu compiler
(fcc/FCC), you may have some problems
compiling MySQL because the Linux header files are very
gcc oriented. The following
configure line should work with
fcc/FCC:

You can also run the echo commands from the
command line as root, but these settings
are lost the next time your computer restarts.

Alternatively, you can set these parameters on startup by
using the sysctl tool, which is used by
many Linux distributions (SuSE has added it as well, beginning
with SuSE Linux 8.0). Just put the following values into a
file named /etc/sysctl.conf:

This should allow the server a limit of 8,192 for the combined
number of connections and open files.

The STACK_SIZE constant in LinuxThreads
controls the spacing of thread stacks in the address space. It
needs to be large enough so that there is plenty of room for
each individual thread stack, but small enough to keep the
stack of some threads from running into the global
mysqld data. Unfortunately, as we have
discovered, the Linux implementation of
mmap() successfully unmaps a mapped region
if you ask it to map out an address currently in use, zeroing
out the data on the entire page instead of returning an error.
So, the safety of mysqld or any other
threaded application depends on “gentlemanly”
behavior of the code that creates threads. The user must take
measures to make sure that the number of running threads at
any time is sufficiently low for thread stacks to stay away
from the global heap. With mysqld, you
should enforce this behavior by setting a reasonable value for
the max_connections variable.

If you build MySQL yourself, you can patch LinuxThreads for
better stack use. See Section 2.12.1.3, “Linux Source Distribution Notes”. If
you do not want to patch LinuxThreads, you should set
max_connections to a value no higher than
500. It should be even less if you have a large key buffer,
large heap tables, or some other things that make
mysqld allocate a lot of memory, or if you
are running a 2.2 kernel with a 2GB patch. If you are using
our binary or RPM version 3.23.25 or later, you can safely set
max_connections at 1500, assuming no large
key buffer or heap tables with lots of data. The more you
reduce STACK_SIZE in LinuxThreads the more
threads you can safely create. We recommend values between
128KB and 256KB.

If you use a lot of concurrent connections, you may suffer
from a “feature” in the 2.2 kernel that attempts
to prevent fork bomb attacks by penalizing a process for
forking or cloning a child. This causes MySQL not to scale
well as you increase the number of concurrent clients. On
single-CPU systems, we have seen this manifested as very slow
thread creation: It may take a long time to connect to MySQL
(as long as one minute), and it may take just as long to shut
it down. On multiple-CPU systems, we have observed a gradual
drop in query speed as the number of clients increases. In the
process of trying to find a solution, we have received a
kernel patch from one of our users who claimed it made a lot
of difference for his site. The patch is available at
http://www.mysql.com/Downloads/Patches/linux-fork.patch.
We have done rather extensive testing of this patch on both
development and production systems. It has significantly
improved MySQL performance without causing any problems and we
recommend it to our users who still run high-load servers on
2.2 kernels.

This issue has been fixed in the 2.4 kernel, so if you are not
satisfied with the current performance of your system, rather
than patching your 2.2 kernel, it might be easier to upgrade
to 2.4. On SMP systems, upgrading also gives you a nice SMP
boost in addition to fixing the fairness bug.

We have tested MySQL on the 2.4 kernel on a two-CPU machine
and found MySQL scales much better. There
was virtually no slowdown on query throughput all the way up
to 1,000 clients, and the MySQL scaling factor (computed as
the ratio of maximum throughput to the throughput for one
client) was 180%. We have observed similar results on a
four-CPU system: Virtually no slowdown as the number of
clients was increased up to 1,000, and a 300% scaling factor.
Based on these results, for a high-load SMP server using a 2.2
kernel, we definitely recommend upgrading to the 2.4 kernel at
this point.

We have discovered that it is essential to run the
mysqld process with the highest possible
priority on the 2.4 kernel to achieve maximum performance.
This can be done by adding a renice -20 $$
command to mysqld_safe. In our testing on a
four-CPU machine, increasing the priority resulted in a 60%
throughput increase with 400 clients.

We are currently also trying to collect more information on
how well MySQL performs with a 2.4 kernel on four-way and
eight-way systems. If you have access such a system and have
done some benchmarks, please send an email message to
<benchmarks@mysql.com> with the results. We will
review them for inclusion in the manual.

To get a core dump on Linux if mysqld dies
with a SIGSEGV signal, you can start
mysqld with the
--core-file option. Note that you also
probably need to raise the core file size by adding
ulimit -c 1000000 to
mysqld_safe or starting
mysqld_safe with
--core-file-size=1000000. See
Section 5.1.3, “mysqld_safe — MySQL Server Startup Script”.

2.12.1.5. Linux x86 Notes

MySQL requires libc 5.4.12 or newer. It is
known to work with libc 5.4.46.
glibc 2.0.6 and later should also work.
There have been some problems with the
glibc RPMs from Red Hat, so if you have
problems, check whether there are any updates. The
glibc 2.0.7-19 and 2.0.7-29 RPMs are known
to work.

If you are using Red Hat 8.0 or a new glibc
2.2.x library, you may see mysqld die in
gethostbyaddr(). This happens because the
new glibc library requires a stack size
greater than 128KB for this call. To fix the problem, start
mysqld with the
--thread-stack=192K option. (Use -O
thread_stack=192K before MySQL 4.) This stack size is
the default on MySQL 4.0.10 and above, so you should not see
the problem.

If you are using gcc 3.0 and above to
compile MySQL, you must install the
libstdc++v3 library before compiling MySQL;
if you do not do this, you get an error about a missing
__cxa_pure_virtual symbol during linking.

On some older Linux distributions,
configure may produce an error like this:

Syntax error in sched.h. Change _P to __P in the
/usr/include/sched.h file.
See the Installation chapter in the Reference Manual.

Just do what the error message says. Add an extra underscore
to the _P macro name that has only one
underscore, and then try again.

You may get some warnings when compiling. Those shown here can
be ignored:

If mysqld always dumps core when it starts,
the problem may be that you have an old
/lib/libc.a. Try renaming it, and then
remove sql/mysqld and do a new
make install and try again. This problem
has been reported on some Slackware installations.

If you get the following error when linking
mysqld, it means that your
libg++.a is not installed correctly:

If mysqld crashes immediately and you are
running Red Hat 5.0 with a version of glibc
older than 2.0.7-5, you should make sure that you have
installed all glibc patches. There is a lot
of information about this in the MySQL mail archives,
available online at http://lists.mysql.com/.

2.12.1.6. Linux SPARC Notes

In some implementations, readdir_r() is
broken. The symptom is that the SHOW
DATABASES statement always returns an empty set.
This can be fixed by removing
HAVE_READDIR_R from
config.h after configuring and before
compiling.

2.12.1.7. Linux Alpha Notes

MySQL 3.23.12 is the first MySQL version that is tested on
Linux-Alpha. If you plan to use MySQL on Linux-Alpha, you
should ensure that you have this version or newer.

We have tested MySQL on Alpha with our benchmarks and test
suite, and it appears to work nicely.

Note that until MySQL version 3.23.52 and 4.0.2, we optimized
the binary for the current CPU only (by using the
-fast compile option). This means that for
older versions, you can use our Alpha binaries only if you
have an Alpha EV6 processor.

For all subsequent releases, we added the -arch
generic flag to our compile options, which ensures
that the binary runs on all Alpha processors. We also compile
statically to avoid library problems. The
configure command looks like this:

On IA-64, the MySQL client binaries use shared libraries. This
means that if you install our binary distribution at a
location other than /usr/local/mysql, you
need to add the path of the directory where you have
libmysqlclient.so installed either to the
/etc/ld.so.conf file or to the value of
your LD_LIBRARY_PATH environment variable.

2.12.2. Mac OS X Notes

On Mac OS X, tar cannot handle long
filenames. If you need to unpack a .tar.gz
distribution, use gnutar instead.

2.12.2.1. Mac OS X 10.x (Darwin)

MySQL should work without major problems on Mac OS X 10.x
(Darwin).

Known issues are:

The connection times (wait_timeout,
interactive_timeout and
net_read_timeout) values are not
honored. The symptom is that persistent connections can
hang for a very long time without getting closed down and
that a 'kill' for a thread will not take affect until the
thread does it a new command

This is probably a signal handling problem in the thread
library where the signal does not break a pending read and
we hope that a future update to the thread libraries will
fix this.

Our binary for Mac OS X is compiled on Darwin 6.3 with the
following configure line:

2.12.2.2. Mac OS X Server 1.2 (Rhapsody)

For current versions of Mac OS X Server, no operating system
changes are necessary before compiling MySQL. Compiling for
the Server platform is the same as for the client version of
Mac OS X. (However, note that MySQL comes preinstalled on Mac
OS X Server, so you need not build it yourself.)

For older versions (Mac OS X Server 1.2, a.k.a. Rhapsody), you
must first install a pthread package before trying to
configure MySQL.

To create a 64-bit Solaris binary using gcc,
add -m64 to CFLAGS and
CXXFLAGS and remove
--enable-assembler from the
configure line. This works only with MySQL
4.0 and up; MySQL 3.23 does not include the required
modifications to support this.

In the MySQL benchmarks, we got a 4% speedup on an UltraSPARC
when using Forte 5.0 in 32-bit mode compared to using
gcc 3.2 with the -mcpu flag.

If you create a 64-bit mysqld binary, it is
4% slower than the 32-bit binary, but can handle more threads
and memory.

When using Solaris 10 for x86_64, you should mount any
filesystems on which you intend to store InnoDB files with the
forcedirectio option. (By default mounting is
done without this option.) Failing to do so will cause a
significant drop in performance when using the
InnoDB storage engine on this platform.

If you get a problem with fdatasync or
sched_yield, you can fix this by adding
LIBS=-lrt to the configure
line

For compilers older than WorkShop 5.3, you might have to edit
the configure script. Change this line:

#if !defined(__STDC__) || __STDC__ != 1

To this:

#if !defined(__STDC__)

If you turn on __STDC__ with the
-Xc option, the Sun compiler cannot compile
with the Solaris pthread.h header file.
This is a Sun bug (broken compiler or broken include file).

If mysqld issues the following error message
when you run it, you have tried to compile MySQL with the Sun
compiler without enabling the -mt multi-thread
option:

libc internal error: _rmutex_unlock: rmutex not held

Add -mt to CFLAGS and
CXXFLAGS and recompile.

If you are using the SFW version of gcc
(which comes with Solaris 8), you must add
/opt/sfw/lib to the environment variable
LD_LIBRARY_PATH before running
configure.

If you are using the gcc available from
sunfreeware.com, you may have many problems.
To avoid this, you should recompile gcc and
GNU binutils on the machine where you are
running them.

If you get the following error when compiling MySQL with
gcc, it means that your
gcc is not configured for your version of
Solaris:

The proper thing to do in this case is to get the newest version
of gcc and compile it with your current
gcc compiler. At least for Solaris 2.5,
almost all binary versions of gcc have old,
unusable include files that break all programs that use threads,
and possibly other programs!

Solaris does not provide static versions of all system libraries
(libpthreads and libdl),
so you cannot compile MySQL with --static. If
you try to do so, you get one of the following errors:

2.12.4. BSD Notes

This section provides information about using MySQL on variants
of BSD Unix.

2.12.4.1. FreeBSD Notes

FreeBSD 4.x or newer is recommended for running MySQL, because
the thread package is much more integrated. To get a secure
and stable system, you should use only FreeBSD kernels that
are marked -RELEASE.

The easiest (and preferred) way to install MySQL is to use the
mysql-server and
mysql-client ports available at
http://www.freebsd.org/. Using these ports
gives you the following benefits:

A working MySQL with all optimizations enabled that are
known to work on your version of FreeBSD.

Automatic configuration and build.

Startup scripts installed in
/usr/local/etc/rc.d.

The ability to use pkg_info -L to see
which files are installed.

The ability to use pkg_delete to remove
MySQL if you no longer want it on your machine.

It is recommended you use MIT-pthreads on FreeBSD 2.x, and
native threads on FreeBSD 3 and up. It is possible to run with
native threads on some late 2.2.x versions, but you may
encounter problems shutting down mysqld.

Unfortunately, certain function calls on FreeBSD are not yet
fully thread-safe. Most notably, this includes the
gethostbyname() function, which is used by
MySQL to convert hostnames into IP addresses. Under certain
circumstances, the mysqld process suddenly
causes 100% CPU load and is unresponsive. If you encounter
this problem, try to start MySQL using the
--skip-name-resolve option.

Alternatively, you can link MySQL on FreeBSD 4.x against the
LinuxThreads library, which avoids a few of the problems that
the native FreeBSD thread implementation has. For a very good
comparison of LinuxThreads versus native threads, see Jeremy
Zawodny's article FreeBSD or Linux for your MySQL
Server? at
http://jeremy.zawodny.com/blog/archives/000697.html.

Known problem when using LinuxThreads on FreeBSD is:

The connection times (wait_timeout,
interactive_timeout and
net_read_timeout) values are not
honored. The symptom is that persistent connections can
hang for a very long time without getting closed down and
that a 'kill' for a thread will not take affect until the
thread does it a new command

This is probably a signal handling problem in the thread
library where the signal does not break a pending read.
This is supposed to be fixed in FreeBSD 5.0

The MySQL build process requires GNU make
(gmake) to work. If GNU
make is not available, you must install it
first before compiling MySQL.

The recommended way to compile and install MySQL on FreeBSD
with gcc (2.95.2 and up) is:

If you get an error from make install that
it cannot find /usr/include/pthreads,
configure did not detect that you need
MIT-pthreads. To fix this problem, remove
config.cache, and then re-run
configure with the
--with-mit-threads option.

Be sure that your name resolver setup is correct. Otherwise,
you may experience resolver delays or failures when connecting
to mysqld. Also make sure that the
localhost entry in the
/etc/hosts file is correct. The file
should start with a line similar to this:

127.0.0.1 localhost localhost.your.domain

FreeBSD is known to have a very low default file handle limit.
See Section A.2.17, “File Not Found”. Start the
server by using the --open-files-limit option
for mysqld_safe, or raise the limits for
the mysqld user in
/etc/login.conf and rebuild it with
cap_mkdb /etc/login.conf. Also be sure that
you set the appropriate class for this user in the password
file if you are not using the default (use chpass
mysqld-user-name). See
Section 5.1.3, “mysqld_safe — MySQL Server Startup Script”.

FreeBSD limits the size of a process to 512MB, even if you
have much more RAM available on the system. So you may get an
error such as this:

Out of memory (Needed 16391 bytes)

In current versions of FreeBSD (at least 4.x and greater), you
may increase this limit by adding the following entries to the
/boot/loader.conf file and rebooting the
machine (these are not settings that can be changed at run
time with the sysctl command):

For older versions of FreeBSD, you must recompile your kernel
in order to change the maximum data segment size for a
process. In this case, you should look at the
MAXDSIZ option in the
LINT config file for more information.

You can change the directory locations if you wish, or just
use the defaults by not specifying any locations.

If you have problems with performance under heavy load, try
using the --skip-thread-priority option to
mysqld. This runs all threads with the same
priority. On BSDI 3.1, this gives better performance, at least
until BSDI fixes its thread scheduler.

If you get the error virtual memory
exhausted while compiling, you should try using
ulimit -v 80000 and running
make again. If this does not work and you
are using bash, try switching to
csh or sh; some BSDI
users have reported problems with bash and
ulimit.

2.12.4.6. BSD/OS Version 4.x Notes

BSDI 4.x has some thread-related bugs. If you want to use
MySQL on this, you should install all thread-related patches.
At least M400-023 should be installed.

On some BSDI 4.x systems, you may get problems with shared
libraries. The symptom is that you cannot execute any client
programs, for example, mysqladmin. In this
case, you need to reconfigure not to use shared libraries with
the --disable-shared option to configure.

Some customers have had problems on BSDI 4.0.1 that the
mysqld binary after a while cannot open
tables. This is because some library/system-related bug causes
mysqld to change current directory without
having asked for that to happen.

The fix is to either upgrade MySQL to at least version 3.23.34
or, after running configure, remove the
line #define HAVE_REALPATH from
config.h before running
make.

Note that this means that you cannot symbolically link a
database directories to another database directory or symbolic
link a table to another database on BSDI. (Making a symbolic
link to another disk is okay).

2.12.5.2. HP-UX Version 11.x Notes

Because of some critical bugs in the standard HP-UX libraries,
you should install the following patches before trying to run
MySQL on HP-UX 11.0:

PHKL_22840 Streams cumulative
PHNE_22397 ARPA cumulative

This solves the problem of getting
EWOULDBLOCK from recv()
and EBADF from accept()
in threaded applications.

If you are using gcc 2.95.1 on an unpatched
HP-UX 11.x system, you may get the following error:

In file included from /usr/include/unistd.h:11,
from ../include/global.h:125,
from mysql_priv.h:15,
from item.cc:19:
/usr/include/sys/unistd.h:184: declaration of C function ...
/usr/include/sys/pthread.h:440: previous declaration ...
In file included from item.h:306,
from mysql_priv.h:158,
from item.cc:19:

The problem is that HP-UX does not define
pthreads_atfork() consistently. It has
conflicting prototypes in
/usr/include/sys/unistd.h:184 and
/usr/include/sys/pthread.h:440.

One solution is to copy
/usr/include/sys/unistd.h into
mysql/include and edit
unistd.h and change it to match the
definition in pthread.h. Look for this
line:

Another reason for not being able to compile is that you did
not define the +DD64 flags as just
described.

Another possibility for HP-UX 11 is to use MySQL binaries for
HP-UX 10.20. We have received reports from some users that
these binaries work fine on HP-UX 11.00. If you encounter
problems, be sure to check your HP-UX patch level.

2.12.5.3. IBM-AIX notes

Automatic detection of xlC is missing from
Autoconf, so a number of variables need to be set before
running configure. The following example
uses the IBM compiler:

If you change the -O3 to -O2
in the preceding configure line, you must
also remove the -qstrict option. This is a
limitation in the IBM C compiler.

If you are using gcc or
egcs to compile MySQL, you
must use the
-fno-exceptions flag, because the exception
handling in gcc/egcs is
not thread-safe! (This is tested with egcs
1.1.) There are also some known problems with IBM's assembler
that may cause it to generate bad code when used with
gcc.

We recommend the following configure line
with egcs and gcc 2.95
on AIX:

The -Wa,-many option is necessary for the
compile to be successful. IBM is aware of this problem but is
in no hurry to fix it because of the workaround that is
available. We do not know if the
-fno-exceptions is required with
gcc 2.95, but because MySQL does not use
exceptions and the option generates faster code, we recommend
that you should always use it with egcs and
gcc.

If you get a problem with assembler code, try changing the
-mcpu=xxx option
to match your CPU. Typically power2,
power, or powerpc may
need to be used. Alternatively, you might need to use
604 or 604e. We are not
positive but suspect that power would
likely be safe most of the time, even on a power2 machine.

If you do not know which CPU is present, execute a
uname -m command. It produces a string that
looks like 000514676700 whose format is
xxyyyyyymmss where xx
and ss are always 00,
yyyyyy is a unique system ID and
mm is the ID of the CPU Planar. A chart of
these values can be found at
http://www16.boulder.ibm.com/pseries/en_US/cmds/aixcmds5/uname.htm.

This gives you a machine type and model which you can use to
determine what type of CPU you have.

If you have problems with signals (MySQL dies unexpectedly
under high load), you may have found an OS bug with threads
and signals. In this case, you can tell MySQL not to use
signals by configuring as follows:

This does not affect the performance of MySQL, but has the
side effect that you cannot kill clients that are
“sleeping” on a connection with
mysqladmin kill or mysqladmin
shutdown. Instead, the client dies when it issues
its next command.

On some versions of AIX, linking with
libbind.a makes
getservbyname() dump core. This is an AIX
bug and should be reported to IBM.

For AIX 4.2.1 and gcc, you have to make the
following changes.

After configuring, edit config.h and
include/my_config.h and change the line
that says this:

#define HAVE_SNPRINTF 1

to this:

#undef HAVE_SNPRINTF

And finally, in mysqld.cc, you need to
add a prototype for initgroups().

#ifdef _AIX41
extern "C" int initgroups(const char *,int);
#endif

If you need to allocate a lot of memory to the
mysqld process, it is not sufficient merely
to use ulimit -d unlimited. You may also
have to modify mysqld_safe, adding a line
something like this:

2.12.5.4. SunOS 4 Notes

On SunOS 4, MIT-pthreads is needed to compile MySQL. This in
turn means you need GNU make.

Some SunOS 4 systems have problems with dynamic libraries and
libtool. You can use the following
configure line to avoid this problem:

./configure --disable-shared --with-mysqld-ldflags=-all-static

When compiling readline, you may get
warnings about duplicate defines. These can be ignored.

When compiling mysqld, there are some
implicit declaration of function warnings.
These can be ignored.

2.12.5.5. Alpha-DEC-UNIX Notes (Tru64)

If you are using egcs 1.1.2 on Digital
Unix, you should upgrade to gcc 2.95.2,
because egcs on DEC has some serious bugs.

When compiling threaded programs under Digital Unix, the
documentation recommends using the -pthread
option for cc and cxx
and the -lmach -lexc libraries (in addition
to -lpthread). You should run
configure something like this:

You can safely ignore these warnings. They occur because
configure can detect only errors, not
warnings.

If you start the server directly from the command line, you
may have problems with it dying when you log out. (When you
log out, your outstanding processes receive a
SIGHUP signal.) If so, try starting the
server like this:

On OSF/1 V4.0D and compiler "DEC C V5.6-071 on Digital Unix
V4.0 (Rev. 878)," the compiler had some strange behavior
(undefined asm symbols).
/bin/ld also appears to be broken (problems
with _exit undefined errors occurring while
linking mysqld). On this system, we have
managed to compile MySQL with the following
configure line, after replacing
/bin/ld with the version from OSF 4.0C:

If you have problems with signals (MySQL dies unexpectedly
under high load), you may have found an OS bug with threads
and signals. In this case, you can tell MySQL not to use
signals by configuring with:

This does not affect the performance of MySQL, but has the
side effect that you cannot kill clients that are
“sleeping” on a connection with
mysqladmin kill or mysqladmin
shutdown. Instead, the client dies when it issues
its next command.

To fix this, you should change to the sql
directory and do a cut-and-paste of the last
gcc line, but change -O3
to -O0 (or add -O0
immediately after gcc if you do not have
any -O option on your compile line). After
this is done, you can just change back to the top-level
directory and run make again.

2.12.5.7. SGI Irix Notes

If you are using Irix 6.5.3 or newer,
mysqld is able to create threads only if
you run it as a user that has CAP_SCHED_MGT
privileges (such as root) or if you give
the mysqld server this privilege with the
following shell command:

chcap "CAP_SCHED_MGT+epi" /opt/mysql/libexec/mysqld

You may have to undefine some symbols in
config.h after running
configure and before compiling.

In some Irix implementations, the alloca()
function is broken. If the mysqld server
dies on some SELECT statements, remove the
lines from config.h that define
HAVE_ALLOC and
HAVE_ALLOCA_H. If mysqladmin
create does not work, remove the line from
config.h that defines
HAVE_READDIR_R. You may have to remove the
HAVE_TERM_H line as well.

SGI recommends that you install all the patches on this page
as a set:

There have also been reports of scheduling problems. If only
one thread is running, performance is slow. Avoid this by
starting another client. This may lead to a two-to-tenfold
increase in execution speed thereafter for the other thread.
This is a poorly understood problem with Irix threads; you may
have to improvise to find solutions until this can be fixed.

If you are compiling with gcc, you can use
the following configure command:

2.12.5.8. SCO UNIX and OpenServer 5.0.x Notes

The current port is tested only on
“sco3.2v5.0.5,” “sco3.2v5.0.6,” and
“sco3.2v5.0.7” systems. There has also been a lot
of progress on a port to “sco 3.2v4.2.” Open
Server 5.0.8(Legend) has native threads and allows files
greater than 2GB. The current maximum file size is 2GB.

We have been able to compile MySQL with the following
configure command on OpenServer with
gcc 2.95.3.

This development system requires the OpenServer Execution
Environment Supplement oss646B on OpenServer 5.0.6 and oss656B
and The OpenSource libraries found in gwxlibs. All OpenSource
tools are in the opensrc directory. They
are available at
ftp://ftp.sco.com/pub/openserver5/opensrc/.

Follow this procedure to configure the Database Services
option. If you are unsure whether an application requires
this, see the documentation provided with the application.

Log in as root.

Enable the SUDS driver by editing the
/etc/conf/sdevice.d/suds file. Change
the N in the second field to a
Y.

Use mkdev aio or the Hardware/Kernel
Manager to enable support for asynchronous I/O and relink
the kernel. To allow users to lock down memory for use
with this type of I/O, update the aiomemlock(F) file. This
file should be updated to include the names of users that
can use AIO and the maximum amounts of memory they can
lock down.

Many applications use setuid binaries so that you need to
specify only a single user. See the documentation provided
with the application to see if this is the case for your
application.

After you complete this process, reboot the system to create a
new kernel incorporating these changes.

To make changes to the kernel, cd to
/etc/conf/bin and use
./idtunename
parameter to make the changes. For example, to
change SEMMS to 200,
execute these commands as root:

# cd /etc/conf/bin
# ./idtune SEMMNS 200

We recommend tuning the system, but the proper parameter
values to use depend on the number of users accessing the
application or database and size the of the database (that is,
the used buffer pool). The following affects the kernel
parameters defined in
/etc/conf/cf.d/stune:

MAXPROC should be set to at least 3000/4000
(depends on number of users) or more.

Also is recommended to use following formula to count value
for SEMMSL, SEMMNS and
SEMMNU:

SEMMSL = 13

13 has been found to be best for both
Progress and MySQL.

SEMMNS = SEMMSL * number
of db servers to be run on the system.

Set SEMMNS to the value of
SEMMSL multiplied by the number of db
servers (maximum) that you are running on the system at one
time.

SEMMNU = SEMMNS

Set the value of SEMMNU to equal the value
of SEMMNS. You could probably set this to
75% of SEMMNS, but this is a conservative
estimate.

You need to at least install the SCO OpenServer Linker and
Application Development Libraries or the OpenServer
Development System to use gcc. You cannot
use the GCC Dev system without installing one of these.

FSU Pthreads can be compiled with SCO Unix 4.2 with tcpip, or
using OpenServer 3.0 or Open Desktop 3.0 (OS 3.0 ODT 3.0) with
the SCO Development System installed using a good port of GCC
2.5.x. For ODT or OS 3.0, you need a good port of GCC 2.5.x.
There are a lot of problems without a good port. The port for
this product requires the SCO Unix Development system. Without
it, you are missing the libraries and the linker that is
needed. You also need
SCO-3.2v4.2-includes.tar.gz. This file
contains the changes to the SCO Development include files that
are needed to get MySQL to build. You need to replace the
existing system include files with these modified header
files. They can be obtained from
ftp://ftp.zenez.com/pub/zenez/prgms/SCO-3.2v4.2-includes.tar.gz.

To build FSU Pthreads on your system, all you should need to
do is run GNU make. The
Makefile in FSU-threads-3.14.tar.gz is
set up to make FSU-threads.

You can run ./configure in the
threads/src directory and select the SCO
OpenServer option. This command copies
Makefile.SCO5 to
Makefile. Then run
make.

To install in the default /usr/include
directory, log in as root, and then
cd to the thread/src
directory and run make install.

Remember that you must use GNU make when
making MySQL.

Note: If you do not start
mysqld_safe as root, you
should get only the default 110 open files per process.
mysqld writes a note about this in the log
file.

With SCO 3.2V4.2, you should use FSU Pthreads version 3.14 or
newer. The following configure command
should work:

The SCO development libraries are re-entrant in FSU
Pthreads. SCO claims that its library functions are
re-entrant, so they must be re-entrant with FSU Pthreads.
FSU Pthreads on OpenServer tries to use the SCO scheme to
make re-entrant libraries.

FSU Pthreads (at least the version at
ftp::/ftp.zenez.com) comes linked with GNU
malloc. If you encounter problems with
memory usage, make sure that
gmalloc.o is included in
libgthreads.a and
libgthreads.so.

In FSU Pthreads, the following system calls are
pthreads-aware: read(),
write(), getmsg(),
connect(), accept(),select(), and
wait().

The CSSA-2001-SCO.35.2 (the patch is listed in custom as
erg711905-dscr_remap security patch (version 2.0.0))
breaks FSU threads and makes mysqld
unstable. You have to remove this one if you want to run
mysqld on an OpenServer 5.0.6 machine.

If you use SCO OpenServer 5, you may need to recompile FSU
pthreads with -DDRAFT7 in
CFLAGS. Otherwise,
InnoDB may hang at a
mysqld startup.

It is probably a good idea to install these patches before
trying to compile/use MySQL.

Beginning with Legend/OpenServer 6.0.0 has native threads and
no 2GB file size limit.

2.12.5.9. SCO UnixWare 7.1.x and OpenUNIX 8.0.0 Notes

We recommend using the latest production release of MySQL.
Should you choose to use an older release of MySQL on UnixWare
7.1.x, you must use a version of MySQL at least as recent as
3.22.13 to get fixes for some portability and OS problems.

We have been able to compile MySQL with the following
configure command on UnixWare 7.1.x:

The version of Berkeley DB that comes with either UnixWare
7.1.4 or OpenServer 6.0.0 is not used when building MySQL.
MySQL instead uses its own version of Berkeley DB. The
configure command needs to build both a
static and a dynamic library in
src_directory/bdb/build_unix/,
but it does not with MySQL's own BDB version. The workaround
is as follows.

Configure as normal for MySQL.

cd bdb/build_unix/

cp -p Makefile Makefile.sav

Use same options and run
../dist/configure.

Run gmake.

cp -p Makefile.sav Makefile

Change to top source directory and run
gmake.

This allows both the shared and dynamic libraries to be made
and work.

By default, the maximum file size on a UnixWare 7.1.1 system
is 1GB, but UnixWare 7.1.4 file size limit is 1 TB with VXFS.
Some OS utilities have a limitation of 2GB. The maximum
possible file size on UnixWare 7 is 1TB with VXFS.

On UnixWare 7.1.4 you do not need to do anything to get large
file support, but to enable large file support on prior
versions of UnixWare 7.1.x, run fsadm.

We recommend tuning the system, but the proper parameter
values to use depend on the number of users accessing the
application or database and size the of the database (that is,
the used buffer pool). The following affects the kernel
parameters defined in
/etc/conf/cf.d/stune:

NPROC should be set to at least 3000/4000
(depends on number of users).

Also is recommended to use following formula to count value
for SEMMSL, SEMMNS, and
SEMMNU:

SEMMSL = 13

13 is what has been found to be the best for both Progress and
MySQL.

SEMMNS = SEMMSL * number
of db servers to be run on the system.

Set SEMMNS to the value of
SEMMSL multiplied by the number of db
servers (maximum) that you are running on the system at one
time.

SEMMNU = SEMMNS

Set the value of SEMMNU to equal the value
of SEMMNS. You could probably set this to
75% of SEMMNS, but this is a conservative
estimate.

2.12.5.10. SCO OpenServer 6.0.x Notes

Key improvements of OpenServer 6 include:

Larger file support up to 1 TB

Multiprocessor support increased from 4 to 32 processors

Increased memory support up to 64 GB

Extending the power of UnixWare into OpenServer 6

Dramatic performance improvement

OpenServer 6.0.0 has the following:

/bin is for commands that behave
exactly the same as on OpenServer 5.0.x.

/u95/bin is for commands that have
better standards conformance, for example Large File
System (LFS) support.

/udk/bin is for commands that behave
the same as on UnixWare 7.1.4. The default is for the LFS
support.

The following is a guide to setting PATH on
OpenServer 6. If the user wants the traditional OpenServer
5.0.x then PATH should be
/bin first. If the user wants LFS
support, the path should be
/u95/bin:/bin. If the user wants UnixWare
7 support first, the path would be
/udk/bin:/u95/bin:/bin:.

We recommend using the latest production release of MySQL.
Should you choose to use an older release of MySQL on
OpenServer 6.0.x, you must use a version of MySQL at least as
recent as 3.22.13 to get fixes for some portability and OS
problems.

We have been able to compile MySQL with the following
configure command on OpenServer 6.0.x:

The version of Berkeley DB that comes with either UnixWare
7.1.4 or OpenServer 6.0.0 is not used when building MySQL.
MySQL instead uses its own version of Berkeley DB. The
configure command needs to build both a
static and a dynamic library in
src_directory/bdb/build_unix/,
but it does not with MySQL's own BDB version. The workaround
is as follows.

We recommend tuning the system, but the proper parameter
values to use depend on the number of users accessing the
application or database and size the of the database (that is,
the used buffer pool). The following affects the kernel
parameters defined in
/etc/conf/cf.d/stune:

NPROC should be set to at least 3000/4000
(depends on number of users).

Also is recommended to use following formula to count value
for SEMMSL, SEMMNS, and
SEMMNU:

SEMMSL = 13

13 has been found to be best for both
Progress and MySQL.

SEMMNS = SEMMSL * number
of db servers to be run on the system.

Set SEMMNS to the value of
SEMMSL multiplied by the number of db
servers (maximum) that you are running on the system at one
time.

SEMMNU = SEMMNS

Set the value of SEMMNU to equal the value
of SEMMNS. You could probably set this to
75% of SEMMNS, but this is a conservative
estimate.

2.12.6. OS/2 Notes

MySQL uses quite a few open files. Because of this, you should
add something like the following to your
CONFIG.SYS file:

SET EMXOPT=-c -n -h1024

If you do not do this, you may encounter the following error:

File 'xxxx' not found (Errcode: 24)

When using MySQL with OS/2 Warp 3, FixPack 29 or above is
required. With OS/2 Warp 4, FixPack 4 or above is required. This
is a requirement of the Pthreads library. MySQL must be
installed on a partition with a type that supports long
filenames, such as HPFS, FAT32, and so on.

The INSTALL.CMD script must be run from
OS/2's own CMD.EXE and may not work with
replacement shells such as 4OS2.EXE.

The scripts/mysql-install-db script has
been renamed. It is called install.cmd and
is a REXX script, which sets up the default MySQL security
settings and creates the WorkPlace Shell icons for MySQL.

Dynamic module support is compiled in but not fully tested.
Dynamic modules should be compiled using the Pthreads runtime
library.

Note: Due to limitations in
OS/2, UDF module name stems must not exceed eight characters.
Modules are stored in the /mysql2/udf
directory; the safe-mysqld.cmd script puts
this directory in the BEGINLIBPATH
environment variable. When using UDF modules, specified
extensions are ignored---it is assumed to be
.udf. For example, in Unix, the shared
module might be named example.so and you
would load a function from it like this:

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'example.so';

In OS/2, the module would be named
example.udf, but you would not specify the
module extension:

2.13. Perl Installation Notes

Perl support for MySQL is provided by means of the
DBI/DBD client interface.
The interface requires Perl 5.6.0 or later. It does not
work if you have an older version of Perl.

If you want to use transactions with Perl DBI, you need to have
DBD::mysql version 1.2216 or newer.
DBD::mysql 2.9003 or newer is recommended.

If you are using the MySQL 4.1 client library, you must use
DBD::mysql 2.9003 or newer.

As of MySQL 3.22.8, Perl support is no longer included with MySQL
distributions. You can obtain the necessary modules from
http://search.cpan.org for Unix, or by using the
ActiveState ppm program on Windows. The
following sections describe how to do this.

2.13.1. Installing Perl on Unix

MySQL Perl support requires that you've installed MySQL client
programming support (libraries and header files). Most
installation methods install the necessary files. However, if
you installed MySQL from RPM files on Linux, be sure that you've
installed the developer RPM. The client programs are in the
client RPM, but client programming support is in the developer
RPM.

If you want to install Perl support, the files you need can be
obtained from the CPAN (Comprehensive Perl Archive Network) at
http://search.cpan.org.

The easiest way to install Perl modules on Unix is to use the
CPAN module. For example:

The DBD::mysql installation runs a number of
tests. These tests attempt to connect to the local MySQL server
using the default username and password. (The default username
is your login name on Unix, and ODBC on
Windows. The default password is “no password.”) If
you cannot connect to the server with those values (for example,
if your account has a password), the tests fail. You can use
force install DBD::mysql to ignore the failed
tests.

DBI requires the
Data::Dumper module. It may be installed; if
not, you should install it before installing
DBI.

It is also possible to download the module distributions in the
form of compressed tar archives and build the
modules manually. For example, to unpack and build a DBI
distribution, use a procedure such as this:

Unpack the distribution into the current directory:

shell> gunzip < DBI-VERSION.tar.gz | tar xvf -

This command creates a directory named
DBI-VERSION.

Change location into the top-level directory of the unpacked
distribution:

The make test command is important because it
verifies that the module is working. Note that when you run that
command during the DBD::mysql installation to
exercise the interface code, the MySQL server must be running or
the test fails.

It is a good idea to rebuild and reinstall the
DBD::mysql distribution whenever you install
a new release of MySQL, particularly if you notice symptoms such
as that all your DBI scripts fail after you
upgrade MySQL.

2.13.3. Problems Using the Perl DBI/DBD Interface

If Perl reports that it cannot find the
../mysql/mysql.so module, the problem is
probably that Perl cannot locate the shared library
libmysqlclient.so.

You should be able to fix this by one of the following methods:

Compile the DBD::mysql distribution using
perl Makefile.PL -static -config rather
than perl Makefile.PL.

Copy libmysqlclient.so to the directory
where your other shared libraries are located (probably
/usr/lib or /lib).

Modify the -L options used to compile
DBD::mysql to reflect the actual location
of libmysqlclient.so.

On Linux, you can add the pathname of the directory where
libmysqlclient.so is located to the
/etc/ld.so.conf file.

Add the pathname of the directory where
libmysqlclient.so is located to the
LD_RUN_PATH environment variable. Some
systems use LD_LIBRARY_PATH instead.

Note that you may also need to modify the -L
options if there are other libraries that the linker fails to
find. For example, if the linker cannot find
libc because it is in
/lib and the link command specifies
-L/usr/lib, change the -L
option to -L/lib or add -L/lib
to the existing link command.

If you get the following errors from
DBD::mysql, you are probably using
gcc (or using an old binary compiled with
gcc):

Add -L/usr/lib/gcc-lib/... -lgcc to the link
command when the mysql.so library gets
built (check the output from make for
mysql.so when you compile the Perl client).
The -L option should specify the pathname of
the directory where libgcc.a is located on
your system.

Another cause of this problem may be that Perl and MySQL are not
both compiled with gcc. In this case, you can
solve the mismatch by compiling both with
gcc.

You may see the following error from
DBD::mysql when you run the tests:

These changes are necessary because the Perl dynaloader does not
load the DBI modules if they were compiled
with icc or cc.

If you want to use the Perl module on a system that does not
support dynamic linking (such as SCO), you can generate a static
version of Perl that includes DBI and
DBD::mysql. The way this works is that you
generate a version of Perl with the DBI code
linked in and install it on top of your current Perl. Then you
use that to build a version of Perl that additionally has the
DBD code linked in, and install that.

Then you must install the new Perl. The output of make
perl indicates the exact make
command you need to execute to perform the installation. On SCO,
this is make -f Makefile.aperl inst_perl
MAP_TARGET=perl.

Next, use the just-created Perl to create another Perl that also
includes a statically linked DBD::mysql by
running these commands in the directory where your
DBD::mysql distribution is located:

This chapter provides a tutorial introduction to MySQL by showing
how to use the mysql client program to create and
use a simple database. mysql (sometimes referred
to as the “terminal monitor” or just
“monitor”) is an interactive program that allows you to
connect to a MySQL server, run queries, and view the results.
mysql may also be used in batch mode: you place
your queries in a file beforehand, then tell
mysql to execute the contents of the file. Both
ways of using mysql are covered here.

To see a list of options provided by mysql,
invoke it with the --help option:

shell> mysql --help

This chapter assumes that mysql is installed on
your machine and that a MySQL server is available to which you can
connect. If this is not true, contact your MySQL administrator. (If
you are the administrator, you need to consult
the relevant portions of this manual, such as
Chapter 5, Database Administration.)

This chapter describes the entire process of setting up and using a
database. If you are interested only in accessing an existing
database, you may want to skip over the sections that describe how
to create the database and the tables it contains.

Because this chapter is tutorial in nature, many details are
necessarily omitted. Consult the relevant sections of the manual for
more information on the topics covered here.

3.1. Connecting to and Disconnecting from the Server

To connect to the server, you will usually need to provide a MySQL
user name when you invoke mysql and, most
likely, a password. If the server runs on a machine other than the
one where you log in, you will also need to specify a host name.
Contact your administrator to find out what connection parameters
you should use to connect (that is, what host, user name, and
password to use). Once you know the proper parameters, you should
be able to connect like this:

shell> mysql -h host -u user -p
Enter password: ********

host and user represent the
host name where your MySQL server is running and the user name of
your MySQL account. Substitute appropriate values for your setup.
The ******** represents your password; enter it
when mysql displays the Enter
password: prompt.

If that works, you should see some introductory information
followed by a mysql> prompt:

The mysql> prompt tells you that
mysql is ready for you to enter commands.

Some MySQL installations allow users to connect as the anonymous
(unnamed) user to the server running on the local host. If this is
the case on your machine, you should be able to connect to that
server by invoking mysql without any options:

shell> mysql

After you have connected successfully, you can disconnect any time
by typing QUIT (or \q) at
the mysql> prompt:

mysql> QUIT
Bye

On Unix, you can also disconnect by pressing Control-D.

Most examples in the following sections assume that you are
connected to the server. They indicate this by the
mysql> prompt.

3.2. Entering Queries

Make sure that you are connected to the server, as discussed in
the previous section. Doing so does not in itself select any
database to work with, but that's okay. At this point, it's more
important to find out a little about how to issue queries than to
jump right in creating tables, loading data into them, and
retrieving data from them. This section describes the basic
principles of entering commands, using several queries you can try
out to familiarize yourself with how mysql
works.

Here's a simple command that asks the server to tell you its
version number and the current date. Type it in as shown here
following the mysql> prompt and press Enter:

A command normally consists of an SQL statement followed by a
semicolon. (There are some exceptions where a semicolon may be
omitted. QUIT, mentioned earlier, is one of
them. We'll get to others later.)

When you issue a command, mysql sends it to
the server for execution and displays the results, then prints
another mysql> prompt to indicate that
it is ready for another command.

mysql displays query output in tabular form
(rows and columns). The first row contains labels for the
columns. The rows following are the query results. Normally,
column labels are the names of the columns you fetch from
database tables. If you're retrieving the value of an
expression rather than a table column (as in the example just
shown), mysql labels the column using the
expression itself.

mysql shows how many rows were returned and
how long the query took to execute, which gives you a rough
idea of server performance. These values are imprecise because
they represent wall clock time (not CPU or machine time), and
because they are affected by factors such as server load and
network latency. (For brevity, the “rows in set”
line is sometimes not shown in the remaining examples in this
chapter.)

Keywords may be entered in any lettercase. The following queries
are equivalent:

A command need not be given all on a single line, so lengthy
commands that require several lines are not a problem.
mysql determines where your statement ends by
looking for the terminating semicolon, not by looking for the end
of the input line. (In other words, mysql
accepts free-format input: it collects input lines but does not
execute them until it sees the semicolon.)

In this example, notice how the prompt changes from
mysql> to -> after you
enter the first line of a multiple-line query. This is how
mysql indicates that it has not yet seen a
complete statement and is waiting for the rest. The prompt is your
friend, because it provides valuable feedback. If you use that
feedback, you can always be aware of what mysql
is waiting for.

If you decide you do not want to execute a command that you are in
the process of entering, cancel it by typing
\c:

mysql> SELECT
-> USER()
-> \c
mysql>

Here, too, notice the prompt. It switches back to
mysql> after you type \c,
providing feedback to indicate that mysql is
ready for a new command.

The following table shows each of the prompts you may see and
summarizes what they mean about the state that
mysql is in:

Prompt

Meaning

mysql>

Ready for new command.

->

Waiting for next line of multiple-line command.

'>

Waiting for next line, waiting for completion of a string that began
with a single quote (‘'’).

">

Waiting for next line, waiting for completion of a string that began
with a double quote (‘"’).

`>

Waiting for next line, waiting for completion of an identifier that
began with a backtick
(‘`’).

/*>

Waiting for next line, waiting for completion of a comment that began
with /*.

The `> prompt was implemented MySQL 4.0.16.
The /*> prompt was implemented in MySQL
4.1.12.

Multiple-line statements commonly occur by accident when you
intend to issue a command on a single line, but forget the
terminating semicolon. In this case, mysql
waits for more input:

mysql> SELECT USER()
->

If this happens to you (you think you've entered a statement but
the only response is a -> prompt), most
likely mysql is waiting for the semicolon. If
you don't notice what the prompt is telling you, you might sit
there for a while before realizing what you need to do. Enter a
semicolon to complete the statement, and mysql
executes it:

The '> and "> prompts
occur during string collection (another way of saying that MySQL
is waiting for completion of a string). In MySQL, you can write
strings surrounded by either ‘'’ or
‘"’ characters (for example,
'hello' or "goodbye"), and
mysql lets you enter strings that span multiple
lines. When you see a '> or
"> prompt, it means that you have entered a
line containing a string that begins with a
‘'’ or
‘"’ quote character, but have not
yet entered the matching quote that terminates the string. This
often indicates that you have inadvertently left out a quote
character. For example:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'>

If you enter this SELECT statement, then press
Enter and wait for the result, nothing happens.
Instead of wondering why this query takes so long, notice the clue
provided by the '> prompt. It tells you that
mysql expects to see the rest of an
unterminated string. (Do you see the error in the statement? The
string 'Smith is missing the second single
quote mark.)

At this point, what do you do? The simplest thing is to cancel the
command. However, you cannot just type \c in
this case, because mysql interprets it as part
of the string that it is collecting. Instead, enter the closing
quote character (so mysql knows you've finished
the string), then type \c:

mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'> '\c
mysql>

The prompt changes back to mysql>,
indicating that mysql is ready for a new
command.

The `> prompt is similar to the
'> and "> prompts, but
indicates that you have begun but not completed a backtick-quoted
identifier.

It is important to know what the '>,
">, and `> prompts
signify, because if you mistakenly enter an unterminated string,
any further lines you type appear to be ignored by
mysql — including a line containing
QUIT. This can be quite confusing, especially
if you do not know that you need to supply the terminating quote
before you can cancel the current command.

3.3. Creating and Using a Database

Once you know how to enter commands, you are ready to access a
database.

Suppose that you have several pets in your home (your menagerie)
and you would like to keep track of various types of information
about them. You can do so by creating tables to hold your data and
loading them with the desired information. Then you can answer
different sorts of questions about your animals by retrieving data
from the tables. This section shows you how to:

Create a database

Create a table

Load data into the table

Retrieve data from the table in various ways

Use multiple tables

The menagerie database is simple (deliberately), but it is not
difficult to think of real-world situations in which a similar
type of database might be used. For example, a database like this
could be used by a farmer to keep track of livestock, or by a
veterinarian to keep track of patient records. A menagerie
distribution containing some of the queries and sample data used
in the following sections can be obtained from the MySQL Web site.
It is available in both compressed tar
(http://www.mysql.com/Downloads/Contrib/Examples/menagerie.tar.gz)
and Zip
(http://www.mysql.com/Downloads/Contrib/Examples/menagerie.zip)
formats.

Use the SHOW statement to find out what
databases currently exist on the server:

The list of databases is probably different on your machine, but
the mysql and test databases
are likely to be among them. The mysql database
is required because it describes user access privileges. The
test database is often provided as a workspace
for users to try things out.

Note that USE, like QUIT,
does not require a semicolon. (You can terminate such statements
with a semicolon if you like; it does no harm.) The
USE statement is special in another way, too:
it must be given on a single line.

You can use the test database (if you have
access to it) for the examples that follow, but anything you
create in that database can be removed by anyone else with access
to it. For this reason, you should probably ask your MySQL
administrator for permission to use a database of your own.
Suppose that you want to call yours menagerie.
The administrator needs to execute a command like this:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

where your_mysql_name is the MySQL user name
assigned to you and your_client_host is the
host from which you connect to the server.

3.3.1. Creating and Selecting a Database

If the administrator creates your database for you when setting
up your permissions, you can begin using it. Otherwise, you need
to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case sensitive (unlike SQL
keywords), so you must always refer to your database as
menagerie, not as
Menagerie, MENAGERIE, or
some other variant. This is also true for table names. (Under
Windows, this restriction does not apply, although you must
refer to databases and tables using the same lettercase
throughout a given query. However, for a variety of reasons, our
recommended best practice is always to use the same lettercase
that was used when the database was created.)

Creating a database does not select it for use; you must do that
explicitly. To make menagerie the current
database, use this command:

mysql> USE menagerie
Database changed

Your database needs to be created only once, but you must select
it for use each time you begin a mysql
session. You can do this by issuing a USE
statement as shown in the example. Alternatively, you can select
the database on the command line when you invoke
mysql. Just specify its name after any
connection parameters that you might need to provide. For
example:

shell> mysql -h host -u user -p menagerie
Enter password: ********

Note that menagerie in the command just shown
is not your password. If you
want to supply your password on the command line after the
-p option, you must do so with no intervening
space (for example, as -pmypassword, not as
-p mypassword). However, putting your
password on the command line is not recommended, because doing
so exposes it to snooping by other users logged in on your
machine.

3.3.2. Creating a Table

Creating the database is the easy part, but at this point it's
empty, as SHOW TABLES tells you:

mysql> SHOW TABLES;
Empty set (0.00 sec)

The harder part is deciding what the structure of your database
should be: what tables you need and what columns should be in
each of them.

You want a table that contains a record for each of your pets.
This can be called the pet table, and it
should contain, as a bare minimum, each animal's name. Because
the name by itself is not very interesting, the table should
contain other information. For example, if more than one person
in your family keeps pets, you might want to list each animal's
owner. You might also want to record some basic descriptive
information such as species and sex.

How about age? That might be of interest, but it's not a good
thing to store in a database. Age changes as time passes, which
means you'd have to update your records often. Instead, it's
better to store a fixed value such as date of birth. Then,
whenever you need age, you can calculate it as the difference
between the current date and the birth date. MySQL provides
functions for doing date arithmetic, so this is not difficult.
Storing birth date rather than age has other advantages, too:

You can use the database for tasks such as generating
reminders for upcoming pet birthdays. (If you think this
type of query is somewhat silly, note that it is the same
question you might ask in the context of a business database
to identify clients to whom you need to send out birthday
greetings in the current week or month, for that
computer-assisted personal touch.)

You can calculate age in relation to dates other than the
current date. For example, if you store death date in the
database, you can easily calculate how old a pet was when it
died.

You can probably think of other types of information that would
be useful in the pet table, but the ones
identified so far are sufficient: name, owner, species, sex,
birth, and death.

VARCHAR is a good choice for the
name, owner, and
species columns because the column values
vary in length. The lengths of those columns need not all be the
same, and need not be 20. You can pick any
length from 1 to 255,
whatever seems most reasonable to you. (If you make a poor
choice and it turns out later that you need a longer field,
MySQL provides an ALTER TABLE statement.)

Several types of values can be chosen to represent sex in animal
records, such as 'm' and
'f', or perhaps 'male' and
'female'. It is simplest to use the single
characters 'm' and 'f'.

The use of the DATE data type for the
birth and death columns is
a fairly obvious choice.

Once you have created a table, SHOW TABLES
should produce some output:

You can use DESCRIBE any time, for example,
if you forget the names of the columns in your table or what
types they have.

3.3.3. Loading Data into a Table

After creating your table, you need to populate it. The
LOAD DATA and INSERT
statements are useful for this.

Suppose that your pet records can be described as shown here.
(Observe that MySQL expects dates in
'YYYY-MM-DD' format; this may be different
from what you are used to.)

name

owner

species

sex

birth

death

Fluffy

Harold

cat

f

1993-02-04

Claws

Gwen

cat

m

1994-03-17

Buffy

Harold

dog

f

1989-05-13

Fang

Benny

dog

m

1990-08-27

Bowser

Diane

dog

m

1979-08-31

1995-07-29

Chirpy

Gwen

bird

f

1998-09-11

Whistler

Gwen

bird

1997-12-09

Slim

Benny

snake

m

1996-04-29

Because you are beginning with an empty table, an easy way to
populate it is to create a text file containing a row for each
of your animals, then load the contents of the file into the
table with a single statement.

You could create a text file pet.txt
containing one record per line, with values separated by tabs,
and given in the order in which the columns were listed in the
CREATE TABLE statement. For missing values
(such as unknown sexes or death dates for animals that are still
living), you can use NULL values. To
represent these in your text file, use \N
(backslash, capital-N). For example, the record for Whistler the
bird would look like this (where the whitespace between values
is a single tab character):

name

owner

species

sex

birth

death

Whistler

Gwen

bird

\N

1997-12-09

\N

To load the text file pet.txt into the
pet table, use this command:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

Note that if you created the file on Windows with an editor that
uses \r\n as a line terminator, you should
use:

(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'.)

You can specify the column value separator and end of line
marker explicitly in the LOAD DATA statement
if you wish, but the defaults are tab and linefeed. These are
sufficient for the statement to read the file
pet.txt properly.