MySQL server has gone awayerror.
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
If you have a script you just have to reconnect and try again. If you
are using the mysql monitor just type
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.
When the client gets a block bigger that
Packet too large error.
mysql client is used you may set a bigger buffer by
starting the client with
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..
On a disk full condition MySQL does the following:
mysqladmin killto the thread and free enough space for 1 row!
See section How does the privilege system work?. And especially See section Why do I get this
Access denied? error..
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:
shell> chown -R USER /your/path/to/mysql/var
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.
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:
UMASK=432 export UMASK ./bin/safe_mysqld
If you get
ERROR '...' not found (Errcode 23) or any other error
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).
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
DATE or a
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'.
By default a MySQL column is case insensitive (although there are some
character sets that never are case insensitive). That means that if you
column like 'a%'; you will get all columns that start
a. If you want to make this search case
sensitive use something like
INDEX(column, "A")=0 to check a
STRCMP(column, "A") = 0 if the whole string should be
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.