New Project from Project Template --> Query limit Error

đź’¬ General Discussion
1

Hello everyone,

I would really appreciate some help with the following issue.

Suite CRM version 7.9.4 but it isn’t version specific. We had it 7.7.8 we upgrade from as well.

We have created a project template (before upgrading to this version). Every time time a user tries to create a project from that template, he will set a Project Name, a Start Date, select the checkbox “Copy All Tasks With Resources:” and then click on Create.

The CRM will freeze for several minutes and after a while the following message is displayed in the browser window:

“Error: Query limit of 20000 reached for AM_ProjectTemplates module.”

The project is created in the background. When the user re-logins it will find it listed in Projects Module.

I googled a bit and the most common suggestion was to add the module in question to the special_query_limit in the config.php file. I added both Projects and Project Templates but it didn’t help.

Are there any other suggestions? We plan to use this way of work (projects based on project templates) a lot and we need the performance to be adequate…

2

What is the special query limit set to?

Does it still give the error 20000 reached?

3

Here’s a fancy query to get a view of your database table sizes:


SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

Look for overgrown tables there, maybe you really have a huge table for some reason. Instead of working to raise the limit, you’d be better off lowering the number of rows on the table, especially if you can tell they’re obviously not useful…

4

[quote=“mikesolomon” post=51322]What is the special query limit set to?

Does it still give the error 20000 reached?[/quote]

Special query limit is set to 50000. I added both Projects and Project Templates to special query list and I tried to create a project from the template twice. No query limit error, projects were created successfully both times but after about 15 minutes with the CRM being unresponsive in the meantime.

This the query output. I don’t think this is the issue in our case: http://imgur.com/a/B2vJl

5

The log is filled with slow query warnings such as the below ones, every time some user tries to create a project from a project template:

Any ideas? Does the issue have to do something with the query?

6

You have an extremely slow database, try repairing it, re-indexing it, restarting it, check if your hardware is failing (especially the hard disk where the database is), check also the network if your database is on a separate computer, etc.

7

Thank you for replying but it certainly isn’t a database related issue. We have a mysql server for a lot of websites, some portals much more demanding in resources that the CRM included and we have no performance issues (all sites share the same resources).

The CRM itself has no performance issues other than that. I have come to the conclusion that it is a bug and/or a badly written query.