Optimizing MySQL Tips

What one can and should optimize

  • Hardware
  • OS / libraries
  • SQL server (setup and queries)
  • API
  • Application

Optimizing hardware for MySQL

  • If you need big tables ( > 2G), you should consider using 64 bit
    hardware like Alpha, Sparc or the upcoming IA64. As MySQL uses a lot
    of 64 bit integers internally, 64 bit CPUs will give much better
    performance.

  • For large databases, the optimization order is normally
    RAM, Fast disks, CPU power.

  • More RAM can speed up key updates by keeping most of the
    used key pages in RAM.

  • If you are not using transaction-safe tables or have big disks and want
    to avoid long file checks, a UPS is good idea to be able to take the
    system down nicely in case of a power failure.

  • For systems where the database is on a dedicated server, one should
    look at 1G Ethernet. Latency is as important as throughput.

Optimizing disks

  • Have one dedicated disk for the system, programs and for temporary files.
    If you do very many changes, put the update logs and transactions logs
    on dedicated disks.

  • Low seek time is important for the database disk; For big tables you can
    estimate that you will need:
    log(row_count) / log(index_block_length/3*2/(key_length + data_ptr_length))+1
    seeks to find a row. For a table with 500,000 rows indexing a medium int:
    log(500,000)/log(1024/3*2/(3+4)) +1 = 4 seeks
    The above index would require: 500,000 * 7 * 3/2 = 5.2M. In real life,
    most of the blocks will be buffered, so probably only 1-2 seeks are needed.

  • For writes you will need (as above) 4 seek requests, however, to
    find where to place the new key, and normally 2 seeks to update the
    index and write the row.

  • For REALLY big databases, your application will be bound by the speed of
    your disk seeks, which increase by N log N as you get more data.

  • Split databases and tables over different disks. In MySQL you can
    use symbolic links for this.

  • Striping disks (RAID 0) will increase both read and write throughput.
  • Striping with mirroring (RAID 0+1) will give you safety and increase the
    read speed. Write speed will be slightly lower.

  • Don’t use mirroring or RAID (except RAID 0) on the disk for temporary files
    or for data that can be easily re-generated..

  • On Linux use hdparm -m16 -d1 on the disks on boot to enable
    reading/writing of multiple sectors at a time, and DMA. This may
    increase the response time by 5-50 %.

  • On Linux, mount the disks with async (default) and noatime.
  • For some specific application, one may want to have a ram disk for
    some very specific tables, but normally this is not needed.

Optimizing OS

  • No swap; If you have memory problems, add more RAM instead or configure
    your system to use less memory.
  • Don’t use NFS disks for data (you will have problems with NFS locking).
  • Increase number of open files for system and for the SQL server.
    (add ulimit -n # in the safe_mysqld script).
  • Increase the number of processes and threads for the system.
  • If you have relatively few big tables, tell your file system to
    not break up the file on different cylinders (Solaris).
  • Use file systems that support big files (Solaris).
  • Choose which file system to use; Reiserfs on Linux is very fast for
    open, read and write. File checks take just a couple of seconds.

Choosing API

  • PERL
    • Portable programs between OS and databases
    • Good for quick prototyping
    • One should use the DBI/DBD interface
  • PHP
    • Simpler to learn than PERL.
    • Uses less resources than PERL, which makes it good for embedding
      in Web servers.
    • One can get more speed by upgrading to PHP4.
  • C
    • The native interface to MySQL.
    • Faster and gives more control
    • Lower level, so you have to work more.
  • C++
    • Higher level gives you more time to code your application.
    • Is still in development.
  • ODBC
    • Works on Windows and Unix
    • Almost portable between different SQL servers.
    • Slow; MyODBC, which is a simple pass-through driver is 19 % slower
      than using a native interface.
    • Many ways to do the same thing; Hard to get things to work as many
      ODBC drivers have different bugs in different areas.
    • Problematic; Microsoft changes the interface once in a while.
    • Insecure future (Microsoft pushes more for OLE than for ODBC).
  • JDBC
    • In theory portable between OS and databases.
    • Can be run in the web client.
  • Python + others
    • May be fine, but we don’t use them.


Optimizing the application

  • One should concentrate on solving the problem.
  • When writing the application one should decide what is most important:
    • Speed
    • Portability between OS
    • Portability between SQL servers
  • Use persistent connections.
  • Cache things in your application to lessen the load of the SQL server.
  • Don’t query columns that you don’t need in your application.
  • Don’t use SELECT * FROM table_name...
  • Benchmark all parts of your application, but put the most effort into
    benchmarking the whole application under the worst possible ‘reasonable’
    load. By doing this in a modular fashion you should be able to replace the
    found bottleneck with a fast ‘dummy module’, you can then easily identify
    the next bottleneck (and so on).
  • Use LOCK TABLES if you do a lot of changes in a batch; For example group
    multiple UPDATES or DELETES together.

Portable applications should use

  • Perl DBI/DBD
  • ODBC
  • JDBC
  • Python (or any other language that has a generalized SQL interface)
  • You should only use SQL constructs which exist in all the target SQL
    servers or can easily be emulated with other constructs. The crash-me
    pages on http://www.mysql.com can help you with this.
  • Write wrappers to provide missing functionality for other OSes / SQL servers.

If you need more speed, you should:

  • Find the bottleneck (CPU, disk, memory, SQL server, OS, API, or application)
    and concentrate on solving this.
  • Use extensions that give you more speed / flexibility.
  • Get to know your SQL server so that you can use the fastest possible
    SQL constructs for your problem and avoid bottlenecks.
  • Optimize your table layouts and queries.
  • Use replication to get more select speed.
  • If you have a slow net connection to the database, use the compressed
    client/server protocol.

Don’t be afraid to make the first version of your application not
perfectly portable; when you have solved your problem, you can always
optimize it later.


Optimizing MySQL

  • Choose compiler and compiler options.
  • Find the best MySQL startup options for your system.
  • Scan the the MySQL manual and read Paul DuBois’ MySQL book.
  • Use EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.
  • Learn how the query optimizer works.
  • Optimize your table formats.
  • Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).
  • Use MySQL extensions to get things done faster.
  • Write a MySQL UDF function if you notice that you would need some
    function in many places.
  • Don’t use GRANT on table level or column level if you don’t really need it.
  • Pay for MySQL support and get help to solve your problem 🙂

Compiling and installing MySQL

  • By choosing the best possible compiler for your system, you can usually get
    10-30 % better performance.
  • On Linux/Intel, compile MySQL with pgcc. (The Pentium optimized version
    of gcc). The binary will only work with Intel Pentium CPUs, however.
  • Use the optimize options that are recommended in the MySQL manual for a
    particular platform.
  • Normally a native compiler for a specific CPU (like Sun Workshop for Sparc)
    should give better performance than gcc, but this is not always the case.
  • Compile MySQL with only the character sets you are going to use.
  • Compile the mysqld executable statically
    (with --with-mysqld-ldflags=-all-static) and strip the final executable
    with strip sql/mysqld.
  • Note that as MySQL doesn’t use C++ exceptions, compiling MySQL without
    exceptions support will give a big performance win!
  • Use native threads (instead of the included mit-pthreads) if your OS supports
    native threads.
  • Test the resulting binary with the MySQL benchmark test.

Maintenance

  • If possible, run OPTIMIZE table once in a while. This is especially
    important on variable size rows that are updated a lot.
  • Update the key distribution statistics in your tables once in a while
    with myisamchk -a; Remember to take down MySQL before doing this!
  • If you get fragmented files, it may be worth it to copy all files to
    another disk, clear the old disk and copy the files back.
  • If you have problems, check your tables with myisamchk or CHECK table.
  • Monitor MySQL status with:
    mysqladmin -i10 processlist extended-status
  • With the MySQL GUI client you can monitor the process list and the status
    in different windows.
  • Use mysqladmin debug to get information about locks and performance.

Optimizing SQL

Use SQL for the things it’s good at, and do other things in your application.

Use the SQL server to:

  • Find rows based on WHERE clause.
  • JOIN tables
  • GROUP BY
  • ORDER BY
  • DISTINCT

Don’t use an SQL server:

  • To validate data (like date)
  • As a calculator

Tips

  • Use keys wisely.
  • Keys are good for searches, but bad for inserts / updates of key columns.
  • Keep by data in the 3rd normal database form, but don’t be afraid of
    duplicating information or creating summary tables if you need more
    speed.
  • Instead of doing a lot of GROUP BYs on a big table, create
    summary tables of the big table and query this instead.
  • UPDATE table set count=count+1 where key_column=constant is very fast!
  • For log tables, it’s probably better to generate summary tables from them
    once in a while than try to keep the summary tables live.
  • Take advantage of default values on INSERT.

Speed difference between different SQL servers (times in seconds)

    Reading 2000000 rows by key: NT Linux
    mysql 367 249
    mysql_odbc 464  
    db2_odbc 1206  
    informix_odbc 121126  
    ms-sql_odbc 1634  
    oracle_odbc 20800  
    solid_odbc 877  
    sybase_odbc 17614  
     
    Inserting (350768) rows: NT Linux
    mysql 381 206
    mysql_odbc 619  
    db2_odbc 3460  
    informix_odbc 2692  
    ms-sql_odbc 4012  
    oracle_odbc 11291  
    solid_odbc 1801  
    sybase_odbc 4802


In the above test, MySQL was run with a 8M cache; the other databases
were run with installations defaults.


Important MySQL startup options

    back_log Change if you do a lot of new connections.
    thread_cache_size Change if you do a lot of new connections.
    key_buffer_size Pool for index pages; Can be made very big
    bdb_cache_size Record and key cache used by BDB tables.
    table_cache Change if you have many tables or
    simultaneous connections
    delay_key_write Set if you need to buffer all key writes
    log_slow_queries Find queries that takes a lot of time
    max_heap_table_size Used with GROUP BY
    sort_buffer Used with ORDER BY and GROUP BY
    myisam_sort_buffer_size Used with REPAIR TABLE
    join_buffer_size When doing a join without keys

Optimizing tables

  • MySQL has a rich set of different types. You should try to use the
    most efficient type for each column.
  • The ANALYSE procedure can help you find the optimal types for a table:
    SELECT * FROM table_name PROCEDURE ANALYSE()
  • Use NOT NULL for columns which will not store null values. This is
    particularly important for columns which you index.
  • Change your ISAM tables to MyISAM.
  • If possible, create your tables with a fixed table format.
  • Don’t create indexes you are not going to use.
  • Use the fact that MySQL can search on a prefix of an index; If you have
    and INDEX (a,b), you don’t need an index on (a).
  • Instead of creating an index on long CHAR/VARCHAR column, index just a prefix
    of the column to save space.
    CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))
  • Use the most efficient table type for each table.
  • Columns with identical information in different tables should be
    declared identically and have identical names.

