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 come up with and with the best compiler options, in an attempt to make it suitable for a high-load server. So if you read the following text, and are in doubt about what you should do, try our binary first to see if it meets your needs, and worry about your own build only after you have discovered that our binary is not good enough. In that case, we would appreciate a note about it, so we can build a better binary next time. For a typical user, even for setups with a lot of concurrent connections and/or tables exceeding 2GB limit, our binary in most cases is the best choice.
MySQL uses LinuxThreads on Linux. If you are using an old Linux version that doesn’t have glibc2, you must install LinuxThreads before trying to compile MySQL. You can get LinuxThreads at http://www.mysql.com/Downloads/Linux/.
Note: We have seen some strange problems with Linux 2.2.14 and MySQL on SMP systems. If you have a SMP system, we recommend you upgrade to Linux 2.4 as soon as possible! Your system will be faster and more stable by doing this!
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 do INSERT DELAYED. We recommend that you not use INSERT DELAYED before upgrading glibc.
If you plan to have 1000+ concurrent connections, you will need to make
some changes to LinuxThreads, recompile it, and relink MySQL against
the new libpthread.a. Increase PTHREAD_THREADS_MAX in
sysdeps/unix/sysv/linux/bits/local_lim.h to 4096 and decrease
STACK_SIZE in linuxthreads/internals.h to 256 KB. The paths are
relative to the root of glibc Note that MySQL will not be
stable with around 600-1000 connections if STACK_SIZE is the default
of 2 M.
If MySQL can’t open enough files, or connections, it may be that you haven’t configured Linux to handle enough files.
In Linux 2.2 and onward, you can check the number of allocated file handlers by doing:
cat /proc/sys/fs/file-max cat /proc/sys/fs/dquot-max cat /proc/sys/fs/super-max
If you have more than 16M of memory, you should add something like the
following in your boot script (/etc/rc/boot.local on SuSE):
echo 65536 > /proc/sys/fs/file-max echo 8192 > /proc/sys/fs/dquot-max echo 1024 > /proc/sys/fs/super-max
You can also run the preceding commands from the command-line as root, but in this case your old limits will be used the next time your computer reboots.
You should also add /etc/my.cnf:
[safe_mysqld] open-files-limit=8192
This should allow MySQL to create up to 8192 connections + 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 will be plenty of room for the stack of each individual thread, but small enough to keep the stack of some threads from running into the global mysqld data. Unfortunately, the Linux implementation of mmap( ), as we have experimentally discovered, will successfully unmap an already mapped region if you ask it to map out an address already 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 the “gentleman” behavior of the code that creates threads. The user must take measures to make sure 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 “gentleman” behavior by setting a reasonable value for the max_connections variable.
If you build MySQL yourself and do not want to mess with patching 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 the values between 128K and 256K.
If you use a lot of concurrent connections, you may suffer from a “feature” in the 2.2 kernel that penalises a process for forking or cloning a child in an attempt to prevent a fork bomb attack. This will cause MySQL not to scale well as you increase the number of concurrent clients. On single-CPU systems, we have seen this manifested in a very slow thread creation, which means it may take a long time to connect to MySQL (as long as 1 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 now 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 now recommend it to our users who are still running 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 just upgrade to 2.4, which will also give you a nice SMP boost in addition to fixing this fairness bug.
We have tested MySQL on the 2.4 kernel on a 2-CPU machine and found MySQL scales much better—there was virtually no slowdown on queries throughput all the way up to 1000 clients, and the MySQL scaling factor (computed as the ratio of maximum throughput to the throughput with one client) was 180%. We have observed similar results on a 4-CPU system—virtually no slowdown as the number of clients was increased up to 1000, and 300% scaling factor. So for a high-load SMP server we would definitely recommend 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 the renice -20 $$ command to safe_mysqld. In our testing on a 4-CPU machine, increasing the priority gave a 60% increase in throughput with 400 clients.
We are currently also trying to collect more info on how well MySQL performs on the 2.4 kernel on 4-way and 8-way systems. If you have access to such a system and have done some benchmarks, please send an email to docs@mysql.com with the results. We will include them in the manual.
There is another issue that greatly hurts MySQL performance, especially on SMP systems. The implementation of mutex in LinuxThreads in glibc-2.1 is very bad for programs with many threads that only hold the mutex for a short time. On an SMP system, ironic as it is, if you link MySQL against unmodified LinuxThreads, removing processors from the machine 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 version 3.23.36 will use 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 chance of this condition 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 will need to apply it in the linuxthreads directory with the patch -p0 </tmp/linuxthreads-2.2.2.patch. We hope it will be 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 will be corrected to some more acceptable values for high-load MySQL setup in the future so that your own build can be reduced to ./configure; make; make install.
We recommend that you use the preceding 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 against the patched version of the library, or build a patched shared version and install it on your system, you are doing it at your own risk with regard to other applications that depend on LinuxThreads.
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 will resolve them.
One known problem with the binary distribution is that with older Linux systems that use libc (like RedHat 4.x or Slackware), you will get some non-fatal problems with hostname resolution. See Section 2.6.1.1.
When using LinuxThreads you will see a minimum of three processes running. These are in fact threads. There will be one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals.
Note that the Linux kernel and the LinuxThread library can by default only have 1024 threads. This means that you can only have up to 1021 connections to MySQL on an unpatched system. The page http://www.volano.com/linuxnotes.html contains information on how to go around this limit.
If you see a dead mysqld daemon process with ps, this usually means that you have found a bug in MySQL or you have a corrupted table. See Section A.4.1.
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 safe_mysqld or starting safe_mysqld with --core-file-sizes=1000000. See Section 4.7.2.
If you are linking your own MySQL client and get the error:
ld.so.1: ./my: fatal: libmysqlclient.so.4: open failed: No such file or directory
when executing them, the problem can be avoided by one of the following methods:
If you are using the Fujitsu compiler (fcc / FCC) you will have some problems compiling MySQL because the Linux header files are very gcc-oriented.
The following configure line should work with fcc/FCC:
CC=fcc CFLAGS="-O -K fast -K lib -K omitfp -Kpreex -D_GNU_SOURCE \ -DCONST=const -DNO_STRTOLL_PROTO" CXX=FCC CXXFLAGS="-O -K fast -K lib \ -K omitfp -K preex --no_exceptions --no_rtti -D_GNU_SOURCE -DCONST=const \ -Dalloca=__builtin_alloca -DNO_STRTOLL_PROTO \ '-D_EXTERN_INLINE=static __inline'" ./configure --prefix=/usr/local/mysql \ --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared \ --with-low-memory
MySQL needs at least Linux Version 2.0.
Warning: We have reports from some MySQL users that they have experienced serious stability problems with MySQL with Linux kernel 2.2.14. If you are using this kernel you should upgrade to 2.2.19 (or newer) or to a 2.4 kernel. If you have a multi-CPU box, then you should seriously consider using 2.4, as this will give you a significant speed boost.
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 bigger than a dynamically linked program but also slightly faster (3-5%). One problem, however, is that you can’t use user-definable functions (UDFs) with a statically linked program. If you are going to write or use UDF functions (this is something only for C or C++ programmers), you must compile MySQL yourself, using dynamic linking.
If you are using a libc-based system (instead of a glibc2 system), you will probably get some problems with hostname resolving and getpwnam( ) with the binary release. (This is because glibc unfortunately depends on some external libraries to resolve hostnames and getpwent( ), even when compiled with -static.) In this case you probably get the following error message when you run mysql_install_db:
Sorry, the host 'xxxx' could not be looked up
or the following error when you try to run mysqld with the --user option:
getpwnam: No such file or directory
You can solve this problem in one of the following ways:
Get a MySQL source distribution (an RPM or the tar.gz distribution) and install this instead.
Execute mysql_install_db --force. This will not execute the resolveip test in mysql_install_db. The downside is that you can’t use hostnames in the grant tables; you must use IP numbers instead (except for localhost). If you are using an old MySQL release that doesn’t support --force, you have to remove the resolveip test in mysql_install with an editor.
Start mysqld with su instead of using --user.
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.
MySQL Perl support requires Version Perl 5.004_03 or newer.
On some Linux 2.2 versions, you may get the error Resource temporarily unavailable when you do a lot of new connections to a mysqld server over TCP/IP.
The problem is that Linux has a delay between when you close a TCP/IP
socket and until this is actually freed by the system. As there is only
room for a finite number of TCP/IP slots, you will get the preceding error if
you try to do too many new TCP/IP connections during a small time, like
when you run the MySQL test-connect benchmark over
TCP/IP.
We have mailed about this problem a couple of times to different Linux mailing lists but have never been able to resolve this properly.
The only known ‘fix’ to this problem is to use persistent connections in your clients or to use sockets, if you are running the database server and clients on the same machine. We hope that the Linux 2.4 kernel will fix this problem in the future.
MySQL requires libc Version 5.4.12 or newer. It’s known to work with libc 5.4.46. glibc Version 2.0.6 and later should also work. There have been some problems with the glibc RPMs from RedHat, 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 gcc 3.0 and above to compile MySQL, you must install the libstdc++v3 library before compiling MySQL; if you don’t do this you will 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 and add an extra underscore to the _P macro that has only one underscore, then try again.
You may get some warnings when compiling; the following can be ignored:
mysqld.cc -o objs-thread/mysqld.o mysqld.cc: In function `void init_signals( )': mysqld.cc:315: warning: assignment of negative value `-1' to `long unsigned int' mysqld.cc: In function `void * signal_hand(void *)': mysqld.cc:346: warning: assignment of negative value `-1' to `long unsigned int'
In Debian GNU/Linux, if you want MySQL to start automatically when the system boots, do the following:
shell> cp support-files/mysql.server /etc/init.d/mysql.server shell> /usr/sbin/update-rc.d mysql.server defaults 99
mysql.server can be found in the share/mysql directory
under the MySQL installation directory or in the
support-files directory of the MySQL source tree.
If mysqld always core dumps when it starts up, the problem may be that
you have an old /lib/libc.a. Try renaming it, 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:
/usr/lib/libc.a(putc.o): In function `_IO_putc': putc.o(.text+0x0): multiple definition of `_IO_putc'
You can avoid using libg++.a by running configure like this:
shell> CXX=gcc ./configure
If you are running gcc 3.0 and above, you can’t use the preceding trick with a setting to CXX=gcc.
In some implementations, readdir_r( ) is broken. The symptom is that
SHOW DATABASES always returns an empty set. This can be fixed by
removing HAVE_READDIR_R from config.h after configuring and
before compiling.
Some problems will require patching your Linux installation. The patch can
be found at
http://www.mysql.com/Downloads/patches/Linux-sparc-2.0.30.diff.
This patch is against the Linux distribution sparclinux-2.0.30.tar.gz
that is available at vger.rutgers.edu (a version of Linux that was
never merged with the official 2.0.30). You must also install LinuxThreads
Version 0.6 or newer.
MySQL Version 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. The main thing we haven’t yet had time to test is how things works with many concurrent users.
When we compiled the standard MySQL binary, we were using SuSE 6.4, kernel 2.2.13-SMP, Compaq C compiler (V6.2-504), and Compaq C++ compiler (V6.3-005) on a Comaq DS20 machine with an Alpha EV6 processor.
You can find the above compilers at http://www.support.compaq.com/alpha-tools/). By using these compilers, instead of gcc, we get about 9-14% better performance with MySQL.
Note that the configure line optimised the binary for the current CPU; this means you can only use our binary if you have an Alpha EV6 processor. We also compile statically to avoid library problems.
CC=ccc CFLAGS="-fast" CXX=cxx CXXFLAGS="-fast -noexceptions -nortti" \ ./configure --prefix=/usr/local/mysql --disable-shared \ --with-extra-charsets=complex --enable-thread-safe-client \ --with-mysqld-ldflags=-non_shared --with-client-ldflags=-non_shared
If you want to use egcs, the following configure line worked for us:
CFLAGS="-O3 -fomit-frame-pointer" CXX=gcc \ CXXFLAGS="-O3 -fomit-frame-pointer -felide-constructors \ -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \ --disable-shared
Some known problems when running MySQL on Linux-Alpha:
Debugging threaded applications like MySQL will not work with gdb 4.18. You should download and use gdb 5.1 instead!
If you try linking mysqld statically when using gcc, the resulting image will core dump at startup. In other words, don’t use --with-mysqld-ldflags=-all-static with gcc.
MySQL should work on MkLinux with the newest glibc package (tested with glibc 2.0.7).
To get MySQL to work on Qube2, (Linux Mips), you need the newest glibc libraries (glibc-2.0.7-29C2 is known to work). You must also use the egcs C++ compiler (egcs-1.0.2-9, gcc 2.95.2 or newer).
To get MySQL to compile on Linux IA64, we use the following compile line, using gcc-2.96:
CC=gcc CFLAGS="-O3 -fno-omit-frame-pointer" CXX=gcc \ CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors \ -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \ "--with-comment=Official MySQL binary" --with-extra-charsets=complex
On IA64 the MySQL client binaries are using shared libraries. This means
that if you install our binary distribution in some other place than
/usr/local/mysql you need to either modify /etc/ld.so.conf
or add the path to the directory where you have libmysqlclient.so
to the LD_LIBRARY_PATH environment variable.
See Section A.3.1.
This section describes using MySQL on Windows. This information
is also provided in the README file that comes with the
MySQL Windows distribution. See Section 2.1.2.
MySQL uses TCP/IP to connect a client to a server. (This will allow any machine on your network to connect to your MySQL server.) Because of this, you must install TCP/IP 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’s 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.
To start the mysqld server, you should start an MS-DOS window and type:
C:\> C:\mysql\bin\mysqld
This will start mysqld in the background without a window.
You can kill the MySQL server by executing:
C:\> C:\mysql\bin\mysqladmin -u root shutdown
This calls the MySQL administration utility as user `root', which is the default Administrator in the MySQL grant system. Please note that the MySQL grant system is wholly independent from any login users under Windows.
Note that Windows 95/98/Me don’t support creation of named pipes. So on those platforms, you can only use named pipes to connect to a remote MySQL server running on a Windows NT/2000/XP server host. (The MySQL server must also support named pipes, of course. For example, using mysqld-opt under NT/2000/XP will not allow named pipe connections. You should use either mysqld-nt or mysqld-max-nt.)
If mysqld doesn’t start, please check the
\mysql\data\mysql.err file to see if the server wrote any
message there to indicate the cause of the problem. You can also
try to start the server with mysqld --standalone; 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 will write a log file
C:\mysqld.trace that should contain the reason why
mysqld doesn’t start. See Section D.1.2.
Use mysqld --help to display all the options that mysqld understands!
To get MySQL to work with TCP/IP on Windows NT 4, you must install service pack 3 (or newer)!
Normally you should install MySQL as a service on Windows NT/2000/XP. In case the server was already running, first stop it using the following command:
C:\mysql\bin> mysqladmin -u root shutdown
This calls the MySQL administration utility as user `root', which is the default Administrator in the MySQL grant system. Please note that the MySQL grant system is wholly independent from any login users under Windows.
Now install the server service:
C:\mysql\bin> mysqld-max-nt --install
If any options are required, they must be specified as "Start parameters" in the Windows Services utility before you start the MySQL service.
The Services utility (Windows Service Control Manager) can be found in the Windows Control Panel (under Administrative Tools on Windows 2000). It is advisable to close the Services utility while performing the --install or --remove operations prevent some odd errors.
For information about which server binary to run, see Section 2.1.2.2.
Please note that from MySQL Version 3.23.44, you have the choice of setting up the service as Manual instead (if you don’t wish the service to be started automatically during the boot process):
C:\mysql\bin> mysqld-max-nt --install-manual
The service is installed with the name MySQL. Once installed, it can be immediately started from the Services utility, or by using the command NET START MySQL.
Once running, mysqld-max-nt can be stopped using mysqladmin from the Services utility, or by using the command NET STOP MySQL.
When running as a service, the operating system will automatically stop the MySQL service on computer shutdown. In MySQL versions < 3.23.47, Windows only waited for a few seconds for the shutdown to complete, and killed the database server process if the time limit was exceeded (potentially causing problems). For instance, at the next startup the InnoDB table handler had to do crash recovery. Starting from MySQL Version 3.23.48, Windows will wait longer for the MySQL server shutdown to complete. If you notice this is not enough for your installation, it is safest to run the MySQL server not as a service, but from the Command prompt, and shut it down with mysqladmin shutdown.
There is a problem that Windows NT (but not Windows 2000/XP) by default only
waits 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, 120000, to have Windows NT wait up to 120 seconds.
Please note that when run as a service, mysqld-max-nt
has no access to a console and so no messages can be seen.
Errors can be checked in c:\mysql\data\mysql.err.
If you have problems installing mysqld-max-nt as a service, try starting it with the full path:
C:\> C:\mysql\bin\mysqld-max-nt --install
If this doesn’t work, you can get mysqld-max-nt to start properly by fixing the path in the registry!
If you don’t want to start mysqld-max-nt as a service, you can start it as follows:
C:\> C:\mysql\bin\mysqld-max-nt --standalone
or
C:\> C:\mysql\bin\mysqld --standalone --debug
The last method gives you a debug trace in
C:\mysqld.trace. See Section D.1.2.
MySQL supports TCP/IP on all Windows platforms and named pipes on NT/2000/XP. The default is to use named pipes for local connections on NT/2000/XP and TCP/IP for all other cases if the client has TCP/IP installed. The hostname specifies which protocol is used:
|
Hostname |
Protocol |
|---|---|
|
NULL (none) |
On NT/2000/XP, try named pipes first; if that doesn’t work, use TCP/IP. On 9x/Me, TCP/IP is used. |
|
. |
Named pipes. |
|
localhost |
TCP/IP to current host. |
|
hostname |
TCP/IP. |
You can force a MySQL client to use named pipes by specifying the --pipe option or by specifying . as the hostname. Use the --socket option to specify the name of the pipe.
Note that starting from 3.23.50, named pipes are only enabled if mysqld is started with --enable-named-pipe. This is because some users have experienced problems shutting down the MySQL server when one uses named pipes.
You can test whether MySQL is working by executing the following commands:
C:\> C:\mysql\bin\mysqlshow C:\> C:\mysql\bin\mysqlshow -u root mysql C:\> C:\mysql\bin\mysqladmin version status proc C:\> C:\mysql\bin\mysql test
If mysqld is slow to answer to connections on Windows 9x/Me, there is probably a problem with your DNS. In this case, start mysqld with --skip-name-resolve and use only localhost and IP numbers in the MySQL grant tables. You can also avoid DNS when connecting to a mysqld-nt MySQL server running on NT/2000/XP by using the --pipe argument to specify use of named pipes. This works for most MySQL clients.
There are two versions of the MySQL command-line tool:
|
Binary |
Description |
|---|---|
|
mysql |
Compiled on native Windows, which offers very limited text-editing capabilities. |
|
mysqlc |
Compiled with the Cygnus GNU compiler and libraries, which offers readline editing. |
If you want to use mysqlc.exe, you must copy
C:\mysql\lib\cygwinb19.dll to your Windows system directory
(\windows\system or similar place).
The default privileges on Windows give all local users full privileges to all databases without specifying a password. To make MySQL more secure, you should set a password for all users and remove the row in the mysql.user table that has Host='localhost' and User=".
You should also add a password for the root user. The following example starts by removing the anonymous user that can be used by anyone to access the test database, then sets a root user password:
C:\> C:\mysql\bin\mysql mysql mysql> DELETE FROM user WHERE Host='localhost' AND User=''; mysql> QUIT C:\> C:\mysql\bin\mysqladmin reload C:\> C:\mysql\bin\mysqladmin -u root password your_password
After you’ve set the password, if you want to take down the mysqld server, you can do so using this command:
C:\> mysqladmin --user=root --password=your_password shutdown
If you are using the old shareware version of MySQL Version 3.21 under Windows, the preceding command will fail with an error: parse error near 'SET OPTION password'. The fix is to upgrade to the current MySQL version, which is freely available.
With the current MySQL versions you can easily add new users and change privileges with GRANT and REVOKE commands. See Section 4.3.1.
Here is a note about how to connect to get a secure connection to remote MySQL server with SSH (by David Carlson, dcarlson@mplcomm.com):
Install an SSH client on your Windows machine. As a user, the best non-free one I’ve found is from SecureCRT from http://www.vandyke.com/. Another option is f-secure from http://www.f-secure.com/. You can also find some free ones on Google at http://directory.google.com/Top/Computers/Security/Products_and_Tools/Cryptography/SSH/Clients/Windows/.
Start your Windows SSH client. Set Host_Name = yourmysqlserver_URL_or_IP. Set userid=your_userid to log in to your server (probably not the same as your MySQL login/password.
Set up port forwarding. Either do a remote forward (set local_port: 3306, remote_host: yourmysqlservername_or_ip, remote_port: 3306 ) or a local forward (set port: 3306, host: localhost, remote port: 3306).
Save everything, otherwise you’ll have to redo it the next time.
Log in to your server with the SSH session you just created.
On your Windows machine, start some ODBC application (such as Access).
Create a new file in Windows and link to MySQL using the ODBC driver the same way you normally do, except type in localhost for the MySQL host server, not yourmysqlservername.
You should now have an ODBC connection to MySQL, encrypted using SSH.
Beginning with MySQL Version 3.23.16, the mysqld-max and mysql-max-nt servers in the MySQL distribution are compiled with the -DUSE_SYMDIR option. This allows you to put a database on different disks by adding a symbolic link to it (in a manner similar to the way that symbolic links work on Unix).
On Windows, you make a symbolic link to a database by creating a file
that contains the path to the destination directory and saving this in
the mysql_data directory under the filename database.sym.
Note that the symbolic link will be used only if the directory
mysql_data_dir\database doesn’t exist.
For example, if the MySQL data directory is C:\mysql\data
and you want to have database foo located at D:\data\foo, you
should create the file C:\mysql\data\foo.sym that contains the
text D:\data\foo\. After that, all tables created in the database
foo will be created in D:\data\foo.
Note that because of the speed penalty you get when opening every table,
we have not enabled this by default even if you have compiled
MySQL with support for this. To enable symlinks you should put
the following entry into your my.cnf or my.ini file:
[mysqld] use-symbolic-links
In MySQL 4.0 we will enable symlinks by default. Then you should instead use the skip-symlink option if you want to disable this.
In your source files, you should include windows.h before you include
mysql.h:
#if defined(_WIN32) || defined(_WIN64) #include <windows.h> #endif #include <mysql.h>
You can either link your code with the dynamic libmysql.lib library,
which is just a wrapper to load in libmysql.dll on demand, or link
with the static mysqlclient.lib library.
Note that as the mysqlclient libraries are compiled as threaded libraries, you should also compile your code to be multi-threaded!
MySQL-Windows has by now proven itself to be very stable. This 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 shouldn’t run mysqld for an extended time on Windows 95 if your server handles many connections! Other versions of Windows don’t suffer from this bug.
- 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/XP to get more speed. The current implementation limits the number of open files MySQL can use to 1024, which means that you will not be able to run as many concurrent threads on NT/2000/XP as on Unix.
- Blocking read
MySQL uses a blocking read for each connection. This means that:
A connection will not be disconnected automatically after 8 hours, as happens with the Unix version of MySQL.
If a connection hangs, it’s impossible to break it without killing MySQL.
mysqladmin kill will not work on a sleeping connection.
mysqladmin shutdown can’t abort as long as there are sleeping connections.
We plan to fix this problem when our Windows developers have figured out a nice workaround.
- DROP DATABASE
You can’t drop a database that is in use by some thread.
- Killing MySQL from the task manager
You can’t kill MySQL from the task manager or with the shutdown utility in Windows 95. You must take it down with mysqladmin shutdown.
- Case-insensitive names
Filenames are case-insensitive on Windows, so database and table names are also case-insensitive in MySQL for Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See Section 6.1.3.
-
The
\directory character Pathname components in Windows 95 are separated by the
\character, which is also the escape character in MySQL. If you are using LOAD DATA INFILE or SELECT ... INTO OUTFILE, 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;
Alternatively, 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;
- Can't open named pipe error
If you use a MySQL 3.22 version on NT with the newest mysql-clients you will get the following error:
error 2017: can't open named pipe to host: . pipe...
This is because the release version of MySQL uses named pipes on NT by default. You can avoid this error by using the --host=localhost option to the new MySQL clients or create an option file
C:\my.cnfthat contains the following information:[client] host = localhost
Starting from 3.23.50, named pipes are only enabled if mysqld is started with --enable-named-pipe.
- Access denied for user error
If you get the error Access denied for user: 'some-user@unknown' to database 'mysql' when accessing a MySQL server on the same machine, this means that MySQL can’t resolve your hostname properly.
To fix this, you should create a file
\windows\hostswith the following information:127.0.0.1 localhost
- ALTER TABLE
While you are executing an ALTER TABLE statement, the table is locked from usage by other threads. This has to do with the fact that on Windows, you can’t delete a file that is in use by another threads. (In the future, we may find some way to work around this problem.)
DROP TABLE on a table that is in use by a MERGE table will not work on Windows because MERGE handler does the table mapping hidden from the upper layer of MySQL. Because Windows doesn’t 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 directives in CREATE TABLE are ignored on Windows because Windows doesn’t support symbolic links.
Here are some open issues for anyone who might want to help us with the Windows release:
Make a single-user MYSQL.DLL server. This should include everything in a standard MySQL server, except thread creation. This will make MySQL much easier to use in applications that don’t need a true client/server and don’t need to access the server from other hosts.
Add some nice start and shutdown icons to the MySQL installation.
When registering mysqld as a service with --install (on NT) it would be nice if you could also add default options on the command-line. For the moment, the workaround is to list the parameters in the
C:\my.cnffile instead.It would be real nice to be able to kill mysqld from the task manager. For the moment, you must use mysqladmin shutdown.
Port readline to Windows for use in the mysql command-line tool.
GUI versions of the standard MySQL clients (mysql, mysqlshow, mysqladmin, and mysqldump) would be nice.
It would be nice if the socket read and write functions in
net.cwere interruptible. This would make it possible to kill open threads with mysqladmin kill on Windows.mysqld always starts in the “C” locale and not in the default locale. We would like to have mysqld use the current locale for the sort order.
Add macros to use the faster thread-safe increment/decrement methods provided by Windows.
Other Windows-specific issues are described in the README file that
comes with the MySQL-Windows distribution.
On Solaris, you may run into trouble even before you get the MySQL distribution unpacked! Solaris tar can’t handle long filenames, so you may see an error like this when you unpack MySQL:
x mysql-3.22.12-beta/bench/Results/ATIS-mysql_odbc-NT_4.0-cmp-db2,\ informix,ms-sql,mysql,oracle,solid,sybase, 0 bytes, 0 tape blocks tar: directory checksum error
In this case, you must use GNU tar (gtar) to unpack the distribution. You can find a precompiled copy for Solaris at http://www.mysql.com/Downloads/.
Sun-native threads work only on Solaris 2.5 and higher. For Version 2.4 and earlier, MySQL will automatically use MIT-pthreads. See Section 2.3.6.
If you get the following error from configure:
checking for restartable system calls... configure: error can not run test programs while cross compiling
this means that you have something wrong with your compiler installation!
In this case you should upgrade your compiler to a newer version. You may
also be able to solve this problem by inserting the following row into the
config.cache file:
ac_cv_sys_restartable_syscalls=${ac_cv_sys_restartable_syscalls='no'}If you are using Solaris on a SPARC, the recommended compiler is gcc 2.95.2. You can find this at http://gcc.gnu.org/. Note that egcs 1.1.1 and gcc 2.8.1 don’t work reliably on SPARC!
The recommended configure line when using gcc 2.95.2 is:
CC=gcc CFLAGS="-O3" \ CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" \ ./configure --prefix=/usr/local/mysql --with-low-memory --enable-assembler
If you have an UltraSPARC, you can get 4% more performance by adding “-mcpu=v8 -Wa,-xarch=v8plusa” to CFLAGS and CXXFLAGS.
If you have a Sun Workshop (Fortre) 5.3 (or newer) compiler, you can run configure like this:
CC=cc CFLAGS="-Xa -fast -xO4 -native -xstrconst -mt" \ CXX=CC CXXFLAGS="-noex -xO4 -mt" \ ./configure --prefix=/usr/local/mysql --enable-assembler
In the MySQL benchmarks, we got a 6% speedup on an UltraSPARC when using Sun Workshop 5.3 compared to using gcc with -mcpu flags.
If you get a problem with fdatasync or sched_yield, you can fix this by adding LIBS=-lrt to the configure line.
The following paragraph is relevant only for compilers older than WorkShop 5.3:
You may also have to edit the configure script to 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
can’t 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 multi-thread option (-mt):
libc internal error: _rmutex_unlock: rmutex not held
Add -mt to CFLAGS and CXXFLAGS and try again.
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. You should recompile gcc and GNU binutils on the machine you will be running them from to avoid any problems.
If you get the following error when compiling MySQL with gcc, it means that your gcc is not configured for your version of Solaris:
shell> gcc -O3 -g -O2 -DDBUG_OFF -o thr_alarm ... ./thr_alarm.c: In function `signal_hand': ./thr_alarm.c:556: too many arguments to function `sigwait'
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 will break all programs that use threads (and possibly other programs)!
Solaris doesn’t provide static versions of all system libraries (libpthreads and libdl), so you can’t compile MySQL with --static. If you try to do so, you will get the error:
ld: fatal: library -ldl: not found or undefined reference to `dlopen' or cannot find -lrt
If too many processes try to connect very rapidly to mysqld, you will see this error in the MySQL log:
Error in accept: Protocol error
You might try starting the server with the --set-variable back_log=50 option as a workaround for this. See Section 4.1.1.
If you are linking your own MySQL client, you might get the following error when you try to execute it:
ld.so.1: ./my: fatal: libmysqlclient.so.#: open failed: No such file or directory
The problem can be avoided by one of the following methods:
If you have problems with configure trying to link with -lz and you don’t have zlib installed, you have two options:
If you want to be able to use the compressed communication protocol, you need to get and install zlib from ftp.gnu.org.
Configure with --with-named-z-libs=no.
If you are using gcc and have problems with loading UDF functions into MySQL, try adding -lgcc to the link line for the UDF function.
If you would like MySQL to start automatically, you can copy
support-files/mysql.server to /etc/init.d and create a
symbolic link to it named /etc/rc3.d/S99mysql.server.
As Solaris doesn’t support core files for setuid( ) applications, you can’t get a core file from mysqld if you are using the --user option.
You can normally use a Solaris 2.6 binary on Solaris 2.7 and 2.8. Most of the Solaris 2.6 issues also apply for Solaris 2.7 and 2.8.
Note that MySQL Versions 3.23.4 and above should be able to autodetect new versions of Solaris and enable workarounds for the following problems!
Solaris 2.7 / 2.8 has some bugs in the include files. You may see the following error when you use gcc:
/usr/include/widec.h:42: warning: `getwc' redefined /usr/include/wchar.h:326: warning: this is the location of the previous definition
If this occurs, you can do the following to fix the problem:
Copy /usr/include/widec.h to .../lib/gcc-lib/os/gcc-version/include and change line 41 from:
#if !defined(lint) && !defined(__lint)
to
#if !defined(lint) && !defined(__lint) && !defined(getwc)
Alternatively, you can edit /usr/include/widec.h directly. Either
way, after you make the fix, you should remove config.cache and run
configure again!
If you get errors like this when you run make, it’s because
configure didn’t detect the curses.h file (probably
because of the error in /usr/include/widec.h):
In file included from mysql.cc:50: /usr/include/term.h:1060: syntax error before `,' /usr/include/term.h:1081: syntax error before `;'
The solution to this is to do one of the following:
Configure with CFLAGS=-DHAVE_CURSES_H CXXFLAGS=-DHAVE_CURSES_H ./configure.
Edit
/usr/include/widec.has indicated previously and rerun configure.Remove the #define HAVE_TERM line from
config.hfile and run make again.
If you get a problem that your linker can’t find -lz when linking
your client program, the problem is probably that your libz.so file is
installed in /usr/local/lib. You can fix this by one of the
following methods:
Add
/usr/local/libto LD_LIBRARY_PATH.Add a link to
libz.sofrom/lib.If you are using Solaris 8, you can install the optional zlib from your Solaris 8 CD distribution.
Configure MySQL with the --with-named-z-libs=no option.
On Solaris 2.8 on x86, mysqld will core dump if you run ‘strip’.
If you are using gcc or egcs on Solaris x86 and you experience problems with core dumps under load, you should use the following configure command:
CC=gcc CFLAGS="-O3 -fomit-frame-pointer -DHAVE_CURSES_H" \ CXX=gcc \ CXXFLAGS="-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions \ -fno-rtti -DHAVE_CURSES_H" \ ./configure --prefix=/usr/local/mysql
This will avoid problems with the libstdc++ library and with C++ exceptions.
If this doesn’t help, you should compile a debug version and run it with a trace file or under gdb. See Section D.1.3.
This section provides information for the various BSD flavours, as well as specific versions within those.
FreeBSD 3.x is recommended for running MySQL since the thread package is much more integrated.
The easiest and therefore the preferred way to install is to use the mysql-server and mysql-client ports available on http://www.freebsd.org/.
Using these gives you:
A working MySQL with all optimisations known to work on your version of FreeBSD enabled.
Automatic configuration and build.
Startup scripts installed in /usr/local/etc/rc.d.
Ability to see which files that are installed with pkg_info -L, and to remove them all with pkg_delete if you no longer want MySQL on that machine.
It is recommended you use MIT-pthreads on FreeBSD 2.x and native threads on Versions 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.
The MySQL Makefiles require GNU make (gmake) to work. If
you want to compile MySQL you need to install GNU make first.
Be sure to have your name resolver set up correct. Otherwise, you may experience resolver delays or failures when connecting to mysqld.
Make sure that the localhost entry in the /etc/hosts file is
correct (otherwise, you will have problems connecting to the database). The
/etc/hosts file should start with a line:
127.0.0.1 localhost localhost.your.domain
The recommended way to compile and install MySQL on FreeBSD with gcc (2.95.2 and up) is:
CC=gcc CFLAGS="-O2 -fno-strength-reduce" \ CXX=gcc CXXFLAGS="-O2 -fno-rtti -fno-exceptions -felide-constructors \ -fno-strength-reduce" \ ./configure --prefix=/usr/local/mysql --enable-assembler gmake gmake install ./scripts/mysql_install_db cd /usr/local/mysql ./bin/mysqld_safe &
If you notice that configure will use MIT-pthreads, you should read the MIT-pthreads notes. See Section 2.3.6.
If you get an error from make install that it can’t find
/usr/include/pthreads, configure didn’t detect that you need
MIT-pthreads. This is fixed by executing these commands:
shell> rm config.cache shell> ./configure --with-mit-threads
FreeBSD is also known to have a very low default file handle limit. See Section A.2.16. Uncomment the ulimit -n section in safe_mysqld or raise the limits for the mysqld user in /etc/login.conf (and rebuild it with cap_mkdb /etc/login.conf). Also be sure 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 4.7.2.
If you have a lot of memory you should consider rebuilding the kernel to allow MySQL to take more than 512M of RAM. Take a look at option MAXDSIZ in the LINT config file for more info.
If you get problems with the current date in MySQL, setting the TZ variable will probably help. See Appendix E.
To get a secure and stable system you should only use FreeBSD kernels that are marked -RELEASE.
To compile on NetBSD you need GNU make. Otherwise, the compile will crash when make tries to run lint on C++ files.
On OpenBSD Version 2.5, you can compile MySQL with native threads with the following options:
CFLAGS=-pthread CXXFLAGS=-pthread ./configure --with-mit-threads=no
Our users have reported that OpenBSD 2.8 has a threading bug which causes problems with MySQL. The OpenBSD developers have fixed the problem, but as of January 25th, 2001, it’s only available in the “-current” branch. The symptoms of this threading bug are: slow response, high load, high CPU usage, and crashes.
If you get an error like Error in accept:: Bad file descriptor or error 9 when trying to open tables or directories, the problem is probably that you haven’t allocated enough file descriptors for MySQL.
In this case try starting safe_mysqld as root with the following options:
--user=mysql --open-files-limit=2048
If you get the following error when compiling MySQL, your ulimit value for virtual memory is too low:
item_func.h: In method `Item_func_ge::Item_func_ge(const Item_func_ge &)': item_func.h:28: virtual memory exhausted make[2]: *** [item_func.o] Error 1
Try using ulimit -v 80000 and run make again. If this doesn’t work and you are using bash, try switching to csh or sh; some BSDI users have reported problems with bash and ulimit.
If you are using gcc, you may also have to use the
--with-low-memory flag for configure to be able to compile
sql_yacc.cc.
If you get problems with the current date in MySQL, setting the TZ variable will probably help. See Appendix E.
Upgrade to BSD/OS Version 3.1. If that is not possible, install BSDIpatch M300-038.
Use the following command when configuring MySQL:
shell> env CXX=shlicc++ CC=shlicc2 \
./configure \
--prefix=/usr/local/mysql \
--localstatedir=/var/mysql \
--without-perl \
--with-unix-socket-path=/var/mysql/mysql.sockThe following is also known to work:
shell> env CC=gcc CXX=gcc CXXFLAGS=-O3 \
./configure \
--prefix=/usr/local/mysql \
--with-unix-socket-path=/var/mysql/mysql.sockYou 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 will run all threads with the same priority; on BSDI Version 3.1, this gives better performance (at least until BSDI fixes their thread scheduler).
If you get the error virtual memory exhausted while compiling, you should try using ulimit -v 80000 and run make again. If this doesn’t work and you are using bash, try switching to csh or sh; some BSDI users have reported problems with bash and ulimit.
BSDI Version 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 Version 4.x systems, you may get problems with shared libraries. The symptom is that you can’t 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 can’t open tables. This is because some library/system-related bug causes mysqld to change the current directory without asking for this!
The fix is to either upgrade to 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 can’t symbolic link a database directory to another database directory or symbolic link a table to another database on BSDI! (Making a symbolic link to another disk is okay.)
MySQL should work without any problems on Mac OS X public beta (Darwin). You don’t need the pthread patches for this OS!
Before trying to configure MySQL on Mac OS X server you must first install the pthread package from http://www.prnet.de/RegEx/mysql.html.
Our binary for Mac OS X is compiled on Rhapsody 5.5 with the following configure line:
CC=gcc CFLAGS="-O2 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O2 \ -fomit-frame-pointer" ./configure --prefix=/usr/local/mysql \ "--with-comment=Official MySQL binary" --with-extra-charsets=complex \ --disable-shared
You might want to also add aliases to your shell’s resource file to access mysql and mysqladmin from the command-line:
alias mysql '/usr/local/mysql/bin/mysql' alias mysqladmin '/usr/local/mysql/bin/mysqladmin'
Some of the binary distributions of MySQL for HP-UX are distributed as an HP depot file and as a tar file. To use the depot file you must be running at least HP-UX 10.x to have access to HP’s software depot tools.
The HP version of MySQL was compiled on an HP 9000/8xx server under HP-UX 10.20, and uses MIT-pthreads. It is known to work well under this configuration. MySQL Versions 3.22.26 and newer can also be built with HP’s native thread package.
Other configurations that may work:
HP 9000/7xx running HP-UX 10.20+
HP 9000/8xx running HP-UX 10.30
The following configurations almost definitely won’t work:
HP 9000/7xx or 8xx running HP-UX 10.x where x < 2
HP 9000/7xx or 8xx running HP-UX 9.x
To install the distribution, use one of the following commands, where /path/to/depot is the full pathname of the depot file:
To install everything, including the server, clients, and development tools:
shell> /usr/sbin/swinstall -s /path/to/depot mysql.full
To install only the server:
shell> /usr/sbin/swinstall -s /path/to/depot mysql.server
To install only the client package:
shell> /usr/sbin/swinstall -s /path/to/depot mysql.client
To install only the development tools:
shell> /usr/sbin/swinstall -s /path/to/depot mysql.developer
The depot places binaries and libraries in /opt/mysql and data in
/var/opt/mysql. The depot also creates the appropriate entries in
/etc/init.d and /etc/rc2.d to start the server automatically
at boot time. Obviously, this entails being root to install.
To install the HP-UX tar.gz distribution, you must have a copy of GNU tar.
There are a couple of small problems when compiling MySQL on HP-UX. We recommend that you use gcc instead of the HP-UX native compiler because gcc produces better code!
We recommend using gcc 2.95 on HP-UX. Don’t use high optimisation flags (like -O6), as this may not be safe on HP-UX.
Note that MIT-pthreads can’t be compiled with the HP-UX compiler because it can’t compile .S (assembler) files.
The following configure line should work:
CFLAGS="-DHPUX -I/opt/dce/include -fpic" \ CXXFLAGS="-DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions \ -fno-rtti" CXX=gcc ./configure --with-pthread \ --with-named-thread-libs='-ldce' --prefix=/usr/local/mysql --disable-shared
If you are compiling gcc 2.95 yourself, you should NOT link it with the DCE libraries (libdce.a or libcma.a) if you want to compile MySQL with MIT-pthreads. If you mix the DCE and MIT-pthreads packages you will get a mysqld to which you cannot connect. Remove the DCE libraries while you compile gcc 2.95!
For HP-UX Version 11.x we recommend MySQL Version 3.23.15 or later.
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 will solve 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 will get the 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 doesn’t define pthreads_atfork( ) consistently.
It has conflicting prototypes in
/usr/include/sys/unistd.h:184 and
/usr/include/sys/pthread.h:440 (the details follow).
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. Here’s the diff:
183,184c183,184 < extern int pthread_atfork(void (*prepare)( ), void (*parent)( ), < void (*child)( )); --- > extern int pthread_atfork(void (*prepare)(void), void (*parent)(void), > void (*child)(void));
After this, the following configure line should work:
CFLAGS="-fomit-frame-pointer -O3 -fpic" CXX=gcc \ CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -O3" \ ./configure --prefix=/usr/local/mysql --disable-shared
Here is some information that a HP-UX Version 11.x user sent us about compiling MySQL with HP-UX:x compiler:
Environment:
proper compilers.
setenv CC cc
setenv CXX aCC
flags
setenv CFLAGS -D_REENTRANT
setenv CXXFLAGS -D_REENTRANT
setenv CPPFLAGS -D_REENTRANT
% aCC -V
aCC: HP ANSI C++ B3910B X.03.14.06
% cc -V /tmp/empty.c
cpp.ansi: HP92453-01 A.11.02.00 HP C Preprocessor (ANSI)
ccom: HP92453-01 A.11.01.00 HP C Compiler
cc: "/tmp/empty.c", line 1: warning 501: Empty source file.
configuration:
./configure --with-pthread \
--prefix=/source-control/mysql \
--with-named-thread-libs=-lpthread \
--with-low-memory
added '#define _CTYPE_INCLUDED' to include/m_ctype.h. This
symbol is the one defined in HP's /usr/include/ctype.h:
/* Don't include std ctype.h when this is included */
#define _CTYPE_H
#define __CTYPE_INCLUDED
#define _CTYPE_INCLUDED
#define _CTYPE_USING /* Don't put names in global namespace. */I had to use the compile-time flag -D_REENTRANT to get the compiler to recognise the prototype for localtime_r. Alternatively I could have supplied the prototype for localtime_r. But I wanted to catch other bugs without needing to run into them. I wasn’t sure where I needed it, so I added it to all flags.
The optimisation flags used by MySQL (-O3) are not recognised by HP’s compilers. I did not change the flags.
If you get the following error from configure:
checking for cc option to accept ANSI C... no configure: error: MySQL requires a ANSI C compiler (and a C++ compiler). Try gcc. See the Installation chapter in the Reference Manual.
check that you don’t have the path to the K&R compiler before the path to the HP-UX C and C++ compiler.
Automatic detection of xlC is missing from Autoconf, so a configure command something like this is needed when compiling MySQL (this example uses the IBM compiler):
export CC="xlc_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192 "
export CXX="xlC_r -ma -O3 -qstrict -qoptimize=3 -qmaxmem=8192"
export CFLAGS="-I /usr/local/include"
export LDLFAGS="-L /usr/local/lib"
export CPPFLAGS=$CFLAGS
export CXXFLAGS=$CFLAGS
./configure --prefix=/usr/local \
--localstatedir=/var/mysql \
--sysconfdir=/etc/mysql \
--sbindir='/usr/local/bin' \
--libexecdir='/usr/local/bin' \
--enable-thread-safe-client \
--enable-large-filesThe preceding options were used to compile the MySQL distribution that can be found at http://www-frec.bull.com/.
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, as 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, which 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:
CC="gcc -pipe -mcpu=power -Wa,-many" \ CXX="gcc -pipe -mcpu=power -Wa,-many" \ CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti" \ ./configure --prefix=/usr/local/mysql --with-low-memory
The -Wa,-many 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 available. We don’t know if the -fno-exceptions is required with gcc 2.95, but as MySQL doesn’t use exceptions and the preceding option generates faster code, we recommend that you should always use this option with egcs / gcc.
If you get a problem with assembler code try changing the -mcpu=xxx to match your CPU. Typically power2, power, or powerpc may need to be used. Alternatively you might need to use 604 or 604e. I’m not positive but I would think using “power” would likely be safe most of the time, even on a power2 machine.
If you don’t know what your CPU is, do a “uname -m”. This will give you back a string that looks like “000514676700”, with a format of xxyyyyyymmss where xx and ss are always 0’s, 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://publib.boulder.ibm.com/doc_link/en_US/a_doc_lib/cmds/aixcmds5/uname.htm. This will give you a machine type and a machine model 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 with:
shell> CFLAGS=-DDONT_USE_THR_ALARM CXX=gcc \
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti \
-DDONT_USE_THR_ALARM" \
./configure --prefix=/usr/local/mysql --with-debug --with-low-memoryThis doesn’t affect the performance of MySQL, but has the side effect that you can’t kill clients that are “sleeping” on a connection with mysqladmin kill or mysqladmin shutdown. Instead, the client will die when it issues its next command.
On some versions of AIX, linking with libbind.a makes getservbyname core dump. 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:
#define HAVE_SNPRINTF 1
to:
#undef HAVE_SNPRINTF
And finally, in mysqld.cc you need to add a prototype for initgoups:
#ifdef _AIX41 extern "C" int initgroups(const char *,int); #endif
On SunOS 4, MIT-pthreads is needed to compile MySQL, which in turn means you will 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:
shell> ./configure --disable-shared --with-mysqld-ldflags=-all-static
When compiling readline, you may get warnings about duplicate defines. These may be ignored.
When compiling mysqld, there will be some implicit declaration of function warnings. These may be ignored.
If you are using egcs 1.1.2 on Digital Unix, you should upgrade to gcc 2.95.2, as 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 libraries -lmach -lexc (in addition to -lpthread). You should run configure something like this:
CC="cc -pthread" CXX="cxx -pthread -O" \ ./configure --with-named-thread-libs="-lpthread -lmach -lexc -lc"
When compiling mysqld, you may see a couple of warnings like this:
mysqld.cc: In function void handle_connections( )': mysqld.cc:626: passing long unsigned int *' as argument 3 of accept(int,sockadddr *, int *)'
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:
shell> nohup mysqld [options] &
nohup causes the command following it to ignore any SIGHUP signal sent from the terminal. Alternatively, start the server by running safe_mysqld, which invokes mysqld using nohup for you. See Section 4.7.2.
If you get a problem when compiling mysys/get_opt.c, just remove the line #define _NO_PROTO from the start of that file!
If you are using Compaq’s CC compiler, the following configure line should work:
CC="cc -pthread" CFLAGS="-O4 -ansi_alias -ansi_args -fast -inline speed all -arch host" CXX="cxx -pthread" CXXFLAGS="-O4 -ansi_alias -ansi_args -fast -inline speed all -arch host \ -noexceptions -nortti" export CC CFLAGS CXX CXXFLAGS ./configure \ --prefix=/usr/local/mysql \ --with-low-memory \ --enable-large-files \ --enable-shared=yes \ --with-named-thread-libs="-lpthread -lmach -lexc -lc" gnumake
If you get a problem with libtool, when compiling with shared libraries as mentioned previously, when linking mysql, you should be able to get around this by issuing:
cd mysql /bin/sh ../libtool --mode=link cxx -pthread -O3 -DDBUG_OFF \ -O4 -ansi_alias -ansi_args -fast -inline speed \ -speculate all \ -arch host -DUNDEF_HAVE_GETHOSTBYNAME_R \ -o mysql mysql.o readline.o sql_string.o completion_hash.o \ ../readline/libreadline.a -lcurses \ ../libmysql/.libs/libmysqlclient.so -lm cd .. gnumake gnumake install scripts/mysql_install_db
If you have problems compiling and have DEC CC and gcc installed, try running configure like this:
CC=cc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 \ ./configure --prefix=/usr/local/mysql
If you get problems with the c_asm.h file, you can create and use
a ‘dummy’ c_asm.h file with:
touch include/c_asm.h CC=gcc CFLAGS=-I./include \ CXX=gcc CXXFLAGS=-O3 \ ./configure --prefix=/usr/local/mysql
Note that the following problems with the ld program can be fixed by downloading the latest DEC (Compaq) patch kit from: http://ftp.support.compaq.com/public/unix/.
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 occuring 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:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
With the Digital compiler “C++ V6.1-029”, the following should work:
CC=cc -pthread
CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all \
-arch host
CXX=cxx -pthread
CXXFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all \
-arch host -noexceptions -nortti
export CC CFLAGS CXX CXXFLAGS
./configure --prefix=/usr/mysql/mysql --with-mysqld-ldflags=-all-static \
--disable-shared --with-named-thread-libs="-lmach -lexc -lc"In some versions of OSF/1, the alloca( ) function is broken. Fix
this by removing the line in config.h that defines 'HAVE_ALLOCA'.
The alloca( ) function also may have an incorrect prototype in /usr/include/alloca.h. The warning resulting from this can be ignored.
configure will use the following thread libraries automatically: --with-named-thread-libs="-lpthread -lmach -lexc -lc".
When using gcc, you can also try running configure like this:
shell> CFLAGS=-D_PTHREAD_USE_D4 CXX=gcc CXXFLAGS=-O3 ./configure ...
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:
shell> CFLAGS=-DDONT_USE_THR_ALARM \
CXXFLAGS=-DDONT_USE_THR_ALARM \
./configure ...This doesn’t affect the performance of MySQL, but has the side effect that you can’t kill clients that are “sleeping” on a connection with mysqladmin kill or mysqladmin shutdown. Instead, the client will die when it issues its next command.
With gcc 2.95.2, you will probably run into the following compile error:
sql_acl.cc:1456: Internal compiler error in `scan_region', at except.c:2566 Please submit a full bug report.
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 don’t have any -O option on your compile line). After this is done you can just change back to the top-level directly and run make again.
If you are using Irix Version 6.5.3 or newer, mysqld will only be able to create threads if you run it as a user with CAP_SCHED_MGT privileges (like root) or give the mysqld server this privilege with the following shell command:
shell> chcap "CAP_SCHED_MGT+epi" /opt/mysql/libexec/mysqld
You may have to undefine some things 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 doesn’t 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 of the patches on this page as a set: http://support.sgi.com/surfzone/patches/patchset/6.2_indigo.rps.html.
At the very minimum, you should install the latest kernel rollup, the latest rld rollup, and the latest libc rollup.
You definitely need all the POSIX patches on this page, for pthreads support: http://support.sgi.com/surfzone/patches/patchset/6.2_posix.rps.html
If you get something like the following error when compiling
mysql.cc:
"/usr/include/curses.h", line 82: error(1084): invalid combination of type
type the following in the top-level directory of your MySQL source tree:
shell> extra/replace bool curses_bool < /usr/include/curses.h \ > include/curses.h shell> make
There have also been reports of scheduling problems. If only one thread is running, things go slow. Avoid this by starting another client. This may lead to a two- to ten-fold 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:
CC=gcc CXX=gcc CXXFLAGS=-O3 \ ./configure --prefix=/usr/local/mysql --enable-thread-safe-client \ --with-named-thread-libs=-lpthread
On Irix 6.5.11 with native Irix C and C++ compilers Version 7.3.1.2, the following is reported to work:
CC=cc CXX=CC CFLAGS='-O3 -n32 -TARG:platform=IP22 -I/usr/local/include \ -L/usr/local/lib' CXXFLAGS='-O3 -n32 -TARG:platform=IP22 \ -I/usr/local/include -L/usr/local/lib' ./configure \ --prefix=/usr/local/mysql --with-innodb --with-berkeley-db \ --with-libwrap=/usr/local \ --with-named-curses-libs=/usr/local/lib/libncurses.a
The current port is tested only on a “sco3.2v5.0.4” and “sco3.2v5.0.5” system. There has also been a lot of progress on a port to “sco 3.2v4.2”.
For the moment the recommended compiler on OpenServer is gcc 2.95.2. With this you should be able to compile MySQL with just:
CC=gcc CXX=gcc ./configure ... (options)
For OpenServer 5.0.X you need to use gcc-2.95.2p1 or newer from the Skunkware (http://www.caldera.com/skunkware/) and choose browser OpenServer packages or by ftp to ftp2.caldera.com in the pub/skunkware/osr5/devtools/gcc directory.
You need the port of GCC 2.5.x for this product and the Development system. They are required on this version of Caldera (SCO) Unix. You cannot just use the GCC Dev system.
You should get the FSU Pthreads package and install it first. This can be found at http://www.cs.wustl.edu/~schmidt/ACE_wrappers/FSU-threads.tar.gz. You can also get a precompiled package from http://www.mysql.com/Downloads/SCO/FSU-threads-3.5c.tar.gz.
FSU Pthreads can be compiled with Caldera (SCO) Unix 4.2 with tcpip. With OpenServer 3.0 or Open Desktop 3.0 (OS 3.0 ODT 3.0), with the Caldera (SCO) Development System installed using a good port of GCC 2.5.x ODT, or with OS 3.0, you will 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 are needed.
To build FSU Pthreads on your system, do the following:
Run ./configure in the
threads/srcdirectory and select the SCO OpenServer option. This command copiesMakefile.SCO5toMakefile.Run make.
To install in the default
/usr/includedirectory, login as root, then cd to thethread/srcdirectory and run make install.
Remember to use GNU make when making MySQL.
If you don’t start safe_mysqld as root, you probably will get only the default 110 open files per process. mysqld will write a note about this in the log file.
With SCO 3.2V5.0.5, you should use FSU Pthreads version 3.5c or newer. You should also use gcc 2.95.2 or newer!
The following configure command should work:
shell> ./configure --prefix=/usr/local/mysql --disable-shared
With SCO 3.2V4.2, you should use FSU Pthreads version 3.5c or newer. The following configure command should work:
shell> CFLAGS="-D_XOPEN_XPG4" CXX=gcc CXXFLAGS="-D_XOPEN_XPG4" \ ./configure \ --prefix=/usr/local/mysql \ --with-named-thread-libs="-lgthreads -lsocket -lgen -lgthreads" \ --with-named-curses-libs="-lcurses"You may get some problems with some include files. You’ll find new SCO-specific include files at http://www.mysql.com/Downloads/SCO/SCO-3.2v4.2-includes.tar.gz. You should unpack this file in the
includedirectory of your MySQL source tree.
Caldera (SCO) development notes:
MySQL should automatically detect FSU Pthreads and link mysqld with -lgthreads -lsocket -lgthreads.
The Caldera (SCO) development libraries are re-entrant in FSU Pthreads. Caldera claims that its libraries’ 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 http://www.mysql.com/) comes linked with GNU malloc. If you encounter problems with memory usage, make sure that
gmalloc.ois included inlibgthreads.aandlibgthreads.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 want to install DBI on Caldera (SCO), you have to edit the
Makefile in DBI-xxx and each subdirectory.
Note that the following assumes gcc 2.95.2 or newer:
OLD: NEW: CC = cc CC = gcc CCCDLFLAGS = -KPIC -W1,-Bexport CCCDLFLAGS = -fpic CCDLFLAGS = -wl,-Bexport CCDLFLAGS = LD = ld LD = gcc -G -fpic LDDLFLAGS = -G -L/usr/local/lib LDDLFLAGS = -L/usr/local/lib LDFLAGS = -belf -L/usr/local/lib LDFLAGS = -L/usr/local/lib LD = ld LD = gcc -G -fpic OPTIMISE = -Od OPTIMISE = -O1 OLD: CCCFLAGS = -belf -dy -w0 -U M_XENIX -DPERL_SCO5 -I/usr/local/include NEW: CCFLAGS = -U M_XENIX -DPERL_SCO5 -I/usr/local/include
This is because the Perl dynaloader will not load the DBI modules if they were compiled with icc or cc.
Perl works best when compiled with cc.
You must use a version of MySQL at least as recent as Version 3.22.13 because that version fixes some portability problems under Unixware.
We have been able to compile MySQL with the following configure command on Unixware Version 7.0.1:
CC=cc CXX=CC ./configure --prefix=/usr/local/mysql
If you want to use gcc, you must use gcc 2.95.2 or newer.
Caldera provides libsocket.so.2 at ftp://stage.caldera.com/pub/security/tools for pre-OSR506 security fixes. Also, the telnetd fix at ftp://stage.caldera.com/pub/security/openserver/CSSA-2001-SCO.10/ as both libsocket.so.2 and libresolv.so.1 with instructions for installing on pre-OSR506 systems.
It’s probably a good idea to install the above patches before trying to compile/use MySQL.
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 don’t do this, you will probably run into 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 in a partition that supports long filenames such as HPFS, FAT32, etc.
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 now called
install.cmd and is a REXX script, which will set up the default
MySQL security settings and create 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.
gcc -Zdll -Zmt -Zcrtdll=pthrdrtl -I../include -I../regex -I.. \
-o example udf_example.cc -L../lib -lmysqlclient udf_example.def
mv example.dll example.udfNote: Due to limitations in OS/2, UDF module name stems must not
exceed 8 characters. Modules are stored in the /mysql2/udf
directory; the safe-mysqld.cmd script will put this directory in
the BEGINLIBPATH environment variable. When using UDF modules,
specified extensions are ignored—the extenions 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:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "example";
We are really interested in getting MySQL to work on BeOS, but unfortunately we don’t have any person who knows BeOS or has time to do a port.
We are interested in finding someone to do a port, and we will help them with any technical questions they may have while doing the port.
We have previously talked with some BeOS developers that have said that MySQL is 80% ported to BeOS, but we haven’t heard from them in a while.
We are really interested in getting MySQL to work on Netware, but unfortunately we don’t have any person who knows Netware or has time to do a port.
We are interested in finding someone to do a port, and we will help them with any technical questions they may have while doing the port.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
