man pages section 1: User Commands

Exit Print View

Updated: July 2014

mysqltest (1)


mysqltest - program to run test cases mysqltest_embedded - program to run embedded test cases


mysqltest [options] [db_name]

mysqltest_embedded [options] [db_name]


MySQL Database System                                MYSQLTEST(1)

     mysqltest - program to run test cases
     mysqltest_embedded - program to run embedded test cases

     mysqltest [options] [db_name]

     mysqltest_embedded [options] [db_name]

     The mysqltest program runs a test case against a MySQL
     server and optionally compares the output with a result
     file. This program reads input written in a special test
     language. Typically, you invoke mysqltest using rather than invoking it directly.

     mysqltest_embedded is similar but is built with support for
     the libmysqld embedded server.

     Features of mysqltest:

     o   Can send SQL statements to MySQL servers for execution

     o   Can execute external shell commands

     o   Can test whether the result from an SQL statement or
         shell command is as expected

     o   Can connect to one or more standalone mysqld servers and
         switch between connections

     o   Can connect to an embedded server (libmysqld), if MySQL
         is compiled with support for libmysqld. (In this case,
         the executable is named mysqltest_embedded rather than

     By default, mysqltest reads the test case on the standard
     input. To run mysqltest this way, you normally invoke it
     like this:

         shell> mysqltest [options] [db_name] < test_file

     You can also name the test case file with a
     --test-file=file_name option.

     The exit value from mysqltest is 0 for success, 1 for
     failure, and 62 if it skips the test case (for example, if
     after checking some preconditions it decides not to run the

     mysqltest supports the following options:

MySQL                Last change: 03/21/2013                    1

MySQL Database System                                MYSQLTEST(1)

     o   --help, -?

         Display a help message and exit.

     o   --basedir=dir_name, -b dir_name

         The base directory for tests.

     o   --character-sets-dir=path

         The directory where character sets are installed.

     o   --compress, -C

         Compress all information sent between the client and the
         server if both support compression.

     o   --cursor-protocol

         Use cursors for prepared statements.

     o   --database=db_name, -D db_name

         The default database to use.

     o   --debug[=debug_options], -#[debug_options]

         Write a debugging log if MySQL is built with debugging
         support. The default debug_options value is

     o   --debug-check

         Print some debugging information when the program exits.

     o   --debug-info

         Print debugging information and memory and CPU usage
         statistics when the program exits.

     o   --host=host_name, -h host_name

         Connect to the MySQL server on the given host.

     o   --include=file_name, -i file_name

         Include the contents of the given file before processing
         the contents of the test file. The included file should
         have the same format as other mysqltest test files. This
         option has the same effect as putting a --source
         file_name command as the first line of the test file.

MySQL                Last change: 03/21/2013                    2

MySQL Database System                                MYSQLTEST(1)

     o   --logdir=dir_name

         The directory to use for log files.

     o   --mark-progress

         Write the line number and elapsed time to

     o   --max-connect-retries=num

         The maximum number of connection attempts when
         connecting to server.

     o   --max-connections=num

         The maximum number of simultaneous server connections
         per client (that is, per test). If not set, the maximum
         is 128. Minimum allowed limit is 8, maximum is 5120.

         This option is available from MySQL 5.1.45.

     o   --no-defaults

         Do not read default options from any option files. If
         used, this must be the first option.

     o   --plugin-dir=path

         The directory in which to look for plugins. It may be
         necessary to specify this option if the default_auth
         argument is used for the connect() command to specify an
         authentication plugin but mysqltest does not find it.
         This option was added in MySQL 5.5.7.

     o   --password[=password], -p[password]

         The password to use when connecting to the server. If
         you use the short option form (-p), you cannot have a
         space between the option and the password. If you omit
         the password value following the --password or -p option
         on the command line, you are prompted for one.

     o   --port=port_num, -P port_num

         The TCP/IP port number to use for the connection.

     o   --protocol={TCP|SOCKET|PIPE|MEMORY}

         Choose the protocol for communication with the server.
         SOCKET is default.

MySQL                Last change: 03/21/2013                    3

MySQL Database System                                MYSQLTEST(1)

         The --protocol option is available from MySQL 5.1.51. It
         is ignored if running with the embedded server.

     o   --ps-protocol

         Use the prepared-statement protocol for communication.

     o   --quiet

         Suppress all normal output. This is a synonym for

     o   --record, -r

         Record the output that results from running the test
         file into the file named by the --result-file option, if
         that option is given. It is an error to use this option
         without also using --result-file.

     o   --result-file=file_name, -R file_name

         This option specifies the file for test case expected
         results.  --result-file, together with --record,
         determines how mysqltest treats the test actual and
         expected results for a test case:

         o   If the test produces no results, mysqltest exits
             with an error message to that effect, unless
             --result-file is given and the named file is an
             empty file.

         o   Otherwise, if --result-file is not given, mysqltest
             sends test results to the standard output.

         o   With --result-file but not --record, mysqltest reads
             the expected results from the given file and
             compares them with the actual results. If the
             results do not match, mysqltest writes a .reject
             file in the same directory as the result file,
             outputs a diff of the two files, and exits with an

         o   With both --result-file and --record, mysqltest
             updates the given file by writing the actual test
             results to it.

     o   --server-arg=value, -A value

         Pass the argument as an argument to the embedded server.
         For example, --server-arg=--tmpdir=/tmp or
         --server-arg=--core. Up to 64 arguments can be given.

MySQL                Last change: 03/21/2013                    4

MySQL Database System                                MYSQLTEST(1)

     o   --server-file=file_name, -F file_name

         Read arguments for the embedded server from the given
         file. The file should contain one argument per line.

     o   --server-public-key-path=file_name

         The path name to a file containing the server RSA public
         key. The file must be in PEM format. The public key is
         used for RSA encryption of the client password for
         connections to the server made using accounts that
         authenticate with the sha256_password plugin. This
         option is ignored for client accounts that do not
         authenticate with that plugin. It is also ignored if
         password encryption is not needed, as is the case when
         the client connects to the server using an SSL

         The server sends the public key to the client as needed,
         so it is not necessary to use this option for RSA
         password encryption to occur. It is more efficient to do
         so because then the server need not send the key.

         For additional discussion regarding use of the
         sha256_password plugin, including how to get the RSA
         public key, see blue]The SHA-256 Authentication

         This option is available only if MySQL was built using
         OpenSSL. It was added in MySQL 5.6.6 under the name
         --server-public-key and renamed in 5.6.7 to

     o   --silent, -s

         Suppress all normal output.

     o   --skip-safemalloc

         Do not use memory allocation checking.

     o   --sleep=num, -T num

         Cause all sleep commands in the test case file to sleep
         num seconds. This option does not affect real_sleep

         As of MySQL 5.0.23, an option value of 0 can be used,
         which effectively disables sleep commands in the test

     o   --socket=path, -S path

MySQL                Last change: 03/21/2013                    5

MySQL Database System                                MYSQLTEST(1)

         The socket file to use when connecting to localhost
         (which is the default host).

     o   --sp-protocol

         Execute DML statements within a stored procedure. For
         every DML statement, mysqltest creates and invokes a
         stored procedure that executes the statement rather than
         executing the statement directly.

     o   --tail-lines=nn

         Specify how many lines of the result to include in the
         output if the test fails because an SQL statement fails.
         The default is 0, meaning no lines of result printed.

     o   --test-file=file_name, -x file_name

         Read test input from this file. The default is to read
         from the standard input.

     o   --timer-file=file_name, -m file_name

         If given, the number of millisecond spent running the
         test will be written to this file. This is used by for its reporting.

     o   --tmpdir=dir_name, -t dir_name

         The temporary directory where socket files are created.

     o   --user=user_name, -u user_name

         The MySQL user name to use when connecting to the

     o   --verbose, -v

         Verbose mode. Print out more information about what the
         program does.

     o   --version, -V

         Display version information and exit.

     o   --view-protocol

         Every SELECT statement is wrapped inside a view. This
         option was added in MySQL 5.0.19.


MySQL                Last change: 03/21/2013                    6

MySQL Database System                                MYSQLTEST(1)

     Copyright (C) 2006, 2013, Oracle and/or its affiliates. All
     rights reserved.

     This software and related documentation are provided under a
     license agreement containing restrictions on use and
     disclosure and are protected by intellectual property laws.
     Except as expressly permitted in your license agreement or
     allowed by law, you may not use, copy, reproduce, translate,
     broadcast, modify, license, transmit, distribute, exhibit,
     perform, publish, or display any part, in any form, or by
     any means. Reverse engineering, disassembly, or
     decompilation of this software, unless required by law for
     interoperability, is prohibited.

     The information contained herein is subject to change
     without notice and is not warranted to be error-free. If you
     find any errors, please report them to us in writing.

     If this software or related documentation is delivered to
     the U.S. Government or anyone licensing it on behalf of the
     U.S. Government, the following notice is applicable:

     U.S. GOVERNMENT RIGHTS Programs, software, databases, and
     related documentation and technical data delivered to U.S.
     Government customers are "commercial computer software" or
     "commercial technical data" pursuant to the applicable
     Federal Acquisition Regulation and agency-specific
     supplemental regulations. As such, the use, duplication,
     disclosure, modification, and adaptation shall be subject to
     the restrictions and license terms set forth in the
     applicable Government contract, and, to the extent
     applicable by the terms of the Government contract, the
     additional rights set forth in FAR 52.227-19, Commercial
     Computer Software License (December 2007). Oracle USA, Inc.,
     500 Oracle Parkway, Redwood City, CA 94065.

     This software is developed for general use in a variety of
     information management applications. It is not developed or
     intended for use in any inherently dangerous applications,
     including applications which may create a risk of personal
     injury. If you use this software in dangerous applications,
     then you shall be responsible to take all appropriate fail-
     safe, backup, redundancy, and other measures to ensure the
     safe use of this software. Oracle Corporation and its
     affiliates disclaim any liability for any damages caused by
     use of this software in dangerous applications.

     Oracle is a registered trademark of Oracle Corporation
     and/or its affiliates. MySQL is a trademark of Oracle
     Corporation and/or its affiliates, and shall not be used
     without Oracle's express written authorization. Other names
     may be trademarks of their respective owners.

MySQL                Last change: 03/21/2013                    7

MySQL Database System                                MYSQLTEST(1)

     This software and documentation may provide access to or
     information on content, products, and services from third
     parties. Oracle Corporation and its affiliates are not
     responsible for and expressly disclaim all warranties of any
     kind with respect to third-party content, products, and
     services. Oracle Corporation and its affiliates will not be
     responsible for any loss, costs, or damages incurred due to
     your access to or use of third-party content, products, or

     This documentation is in prerelease status and is intended
     for demonstration and preliminary use only. It may not be
     specific to the hardware on which you are using the
     software. Oracle Corporation and its affiliates are not
     responsible for and expressly disclaim all warranties of any
     kind with respect to this documentation and will not be
     responsible for any loss, costs, or damages incurred due to
     the use of this documentation.

     The information contained in this document is for
     informational sharing purposes only and should be considered
     in your capacity as a customer advisory board member or
     pursuant to your beta trial agreement only. It is not a
     commitment to deliver any material, code, or functionality,
     and should not be relied upon in making purchasing
     decisions. The development, release, and timing of any
     features or functionality described in this document remains
     at the sole discretion of Oracle.

     This document in any form, software or printed matter,
     contains proprietary information that is the exclusive
     property of Oracle. Your access to and use of this material
     is subject to the terms and conditions of your Oracle
     Software License and Service Agreement, which has been
     executed and with which you agree to comply. This document
     and information contained herein may not be disclosed,
     copied, reproduced, or distributed to anyone outside Oracle
     without prior written consent of Oracle or as specifically
     provided below. This document is not part of your license
     agreement nor can it be incorporated into any contractual
     agreement with Oracle or its subsidiaries or affiliates.

     This documentation is NOT distributed under a GPL license.
     Use of this documentation is subject to the following terms:

     You may create a printed copy of this documentation solely
     for your own personal use. Conversion to other formats is
     allowed as long as the actual content is not altered or
     edited in any way. You shall not publish or distribute this
     documentation in any form or on any media, except if you
     distribute the documentation in a manner similar to how
     Oracle disseminates it (that is, electronically for download

MySQL                Last change: 03/21/2013                    8

MySQL Database System                                MYSQLTEST(1)

     on a Web site with the software) or on a CD-ROM or similar
     medium, provided however that the documentation is
     disseminated together with the software on the same medium.
     Any other use, such as any dissemination of printed copies
     or use of this documentation, in whole or in part, in
     another publication, requires the prior written consent from
     an authorized representative of Oracle. Oracle and/or its
     affiliates reserve any and all rights to this documentation
     not expressly granted above.

     For more information on the terms of this license, or for
     details on how the MySQL documentation is built and
     produced, please visit blue]MySQL Contact & Questions].

     For help with using MySQL, please visit either the
     blue]MySQL Forums] or blue]MySQL Mailing Lists] where you
     can discuss your issues with other MySQL users.

     For additional documentation on MySQL products, including
     translations of the documentation into other languages, and
     downloadable versions in variety of formats, including HTML
     and PDF formats, see the blue]MySQL Documentation Library].

     See attributes(5) for descriptions of the following

     |Availability   | database/mysql-55 |
     |Stability      | Uncommitted       |
      1. The SHA-256 Authentication Plugin

     For more information, please refer to the MySQL Reference
     Manual, which may already be installed locally and which is
     also available online at

     Oracle Corporation (

     This software was built from source available at  The original
     community source was downloaded from

MySQL                Last change: 03/21/2013                    9

MySQL Database System                                MYSQLTEST(1)

     Further information about this software can be found on the
     open source community website at

MySQL                Last change: 03/21/2013                   10