Go to the first, previous, next, last section, table of contents.

Problems and common errors

Some common errors when using MySQL

MySQL server has gone away error.

The most common reason for the MySQL server has gone away error is that the server closed the connection because of a timeout. By default the server closes the connection after 8 hours if nothing has happened.

If you have a script you just have to reconnect and try again. If you are using the mysql monitor just type reconnect.

Out of memory errors

If you do a query and get something like the following error:

mysql: Out of memory at line 42, 'malloc.c'
mysql: neaded 8136 byte (8k), memory in use: 12481367 bytes (12189k))
ERROR 2008: MySQL client got out of memory                

Note that the error refers to the MySQL client. The reason for this error is simply that the client does not have enough memory to store the whole result.

Packet to large

When the client gets a block bigger that net_buffer_length it issues a Packet too large error.

If the mysql client is used you may set a bigger buffer by starting the client with mysql --set-variable= net_buffer_length=1m.

The table is full

This error occurs when an in-memory temporary table gets bigger than tmp_table_size. To avoid this problem one can increase the -O tmp_table_size=# option to mysqld or use the SQL option SQL_BIG_TABLES. See section SET OPTION syntax..

How does MySQL hand a full disk

On a disk full condition MySQL does the following:

Access denied? error.

See section How does the privilege system work?. And especially See section Why do I get this Access denied? error..

How to run MySQL as a normal user.

mysqld (the MySQL server) can run as any user. In order to change mysqld to run as user USER, you'd have to the following:

You don't have to do anything to safe_mysqld to run as a non-root user.

At this point, your mysqld process would be running fine and dandy as user 'USER'. One thing hasn't changed though - the access permissions. By default (right after running the permissions table install script), only user 'root' has access permission to the database. Unless you have changed that, it's still true. This shouldn't stop you from accessing MySQL when you're logged in under a user other than root, just specify -u root to the client program. Note that accessing MySQL as root, by supplying -u root in the command line, doesn't have ANYTHING to do with MySQL running as root, as a user or as anyone else. The access permissions and userbase of MySQL are completely separate from the UNIX users. The only connection to the UNIX users is if you don't use the -u option to clients. In this case the client will try to login into MySQL with your UNIX login name. If your UNIX box itself isn't secured, you should probably at least put a password on the root users in the MySQL access tables, since any johndoe user can run 'mysql -u root dbname' and do whatever he likes.

Problems with file permissions

If you have problems with file permissions, for example when creating a table mysql gives: "ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)", then you might have the wrong value for environment variable UMASK. Default umask is 0664. Fix:

export UMASK

File not found

If you get ERROR '...' not found (Errcode 23) or any other error with errcode 23 from MySQL this means that you haven't allocated enough file descriptors for MySQL. perror # will give you the error message in a more readable form.

There is a commented line ulimit -n 256 in `safe_mysqld'. You can remove this comment and of course increase or decrease the value if you want. You can also make the table cache smaller with: safe_mysqld -O table_cache=32 (the default is 64).

Problems using DATE columns.

The format of DATE is 'YYYY-MM-DD'. Actually nothing else is allowed (ANSI SQL). One should use this format to update or in the WHERE clause, ie select * from table_1 where idate >= '1997-05-05';

As a convenience, MySQL automatically converts the date to a number if used in a number context. It is also smart enough to allow a 'relaxed' string form when updating and in a WHERE with a compare to a TIMESTAMP, DATE or a DATETIME column.

This means that the following works:

insert into table_1 (idate) values (19970505) ;
insert into table_1 (idate) values ('19970505') ;
insert into table_1 (idate) values ('1997-05-05');
insert into table_1 (idate) values ('1997.05.05');
insert into table_1 (idate) values ('1997 05 05');

select idate from table_1 where idate >= '1997-05-05';
select idate from table_1 where idate >= 19970505;
select mod(idate,100)1 from table_1 where idate >= 19970505;
select idate from table_1 where idate >= '19970505';

The following will not work:

select idate from table_1 where strcmp(idate,'19970505')=0;
Because '19970505' is compared as a string to '1997-05-05'.

Case sensitivity in searches.

By default a MySQL column is case insensitive (although there are some character sets that never are case insensitive). That means that if you search with column like 'a%'; you will get all columns that start with A or a. If you want to make this search case sensitive use something like INDEX(column, "A")=0 to check a prefix. Or STRCMP(column, "A") = 0 if the whole string should be the same.

If you want column to always be treated in a case sensitive manner, declare it as BINARY. See section CREATE TABLE syntax..

If you are using Chinese data in the so-called big5 encoding you want to make all character columns BINARY. This works because the sorting order of big5 encoding characters is based on the order of ascii codes.

Go to the first, previous, next, last section, table of contents.

Casa de Bender