“MySQL 服务器已经消失" [英] "MySQL server has gone away"

问题描述

我使用的是 Joomla 2.5.8 并安装了一个自定义组件，该组件从数据库中获取数据并使用 TCPDF 生成自定义 PDF 文件.

I'm using Joomla 2.5.8 and have a custom component installed that fetches data from the DB and generates custom PDF files using TCPDF.

根据用户的请求，模型从数据库加载一堆数据，没问题，没有超时，请求需要几秒钟，之后没有更多的查询发送到数据库.根据接收到的数据进行处理生成PDF文件，并将生成的PDF文件列表重新调整到布局中显示的视图.

Upon request by the user, the model loads a bunch of data from the DB, which is OK, there is no timeout, the request takes a few seconds and there is no more query sent to the database afterwards. Based on the data received, a processing is done to generate PDF files and the list of PDF files generated is retuned to the view to be displayed in the layout.

似乎一切正常，处理也正确完成，但可能是由于模型中的处理时间过长，菜单模块未加载并返回以下错误:

Everything seems to work fine and processing completes correctly but, probably due to the long processing that is done in the model, the menu module is not loading and the following error is returned:

MySQL 服务器不见了

MySQL server has gone away

SQL:

SELECT m.id, m.title, m.module, m.position, m.content, 
  m.showtitle, m.params, mm.menuid 
FROM jos2_modules AS m LEFT JOIN 
  jos2_modules_menu AS mm ON mm.moduleid = m.id LEFT JOIN 
  jos2_extensions AS e ON e.element = m.module AND e.client_id = m.client_id 
WHERE m.published = 1 AND e.enabled = 1 AND 
  (m.publish_up = '0000-00-00 00:00:00' OR m.publish_up <= '2013-10-31 15:42:00') AND
  (m.publish_down = '0000-00-00 00:00:00' OR m.publish_down >= '2013-10-31 15:42:00') AND 
  m.access IN (1,1,2,3,4) AND m.client_id = 0 AND 
  (mm.menuid = 101 OR mm.menuid <= 0) 
ORDER BY m.position, m.ordering

注意，如果我去掉PDF文件处理，是没有错误的.我无法控制在此处理中花费的时间，但也许我没有在正确的地方做这件事.

Note that if I remove the PDF file processing, there is no error. I have no control on the time spent in this processing but maybe I'm not doing it in the right place.

我还从之前的帖子中了解到，MySQL 配置中有一些参数可以在 MySQL ini 文件中进行调整，但这不是我可以做的，因为 SQL 服务器由我的提供商托管.此外，发生错误时我无法访问数据库.

I also understand from previous posts that there are parameters in the MySQL configuration that can be tuned in MySQL ini files but this is not something I can do as the SQL server is hosted by my provider. Moreover, I'm not having access to the database when the error occurs.

有没有人遇到过这种问题?

Has anybody already experience that kind of problem ?

推荐答案

这可能是超时问题.

您是否正在运行用于创建许多 pdf 文件的单个查询?如果是这样，它可能超时，或者 pdf 文件创建可能会关闭查询(更有可能).

Are you running a single query which is used to create many pdf files? If so it could timeout, or the pdf file creation might be closing the query (more likely).

您应该尝试为要创建的每个文档重新运行查询，开销很小，如果查询相同，它们将被缓存.

You should try re-running the query for each document you want to create, the overhead is minimal, if the queries are the same they will be cached.

您发布的查询似乎很快，对于所有查询，请确保存在索引以保持快速.(不是解决方案，但可以解决您的问题).最后，如果查询真正超时，您可以一次创建一个文档并通过 ajax 调用呈现结果，因此您的服务器一次只生成一个文档并且可能不会超时.这也将使网站更快地出现在用户面前.

The query you posted seems pretty fast, for all queries make sure indexes are present to keep them fast. (not a solution but could solve your problem). Finally, if the queries are in real timeout, you could create the documents one at a time and present the result with an ajax call, so your server only generates one document at a time and possibly doesn't timeout. This will also make the site appear quicker to the user.

您也可以尝试增加 php 脚本 set_time_limit(200);，我不确定这是否会有效，如果他们必须这样做，这将是一个糟糕的用户体验等待三分钟才能看到页面，他们会在超时前点击重新加载.

Also you might try increasing the of the php script set_time_limit(200);, I'm not sure if this will be effective though, and it will be a lousy user experience if they have to wait three minutes to see the page, they'll just hit reload before the timeout.

这篇关于“MySQL 服务器已经消失"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！