Chapter聽2.聽Installing and Upgrading MySQL - MySQL 4.1参考手册英文版

AIX 4.x, 5.x with native threads. See Section聽2.13.5.3, 鈥淚BM-AIX notes鈥�.

Amiga.

FreeBSD 5.x and up with native threads.

HP-UX 11.x with the native threads. See Section聽2.13.5.2, 鈥淗P-UX Version 11.x Notes鈥�.

Linux, builds on all fairly recent Linux distributions with glibc 2.3. See Section聽2.13.1, 鈥淟inux Notes鈥�.

Mac OS X. See Section聽2.13.2, 鈥淢ac OS X Notes鈥�.

NetBSD 1.3/1.4 Intel and NetBSD 1.3 Alpha. See Section聽2.13.4.2, 鈥淣etBSD Notes鈥�.

Novell NetWare 6.0 and 6.5. See Section聽2.7, 鈥淚nstalling MySQL on NetWare鈥�.

OpenBSD 2.5 and with native threads. OpenBSD earlier than 2.5 with the MIT-pthreads package. See Section聽2.13.4.3, 鈥淥penBSD 2.5 Notes鈥�.

SCO OpenServer 5.0.X with a recent port of the FSU Pthreads package. See Section聽2.13.5.8, 鈥淪CO UNIX and OpenServer 5.0.x Notes鈥�.

SCO Openserver 6.0.x. See Section聽2.13.5.9, 鈥淪CO OpenServer 6.0.x Notes鈥�.

SCO UnixWare 7.1.x. See Section聽2.13.5.10, 鈥淪CO UnixWare 7.1.x and OpenUNIX 8.0.0 Notes鈥�.

SGI Irix 6.x with native threads. See Section聽2.13.5.7, 鈥淪GI Irix Notes鈥�.

Solaris 2.5 and above with native threads on SPARC and x86. See Section聽2.13.3, 鈥淪olaris Notes鈥�.

Tru64 Unix. See Section聽2.13.5.5, 鈥淎lpha-DEC-UNIX Notes (Tru64)鈥�.

Windows 9x, Me, NT, 2000, XP, and Windows Server 2003. See Section聽2.3, 鈥淚nstalling MySQL on Windows鈥�.

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 CPU different from 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 large, performance is affected by 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 run MySQL successfully on the platform in similar configurations. If this number is high, the likelihood of encountering platform-specific surprises is much smaller.

MD5 checksums

Cryptographic signatures using GnuPG, the GNU Privacy Guard

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

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.

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.

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聽14.2.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.)

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.

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.

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.

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.

If the MySQL server cannot find the mysql privileges database or other critical files, you may see these messsages:

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

These messages often occur when the MySQL base or data directories are installed in different locations than the default locations (C:\mysql and C:\mysql\data, respectively).

This situation may occur when MySQL is upgraded and installed to a new location, but the configuration file is not updated to reflect the new 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 C:\WINDOWS or C:\WINNT. 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:

[mysqld]
# set basedir to your installation path
basedir=C:\\Program Files\\mysql
# set datadir to the location of your data directory
datadir=D:\\MySQLdata

If you change the datadir value in your MySQL configuration file, you must move the contents of the existing MySQL data directory before restarting the MySQL server.

See Section聽2.3.7, 鈥淐reating an Option File鈥�.

If you reinstall or upgrade MySQL without first stopping and removing the existing MySQL service and install MySQL using the MySQL Configuration Wizard, you may see this error:

Error: Cannot create Windows service for MySql. Error: 0

This occurs when the Configuration Wizard tries to install the service and 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 allows the new service to be installed correctly, but leaves the outdated service in place. Although 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:

C:\> sc delete mysql
[SC] DeleteService SUCCESS

If the sc utility is not available for your version of Windows, download the delsrv utility from http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/delsrv-o.asp and use the delsrv mysql syntax.

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! Newer versions of Windows don't 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.

For more information about this problem, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271.

Concurrent reads

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 that 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. That has the following implications if named-pipe connections are enabled:

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

On Windows 95, you cannot kill MySQL from the Task Manager or with the shutdown utility. 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, 鈥淚dentifier 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:

mysql> LOAD DATA INFILE 'C:/tmp/skr.txt' INTO TABLE skr;
mysql> SELECT * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr;

Alternatively, you must double the 鈥�\鈥� character:

mysql> LOAD DATA INFILE 'C:\\tmp\\skr.txt' INTO TABLE skr;
mysql> SELECT * INTO OUTFILE 'C:\\tmp\\skr.txt' FROM skr;

Problems with pipes

Pipes do not work reliably from the Windows command-line prompt. If the pipe includes the character ^Z / CHAR(24), Windows thinks that it has encountered end-of-file and aborts the program.

This is a problem mainly when you try to apply a binary log as follows:

C:\> mysqlbinlog binary_log_file | mysql --user=root

If you have a problem applying the log and suspect that it is because of a ^Z / CHAR(24) character, you can use the following workaround:

C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
C:\> mysql --user=root --execute "source /tmp/bin.sql"

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 MySQL cannot resolve your hostname properly, you may get the following error when you attempt to run a MySQL client program to connect to a server running on the same machine:

Access denied for user 'some_user'@'unknown'
to database 'mysql'

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

127.0.0.1       localhost

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

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 have no C++ compiler, some client programs such as mysql cannot be compiled because they require 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 all clients, but you can ignore any warnings about files such as 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), 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:

shell> ./configure --prefix=/usr/local/mysql
shell> ./configure --prefix=/usr/local \
           --localstatedir=/usr/local/mysql/data

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.

You can also specify the installation directory and data directory locations at server startup time by using the --basedir and --datadir options. These can be given on the command line or in an MySQL option file, although it is more common to use an option file. See Section聽4.3.2, 鈥淯sing Option Files鈥�.

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

shell> ./configure \
           --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock

The socket filename must be an absolute pathname. You can also change the location of mysql.sock at server startup by using a MySQL option file. See Section聽A.1.4.5, 鈥淗ow to Protect or Change the MySQL Unix Socket File鈥�.

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

shell> ./configure --with-client-ldflags=-all-static \
           --with-mysqld-ldflags=-all-static

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 thing to do even if you have those libraries installed. 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.

In most cases, you can get a reasonably optimized MySQL binary by using the options from the preceding list and adding the following options to the configure line:

--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static

The full configure line would, in other words, be something like the following for all recent gcc versions:

CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro \
-felide-constructors -fno-exceptions -fno-rtti" ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static

The binaries we provide on the MySQL Web site at http://dev.mysql.com/downloads/ are all compiled with full optimization and should be perfect for most users. See Section聽2.1.2.5, 鈥淢ySQL Binaries Compiled by MySQL AB鈥�. There are some configuration settings you can tweak to build an even faster binary, but these are only for advanced users. See Section聽7.5.3, 鈥淗ow Compiling and Linking Affects the Speed of MySQL鈥�.

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 West European) character set. To change the default set, use the --with-charset option:

shell> ./configure --with-charset=CHARSET

CHARSET may be one of big5, cp1251, cp1257, czech, danish, dec8, dos, euc_kr, gb2312, gbk, german1, hebrew, hp8, hungarian, koi8_ru, koi8_ukr, latin1, latin2, sjis, swe7, tis620, ujis, usa7, or win1251ukr. See Section聽5.10.1, 鈥淭he Character Set Used for Data and Sorting鈥�. (Additional character sets might be available. Check the output from ./configure --help for the current list.)

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.)

Warning: If you change character sets after having created any tables, you have to run myisamchk -r -q --set-collation=collation_name on every MyISAM 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. (Use --set-character-set before MySQL 4.1.1.)

With the configure option --with-extra-charsets=LIST, you can define which additional character sets should be compiled into the server. LIST is one of the following:

Clients that want to convert characters between the server and the client should use the SET NAMES statement. See Section聽13.5.3, 鈥�SET Syntax鈥�, and Section聽10.4, 鈥淐onnection Character Sets and Collations鈥�.

To configure MySQL with debugging code, use the --with-debug option:

shell> ./configure --with-debug

This causes a safe memory allocator to be included that can find some errors and that provides output about what is happening. See MySQL Internals: Porting.

If your client programs are using threads, you 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聽17.2.16, 鈥淗ow to Make a Threaded Client鈥�.

It is possible to build MySQL with big table support using the --with-big-tables option, beginning with the following MySQL versions:

This option causes the variables that store table row counts to be declared as unsigned long long rather than unsigned long. This enables tables to hold up to approximately 1.844E+19 ((2³²)²) rows rather than 2³² (~4.295E+09) rows. Previously it was necessary to pass -DBIG_TABLES to the compiler manually in order to enable this feature.