How MySQL stores data

  • Databases are stored as directories.
  • Tables are stored as files.
  • Columns are stored in the files in dynamic length or fixed size format.
    In BDB tables the data is stored in pages.
  • Memory-based tables are supported.
  • Databases and tables can be symbolically linked from different disks.
  • On Windows MySQL supports internal symbolic links to databases with
    .sym files.

MySQL table types

  • HEAP tables; Fixed row size tables that are only stored in memory and
    indexed with a HASH index.
  • ISAM tables; The old B-tree table format in MySQL 3.22.
  • MyISAM tables; New version of the ISAM tables with a lot of extensions:
    • Binary portability.
    • Index on NULL columns.
    • Less fragmentation for dynamic-size rows than ISAM tables.
    • Support for big files.
    • Better index compression.
    • Better key statistics.
    • Better and faster auto_increment handling.
  • Berkeley DB (BDB) tables from Sleepycat:
    Transaction-safe (with BEGIN WORK / COMMIT | ROLLBACK).

MySQL row types (only relevant for ISAM/MyISAM tables)

  • MySQL will create the table in fixed size table format if all
    columns are of fixed length format (no VARCHAR, BLOB or TEXT columns).
    If not, the table is created in dynamic-size format.

  • Fixed-size format is much faster and more secure than the dynamic format.
  • The dynamic-size row format normally takes up less space but may be
    fragmented over time if the table is updated a lot.

  • In some cases it’s worth it to move all VARCHAR, BLOB and TEXT columns
    to another table just to get more speed on the main table.

  • With myisampack (pack_isam for ISAM) one can create a read-only, packed
    table. This minimizes disk usage which is very nice when using slow disks.
    The packed tables are perfect to use on log tables which one will not
    update anymore.

MySQL caches (shared between all threads, allocated once)

  • Key cache ; key_buffer_size, default 8M
  • Table cache ; table_cache, default 64
  • Thread cache ; thread_cache_size, default 0.
  • Hostname cache ; Changeable at compile time, default 128.
  • Memory mapped tables ; Currently only used for compressed tables.

Note that MySQL doesn’t have a row cache, but lets the OS handle this!


MySQL buffer variables (not shared, allocated on demand)

  • sort_buffer ; ORDER BY / GROUP BY
  • record_buffer ; Scanning tables
  • join_buffer_size ; Joining without keys
  • myisam_sort_buffer_size ; REPAIR TABLE
  • net_buffer_length ; For reading the SQL statement and buffering
    the result.
  • tmp_table_size ; HEAP-table-size for temporary results.

How the MySQL table cache works

  • Each open instance of a MyISAM table uses an index file and a data
    file. If a table is used by two threads or used twice in the same query,
    MyISAM will share the index file but will open another instance of the
    data file.
  • The cache will temporarily grow larger than the table cache size if all
    tables in the cache are in use. If this happens, the next table that is
    released will be closed.
  • You can check if your table cache is too small by checking the mysqld
    variable Opened_tables. If this value is high you should increase your
    table cache!

MySQL extensions / optimization that gives you speed

  • Use the optimal table type (HEAP, MyISAM, or BDB tables).
  • Use optimal columns for your data.
  • Use fixed row size if possible.
  • Use the different lock types (SELECT HIGH_PRIORITY, INSERT LOW_PRIORITY)
  • Auto_increment
  • REPLACE (REPLACE INTO table_name VALUES (...))
  • INSERT DELAYED
  • LOAD DATA INFILE / LOAD_FILE()
  • Use multi-row INSERT to insert many rows at a time.
  • SELECT INTO OUTFILE
  • LEFT JOIN, STRAIGHT JOIN
  • LEFT JOIN combined with IS NULL
  • ORDER BY can use keys in some cases.
  • If you only query columns that are in one index, only the index tree will
    be used to resolve the query.
  • Joins are normally faster than subselects (this is true for most SQL
    servers).
  • LIMIT
    • SELECT * from table1 WHERE a > 10 LIMIT 10,20
    • DELETE * from table1 WHERE a > 10 LIMIT 10
  • foo IN (list of constants) is very optimized.
  • GET_LOCK()/RELEASE_LOCK()
  • LOCK TABLES
  • INSERT and SELECT can run concurrently.
  • UDF functions that can be loaded into a running server.
  • Compressed read-only tables.
  • CREATE TEMPORARY TABLE
  • CREATE TABLE .. SELECT
  • MyISAM tables with RAID option to split a file over many files to get
    over the 2G limit on some file system.
  • Delayed_keys
  • Replication


When MySQL uses indexes

  • Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.
    • SELECT * FROM table_name WHERE key_part1=1 and key_part2 >
      5;
    • SELECT * FROM table_name WHERE key_part1 IS NULL;
  • When you use a LIKE that doesn't start with a wildcard.
    • SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
  • Retrieving rows from other tables when performing joins.
    • SELECT * from t1,t2 where t1.col=t2.key_part
  • Find the MAX() or MIN() value for a specific index.
    • SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
  • ORDER BY or GROUP BY on a prefix of a key.
    • SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
  • When all columns used in the query are part of one key.
    • SELECT key_part3 FROM table_name WHERE key_part1=1

When MySQL doesn’t use an index

  • Indexes are NOT used if MySQL can calculate that it will probably be
    faster to scan the whole table. For example if key_part1 is evenly
    distributed between 1 and 100, it’s not good to use an index in the
    following query:

    • SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
  • If you are using HEAP tables and you don’t search on all key parts with =
  • When you use ORDER BY on a HEAP table
  • If you are not using the first key part
    • SELECT * FROM table_name WHERE key_part2=1
  • If you are using LIKE that starts with a wildcard
    • SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
  • When you search on one index and do an ORDER BY on another
    • SELECT * from table_name WHERE key_part1 = # ORDER BY key2

Learn to use EXPLAIN

Use EXPLAIN on every query that you think is too slow!

mysql> explain select t3.DateOfAction, t1.TransactionID
    -> from t1 join t2 join t3
    -> where t2.ID = t1.TransactionID and t3.ID = t2.GroupID
    -> order by t3.DateOfAction, t1.TransactionID;
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| table | type   | possible_keys | key     | key_len | ref              | rows | Extra                           |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| t1    | ALL    | NULL          | NULL    |    NULL | NULL             |   11 | Using temporary; Using filesort |
| t2    | ref    | ID            | ID      |       4 | t1.TransactionID |   13 |                                 |
| t3    | eq_ref | PRIMARY       | PRIMARY |       4 | t2.GroupID       |    1 |                                 |
+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+

Types ALL and range signal a potential problem.


Learn to use SHOW PROCESSLIST

Use SHOW processlist to find out what is going on:

+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| Id | User  | Host      | db | Command | Time | State        | Info                                |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+
| 6  | monty | localhost | bp | Query   | 15   | Sending data | select * from station,station as s1 |
| 8  | monty | localhost |    | Query   | 0    |              | show processlist                    |
+----+-------+-----------+----+---------+------+--------------+-------------------------------------+

Use KILL in mysql or mysqladmin to kill off runaway threads.


How to find out how MySQL solves a query

Run the following commands and try to understand the output:

  • SHOW VARIABLES;
  • SHOW COLUMNS FROM ...\G
  • EXPLAIN SELECT ...\G
  • FLUSH STATUS;
  • SELECT ...;
  • SHOW STATUS;

MySQL is extremely good

  • For logging.
  • When you do many connects; connect is very fast.
  • Where you use SELECT and INSERT at the same time.
  • When you don’t combine updates with selects that take a long time.
  • When most selects/updates are using unique keys.
  • When you use many tables without long conflicting locks.
  • When you have big tables (MySQL uses a very compact table format).

Things to avoid with MySQL

  • Updates to a table or INSERT on a table with deleted rows,
    combined with SELECTS that take a long time.
  • HAVING on things you can have in a WHERE clause.
  • JOINS without using keys or keys which are not unique enough.
  • JOINS on columns that have different column types.
  • Using HEAP tables when not using a full key match with =
  • Forgetting a WHERE clause with UPDATE or DELETE in the MySQL
    monitor. If you tend to do this, use the --i-am-a-dummy option to the mysq client.

Different locks in MySQL

  • Internal table locks.
  • LOCK TABLES (Works on all table types)
  • GET_LOCK()/RELEASE_LOCK()
  • Page locks (for BDB tables)
  • ALTER TABLE also does a table lock on BDB tables.
  • LOCK TABLES gives you multiple readers on a table or one writer.
  • Normally a WRITE lock has higher priority than a READ lock to avoid
    starving the writers. For writers that are not important one can use
    the LOW_PRIORITY keyword to let the lock handler prefer readers.

      UPDATE LOW_PRIORITY SET value=10 WHERE id=10;
    

Tricks to give MySQL more information to solve things better

Note that you can always comment out a MySQL feature to make the query portable:

SELECT /*! SQL_BUFFER_RESULTS */ ...
  • SELECT SQL_BUFFER_RESULTS ...
    Will force MySQL to make a temporary result set. As soon as the temporary
    set is done, all locks on the tables are released. This can help when
    you get a problem with table locks or when it takes a long time to
    transfer the result to the client.
  • SELECT SQL_SMALL_RESULT ... GROUP BY ...
    To tell the optimizer that the result set will only contain a few rows.
  • SELECT SQL_BIG_RESULT ... GROUP BY ...
    To tell the optimizer that the result set will contain many rows.
  • SELECT STRAIGHT_JOIN ...
    Forces the optimizer to join the tables in the order in which they are
    listed in the FROM clause.
  • SELECT ... FROM
    table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2

    Forces MySQL to use/ignore the listed indexes.

Example of doing transactions

  • How to do a transaction with MyISAM tables:
    mysql> LOCK TABLES trans READ, customer WRITE;
    mysql> select sum(value) from trans where customer_id=some_id;
    mysql> update customer set total_value=sum_from_previous_statement
               where customer_id=some_id;
    mysql> UNLOCK TABLES;
    
  • How to do a transaction with Berkeley DB tables:
    mysql> BEGIN WORK; 
    mysql> select sum(value) from trans where customer_id=some_id;
    mysql> update customer set total_value=sum_from_previous_statement
               where customer_id=some_id;
    mysql> COMMIT;
    
  • Note that you can often avoid transactions altogether by doing:
    UPDATE customer SET value=value+new_value WHERE customer_id=some_id;
    

Example of using REPLACE

