org.hibernate.engine.jdbc.spi.SqlExceptionHelper 147 - 查询包太大

I'm getting the below error when doing DB query using hibernate where it says the packet for query is too large. I don't quite understand from where it is coming.

我在使用hibernate进行数据库查询时遇到以下错误,它说查询的数据包太大了。我不太清楚它的来源。

21-07-2015 13:36:10,615 INFO [http-bio-9091-exec-7] com.aricent.aricloud.util.LoggerAspect 47 - Entered class com.aricent.aricloud.dao.AricloudDAOImpl Method checkUserName
21-07-2015 13:36:10,626 WARN [http-bio-9091-exec-7] org.hibernate.engine.jdbc.spi.SqlExceptionHelper 145 - SQL Error: 0, SQLState: S1000
21-07-2015 13:36:10,630 ERROR [http-bio-9091-exec-7] org.hibernate.engine.jdbc.spi.SqlExceptionHelper 147 - Packet for query is too large (1083 > 1024). You can change this value on the server by setting the max_allowed_packet' variable.

21-07-2015 13:36:10,615 INFO [http-bio-9091-exec-7] com.aricent.aricloud.util.LoggerAspect 47 - 输入的类com.aricent.aricloud.dao.AricloudDAOImpl方法checkUserName 21-07- 2015 13:36:10,626 WARN [http-bio-9091-exec-7] org.hibernate.engine.jdbc.spi.SqlExceptionHelper 145 - SQL错误:0,SQLState:S1000 21-07-2015 13:36:10,630错误[http-bio-9091-exec-7] org.hibernate.engine.jdbc.spi.SqlExceptionHelper 147 - 查询包太大(1083> 1024)。您可以通过设置max_allowed_pa​​cket'变量在服务器上更改此值。

I have set the variable max_allowed_packet=40M , but the packet seems to take 1024 as value. Where is this value coming from?

我设置了变量max_allowed_pa​​cket = 40M,但数据包似乎取1024作为值。这个值来自哪里?

Here is my method for select calling on which the issue is happening. The UserEntity table has only 13 rows in it and no other tables are having much data. If I restart mysql the application starts working, Please let me know how to fix the issue permanently ?

这是我选择调用问题的方法。 UserEntity表中只有13行,没有其他表有很多数据。如果我重启mysql应用程序开始工作,请让我知道如何永久解决问题?

@Override
@SuppressWarnings("unchecked")
@Transactional
public UserEntity checkUserName(String userName) {
    Session session = mySessionFactory.getCurrentSession();
    Query q = session.createQuery("SELECT e FROM UserEntity e WHERE userName = :userName");
    q.setParameter(USER_NAME, userName);

    List<UserEntity> list = q.list();
    if (!list.isEmpty()) {
        return list.get(0);
    } else {
        return null;
    }
}

1 个解决方案

#1

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

如果使用大型BLOB列或长字符串,则必须增加此值。它应该与您想要使用的最大BLOB一样大。 max_allowed_pa​​cket的协议限制为1GB。该值应为1024的倍数; nonmultiples向下舍入到最接近的倍数。