See Section聽2.13, 鈥淥perating System-Specific Notes鈥�, for options that pertain to particular operating systems.

See Section聽5.8.7.2, 鈥淯sing SSL Connections鈥�, for options that pertain to configuring MySQL to support secure (encrypted) connections.

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.

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:

Internal compiler error: program cc1plus got fatal signal 11
Out of virtual memory
Virtual memory exhausted

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 can't 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:

configure: error: installation or configuration problem:
C++ compiler cannot create executables.

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++ source files 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++. However, we recommend that you not use libg++ or libstdc++ with MySQL because this only increases the binary size of mysqld without providing 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:

gcc -O3 -DDBUG_OFF -rdynamic -o isamchk isamchk.o sort.o  libnisam.a
../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a
 -lpthread -lz -lcrypt -lnsl -lm -lpthread
../mysys/libmysys.a(raid.o)(.text+0x79): In function
`my_raid_create':: undefined reference to `operator new(unsigned)'
../mysys/libmysys.a(raid.o)(.text+0xdd): In function
`my_raid_create':: undefined reference to `operator delete(void*)'
../mysys/libmysys.a(raid.o)(.text+0x129): In function
`my_raid_open':: undefined reference to `operator new(unsigned)'
../mysys/libmysys.a(raid.o)(.text+0x189): In function
`my_raid_open':: undefined reference to `operator delete(void*)'
../mysys/libmysys.a(raid.o)(.text+0x64b): In function
`my_raid_close':: undefined reference to `operator delete(void*)'
collect2: ld returned 1 exit status

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:

shell> CC=gcc
shell> CFLAGS=-O3
shell> CXX=gcc
shell> CXXFLAGS=-O3
shell> export CC CFLAGS CXX CXXFLAGS

See Section聽2.1.2.5, 鈥淢ySQL Binaries Compiled by MySQL AB鈥�, for a list of flag definitions that have been found to be useful on various systems.

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. (You must 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 a 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 MySQL Internals: Porting.

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, you probably do not have g++ installed:

libmysql.c:1329: warning: passing arg 5 of `gethostbyname_r' from
incompatible pointer type
libmysql.c:1329: too few arguments to function `gethostbyname_r'
libmysql.c:1329: warning: assignment makes pointer from integer
without a cast
make[2]: *** [libmysql.lo] Error 1

By default, the configure script attempts to determine the correct number of arguments by using g++ (the GNU C++ compiler). This test yields incorrect results if g++ is not installed. There are two ways to work around this problem:

You must run configure again after making either of those changes.

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 file 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 with a value other than localhost 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, but that is not recommended, anyway.

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:

shell> mysqladmin version
mysqladmin: connect to server at '' failed;
error: 'Can't connect to mysql server on localhost (146)'

The solution to this problem 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 isn't interruptible with SIGINT (break). This is noticeable only 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 might receive warning messages like these (at least on Solaris); they can be ignored:

ld: warning: symbol `_iob' has differing sizes:
    (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
file /usr/lib/libc.so value=0x140);
    /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
ld: warning: symbol `__iob' has differing sizes:
    (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
file /usr/lib/libc.so value=0x140);
    /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken

Some other warnings also can be ignored:

implicit declaration of function `int strtoll(...)'
implicit declaration of function `int strtoul(...)'

We have not gotten readline to work with MIT-pthreads. (This is not needed, but may be interesting for someone.)

Windows XP, Windows 2000, or newer version

Visual Studio .Net 2003 (7.1) compiler system

3GB to 5GB of disk space

Obtain a Windows source distribution packaged by MySQL AB for the particular version of MySQL in which you are interested. These are available from http://dev.mysql.com/downloads/.

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. (Some of the configuration and build steps require tools that work only on Unix.) The BitKeeper approach thus requires:

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.

Windows distributions contain preinitialized grant tables that are installed automatically.

On Unix, the grant tables are populated by the mysql_install_db program. Some installation methods run this program for you. Others require that you execute it manually. For details, see Section聽2.10.2, 鈥淯nix Post-Installation Procedures鈥�.

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 root without a password and be granted all privileges.

Two anonymous-user accounts are created, each with an empty username. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts.

You should assign a password to each MySQL root account.

Use the SET PASSWORD statement

Use the mysqladmin command-line client program

Use the UPDATE statement

Check the items in Section聽2.11, 鈥淯pgrading MySQL鈥�, to see whether any of them might affect your applications.

Check the items in the change lists 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.

Read the 4.1 news items to see what significant new features you can use in 4.1. See Section聽B.1, 鈥淐hanges in release 4.1.x (Production)鈥�.

If you are running MySQL Server on Windows, see Section聽2.3.14, 鈥淯pgrading MySQL on Windows鈥�. You should also be aware that two of the Windows MySQL servers were renamed in MySQL 4.1. See Section聽2.3.8, 鈥淪electing a MySQL Server Type鈥�.

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聽5.5.4, 鈥�mysql_fix_privilege_tables 鈥� Upgrade MySQL System 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.

If you are using replication, see Section聽6.6, 鈥淯pgrading a Replication Setup鈥�, for information on upgrading your replication setup.

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.

MySQL 4.1.3 introduces support for per-connection time zones. See Section聽5.10.8, 鈥淢ySQL Server Time Zone Support鈥�. To enable recognition of named time zones, you should create the time zone tables in the mysql database. For instructions, see Section聽2.10, 鈥淧ost-Installation Setup and Testing鈥�.

If you are using an old DBD-mysql module (Msql-MySQL-modules) you must upgrade to the newer DBD-mysql module. Anything above DBD-mysql 2.xx should be satisfactory.

If you do not upgrade, some methods (such as DBI->do()) do not notice error conditions correctly.

The --defaults-file=option-file-name option gives an error if the option file does not exist.

Some notes about upgrading from MySQL 4.0 to MySQL 4.1 on Netware: Make sure to upgrade Perl and PHP versions. Download Perl 5 for Netware from http://forge.novell.com/modules/xfmod/project/?perl5 and PHP from http://forge.novell.com/modules/xfmod/project/?php. Download and install the Perl module for MySQL 4.1 from http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1126 and the PHP Extension for MySQL 4.1 from http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1078.

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.

Incompatible change: MySQL interprets length specifications in character column definitions in characters. (Earlier versions interpret them in bytes.) For example, CHAR(N) means N characters, not N bytes.

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);

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 data 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.

