Chapter 24. Extending MySQL

Table of Contents

24.1. MySQL Internals
24.1.1. MySQL Threads
24.1.2. MySQL Test Suite
24.2. Adding New Functions to MySQL
24.2.1. Features of the User-Defined Function Interface
24.2.2. CREATE FUNCTION Syntax
24.2.3. DROP FUNCTION Syntax
24.2.4. Adding a New User-Defined Function
24.2.5. Adding a New Native Function
24.3. Adding New Procedures to MySQL
24.3.1. Procedure Analyse
24.3.2. Writing a Procedure

24.1. MySQL Internals

This chapter describes a lot of things that you need to know when working on the MySQL code. If you plan to contribute to MySQL development, want to have access to the bleeding-edge versions of the code, or just want to keep track of development, follow the instructions in Section 2.9.3, “Installing from the Development Source Tree”. If you are interested in MySQL internals, you should also subscribe to our internals mailing list. This list has relatively low traffic. For details on how to subscribe, please see Section 1.7.1, “MySQL Mailing Lists”. All developers at MySQL AB are on the internals list and we help other people who are working on the MySQL code. Feel free to use this list both to ask questions about the code and to send patches that you would like to contribute to the MySQL project!

24.1.1. MySQL Threads

The MySQL server creates the following threads:

  • One thread manages TCP/IP file connection requests and creates a new dedicated thread to handle the authentication and SQL statement processing for each connection. (On Unix, this thread also manages Unix socket file connection requests.) On Windows, a similar thread manages shared-memory connection requests, and on Windows NT-based systems, a thread manages named-pipe connection requests. Every client connection has its own thread, although the manager threads try to avoid creating threads by consulting the thread cache first to see whether a cached thread can be used for a new connection.

  • On Windows NT, there is a named pipe handler thread that does the same work as the TCP/IP connection thread on named pipe connect requests.

  • On a master replication server, slave server connections are like client connections: There is one thread per connected slave.

  • On a slave replication server, an I/O thread is started to connect to the master server and read updates from it. An SQL thread is started to apply updates read from the master. These two threads run independently and can be started and stopped independently.

  • The signal thread handles all signals. This thread also normally handles alarms and calls process_alarm() to force timeouts on connections that have been idle too long.

  • If mysqld is compiled with -DUSE_ALARM_THREAD, a dedicated thread that handles alarms is created. This is only used on some systems where there are problems with sigwait() or if you want to use the thr_alarm() code in your application without a dedicated signal handling thread.

  • If the server is started with the --flush_time=val option, a dedicated thread is created to flush all tables every val seconds.

  • Each table for which INSERT DELAYED statements are issued gets its own thread.

mysqladmin processlist only shows the connection, INSERT DELAYED, and replication threads.

24.1.2. MySQL Test Suite

The test system that is included in Unix source and binary distributions makes it possible for users and developers to perform regression tests on the MySQL code. These tests can be run on Unix.

The current set of test cases doesn't test everything in MySQL, but it should catch most obvious bugs in the SQL processing code, operating system or library issues, and is quite thorough in testing replication. Our goal is to have the tests cover 100% of the code. We welcome contributions to our test suite. You may especially want to contribute tests that examine the functionality critical to your system because this ensures that all future MySQL releases work well with your applications.

The test system consists of a test language interpreter (mysqltest), a shell script to run all tests (mysql-test-run), the actual test cases written in a special test language, and their expected results. To run the test suite on your system after a build, type make test from the source root directory, or change location to the mysql-test directory and type ./mysql-test-run. If you have installed a binary distribution, change location to the mysql-test directory under the installation root directory (for example, /usr/local/mysql/mysql-test), and run ./mysql-test-run. All tests should succeed. If any do not, you should try to find out why and report the problem if it indicates a bug in MySQL. See Section 1.8, “How to Report Bugs or Problems”.

If one test fails, you should run mysql-test-run with the --force option to check whether any other tests fail.

If you have a copy of mysqld running on the machine where you want to run the test suite, you do not have to stop it, as long as it is not using ports 9306 or 9307. If either of those ports is taken, you should edit mysql-test-run and change the values of the master or slave port to one that is available.

In the mysql-test directory, you can run an individual test case with ./mysql-test-run test_name.

You can use the mysqltest language to write your own test cases. This is documented in the MySQL Test Framework manual, available at

If you have a question about the test suite, or have a test case to contribute, send an email message to the MySQL internals mailing list. See Section 1.7.1, “MySQL Mailing Lists”. This list does not accept attachments, so you should FTP all the relevant files to: