I am trying to export all my contacts and accounts (63K+) but suite CRM crashes every time i try to execute- I had heard that this may be a well known issue, has anybody worked around this issue yet?
check you php config, you should add more execution time, 63K of data is not exported in 30seconds
best regards
As mike said (but being more specific), look at your php.ini file, a value called max_execution_time.
I need to export 450,000 records - what should the max execution be set to? Any other configuration changes need to be made as well?
Many thanks in advance
Just try it, whatever works is good. Update the max_execution_time as much as you need. Then you can bring it back down after the operation.
You can also look at memory size in php.ini, in case that becomes a problem (you should see out of memory errors in php_errors.log).
Also, if exporting doesn’t work for such big quantities (I’m curious to find out if it does, and how long it takes), you can always get your data directly from the database using phpMyAdmin.
I have tried to increase the time to 7200 and higher however it still hangs then errors out within the same period of time regardless of the settings. I’ve also increased the max memory setting to 2048, 4096 and higher however that seems to make it crash even faster.
Any idea why the database would exhibit the same behavior regardless of the execution time and max memory settings? It complains about the memory allocation however giving it more RAM makes no difference.
The system has 32 GB of memory and I could assign 8 GB if needed but that probably won’t help either.
I’m running out of options 
If you could post exact information, it would help. Sometimes the difference between a working configuration and a non-working one is a very very small detail. So I would be able to help you better if you posted the exact text of your configurations, the exact error messages you’re getting, etc.
You might not be using the correct php.ini. There are several in a typical Linux system. You should try checking if your values are getting used by confirming with phpinfo(). You can get that form Admin/Diagnostics.
I am assuming you are trying the exports from within SuiteCRM app, but if you’re using some kind of custom script please let me know.
Let me know if you don’t get my reply including the diagnostics file which was icnluded.
I didn’t get anything. How did you send it, did you try replying here?
Anyway, you don’t have to send me any file. Just check if the values you’re using in php.ini are taking effect as they show in phpinfo.
My value for max_execution_time is 1000 and my value for memory_limit is 512M (notice the M) and I’ve never had problems with this. I wouldn’t mind making them a lot higher though, if needed.
I am running XAMPP in Windows 7 Ultimate with the system having 32 GB of memory. I have verified permissions and all is set properly far as I can tell.
I have set the max upload file size to 300 MB in Admin - System Settings.
The Add File option isn’t working when I attempt to attached the diagnostic file.
My php.ini settings are:
max_execution_time=6000
max_input_time=720
memory_limit=512M
max_input_time = -1
output_buffering=4096
error_reporting=E_ALL & ~E_DEPRECATED & ~E_STRICT & ~E_NOTICE
post_max_size=256M
[21-Apr-2017 14:05:07 Europe/Berlin] PHP Fatal error: Out of memory (allocated 1040187392) (tried to allocate 28672 bytes) in C:\xampp\htdocs\SuiteCRM\include\export_utils.php on line 955
[21-Apr-2017 14:05:07 Europe/Berlin] PHP Fatal error: Out of memory (allocated 1040187392) (tried to allocate 20480 bytes) in Unknown on line 0
[21-Apr-2017 14:05:07 Europe/Berlin] PHP Fatal error: Out of memory (allocated 2097152) (tried to allocate 229376 bytes) in C:\xampp\htdocs\SuiteCRM\custom\include\language\en_us.lang.php on line 590352
The diagnostic report for phpinfo is as follows:
Calendar support enabled
Core
PHP Version 7.0.4
Directive Local Value Master Value
allow_url_fopen On On
allow_url_include Off Off
arg_separator.input & &
arg_separator.output & &
auto_append_file no value no value
auto_globals_jit On On
auto_prepend_file no value no value
browscap C:\xampp\php\extras\browscap.ini C:\xampp\php\extras\browscap.ini
default_charset UTF-8 UTF-8
default_mimetype text/html text/html
disable_classes no value no value
disable_functions no value no value
display_errors Off Off
display_startup_errors On On
doc_root no value no value
docref_ext no value no value
docref_root no value no value
enable_dl Off Off
enable_post_data_reading On On
error_append_string no value no value
error_log C:\xampp\php\logs\php_error_log C:\xampp\php\logs\php_error_log
error_prepend_string no value no value
error_reporting 22519 22519
exit_on_timeout Off Off
expose_php On On
extension_dir C:\xampp\php\ext C:\xampp\php\ext
file_uploads On On
highlight.comment #FF8000 #FF8000
highlight.default #0000BB #0000BB
highlight.html #000000 #000000
highlight.keyword #007700 #007700
highlight.string #DD0000 #DD0000
html_errors On On
ignore_repeated_errors Off Off
ignore_repeated_source Off Off
ignore_user_abort Off Off
implicit_flush Off Off
include_path C:\xampp\htdocs\SuiteCRM;C:\xampp\htdocs\SuiteCRM\include\HTMLPurifier/standalone;C:\xampp\htdocs\SuiteCRM\include/…;C:\xampp\php\PEAR C:\xampp\php\PEAR
input_encoding no value no value
internal_encoding no value no value
log_errors On On
log_errors_max_len 1024 1024
mail.add_x_header On On
mail.force_extra_parameters no value no value
mail.log no value no value
max_execution_time 3600 6000
max_file_uploads 20 20
max_input_nesting_level 64 64
max_input_time 720 720
max_input_vars 1000 1000
memory_limit 1024M 1024M
open_basedir no value no value
output_buffering 4096 4096
output_encoding no value no value
output_handler no value no value
post_max_size 256M 256M
precision 14 14
realpath_cache_size 16K 16K
realpath_cache_ttl 120 120
register_argc_argv Off Off
report_memleaks On On
report_zend_debug On On
request_order GP GP
sendmail_from no value no value
sendmail_path no value no value
serialize_precision 17 17
short_open_tag Off Off
SMTP localhost localhost
smtp_port 25 25
sql.safe_mode Off Off
sys_temp_dir no value no value
track_errors Off Off
unserialize_callback_func no value no value
upload_max_filesize 512M 512M
upload_tmp_dir C:\xampp\tmp C:\xampp\tmp
user_dir no value no value
user_ini.cache_ttl 300 300
user_ini.filename .user.ini .user.ini
variables_order GPCS GPCS
windows.show_crt_warning Off Off
xmlrpc_error_number 0 0
xmlrpc_errors Off Off
zend.assertions 1 1
zend.detect_unicode On On
zend.enable_gc On On
zend.multibyte Off Off
zend.script_encoding no value no value
Here is my diagnostics file - hopefully you will get it this time
You don’t need to send that zip file, phpinfo is enough. You don’t even need to generate so many diagnostics, just tick phpinfo option. I recommend deleting that post with the file, you’re posting your tables, your customizations, online for everyone to see…
-
Is the php.ini file you’ve been editing C:\xampp\php\php.ini ?
-
If you change the memory_limit to 2048M does the error change to something like “allocated 2080374784” or does it stay the same?
Note that getting phpinfo from a command-line invocation of php is not the same thing as getting it from within the web server. Different php.ini’s apply. So keep checking your vales only from within Admin/diagnostics.
An alternative is to create a file in the root of your system called info.php with this:
<?php
phpinfo();
?>
and then you can easily type in your browser http://www.yourdomain.com/info.php
I changed max memory from 1024 to 2048M and now get the error:
Out of memory (allocated 1189085184) (tried to allocate 138412080 bytes) in C:\xampp\htdocs\SuiteCRM\include\export_utils.php on line 443
What’s your answer to my question number 1 above, please?
I’m still not 100% convinced that the values you’re changing are getting recognized by your system.
Or maybe you’re simply trying to export too much stuff (450.000 is a lot). It might be a better option to export directly from MySQL…
How do I export all the fields including custom fields from MySQL? I tried an export and my custom fields aren’t exported. I’d like the same fields exported through MySQL as SuiteCRM does when using the interface.
Gonna try exporting through Google Chrome to see if it works using that browser instead of Firefox - will let you know if that works.
I have tried changing max memory to 16192M and 8096M with no effect
attached is the phpinfo with 8096M as max memory
I am working with the C:\xampp\php\php.ini file when changing values.
Nope still not working
Guess my only option is to somehow export the data through My SQL - I tried through MySQL but so far have only been able to export the standard fields
In oder to export everything correctly you must have knowledge of the database schema.
The following is an example query to export contacts including custom fields to a csv file.
select * from contacts inner join contacts_cstm on contacts.id = contacts_cstm.id_c INTO OUTFILE 'c:/contacts.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';This method has some drawbacks:
. the query doesn’t add the headers so you have to do it manaully or through another query
. if the output file (in my example it is: contacts.csv) already exists you will get an error so make sure you delete it before executing the query
. my example query only considers the table contacts and contacts_cstm (custom fields) but it doesn’t take into account other information such as email addresses, which are stored in a different table and other information related into other tables
. to make it complete you will have to work out a more complex query, given the complexity of the database I don’t think it will be easy in one single query
Additional info
In order to be able to run this query I have had to comment out by prepending a semi-colon the line with secure_file_priv= in my.ini
Comment:
It will be probably easier if you write a php script that loops through the database and collects all information from the necessary tables and then appends to a csv file. It will be quite long to execute but you can do it in different runs
2 Likes
I ran the query (copy & pasted the code) and got the following error:
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0013 seconds.)
Any idea what went wrong?
Thanks again
You can start with just this part of the query to make it simpler and show results inside phpMyAdmin:
select * from contacts inner join contacts_cstm on contacts.id = contacts_cstm.id_c
But 0 rows… did you start by clicking the suitecrm database, then clicking “SQL” and entering the query?
You do have Contacts on your database, right? And you do have a contacts_cstm table?