REPLACE works exactly like INSERT, except that if an old record in
the table has the same value as a new record on a unique index, the
old record is deleted before the new record is inserted.

Instead of using

  SELECT 1 FROM t1 WHERE key=#
  IF found-row
    LOCK TABLES t1
    DELETE FROM t1 WHERE key1=#
    INSERT INTO t1 VALUES (...)
    UNLOCK TABLES t1;
  ENDIF

Do

  REPLACE INTO t1 VALUES (...)    

General tips

  • Use short primary keys. Use numbers, not strings, when joining tables.
  • When using multi-part keys, the first part should be the most-used key.
  • When in doubt, use columns with more duplicates first to get better
    key compression.
  • If you run the client and MySQL server on the same machine, use sockets
    instead of TCP/IP when connecting to MySQL (this can give you up to a
    7.5 % improvement). You can do this by specifying no hostname or localhost
    when connecting to the MySQL server.
  • Use --skip-locking (default on some OSes) if possible. This will turn off
    external locking and will give better performance.
  • Use application-level hashed values instead of using long keys:
      SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND
      col_1='constant' AND col_2='constant'
    
  • Store BLOB’s that you need to access as files in files. Store only
    the file name in the database.
  • It is faster to remove all rows than to remove a large part of the rows.
  • If SQL is not fast enough, take a look at the lower level interfaces
    to access the data.

Benefits of using MySQL 3.23

  • MyISAM ; Portable BIG table format
  • HEAP ; In memory tables
  • Berkeley DB ; Transactional tables from Sleepycat.
  • A lot of raised limits
  • Dynamic character sets
  • More STATUS variables.
  • CHECK and REPAIR table.
  • Faster GROUP BY and DISTINCT
  • LEFT JOIN ... IF NULL optimization.
  • CREATE TABLE ... SELECT
  • CREATE TEMPORARY table_name (...)
  • Automatic conversion of temporary HEAP to MyISAM tables
  • Replication
  • mysqlhotcopy script.

Important features that we are actively working on

  • Improving transactions
  • Fail safe replication
  • Text searching
  • Delete with many tables (Updates with many tables will be done after this.)
  • Better key cache
  • Atomic RENAME (RENAME TABLE foo as foo_old, foo_new as foo)
  • A query cache
  • MERGE TABLES
  • A better GUI client

Author: Tim Smith

Data type: what is an int(11)?

Over and over I see developers that don’t understand what int(11) really means. Their confusion is understandable. Many know what defining a char(10) means (a fixed-sized character string that allows up to 10 characters). However, ints are different.

First of all, there are 5 types of integer. They are all fixed size.
Type # of bytes
tinyint 1
smallint 2
mediumint 3
int 4
bigint 8

As you can see from the chart, an int is always 4 bytes. That can store signed numbers from -2 billion to +2 billion (and unsigned numbers 0 to 4B). So, what does it mean if you declare an int(5)? It does not restrict the number of digits to 5… It may actually do nothing! The (5) part is a display width. It’s only used if you use UNSIGNED and ZEROFILL with an integer type. Then the display of those numbers will be zero-padded on the left to 5 digits if they contain less than 5 digits. Example:

CREATE TABLE `foo` (
`bar` int(5) unsigned zerofill DEFAULT NULL
)

SELECT * FROM foo;
+———+

| bar |

+———+

| 00042 |

| 00101 |

| 9876543 |

+———+

Author:Sarah Sproehnle

List of MySQL Errors with codes

1000

SQLSTATE: HY000 (ER_HASHCHK) hashchk

1001

SQLSTATE: HY000 (ER_NISAMCHK) isamchk

1002

SQLSTATE: HY000 (ER_NO) NO

1003

SQLSTATE: HY000 (ER_YES) YES

1004

SQLSTATE: HY000 (ER_CANT_CREATE_FILE) Can’t create file ‘%s’ (errno: %d)

1005

SQLSTATE: HY000 (ER_CANT_CREATE_TABLE) Can’t create table ‘%s’ (errno: %d)

1006

SQLSTATE: HY000 (ER_CANT_CREATE_DB) Can’t create database ‘%s’ (errno: %d)

1007

SQLSTATE: HY000 (ER_DB_CREATE_EXISTS) Can’t create database ‘%s’; database exists

1008

SQLSTATE: HY000 (ER_DB_DROP_EXISTS) Can’t drop database ‘%s’; database doesn’t exist

1009

SQLSTATE: HY000 (ER_DB_DROP_DELETE) Error dropping database (can’t delete ‘%s’, errno: %d)

1010

SQLSTATE: HY000 (ER_DB_DROP_RMDIR) Error dropping database (can’t rmdir ‘%s’, errno: %d)

1011

SQLSTATE: HY000 (ER_CANT_DELETE_FILE) Error on delete of ‘%s’ (errno: %d)

1012

SQLSTATE: HY000 (ER_CANT_FIND_SYSTEM_REC) Can’t read record in system table

1013

SQLSTATE: HY000 (ER_CANT_GET_STAT) Can’t get status of ‘%s’ (errno: %d)

1014

SQLSTATE: HY000 (ER_CANT_GET_WD) Can’t get working directory (errno: %d)

1015

SQLSTATE: HY000 (ER_CANT_LOCK) Can’t lock file (errno: %d)

1016

SQLSTATE: HY000 (ER_CANT_OPEN_FILE) Can’t open file: ‘%s’ (errno: %d)

1017

SQLSTATE: HY000 (ER_FILE_NOT_FOUND) Can’t find file: ‘%s’ (errno: %d)

1018

SQLSTATE: HY000 (ER_CANT_READ_DIR) Can’t read dir of ‘%s’ (errno: %d)

1019

SQLSTATE: HY000 (ER_CANT_SET_WD) Can’t change dir to ‘%s’ (errno: %d)

1020

SQLSTATE: HY000 (ER_CHECKREAD) Record has changed since last read in table ‘%s’

1021

SQLSTATE: HY000 (ER_DISK_FULL) Disk full (%s); waiting for someone to free some space…

1022

SQLSTATE: 23000 (ER_DUP_KEY) Can’t write; duplicate key in table ‘%s’

1023

SQLSTATE: HY000 (ER_ERROR_ON_CLOSE) Error on close of ‘%s’ (errno: %d)

1024

SQLSTATE: HY000 (ER_ERROR_ON_READ) Error reading file ‘%s’ (errno: %d)

1025

SQLSTATE: HY000 (ER_ERROR_ON_RENAME) Error on rename of ‘%s’ to ‘%s’ (errno: %d)

1026

SQLSTATE: HY000 (ER_ERROR_ON_WRITE) Error writing file ‘%s’ (errno: %d)

1027

SQLSTATE: HY000 (ER_FILE_USED) ‘%s’ is locked against change

1028

SQLSTATE: HY000 (ER_FILSORT_ABORT) Sort aborted

1029

SQLSTATE: HY000 (ER_FORM_NOT_FOUND) View ‘%s’ doesn’t exist for ‘%s’

1030

SQLSTATE: HY000 (ER_GET_ERRNO) Got error %d from storage engine

1031

SQLSTATE: HY000 (ER_ILLEGAL_HA) Table storage engine for ‘%s’ doesn’t have this option

1032

SQLSTATE: HY000 (ER_KEY_NOT_FOUND) Can’t find record in ‘%s’

1033

SQLSTATE: HY000 (ER_NOT_FORM_FILE) Incorrect information in file: ‘%s’

1034

SQLSTATE: HY000 (ER_NOT_KEYFILE) Incorrect key file for table ‘%s’; try to repair it

1035

SQLSTATE: HY000 (ER_OLD_KEYFILE) Old key file for table ‘%s’; repair it!

1036

SQLSTATE: HY000 (ER_OPEN_AS_READONLY) Table ‘%s’ is read only

1037

SQLSTATE: HY001 (ER_OUTOFMEMORY) Out of memory; restart server and try again (needed %d bytes)

1038

SQLSTATE: HY001 (ER_OUT_OF_SORTMEMORY) Out of sort memory; increase server sort buffer size

1039

SQLSTATE: HY000 (ER_UNEXPECTED_EOF) Unexpected EOF found when reading file ‘%s’ (errno: %d)

1040

SQLSTATE: 08004 (ER_CON_COUNT_ERROR) Too many connections

1041

SQLSTATE: HY000 (ER_OUT_OF_RESOURCES) Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use ‘ulimit’ to allow mysqld to use more memory or you can add more swap space

1042

SQLSTATE: 08S01 (ER_BAD_HOST_ERROR) Can’t get hostname for your address

1043

SQLSTATE: 08S01 (ER_HANDSHAKE_ERROR) Bad handshake

1044

SQLSTATE: 42000 (ER_DBACCESS_DENIED_ERROR) Access denied for user ‘%s’@’%s’ to database ‘%s’

1045

SQLSTATE: 28000 (ER_ACCESS_DENIED_ERROR) Access denied for user ‘%s’@’%s’ (using password: %s)

1046

SQLSTATE: 3D000 (ER_NO_DB_ERROR) No database selected

1047

SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR) Unknown command

1048

SQLSTATE: 23000 (ER_BAD_NULL_ERROR) Column ‘%s’ cannot be null

1049

SQLSTATE: 42000 (ER_BAD_DB_ERROR) Unknown database ‘%s’

1050

SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR) Table ‘%s’ already exists

1051

SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR) Unknown table ‘%s’

1052

SQLSTATE: 23000 (ER_NON_UNIQ_ERROR) Column ‘%s’ in %s is ambiguous

1053

SQLSTATE: 08S01 (ER_SERVER_SHUTDOWN) Server shutdown in progress

1054

SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR) Unknown column ‘%s’ in ‘%s’

1055

SQLSTATE: 42000 (ER_WRONG_FIELD_WITH_GROUP) ‘%s’ isn’t in GROUP BY

1056

SQLSTATE: 42000 (ER_WRONG_GROUP_FIELD) Can’t group on ‘%s’

1057

SQLSTATE: 42000 (ER_WRONG_SUM_SELECT) Statement has sum functions and columns in same statement

1058

SQLSTATE: 21S01 (ER_WRONG_VALUE_COUNT) Column count doesn’t match value count

1059

SQLSTATE: 42000 (ER_TOO_LONG_IDENT) Identifier name ‘%s’ is too long

1060

SQLSTATE: 42S21 (ER_DUP_FIELDNAME) Duplicate column name ‘%s’

1061

SQLSTATE: 42000 (ER_DUP_KEYNAME) Duplicate key name ‘%s’

1062

SQLSTATE: 23000 (ER_DUP_ENTRY) Duplicate entry ‘%s’ for key %d

1063

