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
.
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 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
.
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 kill
to the thread and
free enough space for 1 row!
Access denied?
error.
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
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).
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'.
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.