Incompatible change: For ENUM columns that had enumeration values containing commas, the commas were mapped to 0xff internally. However, this rendered the commas indistinguishable from true 0xff characters in the values. This no longer occurs. However, the fix requires that you dump and reload any tables that have ENUM columns containing true 0xff in their values: Dump the tables using mysqldump with the current server before upgrading from a version of MySQL 4.1 older than 4.1.23 to version 4.1.23 or newer.

Incompatible change: The interface to aggregate user-defined functions changed as of MySQL 4.1.1. You must declare a xxx_clear() function for each aggregate function XXX(). xxx_clear() is used instead of xxx_reset(). See Section聽19.2.4.2, 鈥淯DF Calling Sequences for Aggregate Functions鈥�.

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.

If you have table columns that store character data represented in a character set that the 4.1 server supports directly, you can convert the columns to the proper character set using the instructions in Section聽10.9.2, 鈥淐onverting 4.0 Character Columns to 4.1 Format鈥�. Also, database, table, and column identifiers are stored internally using Unicode (UTF-8) regardless of the default character set. See Section聽9.2, 鈥淒atabase, Table, Index, Column, and Alias Names鈥�.

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.12, 鈥�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.21, the lc_time_names system variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. See Section聽5.10.9, 鈥淢ySQL Server Locale Support鈥�.

As of MySQL 4.1.10a, the server by default no longer loads user-defined functions (UDFs) 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聽19.2.4.6, 鈥淯ser-Defined Function Security Precautions鈥�.

mysqldump has the --opt and --quote-names options enabled by default. You can turn these off using --skip-opt and --skip-quote-names.

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.

Incompatible change: TIMESTAMP is returned in MySQL 4.1 as a string in 'YYYY-MM-DD HH:MM:SS' format. (See Section聽11.3.1.2, 鈥�TIMESTAMP Properties as of MySQL 4.1鈥�.) From 4.0.12 on, the --new option can be used to make a 4.0 server behave as 4.1 in this respect. The effect of this option is described in Section聽11.3.1.1, 鈥�TIMESTAMP Properties Prior to MySQL 4.1鈥�.

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:

mysql> SELECT CAST(0xFEFF AS UNSIGNED INTEGER)
    ->        < CAST(0xFF AS UNSIGNED INTEGER);
        -> 0

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.13, conversion of DATETIME values to numeric form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format.