SQLSTATE: 42000 (ER_WRONG_FIELD_SPEC) Incorrect column specifier for column ‘%s’

1064

SQLSTATE: 42000 (ER_PARSE_ERROR) %s near ‘%s’ at line %d

1065

SQLSTATE: HY000 (ER_EMPTY_QUERY) Query was empty

1066

SQLSTATE: 42000 (ER_NONUNIQ_TABLE) Not unique table/alias: ‘%s’

1067

SQLSTATE: 42000 (ER_INVALID_DEFAULT) Invalid default value for ‘%s’

1068

SQLSTATE: 42000 (ER_MULTIPLE_PRI_KEY) Multiple primary key defined

1069

SQLSTATE: 42000 (ER_TOO_MANY_KEYS) Too many keys specified; max %d keys allowed

1070

SQLSTATE: 42000 (ER_TOO_MANY_KEY_PARTS) Too many key parts specified; max %d parts allowed

1071

SQLSTATE: 42000 (ER_TOO_LONG_KEY) Specified key was too long; max key length is %d bytes

1072

SQLSTATE: 42000 (ER_KEY_COLUMN_DOES_NOT_EXITS) Key column ‘%s’ doesn’t exist in table

1073

SQLSTATE: 42000 (ER_BLOB_USED_AS_KEY) BLOB column ‘%s’ can’t be used in key specification with the used table type

1074

SQLSTATE: 42000 (ER_TOO_BIG_FIELDLENGTH) Column length too big for column ‘%s’ (max = %d); use BLOB instead

1075

SQLSTATE: 42000 (ER_WRONG_AUTO_KEY) Incorrect table definition; there can be only one auto column and it must be defined as a key

1076

SQLSTATE: HY000 (ER_READY) %s: ready for connections. Version: ‘%s’ socket: ‘%s’ port: %d

1077

SQLSTATE: HY000 (ER_NORMAL_SHUTDOWN) %s: Normal shutdown

1078

SQLSTATE: HY000 (ER_GOT_SIGNAL) %s: Got signal %d. Aborting!

1079

SQLSTATE: HY000 (ER_SHUTDOWN_COMPLETE) %s: Shutdown complete

1080

SQLSTATE: 08S01 (ER_FORCING_CLOSE) %s: Forcing close of thread %ld user: ‘%s’

1081

SQLSTATE: 08S01 (ER_IPSOCK_ERROR) Can’t create IP socket

1082

SQLSTATE: 42S12 (ER_NO_SUCH_INDEX) Table ‘%s’ has no index like the one used in CREATE INDEX; recreate the table

1083

SQLSTATE: 42000 (ER_WRONG_FIELD_TERMINATORS) Field separator argument is not what is expected; check the manual

1084

SQLSTATE: 42000 (ER_BLOBS_AND_NO_TERMINATED) You can’t use fixed rowlength with BLOBs; please use ‘fields terminated by’

1085

SQLSTATE: HY000 (ER_TEXTFILE_NOT_READABLE) The file ‘%s’ must be in the database directory or be readable by all

1086

SQLSTATE: HY000 (ER_FILE_EXISTS_ERROR) File ‘%s’ already exists

1087

SQLSTATE: HY000 (ER_LOAD_INFO) Records: %ld Deleted: %ld Skipped: %ld Warnings: %ld

1088

SQLSTATE: HY000 (ER_ALTER_INFO) Records: %ld Duplicates: %ld

1089

SQLSTATE: HY000 (ER_WRONG_SUB_KEY) Incorrect sub part key; the used key part isn’t a string, the used length is longer than the key part, or the storage engine doesn’t support unique sub keys

1090

SQLSTATE: 42000 (ER_CANT_REMOVE_ALL_FIELDS) You can’t delete all columns with ALTER TABLE; use DROP TABLE instead

1091

SQLSTATE: 42000 (ER_CANT_DROP_FIELD_OR_KEY) Can’t DROP ‘%s’; check that column/key exists

1092

SQLSTATE: HY000 (ER_INSERT_INFO) Records: %ld Duplicates: %ld Warnings: %ld

1093

SQLSTATE: HY000 (ER_UPDATE_TABLE_USED) You can’t specify target table ‘%s’ for update in FROM clause

1094

SQLSTATE: HY000 (ER_NO_SUCH_THREAD) Unknown thread id: %lu

1095

SQLSTATE: HY000 (ER_KILL_DENIED_ERROR) You are not owner of thread %lu

1096

SQLSTATE: HY000 (ER_NO_TABLES_USED) No tables used

1097

SQLSTATE: HY000 (ER_TOO_BIG_SET) Too many strings for column %s and SET

1098

SQLSTATE: HY000 (ER_NO_UNIQUE_LOGFILE) Can’t generate a unique log-filename %s.(1-999)

1099

SQLSTATE: HY000 (ER_TABLE_NOT_LOCKED_FOR_WRITE) Table ‘%s’ was locked with a READ lock and can’t be updated

1100

SQLSTATE: HY000 (ER_TABLE_NOT_LOCKED) Table ‘%s’ was not locked with LOCK TABLES

1101

SQLSTATE: 42000 (ER_BLOB_CANT_HAVE_DEFAULT) BLOB/TEXT column ‘%s’ can’t have a default value

1102

SQLSTATE: 42000 (ER_WRONG_DB_NAME) Incorrect database name ‘%s’

1103

SQLSTATE: 42000 (ER_WRONG_TABLE_NAME) Incorrect table name ‘%s’

1104

SQLSTATE: 42000 (ER_TOO_BIG_SELECT) The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

1105

SQLSTATE: HY000 (ER_UNKNOWN_ERROR) Unknown error

1106

SQLSTATE: 42000 (ER_UNKNOWN_PROCEDURE) Unknown procedure ‘%s’

1107

SQLSTATE: 42000 (ER_WRONG_PARAMCOUNT_TO_PROCEDURE) Incorrect parameter count to procedure ‘%s’

1108

SQLSTATE: HY000 (ER_WRONG_PARAMETERS_TO_PROCEDURE) Incorrect parameters to procedure ‘%s’

1109

SQLSTATE: 42S02 (ER_UNKNOWN_TABLE) Unknown table ‘%s’ in %s

1110

SQLSTATE: 42000 (ER_FIELD_SPECIFIED_TWICE) Column ‘%s’ specified twice

1111

SQLSTATE: HY000 (ER_INVALID_GROUP_FUNC_USE) Invalid use of group function

1112

SQLSTATE: 42000 (ER_UNSUPPORTED_EXTENSION) Table ‘%s’ uses an extension that doesn’t exist in this MySQL version

1113

SQLSTATE: 42000 (ER_TABLE_MUST_HAVE_COLUMNS) A table must have at least 1 column

1114

SQLSTATE: HY000 (ER_RECORD_FILE_FULL) The table ‘%s’ is full

1115

SQLSTATE: 42000 (ER_UNKNOWN_CHARACTER_SET) Unknown character set: ‘%s’

1116

SQLSTATE: HY000 (ER_TOO_MANY_TABLES) Too many tables; MySQL can only use %d tables in a join

1117

SQLSTATE: HY000 (ER_TOO_MANY_FIELDS) Too many columns

1118

SQLSTATE: 42000 (ER_TOO_BIG_ROWSIZE) Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld. You have to change some columns to TEXT or BLOBs

1119

SQLSTATE: HY000 (ER_STACK_OVERRUN) Thread stack overrun: Used: %ld of a %ld stack. Use ‘mysqld -O thread_stack=#’ to specify a bigger stack if needed

1120

SQLSTATE: 42000 (ER_WRONG_OUTER_JOIN) Cross dependency found in OUTER JOIN; examine your ON conditions

1121

SQLSTATE: 42000 (ER_NULL_COLUMN_IN_INDEX) Column ‘%s’ is used with UNIQUE or INDEX but is not defined as NOT NULL

1122

SQLSTATE: HY000 (ER_CANT_FIND_UDF) Can’t load function ‘%s’

1123

SQLSTATE: HY000 (ER_CANT_INITIALIZE_UDF) Can’t initialize function ‘%s’; %s

1124

SQLSTATE: HY000 (ER_UDF_NO_PATHS) No paths allowed for shared library

1125

SQLSTATE: HY000 (ER_UDF_EXISTS) Function ‘%s’ already exists

1126

SQLSTATE: HY000 (ER_CANT_OPEN_LIBRARY) Can’t open shared library ‘%s’ (errno: %d %s)

1127

SQLSTATE: HY000 (ER_CANT_FIND_DL_ENTRY) Can’t find function ‘%s’ in library’

1128

SQLSTATE: HY000 (ER_FUNCTION_NOT_DEFINED) Function ‘%s’ is not defined

1129

SQLSTATE: HY000 (ER_HOST_IS_BLOCKED) Host ‘%s’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

1130

SQLSTATE: HY000 (ER_HOST_NOT_PRIVILEGED) Host ‘%s’ is not allowed to connect to this MySQL server

1131

SQLSTATE: 42000 (ER_PASSWORD_ANONYMOUS_USER) You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords

1132

SQLSTATE: 42000 (ER_PASSWORD_NOT_ALLOWED) You must have privileges to update tables in the mysql database to be able to change passwords for others

1133

SQLSTATE: 42000 (ER_PASSWORD_NO_MATCH) Can’t find any matching row in the user table

1134

SQLSTATE: HY000 (ER_UPDATE_INFO) Rows matched: %ld Changed: %ld Warnings: %ld

1135

SQLSTATE: HY000 (ER_CANT_CREATE_THREAD) Can’t create a new thread (errno %d); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

1136

SQLSTATE: 21S01 (ER_WRONG_VALUE_COUNT_ON_ROW) Column count doesn’t match value count at row %ld

1137

SQLSTATE: HY000 (ER_CANT_REOPEN_TABLE) Can’t reopen table: ‘%s’

1138

SQLSTATE: 42000 (ER_INVALID_USE_OF_NULL) Invalid use of NULL value

1139

SQLSTATE: 42000 (ER_REGEXP_ERROR) Got error ‘%s’ from regexp

1140

SQLSTATE: 42000 (ER_MIX_OF_GROUP_FUNC_AND_FIELDS) Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

1141

SQLSTATE: 42000 (ER_NONEXISTING_GRANT) There is no such grant defined for user ‘%s’ on host ‘%s’

1142

SQLSTATE: 42000 (ER_TABLEACCESS_DENIED_ERROR) %s command denied to user ‘%s’@’%s’ for table ‘%s’

1143

SQLSTATE: 42000 (ER_COLUMNACCESS_DENIED_ERROR) %s command denied to user ‘%s’@’%s’ for column ‘%s’ in table ‘%s’

