Unable to export large number of leads.

[Richie]

Using SuiteCRM 7.3 - Unable to export large volume of leads. Exporting of contacts and leads to excell works fine for small volumes, but not 14,000 leads. When I check “all” leads and “export,” the browser page turns white and hangs every time. I have never been able to get this to work even in previous versions of SuiteCRM. I added the Custom Export Plug-in - still same result.

Any suggestions or solutions out there? Thanks,

[John]

Hi,

It might be worth increasing the Timeout session length.

On our wiki, we recommend setting these in your php.ini:

Set post_max_size to at least 60MB
Set upload_max_filesize settings to at least 60MB
Set max_input_time to a large number
Set memory_limit to 256MB 

If you make these changes, you’ll have to restart your web server.

There’s also a post about increasing timeout limits here: https://suitecrm.com/forum/suitecrm-7-0-discussion/4456-session-time-out-increase

Does this help your issue?

[Richie]

Hi John -

Thanks very much - your advice was the solution. I increased the max_input_time from 60s to 180s and increased memory_limit from 128mb to 256mb. Works great. That was a BIG help!

Best wishes,

[dspaan]

Hi,

I have the same problem, when i export 20 records from my custom module it goes fine but when i export 4000 records i get a white screen.

I changed the php.ini settings to recommended values but the problem remains after restarting apache.

In the error log i see this:

Fout bij exporteren A001_Platform_vastelasten:
.SELECT
a001_platform_vastelasten.,
email_addresses.email_address email_address,
‘’ email_addresses_non_primary, users.user_name as assigned_user_name FROM a001_platform_vastelasten LEFT JOIN users
ON a001_platform_vastelasten.assigned_user_id=users.id LEFT JOIN email_addr_bean_rel on a001_platform_vastelasten.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module = ‘A001_Platform_vastelasten’ and email_addr_bean_rel.deleted = 0 and email_addr_bean_rel.primary_address = 1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id LEFT JOIN a001_platform_vastelasten_cstm ON a001_platform_vastelasten.id = a001_platform_vastelasten_cstm.id_c WHERE () AND a001_platform_vastelasten.deleted=0 Query Failed: SELECT
a001_platform_vastelasten.,
email_addresses.email_address email_address,
‘’ email_addresses_non_primary, users.user_name as assigned_user_name FROM a001_platform_vastelasten LEFT JOIN users
ON a001_platform_vastelasten.assigned_user_id=users.id LEFT JOIN email_addr_bean_rel on a001_platform_vastelasten.id = email_addr_bean_rel.bean_id and email_addr_bean_rel.bean_module = ‘A001_Platform_vastelasten’ and email_addr_bean_rel.deleted = 0 and email_addr_bean_rel.primary_address = 1 LEFT JOIN email_addresses on email_addresses.id = email_addr_bean_rel.email_address_id LEFT JOIN a001_platform_vastelasten_cstm ON a001_platform_vastelasten.id = a001_platform_vastelasten_cstm.id_c WHERE () AND a001_platform_vastelasten.deleted=0 : MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘) AND a001_platform_vastelasten.deleted=0’ at line 5

[amariussi]

Have you tried increasing further the figures? Including max_execution_time?
Try for time related something like 600 and for size someting like 1024MB (provided there are enough resources in the system)

What variables have you set? Are you sure yu increased all the necessary variables?

The error message you posted says, at the end:

MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND a001_platform_vastelasten.

There may be a problem with a custom field.

[dspaan]

Hi,

I did set everything to quite extreme values:

post_max_size to at least 250MB
upload_max_filesize settings to at least 250MB
max_input_time to 5000
memory_limit to 2048MB

In php.ini enabled error logging and got this:

[quote]
[06-Mar-2017 21:40:03 Europe/Amsterdam] PHP Strict Standards: Only variables should be passed by reference in /srv/www/htdocs/suitecrm/include/export_utils.php on line 556
[06-Mar-2017 21:40:03 Europe/Amsterdam] PHP Notice: Undefined variable: where in /srv/www/htdocs/suitecrm/include/export_utils.php on line 559
[06-Mar-2017 21:40:03 Europe/Amsterdam] PHP Notice: Undefined variable: where in /srv/www/htdocs/suitecrm/include/export_utils.php on line 560[/quote]

But i think that had to do with strict logging.

Permissions are like this:

sudo chown -R www-data:www-data .

sudo chmod -R 755 .

sudo chmod -R 775 cache custom modules themes data upload config_override.php

I followed this guide:

http://support.sugarcrm.com/Knowledge_Base/Troubleshooting/Troubleshooting_Blank_Pages_or_Partial_Page_Loads/

When i use inspect mode in Chrome i do not see any error in the console when i get that white screen.

I also used quick repair and removed the cache folder. No improvement so far. I turned display errors on, but nothing.

I’m using a custom module which i created with module builder, how can i find out which field could be causing this?

Which variables are you referring too?

[amariussi]

Towards the end of the query there is a where statement with open and close parentheses: WHERE () AND …

I am not sure, but that seems to be an error. Something seems to be missing there. There should be something in the parentheses.

Unfortunately I am not familiar with this part of the application. I hope that someone who knows it better can help.

[dspaan]

What i did now is go to system settings and change the maximum records on page in listview to 5000 (because the total nr. of records i wanted to export was 4300) and now i can export without a problem! So this means it has to do with pagination?

[dspaan]

It looks like i found a workaround for my problem. The original problem is still unfixed. What i do now is create a quickfiler on the listview page to only show the records that were created on a certain date (this still is the same amount of 4000+ records that i wanted to export) and then i can export without problems. So adding a filter in the mix fixes the query for some reason.

[billlee]

@amariussi

Towards the end of the query there is a where statement with open and close parentheses: WHERE () AND …

I am not sure, but that seems to be an error. Something seems to be missing there. There should be something in the parentheses.

Unfortunately I am not familiar with this part of the application. I hope that someone who knows it better can help.

Yes this seems to be a bug as the emtpy () shows that you did not choose a filter, if you choose a filter it will work fine but thats not really the solution.