mysqldump忽略视图

转载

码农小哥 2024-11-29 19:40:08

文章标签 mysqldump忽略视图数据库 java mysql sql 文章分类 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>

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%';

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

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

从公司用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`)));

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。