1144

SQLSTATE: 42000 (ER_ILLEGAL_GRANT_FOR_TABLE) Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

1145

SQLSTATE: 42000 (ER_GRANT_WRONG_HOST_OR_USER) The host or user argument to GRANT is too long

1146

SQLSTATE: 42S02 (ER_NO_SUCH_TABLE) Table ‘%s.%s’ doesn’t exist

1147

SQLSTATE: 42000 (ER_NONEXISTING_TABLE_GRANT) There is no such grant defined for user ‘%s’ on host ‘%s’ on table ‘%s’

1148

SQLSTATE: 42000 (ER_NOT_ALLOWED_COMMAND) The used command is not allowed with this MySQL version

1149

SQLSTATE: 42000 (ER_SYNTAX_ERROR) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

1150

SQLSTATE: HY000 (ER_DELAYED_CANT_CHANGE_LOCK) Delayed insert thread couldn’t get requested lock for table %s

1151

SQLSTATE: HY000 (ER_TOO_MANY_DELAYED_THREADS) Too many delayed threads in use

1152

SQLSTATE: 08S01 (ER_ABORTING_CONNECTION) Aborted connection %ld to db: ‘%s’ user: ‘%s’ (%s)

1153

SQLSTATE: 08S01 (ER_NET_PACKET_TOO_LARGE) Got a packet bigger than ‘max_allowed_packet’ bytes

1154

SQLSTATE: 08S01 (ER_NET_READ_ERROR_FROM_PIPE) Got a read error from the connection pipe

1155

SQLSTATE: 08S01 (ER_NET_FCNTL_ERROR) Got an error from fcntl()

1156

SQLSTATE: 08S01 (ER_NET_PACKETS_OUT_OF_ORDER) Got packets out of order

1157

SQLSTATE: 08S01 (ER_NET_UNCOMPRESS_ERROR) Couldn’t uncompress communication packet

1158

SQLSTATE: 08S01 (ER_NET_READ_ERROR) Got an error reading communication packets

1159

SQLSTATE: 08S01 (ER_NET_READ_INTERRUPTED) Got timeout reading communication packets

1160

SQLSTATE: 08S01 (ER_NET_ERROR_ON_WRITE) Got an error writing communication packets

1161

SQLSTATE: 08S01 (ER_NET_WRITE_INTERRUPTED) Got timeout writing communication packets

1162

SQLSTATE: 42000 (ER_TOO_LONG_STRING) Result string is longer than ‘max_allowed_packet’ bytes

1163

SQLSTATE: 42000 (ER_TABLE_CANT_HANDLE_BLOB) The used table type doesn’t support BLOB/TEXT columns

1164

SQLSTATE: 42000 (ER_TABLE_CANT_HANDLE_AUTO_INCREMENT) The used table type doesn’t support AUTO_INCREMENT columns

1165

SQLSTATE: HY000 (ER_DELAYED_INSERT_TABLE_LOCKED) INSERT DELAYED can’t be used with table ‘%s’ because it is locked with LOCK TABLES

1166

SQLSTATE: 42000 (ER_WRONG_COLUMN_NAME) Incorrect column name ‘%s’

1167

SQLSTATE: 42000 (ER_WRONG_KEY_COLUMN) The used storage engine can’t index column ‘%s’

1168

SQLSTATE: HY000 (ER_WRONG_MRG_TABLE) All tables in the MERGE table are not identically defined

1169

SQLSTATE: 23000 (ER_DUP_UNIQUE) Can’t write, because of unique constraint, to table ‘%s’

1170

SQLSTATE: 42000 (ER_BLOB_KEY_WITHOUT_LENGTH) BLOB/TEXT column ‘%s’ used in key specification without a key length

1171

SQLSTATE: 42000 (ER_PRIMARY_CANT_HAVE_NULL) All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

1172

SQLSTATE: 42000 (ER_TOO_MANY_ROWS) Result consisted of more than one row

1173

SQLSTATE: 42000 (ER_REQUIRES_PRIMARY_KEY) This table type requires a primary key

1174

SQLSTATE: HY000 (ER_NO_RAID_COMPILED) This version of MySQL is not compiled with RAID support

1175

SQLSTATE: HY000 (ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE) You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

1176

SQLSTATE: HY000 (ER_KEY_DOES_NOT_EXITS) Key ‘%s’ doesn’t exist in table ‘%s’

1177

SQLSTATE: 42000 (ER_CHECK_NO_SUCH_TABLE) Can’t open table

1178

SQLSTATE: 42000 (ER_CHECK_NOT_IMPLEMENTED) The storage engine for the table doesn’t support %s

1179

SQLSTATE: 25000 (ER_CANT_DO_THIS_DURING_AN_TRANSACTION) You are not allowed to execute this command in a transaction

1180

SQLSTATE: HY000 (ER_ERROR_DURING_COMMIT) Got error %d during COMMIT

1181

SQLSTATE: HY000 (ER_ERROR_DURING_ROLLBACK) Got error %d during ROLLBACK

1182

SQLSTATE: HY000 (ER_ERROR_DURING_FLUSH_LOGS) Got error %d during FLUSH_LOGS

1183

SQLSTATE: HY000 (ER_ERROR_DURING_CHECKPOINT) Got error %d during CHECKPOINT

1184

