MySQL 启动错误: [ERROR] Can't find messagefile 'usrshareerrmsg.sys'（转载）

110623 21:49:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

110623 21:49:41 [ERROR] Can't find messagefile '/usr/share/errmsg.sys'

110623 21:49:41 [Warning] Can't create test file /var/lib/mysql/ubuntu.lower-test

110623 21:49:41 [Warning] Can't create test file /var/lib/mysql/ubuntu.lower-test

/usr/local/mysql/bin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2)

110623 21:49:41 [ERROR] Aborting

从日志上发现，mysql的启动目录不对。

修改启动命令：

sudo mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql &

即可启动成功:

110623 21:49:41 [Note]

110623 21:49:41 mysqld_safe mysqld from pid file /var/lib/mysql/ubuntu.pid ended

110623 21:57:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/

110623 21:57:21 InnoDB: The InnoDB memory heap is disabled

110623 21:57:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins

110623 21:57:21 InnoDB: Compressed tables use zlib 1.2.3.3

110623 21:57:21 InnoDB: Initializing buffer pool, size = 128.0M

110623 21:57:21 InnoDB: Completed initialization of buffer pool

110623 21:57:21 InnoDB: highest supported file format is Barracuda.

110623 21:57:21 InnoDB: Waiting for the background threads to start

110623 21:57:22 InnoDB: 1.1.7 startedlog sequence number 1607993

110623 21:57:22 [Note] Event Scheduler: Loaded 0 events

110623 21:57:22 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.5.13' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Source distribution

配置mysql 数据目录之后，重新启动服务，发现启动失败。 查询服务状态： 网上找了一下，几乎都是说新的目录没有权限导致，需要设置一下目录权限 但是我的目录权限已经修改过了，没有问题。看来遇到疑难杂症了，可能有其他问题。这个时候突然想到mysql配置了日志，为什么不去看一下日志。 打开 /var/log/mysqld.log ,赫然发现一些错误信息： 看来是 errmsg 文件找不到，我去目录下看了，确实没有。 我在根目录下搜索这个文件，得到以下的结果： 一看名字，就能猜出来和语言有关。 5.5版本的语法，需要在配置文件中修改配置语言选项 我现在是5.7版本，直接copy过去好了。 重启，结果还是不行，又查日志。

MySQL的异常处理分析如下：

标准格式

DECLARE

handler_type

HANDLER

FOR

condition_value[,...]

statement

handler_type:

CONTINUE

|

EXIT

|

UNDO

--这个暂时不支持

condition_value:

SQLSTATE

[VALUE]

sqlstate_value

|

condition_name

|

SQLWARNING

|

NOT

FOUND

|

SQLEXCEPTION

|

mysql_error_code

condition_value细节

1、常用MYSQL

ERROR

CODE

列表

http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

更多错误列表见MySQL安装路径下

比如我的/usr/local/mysql/share/mysql/errmsg.txt

说明一下：SQLSTATE

[VALUE]

sqlstate_value这种格式是专门为ANSI

SQL

和

ODBC以及其他的标准.

并不是所有的MySQL

ERROR

CODE

都映射到SQLSTATE。

2、如果你不想插ERROR

CODE的话，就用速记条件来代替

SQLWARNING

代表所有以01开头的错误代码

NOT

FOUND

代表所有以02开头的错误代码，当然也可以代表一个游标到达数据集的末尾。

SQLEXCEPTION

代表除了SQLWARNING和NOT

FOUND

的所有错误代码

3、我们现在就用手册上的例子

CREATE

TABLE

t

(s1

int,primary

key

(s1))

mysql>

use

t_girl

Database

changed

mysql>

CREATE

TABLE

t

(s1

int,primary

key

(s1))

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

mysql>

mysql>

DELIMITER

||

mysql>

CREATE

PROCEDURE

handlerdemo

()

->

BEGIN

->

DECLARE

EXIT

HANDLER

FOR

SQLSTATE

'23000'

BEGIN

END

--

遇到重复键值就退出

->

SET

@x

=

1

->

INSERT

INTO

t

VALUES

(1)

->

SET

@x

=

2

->

INSERT

INTO

t

VALUES

(1)

->

SET

@x

=

3

->

END||

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

DELIMITER

mysql>

call

handlerdemo()

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

select

@x

+------+

|

@x

|

+------+

|

2

|

+------+

1

row

in

set

(0.00

sec)

mysql>

call

handlerdemo()

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

select

@x

+------+

|

@x

|

+------+

|

1

|

+------+

1

row

in

set

(0.00

sec)

mysql>

现在来看一下遇到错误继续的情况

mysql>

truncate

table

t

Query

OK,

0

rows

affected

(0.01

sec)

mysql>

DELIMITER

$$

mysql>

DROP

PROCEDURE

IF

EXISTS

`t_girl`.`handlerdemo`$$

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

CREATE

DEFINER=`root`@`localhost`

PROCEDURE

`handlerdemo`()

->

BEGIN

->

DECLARE

CONTINUE

HANDLER

FOR

SQLSTATE

'23000'

BEGIN

END

->

SET

@x

=

1

->

INSERT

INTO

t

VALUES

(1)

->

SET

@x

=

2

->

INSERT

INTO

t

VALUES

(1)

->

SET

@x

=

3

->

END$$

Query

OK,

0

rows

affected

(0.01

sec)

mysql>

DELIMITER

mysql>

call

handlerdemo()

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

select

@x

+------+

|

@x

|

+------+

|

3

|

+------+

1

row

in

set

(0.00

sec)

mysql>

call

handlerdemo()

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

select

@x

+------+

|

@x

|

+------+

|

3

|

+------+

1

row

in

set

(0.00

sec)

mysql>

可以看到，始终执行到最后。

当然，上面的SQLSTATE

'23000'可以替换为1062

我们来看一下警告。

mysql>

alter

table

t

add

s2

int

not

null

Query

OK,

0

rows

affected

(0.01

sec)

Records:

0

Duplicates:

0

Warnings:

0

此列没有默认值，插入的时候会出现警告或者1364错误提示。

mysql>

DELIMITER

$$

mysql>

DROP

PROCEDURE

IF

EXISTS

`t_girl`.`handlerdemo`$$

Query

OK,

0

rows

affected,

1

warning

(0.00

sec)

mysql>

CREATE

DEFINER=`root`@`localhost`

PROCEDURE

`handlerdemo`()

->

BEGIN

->

DECLARE

CONTINUE

HANDLER

FOR

1062

BEGIN

END

->

DECLARE

CONTINUE

HANDLER

FOR

SQLWARNING

->

BEGIN

->

update

t

set

s2

=

2

->

END

->

DECLARE

CONTINUE

HANDLER

FOR

1364

->

BEGIN

->

INSERT

INTO

t(s1,s2)

VALUES

(1,3)

->

END

->

SET

@x

=

1

->

INSERT

INTO

t(s1)

VALUES

(1)

->

SET

@x

=

2

->

INSERT

INTO

t(s1)

VALUES

(1)

->

SET

@x

=

3

->

END$$

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

DELIMITER

mysql>

call

handlerdemo()

Query

OK,

0

rows

affected

(0.00

sec)

mysql>

select

*

from

t

+----+----+

|

s1

|

s2

|

+----+----+

|

1

|

3

|

+----+----+

1

row

in

set

(0.00

sec)

遇到错误的时候插入的新记录。

mysql>

select

@x

+------+

|

@x

|

+------+

|

3

|

+------+

1

row

in

set

(0.00

sec)

---