All geek questions in one place

Why does CodeIgniter exhausing allow memory size?

I get a memory exhaustion error where I should not occupy the memory!

The application is located in Windows 8 Server / IIS i / PHP 5.5 / CodeIgniter / MS SQL Server

The error is as follows:

[23-May-2014 10:56:57 America / New_York] PHP Fatal error: memory allowed 134217728 bytes exhausted (tried to allocate 1992 bytes) in C: \ inetpub \ wwwroot \ application \ models \ DW_import.php on line 112

[23-May-2014 11:07:34 America / New_York] PHP Fatal error: The allowed memory size is 134217728 bytes exhausted (tried to allocate 2438 bytes) in C: \ inetpub \ wwwroot \ application \ models \ DW_import.php on line 113

The script looks for several different CSV files in the directory to import into the database. Keep in mind that import files are huge, and some up to 4 gigabytes of data. As far as I can see, there are no variables that constantly combine data that can lead to this problem. The script (model) is executed (there is no view for this controller, only the model):

DW_import.php

<?php

class dw_import extends CI_Model {

    public function import(){

        global $file,$errLogFile,$logFile,$tableName, $fieldList, $file, $count, $line, $query;

        $this->load->database(); // init db connection

        // map file types to database tables
        $fileToDBArr = array(
            'Customers' => 'customer',
            'Customers_Historical' => 'customer_historical',
            'Orders' => 'order',
            'Customer_AR_Aggs' => 'customer_ar_aging_agg'
        );

        // extend timeout of this script
        ini_set('max_execution_time', 3600);

        // error handler to log errors and continue processing
        function myErrorHandler($errno,$errstr,$errfile,$errline){

            global $file,$errLogFile,$logFile,$tableName, $fieldList, $file, $count, $line, $query;
            // error - store in DB
            //echo "<br>[$errno $errstr $errfile $errline $tableName $file $count] $errLogFile<br>";
            $err = "#$errno $errstr $errfile on line $errline :: Table $tableName File $file Row# $count Headers: $fieldList Data: $line";
            echo $err;
            file_put_contents($errLogFile,$err,FILE_APPEND);
        };

        set_error_handler("myErrorHandler");

        // set temp error log file
        $errLogFile = "C:/Data_Updates/logs/general." . date('YmdHis') . ".errLog";

        // loop thru file types
        foreach($fileToDBArr as $fileType=>$table){
            // get the files for this import type
            $fileArr = glob('C:/Data_Updates/'.$fileType.'.*');
            sort($fileArr,SORT_STRING); // sort so earlier files (by date in file name) will process first

            // loop thru files found
            foreach($fileArr as $file){
                // set log file paths specific to this import file
                $errLogFile = str_replace('Data_Updates/','Data_Updates/logs/',$file) . "." . date('YmdHis') . ".errLog";
                $logFile = str_replace('Data_Updates/','Data_Updates/logs/',$file) . "." . date('YmdHis') . ".log";

                file_put_contents($logFile,"---BEGIN---",FILE_APPEND); // log

                // lets get the file type and translate it into a table name
                preg_match('/C:\/Data_Updates\/([^\.]+)/',$file,$matches);
                $fileType = $matches[1];
                $tableName = $fileToDBArr[$fileType];


                // lets get the first row as a field list
                $fp = fopen($file,'r');
                //$fieldList = str_replace('"','',fgets($fp));

                // counters to track status
                $count = 0;
                $startPoint = 0;

                // see if continuation, set startPoint to last row imported from file
                $query = "SELECT max(import_line) as maxline FROM $tableName WHERE import_file = '" . addslashes($file) . "'";
                $result = $this->db->query($query);

                foreach($result->result() as $row) $startPoint = $row->maxline+1; // set the startPoint if this is continuation

                file_put_contents($logFile,"\nstartPoint $startPoint",FILE_APPEND); // log      

                // loop thru file lines
                while (!feof($fp)) {
                    $line = fgets($fp);
                    // reformat those pesky dates from m/d/y to y-m-d
                    $line = preg_replace('/, ?(\d{1,2})\/(\d{1,2})\/(\d{4})/',',${3}-${1}-${2}',$line);

                    if(!$count){
                        // header row - set aside to use for column headers on insert statements
                        $fieldList = str_replace('"','',$line);
                        file_put_contents($logFile,"\nHeaders: $fieldList",FILE_APPEND); // log
                    } elseif($count >= $startPoint && trim($line)) {

                        // data row - insert into DB
                        $lineArr = str_getcsv($line); // turn this CSV line into an array
                        // build the insert query
                        $query = "INSERT INTO $tableName ($fieldList,import_date,import_file,import_line)
                        VALUES (";
                        foreach($lineArr as $k=>$v) $query .= ($v !== '') ? "'".addslashes(utf8_encode($v))."'," : " NULL,";
                        $query .= "now(),'" . addslashes($file). "',$count)
                        ON DUPLICATE KEY UPDATE ";
                        foreach(explode(',',$fieldList) as $k=>$v) $query .= "\n$v=" . (($lineArr[$k] !== '') ? "\"" . addslashes(utf8_encode($lineArr[$k])) . "\"" : "NULL") . ", ";
                        $query .= "import_date = now(),import_file='" . addslashes($file) . "',import_line = $count ";


                        if(!$this->db->query($query)) {
                            trigger_error('db error ' . $this->db->_error_number() . ' ' . $this->db->_error_message());
                            $status = 'error ';
                        } else {
                            $status = 'success ';   
                        };

                        file_put_contents($logFile,"row: $count status: $status data: $line",FILE_APPEND); // log'

                    } else {
                        // skipped - this row was already imported from this file
                        // removed log to speed up
                        file_put_contents($logFile,"row: $count status: SKIPPED data: $line",FILE_APPEND); // log
                    }; // if $count
                    $count++;
                }; // while $fp
                fclose($fp);

                // file complete - move file to archive
                rename($file,str_replace('Data_Updates/','Data_Updates/archive/',$file));
                file_put_contents($logFile,"-- END --",FILE_APPEND); // log
            }; // each $fileArr

        }; // each $globArr

    } // end import function
} // end class 

?>

Any help would be appreciated!

******** EDIT

Based on the recommendations of several people, I added some changes. These changes only affect the "data row row in DB" section of the loop logic. You can see the addition of a log to track memory_get_peak_usage, the addition of unset () and clearcachestat (). below code is some log data:

                        file_put_contents($logFile,memory_get_peak_usage() . " line 1 \n\r",FILE_APPEND); 
                        // data row - insert into DB
                        if(isset($lineArr)) unset($lineArr); 
                        file_put_contents($logFile,memory_get_peak_usage() . " line 1.1 \n\r",FILE_APPEND);
                        $lineArr = str_getcsv($line); // turn this CSV line into an array
                        // build the insert query
                        file_put_contents($logFile,memory_get_peak_usage() . " line 2 lineArr size: " . strlen(implode(',',$lineArr)) . "\n\r",FILE_APPEND);
                        if(isset($query)) unset($query);  
                        file_put_contents($logFile,memory_get_peak_usage() . " line 2.1 lineArr size: " . strlen(implode(',',$lineArr)) . "\n\r",FILE_APPEND);
                        $query = "INSERT INTO $tableName ($fieldList,import_date,import_file,import_line)
                        VALUES (";
                        file_put_contents($logFile,memory_get_peak_usage() . " line 2.2 lineArr size: " . strlen(implode(',',$lineArr)) . "\n\r",FILE_APPEND);
                        foreach($lineArr as $k=>$v) $query .= ($v !== '') ? "'".addslashes(utf8_encode($v))."'," : " NULL,";
                        $query .= "now(),'" . addslashes($file). "',$count)
                        ON DUPLICATE KEY UPDATE ";
                        file_put_contents($logFile,memory_get_peak_usage() . " line 2.3 lineArr size: " . strlen(implode(',',$lineArr)) . "\n\r",FILE_APPEND);

                        foreach(explode(',',$fieldList) as $k=>$v) $query .= "\n$v=" . (($lineArr[$k] !== '') ? "\"" . addslashes(utf8_encode($lineArr[$k])) . "\"" : "NULL") . ", ";
                        file_put_contents($logFile,memory_get_peak_usage() . " line 2.4 lineArr size: " . strlen(implode(',',$lineArr)) . "\n\r",FILE_APPEND);
                        $query .= "import_date = now(),import_file='" . addslashes($file) . "',import_line = $count ";
                        file_put_contents($logFile,memory_get_peak_usage() . " line 3 query size: " . strlen($query) . "\n\r",FILE_APPEND);

                        if(!$this->db->query($query)) {
                            trigger_error('db error ' . $this->db->_error_number() . ' ' . $this->db->_error_message());
                            $status = 'error ';
                        } else {
                            $status = 'success ';   
                        };

                        clearstatcache();

Log data: (the leftmost number is the result of calling memory_get_peak_usage ()

2724960 line 1.1 
2724960 line 2 lineArr size: 194
2724960 line 2.1 lineArr size: 194
2724960 line 2.2 lineArr size: 194
2724960 line 2.3 lineArr size: 194
2727392 line 2.4 lineArr size: 194
2727392 line 3 query size: 2346

2727392 line 1 
2727392 line 1.1 
2727392 line 2 lineArr size: 194
2727392 line 2.1 lineArr size: 194
2727392 line 2.2 lineArr size: 194
2727392 line 2.3 lineArr size: 194
2729944 line 2.4 lineArr size: 194
2729944 line 3 query size: 2346

2729944 line 1 
2729944 line 1.1 
2729944 line 2 lineArr size: 194
2729944 line 2.1 lineArr size: 194
2729944 line 2.2 lineArr size: 194
2729944 line 2.3 lineArr size: 194
2732448 line 2.4 lineArr size: 194
2732448 line 3 query size: 2346

2732448 line 1.1 
2732448 line 2 lineArr size: 194
2732448 line 2.1 lineArr size: 194
2732448 line 2.2 lineArr size: 194
2732448 line 2.3 lineArr size: 194
2735088 line 2.4 lineArr size: 194
2735088 line 3 query size: 2346

, 2.3 2.4, :

foreach(explode(',',$fieldList) as $k=>$v) $query .= "\n$v=" . (($lineArr[$k] !== '') ? "\"" . addslashes(utf8_encode($lineArr[$k])) . "\"" : "NULL") . ", ";

?

+4
2

:

$this->load->database(); // init db connection, already in code
$this->db->save_queries = false; // ADD THIS LINE TO SOLVE ISSUE

CodeIgniter. CI, -, , /. , , . CI .

, , memory_get_peak_usage() , , (PHP-?).

(unset, clearstatcache ..) , .

+6

set_time_limit(0) . mram, clearstatcache();

0

Source: https://habr.com/ru/post/1541805/

More articles:


All Articles