首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL服务器配置问题

问MySQL服务器配置问题
EN

Stack Overflow用户
提问于 2015-05-06 07:24:16
回答 1查看 1.4K关注 0票数 0

正如我的主题所述,max_allowed_packet变量不起作用。我有一个eclipse项目,它加载csv文件并通过servlet将它们存储到mysql数据库中。对于小于100万的文件,它可以正常工作。但是我的文件是95 my大,并且我在mysqld部分的my.ini文件中将‘my.ini’变量增加到了500米。但它不起作用。Eclipse给了我同样的错误。请看这两张照片:

月食

​

​

my.ini

我的..。

​

​

谢谢你的帮助

编辑*

代码语言:javascript
复制
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (190104490 > 26214400). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3540)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
at upload.Upload.doPost(Upload.java:71)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

这是文件上传并存储在mysql中的主要代码:

代码语言:javascript
复制
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    response.setContentType("text/html;charset=UTF-8");

    Part part = request.getPart("file");

    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    InputStream in = part.getInputStream();
    CSV csv = new CSV();


    String name = null;
    String header = part.getHeader("content-disposition");
    if(header.contains("filename"))
    {
        name = header.substring(header.lastIndexOf("=") + 1, header.length()).replace("\"", "");
    }


    int i = 0;
    while((i = in.read()) != -1 )
    {
        baos.write(i);
    }
    csv.setCsvFile(baos.toByteArray());
    csv.setName(name);
    baos.flush();

    try {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    }
    try {           
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/loadcsvup","*****","*******");        
        PreparedStatement stmt = con.prepareStatement("INSERT INTO loadcsvup.csvfiles (csv_name,file) VALUES (?,?)");
        stmt.setString(1, csv.getName());
        stmt.setBytes(2, csv.getCsvFile());         
        stmt.executeUpdate();

        stmt.close();
        con.close();

    } catch (SQLException e) {
        e.printStackTrace();
    }


}

编辑2*

好的,现在我已经在my.ini文件的de部分设置了varibale。似乎是工作。但是现在我得到了这个错误代码:

代码语言:javascript
复制
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The size of   BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
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:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.Util.getInstance(Util.java:372)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2535)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1911)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2145)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2081)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2066)
at upload.Upload.doPost(Upload.java:71)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)

我把可变的东西设置成这样:

代码语言:javascript
复制
[mysqld]
innodb_file_per_table=1
innodb_buffer_pool_size=1GB
innodb_log_file_size=128M
innodb_log_buffer_size=8M
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-05-06 08:02:13

检查点

  • 更改my.ini后,您是否重新启动了mysql服务?
  • 运行查询SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'并查看值。它应该是500 be (等字节)
  • 如果上面的查询没有返回500米,那么尝试运行SET GLOBAL max_allowed_packet = 500 * 1024 * 1024。然后重复第二步。
  • 如果值显示为500 M,则重复文件上载。

如果问题仍然存在,那么它可能就像mysql正在寻找不同的变量一样。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/30070123

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档