MYSQL ERROR 1396 (HY000) 错误的解决办法

mysql中创建一个database和用户，并赋权限：

create database dbdna

CREATE USER 'dbdna'@'%' IDENTIFIED BY 'dbdna'

GRANT ALL ON dbdna.* TO 'dbdna'@'%'

一旦删除database，还需要删除用户，有时候疏忽忘记删除的话，再次创建用户会报Error Code: 1396。

遇到过几次，每次都忘记怎么解决，关键是网上的很多做法都是错误的，汗一个。。。好记性不如烂笔头啊。

解决方法如下：

root登录后

drop user 'dbdna'

CREATE USER 'dbdna'@'%' IDENTIFIED BY 'dbdna'

GRANT ALL ON dbdna.* TO 'dbdna'@'%'

网上很多都是用delete删除用户，试过不管用

参考： http://stackoverflow.com/questions/5555328/error-1396-hy000-operation-create-user-failed-for-jacklocalhost

MySQL中出现连接错误:

ERROR

1045

(28000):

Access

denied

for

user

----------------------------------------------------------------------------------

1.添加用户

shell>mysql

mysql>use

mysql

mysql>grant

all

privileges

on

*.*

to

'test'

identified

by

'test'

with

grant

option

mysql>flush

privileges

[也可以使用下面两种方式建立用户]

------------------------

$1直接 *** 作mysql.user这张表

$2使用DDL语言,

定义用户:create

user

test

identified

by

'test'

删除用户:drop

user

test

------------------------

2.使用test登录(总是无法连接)

shell>mysql

-u

test

-p

ERROR

1045

(28000):

Access

denied

for

user

'test'@'localhost'

(using

password:

YES)

3.查看user表,我们会发现user表中的user字段有NULL

mysql>

select

host,user

from

user

+------------------+------+

|

host

|

user

|

+------------------+------+

|

%

|

test

|

|

localhost

|

|

|

localhost

|

root

|

|

test.saeg.com.cn

|

|

|

test.saeg.com.cn

|

root

|

+------------------+------+

5

rows

in

set

(0.00

sec)

4.删除user.user中值为NULL的,或更新NULL为test

4.1)delete

from

user

where

user

is

NULL

4.2)update

user

set

user='test'

where

user

is

NULL

问题解决

另:[我的系统是RHEL4.0]

1.mysql的配置文件my-*.cnf

/usr/local/mysql/

2.初始化数据库

/usr/bin/mysql_install_db

---