SQLSTATE: 08S01 (ER_NEW_ABORTING_CONNECTION) Aborted connection %ld to db: ‘%s’ user: ‘%s’ host: `%s’ (%s)

1185

SQLSTATE: HY000 (ER_DUMP_NOT_IMPLEMENTED) The storage engine for the table does not support binary table dump

1186

SQLSTATE: HY000 (ER_FLUSH_MASTER_BINLOG_CLOSED) Binlog closed, cannot RESET MASTER

1187

SQLSTATE: HY000 (ER_INDEX_REBUILD) Failed rebuilding the index of dumped table ‘%s’

1188

SQLSTATE: HY000 (ER_MASTER) Error from master: ‘%s’

1189

SQLSTATE: 08S01 (ER_MASTER_NET_READ) Net error reading from master

1190

SQLSTATE: 08S01 (ER_MASTER_NET_WRITE) Net error writing to master

1191

SQLSTATE: HY000 (ER_FT_MATCHING_KEY_NOT_FOUND) Can’t find FULLTEXT index matching the column list

1192

SQLSTATE: HY000 (ER_LOCK_OR_ACTIVE_TRANSACTION) Can’t execute the given command because you have active locked tables or an active transaction

1193

SQLSTATE: HY000 (ER_UNKNOWN_SYSTEM_VARIABLE) Unknown system variable ‘%s’

1194

SQLSTATE: HY000 (ER_CRASHED_ON_USAGE) Table ‘%s’ is marked as crashed and should be repaired

1195

SQLSTATE: HY000 (ER_CRASHED_ON_REPAIR) Table ‘%s’ is marked as crashed and last (automatic?) repair failed

1196

SQLSTATE: HY000 (ER_WARNING_NOT_COMPLETE_ROLLBACK) Some non-transactional changed tables couldn’t be rolled back

1197

SQLSTATE: HY000 (ER_TRANS_CACHE_FULL) Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again

1198

SQLSTATE: HY000 (ER_SLAVE_MUST_STOP) This operation cannot be performed with a running slave; run STOP SLAVE first

1199

SQLSTATE: HY000 (ER_SLAVE_NOT_RUNNING) This operation requires a running slave; configure slave and do START SLAVE

1200

SQLSTATE: HY000 (ER_BAD_SLAVE) The server is not configured as slave; fix in config file or with CHANGE MASTER TO

1201

SQLSTATE: HY000 (ER_MASTER_INFO) Could not initialize master info structure; more error messages can be found in the MySQL error log

1202

SQLSTATE: HY000 (ER_SLAVE_THREAD) Could not create slave thread; check system resources

1203

SQLSTATE: 42000 (ER_TOO_MANY_USER_CONNECTIONS) User %s has already more than ‘max_user_connections’ active connections

1204

SQLSTATE: HY000 (ER_SET_CONSTANTS_ONLY) You may only use constant expressions with SET

1205

SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT) Lock wait timeout exceeded; try restarting transaction

1206

SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) The total number of locks exceeds the lock table size

1207

SQLSTATE: 25000 (ER_READ_ONLY_TRANSACTION) Update locks cannot be acquired during a READ UNCOMMITTED transaction

1208

SQLSTATE: HY000 (ER_DROP_DB_WITH_READ_LOCK) DROP DATABASE not allowed while thread is holding global read lock

1209

SQLSTATE: HY000 (ER_CREATE_DB_WITH_READ_LOCK) CREATE DATABASE not allowed while thread is holding global read lock

1210

SQLSTATE: HY000 (ER_WRONG_ARGUMENTS) Incorrect arguments to %s

1211

SQLSTATE: 42000 (ER_NO_PERMISSION_TO_CREATE_USER) ‘%s’@’%s’ is not allowed to create new users

1212

SQLSTATE: HY000 (ER_UNION_TABLES_IN_DIFFERENT_DIR) Incorrect table definition; all MERGE tables must be in the same database

1213

SQLSTATE: 40001 (ER_LOCK_DEADLOCK) Deadlock found when trying to get lock; try restarting transaction

1214

SQLSTATE: HY000 (ER_TABLE_CANT_HANDLE_FT) The used table type doesn’t support FULLTEXT indexes

1215

SQLSTATE: HY000 (ER_CANNOT_ADD_FOREIGN) Cannot add foreign key constraint

1216

SQLSTATE: 23000 (ER_NO_REFERENCED_ROW) Cannot add or update a child row: a foreign key constraint fails

1217

SQLSTATE: 23000 (ER_ROW_IS_REFERENCED) Cannot delete or update a parent row: a foreign key constraint fails

1218

SQLSTATE: 08S01 (ER_CONNECT_TO_MASTER) Error connecting to master: %s

1219

SQLSTATE: HY000 (ER_QUERY_ON_MASTER) Error running query on master: %s

1220

SQLSTATE: HY000 (ER_ERROR_WHEN_EXECUTING_COMMAND) Error when executing command %s: %s

1221

SQLSTATE: HY000 (ER_WRONG_USAGE) Incorrect usage of %s and %s

1222

SQLSTATE: 21000 (ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT) The used SELECT statements have a different number of columns

1223

SQLSTATE: HY000 (ER_CANT_UPDATE_WITH_READLOCK) Can’t execute the query because you have a conflicting read lock

1224

SQLSTATE: HY000 (ER_MIXING_NOT_ALLOWED) Mixing of transactional and non-transactional tables is disabled

1225

SQLSTATE: HY000 (ER_DUP_ARGUMENT) Option ‘%s’ used twice in statement

1226

SQLSTATE: 42000 (ER_USER_LIMIT_REACHED) User ‘%s’ has exceeded the ‘%s’ resource (current value: %ld)

1227

SQLSTATE: HY000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need the %s privilege for this operation

1228

SQLSTATE: HY000 (ER_LOCAL_VARIABLE) Variable ‘%s’ is a SESSION variable and can’t be used with SET GLOBAL

1229

SQLSTATE: HY000 (ER_GLOBAL_VARIABLE) Variable ‘%s’ is a GLOBAL variable and should be set with SET GLOBAL

1230

SQLSTATE: 42000 (ER_NO_DEFAULT) Variable ‘%s’ doesn’t have a default value

1231

SQLSTATE: 42000 (ER_WRONG_VALUE_FOR_VAR) Variable ‘%s’ can’t be set to the value of ‘%s’

1232

SQLSTATE: 42000 (ER_WRONG_TYPE_FOR_VAR) Incorrect argument type to variable ‘%s’

1233

SQLSTATE: HY000 (ER_VAR_CANT_BE_READ) Variable ‘%s’ can only be set, not read

1234

SQLSTATE: 42000 (ER_CANT_USE_OPTION_HERE) Incorrect usage/placement of ‘%s’

1235

SQLSTATE: 42000 (ER_NOT_SUPPORTED_YET) This version of MySQL doesn’t yet support ‘%s’

1236

SQLSTATE: HY000 (ER_MASTER_FATAL_ERROR_READING_BINLOG) Got fatal error %d: ‘%s’ from master when reading data from binary log

1237

SQLSTATE: HY000 (ER_SLAVE_IGNORED_TABLE) Slave SQL thread ignored the query because of replicate-*-table rules

1238

SQLSTATE: HY000 (ER_INCORRECT_GLOBAL_LOCAL_VAR) Variable ‘%s’ is a %s variable

1239

SQLSTATE: 42000 (ER_WRONG_FK_DEF) Incorrect foreign key definition for ‘%s’: %s

1240

SQLSTATE: HY000 (ER_KEY_REF_DO_NOT_MATCH_TABLE_REF) Key reference and table reference don’t match

1241

SQLSTATE: 21000 (ER_OPERAND_COLUMNS) Operand should contain %d column(s)

1242

SQLSTATE: 21000 (ER_SUBQUERY_NO_1_ROW) Subquery returns more than 1 row

1243

SQLSTATE: HY000 (ER_UNKNOWN_STMT_HANDLER) Unknown prepared statement handler (%.*s) given to %s

1244

SQLSTATE: HY000 (ER_CORRUPT_HELP_DB) Help database is corrupt or does not exist

1245

SQLSTATE: HY000 (ER_CYCLIC_REFERENCE) Cyclic reference on subqueries

1246

SQLSTATE: HY000 (ER_AUTO_CONVERT) Converting column ‘%s’ from %s to %s

1247

SQLSTATE: 42S22 (ER_ILLEGAL_REFERENCE) Reference ‘%s’ not supported (%s)

1248

SQLSTATE: 42000 (ER_DERIVED_MUST_HAVE_ALIAS) Every derived table must have its own alias

1249

SQLSTATE: 01000 (ER_SELECT_REDUCED) Select %u was reduced during optimization

1250

SQLSTATE: 42000 (ER_TABLENAME_NOT_ALLOWED_HERE) Table ‘%s’ from one of the SELECTs cannot be used in %s

1251

SQLSTATE: 08004 (ER_NOT_SUPPORTED_AUTH_MODE) Client does not support authentication protocol requested by server; consider upgrading MySQL client

1252

SQLSTATE: 42000 (ER_SPATIAL_CANT_HAVE_NULL) All parts of a SPATIAL index must be NOT NULL

1253

SQLSTATE: 42000 (ER_COLLATION_CHARSET_MISMATCH) COLLATION ‘%s’ is not valid for CHARACTER SET ‘%s’

1254

SQLSTATE: HY000 (ER_SLAVE_WAS_RUNNING) Slave is already running

1255

SQLSTATE: HY000 (ER_SLAVE_WAS_NOT_RUNNING) Slave has already been stopped

1256

SQLSTATE: HY000 (ER_TOO_BIG_FOR_UNCOMPRESS) Uncompressed data size too large; the maximum size is %d (probably, length of uncompressed data was corrupted)

1257

SQLSTATE: HY000 (ER_ZLIB_Z_MEM_ERROR) ZLIB: Not enough memory

1258

SQLSTATE: HY000 (ER_ZLIB_Z_BUF_ERROR) ZLIB: Not enough room in the output buffer (probably, length of uncompressed data was corrupted)

1259

SQLSTATE: HY000 (ER_ZLIB_Z_DATA_ERROR) ZLIB: Input data corrupted

1260

SQLSTATE: HY000 (ER_CUT_VALUE_GROUP_CONCAT) %d line(s) were cut by GROUP_CONCAT()

1261

SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Row %ld doesn’t contain data for all columns

1262

SQLSTATE: 01000 (ER_WARN_TOO_MANY_RECORDS) Row %ld was truncated; it contained more data than there were input columns

1263

SQLSTATE: 01000 (ER_WARN_NULL_TO_NOTNULL) Data truncated; NULL supplied to NOT NULL column ‘%s’ at row %ld

1264

SQLSTATE: 01000 (ER_WARN_DATA_OUT_OF_RANGE) Data truncated; out of range for column ‘%s’ at row %ld

1265

SQLSTATE: 01000 (ER_WARN_DATA_TRUNCATED) Data truncated for column ‘%s’ at row %ld

1266

SQLSTATE: HY000 (ER_WARN_USING_OTHER_HANDLER) Using storage engine %s for table ‘%s’

1267

SQLSTATE: HY000 (ER_CANT_AGGREGATE_2COLLATIONS) Illegal mix of collations (%s,%s) and (%s,%s) for operation ‘%s’

1268

SQLSTATE: HY000 (ER_DROP_USER) Can’t drop one or more of the requested users

1269

SQLSTATE: HY000 (ER_REVOKE_GRANTS) Can’t revoke all privileges, grant for one or more of the requested users

1270

SQLSTATE: HY000 (ER_CANT_AGGREGATE_3COLLATIONS) Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation ‘%s’

1271

SQLSTATE: HY000 (ER_CANT_AGGREGATE_NCOLLATIONS) Illegal mix of collations for operation ‘%s’

1272

SQLSTATE: HY000 (ER_VARIABLE_IS_NOT_STRUCT) Variable ‘%s’ is not a variable component (can’t be used as XXXX.variable_name)

1273

SQLSTATE: HY000 (ER_UNKNOWN_COLLATION) Unknown collation: ‘%s’

1274

SQLSTATE: HY000 (ER_SLAVE_IGNORED_SSL_PARAMS) SSL parameters in CHANGE MASTER are ignored because this MySQL slave was compiled without SSL support; they can be used later if MySQL slave with SSL is started

1275

SQLSTATE: HY000 (ER_SERVER_IS_IN_SECURE_AUTH_MODE) Server is running in –secure-auth mode, but ‘%s’@’%s’ has a password in the old format; please change the password to the new format

1276

SQLSTATE: HY000 (ER_WARN_FIELD_RESOLVED) Field or reference ‘%s%s%s%s%s’ of SELECT #%d was resolved in SELECT #%d

1277

SQLSTATE: HY000 (ER_BAD_SLAVE_UNTIL_COND) Incorrect parameter or combination of parameters for START SLAVE UNTIL

1278

SQLSTATE: HY000 (ER_MISSING_SKIP_SLAVE) It is recommended to use –skip-slave-start when doing step-by-step replication with START SLAVE UNTIL; otherwise, you will get problems if you get an unexpected slave’s mysqld restart

1279

SQLSTATE: HY000 (ER_UNTIL_COND_IGNORED) SQL thread is not to be started so UNTIL options are ignored

1280

SQLSTATE: 42000 (ER_WRONG_NAME_FOR_INDEX) Incorrect index name ‘%s’

1281

SQLSTATE: 42000 (ER_WRONG_NAME_FOR_CATALOG) Incorrect catalog name ‘%s’

1282

SQLSTATE: HY000 (ER_WARN_QC_RESIZE) Query cache failed to set size %lu; new query cache size is %lu

1283

SQLSTATE: HY000 (ER_BAD_FT_COLUMN) Column ‘%s’ cannot be part of FULLTEXT index

1284

SQLSTATE: HY000 (ER_UNKNOWN_KEY_CACHE) Unknown key cache ‘%s’

1285

SQLSTATE: HY000 (ER_WARN_HOSTNAME_WONT_WORK) MySQL is started in –skip-name-resolve mode; you must restart it without this switch for this grant to work

1286

SQLSTATE: 42000 (ER_UNKNOWN_STORAGE_ENGINE) Unknown table engine ‘%s’

1287

SQLSTATE: HY000 (ER_WARN_DEPRECATED_SYNTAX) ‘%s’ is deprecated; use ‘%s’ instead

1288

SQLSTATE: HY000 (ER_NON_UPDATABLE_TABLE) The target table %s of the %s is not updatable

1289

SQLSTATE: HY000 (ER_FEATURE_DISABLED) The ‘%s’ feature is disabled; you need MySQL built with ‘%s’ to have it working

1290

SQLSTATE: HY000 (ER_OPTION_PREVENTS_STATEMENT) The MySQL server is running with the %s option so it cannot execute this statement

1291

SQLSTATE: HY000 (ER_DUPLICATED_VALUE_IN_TYPE) Column ‘%s’ has duplicated value ‘%s’ in %s

1292

SQLSTATE: HY000 (ER_TRUNCATED_WRONG_VALUE) Truncated incorrect %s value: ‘%s’

1293

SQLSTATE: HY000 (ER_TOO_MUCH_AUTO_TIMESTAMP_COLS) Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

1294

SQLSTATE: HY000 (ER_INVALID_ON_UPDATE) Invalid ON UPDATE clause for ‘%s’ column

1295

SQLSTATE: HY000 (ER_UNSUPPORTED_PS) This command is not supported in the prepared statement protocol yet

1296

SQLSTATE: HY000 (ER_GET_ERRMSG) Got error %d ‘%s’ from %s

1297

SQLSTATE: HY000 (ER_GET_TEMPORARY_ERRMSG) Got temporary error %d ‘%s’ from %s

1298

SQLSTATE: HY000 (ER_UNKNOWN_TIME_ZONE) Unknown or incorrect time zone: ‘%s’

1299

SQLSTATE: HY000 (ER_WARN_INVALID_TIMESTAMP) Invalid TIMESTAMP value in column ‘%s’ at row %ld

1300

SQLSTATE: HY000 (ER_INVALID_CHARACTER_STRING) Invalid %s character string: ‘%s’

1301

SQLSTATE: HY000 (ER_WARN_ALLOWED_PACKET_OVERFLOWED) Result of %s() was larger than max_allowed_packet (%d) – truncated

1302

SQLSTATE: 2F003 (ER_SP_NO_RECURSIVE_CREATE) Can’t create a %s from within another stored routine

1303

SQLSTATE: 42000 (ER_SP_ALREADY_EXISTS) %s %s already exists

1304

SQLSTATE: 42000 (ER_SP_DOES_NOT_EXIST) %s %s does not exist

1305

SQLSTATE: HY000 (ER_SP_DROP_FAILED) Failed to DROP %s %s

1306

SQLSTATE: HY000 (ER_SP_STORE_FAILED) Failed to CREATE %s %s

1307

SQLSTATE: 42000 (ER_SP_LILABEL_MISMATCH) %s with no matching label: %s

1308

SQLSTATE: 42000 (ER_SP_LABEL_REDEFINE) Redefining label %s

1309

SQLSTATE: 42000 (ER_SP_LABEL_MISMATCH) End-label %s without match

1310

SQLSTATE: 01000 (ER_SP_UNINIT_VAR) Referring to uninitialized variable %s

1311

SQLSTATE: 0A000 (ER_SP_BADSELECT) SELECT in a stored procedure must have INTO

1312

SQLSTATE: 42000 (ER_SP_BADRETURN) RETURN is only allowed in a FUNCTION

1313

SQLSTATE: 0A000 (ER_SP_BADSTATEMENT) Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION

1314

SQLSTATE: 42000 (ER_UPDATE_LOG_DEPRECATED_IGNORED) The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been ignored

1315

SQLSTATE: 42000 (ER_UPDATE_LOG_DEPRECATED_TRANSLATED) The update log is deprecated and replaced by the binary log; SET SQL_LOG_UPDATE has been translated to SET SQL_LOG_BIN

1316

SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) Query execution was interrupted

1317

SQLSTATE: 42000 (ER_SP_WRONG_NO_OF_ARGS) Incorrect number of arguments for %s %s; expected %u, got %u

1318

SQLSTATE: 42000 (ER_SP_COND_MISMATCH) Undefined CONDITION: %s

1319

SQLSTATE: 42000 (ER_SP_NORETURN) No RETURN found in FUNCTION %s

1320

SQLSTATE: 2F005 (ER_SP_NORETURNEND) FUNCTION %s ended without RETURN

1321

SQLSTATE: 42000 (ER_SP_BAD_CURSOR_QUERY) Cursor statement must be a SELECT

1322

SQLSTATE: 42000 (ER_SP_BAD_CURSOR_SELECT) Cursor SELECT must not have INTO

1323

SQLSTATE: 42000 (ER_SP_CURSOR_MISMATCH) Undefined CURSOR: %s

1324

SQLSTATE: 24000 (ER_SP_CURSOR_ALREADY_OPEN) Cursor is already open

1325

SQLSTATE: 24000 (ER_SP_CURSOR_NOT_OPEN) Cursor is not open

1326

SQLSTATE: 42000 (ER_SP_UNDECLARED_VAR) Undeclared variable: %s

1327

SQLSTATE: HY000 (ER_SP_WRONG_NO_OF_FETCH_ARGS) Incorrect number of FETCH variables

1328

SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA) No data to FETCH

1329

SQLSTATE: 42000 (ER_SP_DUP_PARAM) Duplicate parameter: %s

1330

SQLSTATE: 42000 (ER_SP_DUP_VAR) Duplicate variable: %s

1331

SQLSTATE: 42000 (ER_SP_DUP_COND) Duplicate condition: %s

1332

SQLSTATE: 42000 (ER_SP_DUP_CURS) Duplicate cursor: %s

1333

SQLSTATE: HY000 (ER_SP_CANT_ALTER) Failed to ALTER %s %s

1334

SQLSTATE: 0A000 (ER_SP_SUBSELECT_NYI) Subselect value not supported

1335

SQLSTATE: 42000 (ER_SP_NO_USE) USE is not allowed in a stored procedure

1336

SQLSTATE: 42000 (ER_SP_VARCOND_AFTER_CURSHNDLR) Variable or condition declaration after cursor or handler declaration

1337

SQLSTATE: 42000 (ER_SP_CURSOR_AFTER_HANDLER) Cursor declaration after handler declaration

1338

SQLSTATE: 20000 (ER_SP_CASE_NOT_FOUND) Case not found for CASE statement

1339

SQLSTATE: HY000 (ER_FPARSER_TOO_BIG_FILE) Configuration file ‘%s’ is too big

1340

SQLSTATE: HY000 (ER_FPARSER_BAD_HEADER) Malformed file type header in file ‘%s’

1341

SQLSTATE: HY000 (ER_FPARSER_EOF_IN_COMMENT) Unexpected end of file while parsing comment ‘%s’

1342

SQLSTATE: HY000 (ER_FPARSER_ERROR_IN_PARAMETER) Error while parsing parameter ‘%s’ (line: ‘%s’)

1343

SQLSTATE: HY000 (ER_FPARSER_EOF_IN_UNKNOWN_PARAMETER) Unexpected end of file while skipping unknown parameter ‘%s’

1344

SQLSTATE: HY000 (ER_VIEW_NO_EXPLAIN) EXPLAIN/SHOW can not be issued; lacking privileges for underlying table

1345

SQLSTATE: HY000 (ER_FRM_UNKNOWN_TYPE) File ‘%s’ has unknown type ‘%s’ in its header

1346

SQLSTATE: HY000 (ER_WRONG_OBJECT) ‘%s.%s’ is not %s

1347

SQLSTATE: HY000 (ER_NONUPDATEABLE_COLUMN) Column ‘%s’ is not updatable

1348

SQLSTATE: HY000 (ER_VIEW_SELECT_DERIVED) View’s SELECT contains a subquery in the FROM clause

1349

SQLSTATE: HY000 (ER_VIEW_SELECT_PROCEDURE) View’s SELECT contains a PROCEDURE clause

1350

SQLSTATE: HY000 (ER_VIEW_SELECT_VARIABLE) View’s SELECT contains a variable or parameter

1351

SQLSTATE: HY000 (ER_VIEW_SELECT_TMPTABLE) View’s SELECT contains a temporary table ‘%s’

1352

SQLSTATE: HY000 (ER_VIEW_WRONG_LIST) View’s SELECT and view’s field list have different column counts

1353

SQLSTATE: HY000 (ER_WARN_VIEW_MERGE) View merge algorithm can’t be used here for now (assumed undefined algorithm)

1354

SQLSTATE: HY000 (ER_WARN_VIEW_WITHOUT_KEY) View being updated does not have complete key of underlying table in it

1355

SQLSTATE: HY000 (ER_VIEW_INVALID) View ‘%s.%s’ references invalid table(s) or column(s)

1356

SQLSTATE: HY000 (ER_SP_NO_DROP_SP) Can’t drop a %s from within another stored routine

1357

SQLSTATE: HY000 (ER_SP_GOTO_IN_HNDLR) GOTO is not allowed in a stored procedure handler

2000

(CR_UNKNOWN_ERROR) Unknown MySQL error

2001

(CR_SOCKET_CREATE_ERROR) Can’t create UNIX socket (%d)

2002

(CR_CONNECTION_ERROR) Can’t connect to local MySQL server through socket ‘%s’ (%d)

2003

(CR_CONN_HOST_ERROR) Can’t connect to MySQL server on ‘%s’ (%d)

2004

(CR_IPSOCK_ERROR) Can’t create TCP/IP socket (%d)

2005

(CR_UNKNOWN_HOST) Unknown MySQL server host ‘%s’ (%d)

2006

(CR_SERVER_GONE_ERROR) MySQL server has gone away

2007

(CR_VERSION_ERROR) Protocol mismatch; server version = %d, client version = %d

2008

(CR_OUT_OF_MEMORY) MySQL client ran out of memory

2009

(CR_WRONG_HOST_INFO) Wrong host info

2010

(CR_LOCALHOST_CONNECTION) Localhost via UNIX socket

2011

(CR_TCP_CONNECTION) %s via TCP/IP

2012

(CR_SERVER_HANDSHAKE_ERR) Error in server handshake

2013

(CR_SERVER_LOST) Lost connection to MySQL server during query

2014

(CR_COMMANDS_OUT_OF_SYNC) Commands out of sync; you can’t run this command now

2015

(CR_NAMEDPIPE_CONNECTION) %s via named pipe

2016

(CR_NAMEDPIPEWAIT_ERROR) Can’t wait for named pipe to host: %s pipe: %s (%lu)

2017

(CR_NAMEDPIPEOPEN_ERROR) Can’t open named pipe to host: %s pipe: %s (%lu)

2018

(CR_NAMEDPIPESETSTATE_ERROR) Can’t set state of named pipe to host: %s pipe: %s (%lu)

2019

(CR_CANT_READ_CHARSET) Can’t initialize character set %s (path: %s)

2020

(CR_NET_PACKET_TOO_LARGE) Got packet bigger than ‘max_allowed_packet’ bytes

2021

(CR_EMBEDDED_CONNECTION) Embedded server

2022

(CR_PROBE_SLAVE_STATUS) Error on SHOW SLAVE STATUS:

2023

(CR_PROBE_SLAVE_HOSTS) Error on SHOW SLAVE HOSTS:

2024

(CR_PROBE_SLAVE_CONNECT) Error connecting to slave:

2025

(CR_PROBE_MASTER_CONNECT) Error connecting to master:

2026

(CR_SSL_CONNECTION_ERROR) SSL connection error

2027

(CR_MALFORMED_PACKET) Malformed packet

2028

(CR_WRONG_LICENSE) This client library is licensed only for use with MySQL servers having ‘%s’ license

2029

(CR_NULL_POINTER) Invalid use of null pointer

2030

(CR_NO_PREPARE_STMT) Statement not prepared

2031

(CR_PARAMS_NOT_BOUND) No data supplied for parameters in prepared statement

2032

(CR_DATA_TRUNCATED) Data truncated

2033

(CR_NO_PARAMETERS_EXISTS) No parameters exist in the statement

2034

(CR_INVALID_PARAMETER_NO) Invalid parameter number

2035

(CR_INVALID_BUFFER_USE) Can’t send long data for non-string/non-binary data types (parameter: %d)

2036

(CR_UNSUPPORTED_PARAM_TYPE) Using unsupported buffer type: %d (parameter: %d)

2037

(CR_SHARED_MEMORY_CONNECTION) Shared memory (%lu)

2038

(CR_SHARED_MEMORY_CONNECT_REQUEST_ERROR) Can’t open shared memory; client could not create request event (%lu)

2039

(CR_SHARED_MEMORY_CONNECT_ANSWER_ERROR) Can’t open shared memory; no answer event received from server (%lu)

2040

(CR_SHARED_MEMORY_CONNECT_FILE_MAP_ERROR) Can’t open shared memory; server could not allocate file mapping (%lu)

2041

(CR_SHARED_MEMORY_CONNECT_MAP_ERROR) Can’t open shared memory; server could not get pointer to file mapping (%lu)

2042

(CR_SHARED_MEMORY_FILE_MAP_ERROR) Can’t open shared memory; client could not allocate file mapping (%lu)

2043

(CR_SHARED_MEMORY_MAP_ERROR) Can’t open shared memory; client could not get pointer to file mapping (%lu)

2044

(CR_SHARED_MEMORY_EVENT_ERROR) Can’t open shared memory; client could not create %s event (%lu)

2045

(CR_SHARED_MEMORY_CONNECT_ABANDONED_ERROR) Can’t open shared memory; no answer from server (%lu)

2046

(CR_SHARED_MEMORY_CONNECT_SET_ERROR) Can’t open shared memory; cannot send request event to server (%lu)

2047

(CR_CONN_UNKNOW_PROTOCOL) Wrong or unknown protocol

2048

(CR_INVALID_CONN_HANDLE) Invalid connection handle

2049

(CR_SECURE_AUTH) Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled)

2050

(CR_FETCH_CANCELED) Row retrieval was canceled by mysql_stmt_close() call

2051

(CR_NO_DATA) Attempt to read column without prior row fetch

Author: Jay

MySQL Stored Procedures

What are Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS’s raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here – if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.

A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn’t been an option. That has been limiting. Some have claimed that there are two schools of thought – one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?Why use stored procedures?
They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used – the stored procedure remains consistent. If your setup involves different clients, different programming languages – the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.
They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.
A simple example

A stored procedure is simply some SQL statements. Almost any valid SQL can go inside a stored procedure, with a few exceptions, which we will look at, at a later date. Let’s set up a basic stored procedure first. This one will simply say ‘Hello’ in the Xhosa language – Molo.
mysql> CREATE PROCEDURE molo() SELECT ‘Molo’;
Query OK, 0 rows affected (0.00 sec)

It is as simple as that. And to call it:
mysql> CALL molo()\G
*************************** 1. row ***************************
Molo: Molo
1 row in set (0.00 sec)

Hardly useful, but the basics are there. CREATE PROCEDURE sp_name() will define the procedure, and CALL sp_name() will call the procedure.
Parameters

The real benefit of a stored procedure is of course when you can pass values to it, as well as receive values back. The concept of parameters should be familiar to anyone who has had experience with any procedural programming experience.

There are three types of parameter:
IN: The default. This parameter is passed to the procedure, and can change inside the procedure, but remains unchanged outside.
OUT: No value is supplied to the procedure (it is assumed to be NULL), but it can be modified inside the procedure, and is available outside the procedure.
INOUT: The characteristics of both IN and OUT parameters. A value can be passed to the procedure, modified there as well as passed back again.

Mastery of stored procedures does require knowledge of session variables. Most of you probably know how to use session variables already, but if not, the concept is simple. You can assign a value to a variable, and retrieve it later. Here is an example, setting the variable x to the Xhosa word for hello to a group of people.
mysql> SET @x=’Molweni’;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molweni
1 row in set (0.00 sec)
An IN example

Here is an example of a stored procedure demonstrating the use of an IN parameter. Since IN is the default, there is no need to specify the parameter as such.
mysql> CREATE PROCEDURE sp_in(p VARCHAR(10)) SET @x = P;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_in(‘Molo’);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: Molo
1 row in set (0.00 sec)

The session variable @x is set inside of the procedure, based upon the parameter P, which is passed to the procedure, and remains unchanged.
An OUT example
mysql> SET @x=’Molweni’;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P=’molo’;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_out(@x);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: molo
1 row in set (0.00 sec)

We reset @x just to make sure the final result is not a legacy of the previous procedure. This time, the parameter P is changed inside of the procedure, while the session variable is passed to the procedure, ready to receive the result.
An INOUT example
mysql> CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_inout(2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x\G
*************************** 1. row ***************************
@x: 4
1 row in set (0.00 sec)

Here, a parameter is passed to the procedure, used in the calculation, and the results are made available to the session variable @x.

Getting information about existing stored procedures

It is clearly necessary to be able to get more information about any stored procedures later, such as a list of procedures available, and the definitions. There are MySQL-specific ways to do this, and the syntax should be familiar to experienced MySQL users. SHOW PROCEDURE STATUS returns a list of stored procedures, and some metadata about them, while SHOW CREATE PROCEDURE returns the definition of a particular procedure.
mysql> SHOW PROCEDURE STATUS\G
************* 1. row ************
Db: test
Name: molo
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-07-29 19:20:27
Created: 2005-07-29 19:20:27
Security_type: DEFINER
Comment:
************* 2. row ************
Db: test
Name: sp_in
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 11:58:34
Created: 2005-08-02 11:58:34
Security_type: DEFINER
Comment:
************* 3. row ************
Db: test
Name: sp_inout
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 12:16:18
Created: 2005-08-02 12:16:18
Security_type: DEFINER
Comment:
************* 4. row ************
Db: test
Name: sp_out
Type: PROCEDURE
Definer: ian@localhost
Modified: 2005-08-02 12:01:56
Created: 2005-08-02 12:01:56
Security_type: DEFINER
Comment:
4 rows in set (0.00 sec)

It will become clear what all these fields mean as we progress through the rest of this tutorial series.
mysql> SHOW CREATE PROCEDURE molo\G
*************************** 1. row ***************************
Procedure: molo
sql_mode:
Create Procedure: CREATE PROCEDURE ‘test’.’molo'()
SELECT ‘Molo’
1 row in set (0.00 sec)

There is also an ANSI-standard way of doing it, which will be more familiar to other users.
mysql> SELECT * FROM INFORMATION_SCHEMA.ROUTINES\G
*************************** 1. row ***************************
SPECIFIC_NAME: molo
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: molo
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SELECT ‘Molo’
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-07-29 19:20:27
LAST_ALTERED: 2005-07-29 19:20:27
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 2. row ***************************
SPECIFIC_NAME: sp_in
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_in
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x = P
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 11:58:34
LAST_ALTERED: 2005-08-02 11:58:34
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 3. row ***************************
SPECIFIC_NAME: sp_inout
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_inout
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET @x=P*2
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 12:16:18
LAST_ALTERED: 2005-08-02 12:16:18
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
*************************** 4. row ***************************
SPECIFIC_NAME: sp_out
ROUTINE_CATALOG: NULL
ROUTINE_SCHEMA: test
ROUTINE_NAME: sp_out
ROUTINE_TYPE: PROCEDURE
DTD_IDENTIFIER:
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: SET P=’molo’
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE:
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS_SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2005-08-02 12:01:56
LAST_ALTERED: 2005-08-02 12:01:56
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: ian@localhost
4 rows in set (0.01 sec)

Let’s introduce some more complex examples. First, we will create a sample table.

mysql> CREATE table sp1 (id INT, txt VARCHAR(10), PRIMARY KEY(id));
Query OK, 0 rows affected (0.11 sec)

Delimiters, and multi-statement procedures

Stored procedures of course are not that useful if they are just one statement. The effects of all the procedures we have looked at so far could have had been duplicated much more easily with a single SQL statement. Useful procedures are much longer than that. Those of you who are on the ball may be thinking of a complication. How can we differentiate between multiple statements inside the procedure, and the end of the procedure? We have to create a different delimiter to end the CREATE PROCEDURE statement. Here is how:
mysql> DELIMITER |

Note that there is no semicolon after the ‘|’ symbol, which we will use as the delimiter for our purposes. You have to choose a delimiter that does not appear in your procedure, and it can be more than one character.

mysql> CREATE PROCEDURE sp_ins (P VARCHAR(10))
-> BEGIN
-> SET @x=CHAR_LENGTH(P);
-> SET @y = HEX(P);
-> INSERT INTO sp1(id,txt) VALUES(@x,@y);
-> END|

Query OK, 0 rows affected (0.05 sec)

mysql> CALL sp_ins(‘ABC’);
-> |

Query OK, 1 row affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT * FROM sp1\G

*************************** 1. row ***************************
id: 3
txt: 414243
1 row in set (0.00 sec)

Note what happened when we tried to call the procedure. Because MySQL was still using the | symbol as a delimiter, and not the semicolon, the statement did not run after the semicolon. We first needed to close it with the piping symbol. Afterwards, we reset the delimiter back to normal, and test that the records were correctly added to the sp1 table.
Procedure variables

Stored procedures do not only make use of the standard SQL statements. You can also DECLARE variables that exist only inside the procedure, as well as assign values to them with the SET statement without using the ‘@’ symbol, required for session variables. Here is an example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_declare (P INT)
-> BEGIN
-> DECLARE x INT;
-> DECLARE y INT DEFAULT 10;
-> SET x = P*y;
-> INSERT INTO sp1(id,txt) VALUES(x,HEX(‘DEF’));
-> END|

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sp_declare(4);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sp1\G
*************************** 1. row ***************************
id: 3
txt: 414243
*************************** 2. row ***************************
id: 40
txt: 444546
2 rows in set (0.00 sec)

Variables that are declared without a default, such as x above, are set to NULL.
Populating variables from a pre-existing table

Now that you have seen how to INSERT records into a table from within a procedure, you may be wondering how we get values out of an existing table. Here is a simple example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE sp_select ()
-> BEGIN
-> DECLARE x INT;
-> DECLARE y VARCHAR(10);
-> SELECT id,txt INTO x,y FROM sp1 LIMIT 1;
-> SELECT x,y;
-> END|

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sp_select()\G

*************************** 1. row ***************************
x: 3
y: 414243
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Author: Ian Gilfillan

Advantages of stored procedures, triggers, indexes

A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don’t need to keep re-issuing the entire query but can refer to the stored procedure. This provides better overall performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server. However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side. Triggers will also be implemented.

A trigger is effectively a type of stored procedure, one that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that stored procedure automatically deletes the corresponding customer from a customer table when all his transactions are deleted.

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially. If you need to access most of the rows, it is faster to read sequentially, because this minimizes disk seeks.

« Older entries Newer entries »