MySQL错误总结

 

1. 提示：ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'。

1.关闭mysql
   # service mysqld stop
2.屏蔽权限
   # mysqld_safe --skip-grant-tables &
3.新开起一个终端输入
   # mysql -uroot mysql
   mysql> FLUSH PRIVILEGES;//记得要这句话，否则如果关闭先前的终端，又会出现原来的错误

   mysql> GRANT ALL PRIVILEGES ON *.* TO dbuser@"%"  WITH GRANT OPTION;
   mysql> UPDATE user SET PASSWORD=PASSWORD('123456') , HOST='%' WHERE USER='root';
   mysql> delete from user where USER='';
   mysql> \q

 

2. MySQL错误：Got error 28 from storage engine

现象是：

 

MySQL 执行语句
  SELECT DISTINCT field_report_name_value  FROM content_type_product_line_report ;

 

报错

Got error 28 from storage engine

 

user warning: Got error 28 from storage engine query: select distinct(field_report_name_value) from content_type_product_line_report where field_report_product_line_nid='39'

 

产生该错误的原因是：
临时空间不够，无法执行此SQL语句。

解决办法：
清空/tmp目录，或者修改my.cnf中的tmpdir参数，指向具有足够空间目录。

 

 

3. The user specified as a definer (''@'') does not exist的解决办法

 

从公司用Navicat导出sql脚本的数据库，回去后再倒入，运行项目时控制台报的错误如下：
Caused by: java.sql.SQLException: The user specified as a definer (''@'') does not exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
        at org.hibernate.loader.Loader.doQuery(Loader.java:674)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
        at org.hibernate.loader.Loader.doList(Loader.java:2220)
        ... 73 more


以上的出错是上传新工程到服务器上时出的错。出错的意思是数据库访问时没有‘@’这个用户.这主要是在新建视图和存储过程时，直截在mysql可视图形工具里copy代码到服务器上去运行，mysql里会自动生成一些代码，比如copy出来的视图语句：
-- ----------------------------
-- View structure for `sys_teacher_role_view`
-- ----------------------------
DROP VIEW IF EXISTS `sys_teacher_role_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `sys_teacher_role_view` AS select `tea_teacher`.`teaid` AS `teaid`,`sys_user_role`.`employee_nember` AS `employee_nember`,`tea_teacher`.`staffnumbers` AS `staffnumbers`,`tea_teacher`.`teachername` AS `teachername`,`sys_user_role`.`role_id` AS `role_id`,`tea_teacher`.`identity_card` AS `identity_card` from (`tea_teacher` left join `sys_user_role` on((`sys_user_role`.`employee_nember` = `tea_teacher`.`teaid`)));

应该改为：
-- ----------------------------
-- View structure for `sys_teacher_role_view`
-- ----------------------------
DROP VIEW IF EXISTS `sys_teacher_role_view`;
CREATE VIEW `sys_teacher_role_view` AS select `tea_teacher`.`teaid` AS `teaid`,`sys_user_role`.`employee_nember` AS `employee_nember`,`tea_teacher`.`staffnumbers` AS `staffnumbers`,`tea_teacher`.`teachername` AS `teachername`,`sys_user_role`.`role_id` AS `role_id`,`tea_teacher`.`identity_card` AS `identity_card` from (`tea_teacher` left join `sys_user_role` on((`sys_user_role`.`employee_nember` = `tea_teacher`.`teaid`)));

存储过程也是一样的，往往就会忽略这个问题而导致上面的错误.

 

 

4. MySQL异常：ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes。

这是因为没有调整mysql的默认配置，默认最大只能处理16M的文件，而我要导入的伪造数据有50M的一个SQL。在命令行下也可以查看一下这个配置项的值：

show VARIABLES like '%max_allowed_packet%';

   返回结果是字节数，比如1048576，就是1M。

 

于是调整配置文件： /etc/my.cnf

修改mysql的最大允许包大小
在[mysqld]部分(不在这部分没用)添加一句：
max_allowed_packet=50M
重启mysql服务就可以了。