mysql导数据遇到错误提示ERROR 1227,Acess denied

如果项目中的mysql驱动使用是如下版本:

作者:天一阁图书管理员

【说明】今天还是完成了一定东西的,上午是jdbc的测试,下午是
数据库连接池 和 dbutils
的测试,晚上是mybatis的测试,都是找了一些网上的例子运行了一下,解决出现的问题。

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.9</version></dependency>

今天群里有前同事在问mysqldump出来的sql文件在导入的时候,报错error1227(42000)at
line 18: Acess denied; you need (at least one of) the SUPER privilege(s)
for this operation

一:今日完成

请升级至如下版本(5版本的最后一个版本):

定位到18行显示的是一个这样的语句: set @@session.sql_log_bin=0;

1)junit注解的意义

<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.46</version></dependency>

按正常情况来说是不会出现这种情况的,这个语句只是控制当前session的查询是否写入binlog。群里的人都觉得这个问题很奇怪。

图片 1

解决了:不能连接除了3306之外端口服务的bug。

解决方案1:
大家看到既然是权限问题,那么就提权吧,或者使用root吧。但是前同事说是跳板机转过去的,没有办法用root,而提权和授权(grant)也需要root权限。方案1被否。

 2)如何安装maven插件

项目背景:

1.项目中使用的c3p0;2.mysql驱动版本 5.1.9

解决方案2:
既然不能用root,只能想其它办法了,但是我疑惑的是,为什么会出现用户无法控制自己的session变量的情况?于是搜索了一下,跳到mysql官网,找到这样一段描述:
sql_log_bin
This variable controls whether logging to the binary log is done. The default value is 1 (do logging). To change logging for the current session, change the session value of this variable. The session user must have the [SUPER ](https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_super) privilege to set this variable. *Setting this variable to 0 prevents GTIDs from being assigned to transactions in the binary log*. If you are using GTIDs for replication, this means that, even when binary logging is later enabled once again, the GTIDs written into the log from this point do not account for any transactions that occurred in the meantime—in effect, those transactions are lost. In MySQL 5.7, it is not possible to set @@session.sql_log_bin within a transaction or subquery. (Bug #53437)
注意最后一行,上面说MySQL5.7有个bug,无法在子查询或者事务中执行set
@@session.sql_log_bin。但是尽信书不如无书,我自己在mysql中执行了一下:

其中groupId,
artifactId, version这三个组合标示依赖的具体工程,而且
这个依赖工程必需是maven中心包管理范围内的,如果碰上非开源包,maven支持不了这个包,那么则有有三种
方法处理:

项目中报错信息:
2018-05-25 18:42:59,692 INFO AbstractPoolBackedDataSource:203 - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge46g9v15v8fgr1fzep76|391eb4d7, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge46g9v15v8fgr1fzep76|391eb4d7, idleConnectionTestPeriod -> 0, initialPoolSize -> 2, jdbcUrl -> jdbc:mysql://192.168.1.41:3307/uat_enginev4_mysql, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 4, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 2, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ] 2018-05-25 18:43:30,059 WARN BasicResourcePool:214 - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4345974b -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts . Last acquisition attempt exception: java.sql.SQLException: null, message from server: "Host '192.168.16.168' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'" at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1104) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) at sun.reflect.GeneratedConstructorAccessor21.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1127) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1114) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1855) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.16-0ubuntu0.16.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> set @@session.sql_log_bin=0;
Query OK, 0 rows affected (0.03 sec)

1.本地安装这个插件install
plugin

jdbc报错信息:
java.sql.SQLException: Access denied for user 'root'@'192.168.16.168' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919) at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3996) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247)

说明这个bug已经修复了,那这个同事用的mysql版本是不是声名狼藉的mysql5.6?让他确认了一下,确实是5.6。到此问题的根源找到了,解决方法也就有了。直接删除sql中所有的set
@@session.sql_log_bin语句就可以了。

例如:mvn
install:intall-file -Dfile=non-maven-proj.jar -DgroupId=som.group
-DartifactId=non-maven-proj -Dversion=1

排查过程:

1.使用客户端工具Navicat测试可以连通;2.使用Druid
问题依然存在;3.写了一个单元测试是ok的;(当时没有注意我的单元测试使用的版本是5.1.40,项目中使用的5.1.9,自坑了3个小时,汗颜!!!)[单元测试尽量写在当前使用的项目中,切记]4.网上各种搜索,大部分说是mysql启动配置文件有问题,害的DBA同事各种验证各种改;5.问题解决之前也在官方网站寻找相关,可惜没找到。像这么大的bug,官方应该有说明才是,比如修复列表之类的,可喜的是找到了。详见[
Nehlmeier在5.1.10中已修复)

重要链接:(Mysql Bugs Home)
[说明:可在此页面提bug也可以检索别人提的bug或已解决的bug]

2.创建自己的repositories并且部署这个包,使用类似上面的deploy:deploy-file命令,

3.设置scope为system,并且指定系统路径。

 

3)出现的一个bug,经过调整发现是dbcp和mysql驱动存在版本不兼容问题

图片 2

4)这是查看dbcp源码发现的代码问题

图片 3

修改如下(其实可以在头部导入包的)

图片 4

5)c3p0数据库连接池的使用

图片 5

5)下面是目录结构

图片 6图片 7图片 8图片 9

6)这是看的一个微信公众号的推送例子

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website