Incompatible change: In MySQL 4.1.12, the behavior of LOAD DATA INFILE and SELECT ... INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values both are empty. Formerly, a column was read or written the display width of the column. For example, INT(4) was read or written using a field with a width of 4. Now columns are read and written using a field width wide enough to hold all values in the field. However, data files written before this change was made might not be reloaded correctly with LOAD DATA INFILE for MySQL 4.1.12 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE. For more information, see Section聽13.2.5, 鈥�LOAD DATA INFILE Syntax鈥�.

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.

Incompatible change: The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE ...

We did not make this change in 4.0 to avoid breaking any old 4.0 applications that were using the old syntax. However, if you use such DELETE statements and are using replication, the change in syntax means that a 4.0 master cannot replicate to 4.1 (or higher) slaves.

Some keywords are reserved in MySQL 4.1 that were not reserved in MySQL 4.0. See Section聽9.3, 鈥淩eserved Words鈥�.

The LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section聽13.6.2.2, 鈥�LOAD DATA FROM MASTER Syntax鈥�, for recommended alternatives.

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.

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.

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:

if (mysql_real_query(mysql_object, query, query_length) == -1)
{
  printf("Got error");
}

Change the call to test for a non-zero value instead:

if (mysql_real_query(mysql_object, query, query_length) != 0)
{
  printf("Got error");
}

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聽17.2.4, 鈥淐 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.

Check the items in Section聽2.11, 鈥淯pgrading MySQL鈥�, to see whether any of them might affect your applications.

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.

Read the 4.0 changelog to see what significant new features you can use in 4.0. See Section聽B.2, 鈥淐hanges in release 4.0.x鈥�.

If you are running MySQL Server on Windows, see Section聽2.3.14, 鈥淯pgrading MySQL on Windows鈥�.

After upgrading, update the grant tables to add new privileges and features. This procedure uses the mysql_fix_privilege_tables script and is described in Section聽5.5.4, 鈥�mysql_fix_privilege_tables 鈥� Upgrade MySQL System Tables鈥�.

If you are using replication, see Section聽6.6, 鈥淯pgrading a Replication Setup鈥�, for information on upgrading your replication setup.

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 storage engine 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.

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聽19.2.4.6, 鈥淯ser-Defined Function Security Precautions鈥�.

MySQL 4.0 has many new privileges in the mysql.user table. See Section聽5.7.3, 鈥淧rivileges Provided by MySQL鈥�.

In order for these new privileges to work, you must update the grant tables. The procedure for this is described in Section聽5.5.4, 鈥�mysql_fix_privilege_tables 鈥� Upgrade MySQL System 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.

From version 4.0.2 on, the option --safe-show-database is deprecated (and no longer does anything). See Section聽5.6.3, 鈥淪ecurity-Related mysqld Options鈥�.

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:

The startup options record_buffer, sort_buffer, and warnings still work in MySQL 4.0 but are deprecated.

Some keywords are reserved in MySQL 4.0 that were not reserved in MySQL 3.23. See Section聽9.3, 鈥淩eserved Words鈥�.

The following SQL variables have been renamed:

The older names still work in MySQL 4.0 but are deprecated.

You must use SET GLOBAL SQL_SLAVE_SKIP_COUNTER=skip_count instead of SET SQL_SLAVE_SKIP_COUNTER=skip_count.

SHOW MASTER STATUS returns an empty set if binary logging is not enabled.

SHOW SLAVE STATUS returns an empty set if the slave is not initialized.

SHOW INDEX has two more columns in 4.0 than in 3.23 (Null and Index_type).

The format of SHOW OPEN TABLES changed.

As of MySQL 4.0.11, ORDER BY col_name DESC sorts NULL values last. In 3.23 and in earlier 4.0 versions, this was not always consistent.

CHECK, LOCALTIME, and LOCALTIMESTAMP are reserved words.

DOUBLE and FLOAT columns honor the UNSIGNED flag on storage (previously, UNSIGNED was ignored for these columns).

The result of all bitwise operators (|, &, <<, >>, and ~) is unsigned. This may cause problems if you are using them in a context where you want a signed result. See Section聽12.9, 鈥淐ast Functions and Operators鈥�.

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.6, 鈥淪QL Modes鈥�.

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.8.5, 鈥淔ine-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).

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.

Multi-threaded clients should use mysql_thread_init() and mysql_thread_end(). See Section聽17.2.16, 鈥淗ow to Make a Threaded Client鈥�.

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.