Fatal ERROR in Moodle

Fatal ERROR in Moodle

by Ahmed Eissa -
Number of replies: 11

Dear All,

I am building a block which needs to execute this SQL statement:

$sql = 'SELECT c.fullname AS CourseName, from_unixtime(l.time) AS DateandTime, u.username AS Username,
           l.ip AS IPAddress, u.firstname AS FirstName, u.lastname AS LastName, u.email AS Email, l.module As Activity,
           l.action As Action, l.url As URL, l.info AS Information, r.roleid AS RoleID, l.userid AS UserID, c.id as CourseID
           FROM {log} l, {user} u, {role_assignments} r, {course} c
           WHERE r.userid = l.userid AND c.id = ' . $courseid;

   if($Flag == 0)
   {
      $sql = $sql . " ORDER BY DateandTime Desc";
   }
   elseif($Flag == 1)
   {
      $sql = $sql . " ORDER BY Username Asc";
   }

 

Where "$courseid" is the selected course ID. when executed, Moodle is returning this ERROR message:

 

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2048 bytes) in D:\Moodle\lib\weblib.php on line 1277

 

I did use this php command:

 

ini_set('memory_limit', '-1');

 

But I know that this is not a solution.

 

Could anyone help me in this and I will be appreciated?

 

Thanks in advance.

 

Best regards,

 

Ahmed

Average of ratings: -
In reply to Ahmed Eissa

Re: Fatal ERROR in Moodle

by Ahmed Eissa -

Now after modifying the SQL statement, I am getting this ERROR:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 925 bytes) in D:\Moodle\lib\dml\moodle_database.php on line 842

 

And when refreshing the page, I will get this error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 528 bytes) in D:\Moodle\lib\dml\mysqli_native_moodle_database.php on line 1022

 

Is this a BUG in Moodle?

 

Thanks in advance.

Average of ratings: -
In reply to Ahmed Eissa

Re: Fatal ERROR in Moodle

by Miguel Santos -

Hi Ahmed,

    I am not sure if just setting memory_limit is going to be the best solution.  I looked at your query and it is not very optimized.  I tweaked it and included some other tables to pair down the rows returned

 

$sql = 'SELECT c.fullname AS CourseName, from_unixtime(l.time) AS DateandTime,
u.username AS Username, l.ip AS IPAddress,
u.firstname AS FirstName, u.lastname AS LastName, u.email AS Email,
l.module As Activity, l.action As Action, l.url As URL, l.info AS Information,
r.roleid AS RoleID, l.userid AS UserID, c.id as CourseID
FROM {log} l JOIN {user} u ON l.userid = u.id
JOIN {course} c ON l.course = c.id
JOIN {context} cx ON cx.contextlevel = 50 AND l.course = cx.instanceid
JOIN {role_assignments} r ON cx.id = r.contextid AND l.userid = r.userid
WHERE l.course = ' . $courseid;

 

I think that will give you the same results quicker and much less overhead.  

 

Hope that works for you.

Average of ratings: -
In reply to Miguel Santos

Re: Fatal ERROR in Moodle

by Ahmed Eissa -

Hi Miguel,

Many thanks for your time and help.

I tested my SQL query on "MySQL Workbench" and it's works fine. My query is producing 21599 records, while your query is producing only 71 records. I need the 21599 records as that what should the code produce.

 

Any suggestions?

Thanks in advance.

Best regards,

Ahmed

 

Average of ratings: -
In reply to Ahmed Eissa

Re: Fatal ERROR in Moodle

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Unless you really need all 21599 records in memory at once (which would explain your problem) - can I suggest 2 alternatives:

1. $rs = $DB->get_recordset_sql - you can then loop through the results using 'foreach ($rs as $record)', but each record is loaded into memory one at a time.

2. Page the results - use the limit + limitfrom params for get_recordset_sql / get_records_sql to only grab a subset of the results at a time, then use $OUTPUT->paging_bar to generate a paging bar to select which page of results to display (I find it hard to imagine most users wanting to see over 20000 results on one page).

 

Average of ratings: -
In reply to Davo Smith

Re: Fatal ERROR in Moodle

by Ahmed Eissa -

Dear Davo,

 

I am already using get_recordset_sql to get the 21599 records, and also looping into it using foreach keyword like what you said.

I don't want to display the results to the user, as I need them to be in the memory so I could do further calculations. So, I can't page the results.

 

Any other suggestions?

 

Thanks in advance.

 

Ahmed

 

Average of ratings: -
In reply to Ahmed Eissa

Re: Fatal ERROR in Moodle

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Whatever calculations you need to be doing on the results, you are going to have to find some way to do them one record at a time.

Unless I've missed something, you haven't made it clear what you are actually trying to do with the results, but whatever it is I'm sure there must be some way to generate the final data you want without storing all of the records in memory at once (e.g. keeping a running total of some kind, or writing the results out to a data file as they are calculated, then discarding the record from memory once it has been processed).

You could also try the raise_memory_limit Moodle function to give yourself a bit of extra space to work with - but you may still find that your method is running up against a fundamental problem of wanting to store more data in memory than is available for use on your server.

 

Average of ratings: -
In reply to Davo Smith

Re: Fatal ERROR in Moodle

by Ahmed Eissa -

Hi Davo,

The problem is that the error occurred while executing not after executing. Means that it happens while generating the results in the record set.

 

When I looked into the "moodle\lib\weblib.php" which causing one of the errors at line 1277, I find this comment:

   
    if ($strcache === false or count($strcache) > 2000) { // this number might need some tuning to limit memory usage in cron
        $strcache = array();
    }

 

Why the author put this comment?

 

Also, I can't put the results into a file, as after uploading the code to the main server, I don't have permissions to do that.

 

Any suggestions?

 

Thanks in advance.

 

Ahmed

Average of ratings: -
In reply to Ahmed Eissa

Re: Fatal ERROR in Moodle

by lior gil -
Picture of Core developers

Hi Ahmed,

It looks like you want to temporarily duplicate a chunk of the DB and work from it. I can only guess your goal is to access multiple rows and you want to avoid the calling to the DB every time.

A while ago I wrote a code that used the log table for some statistics. The way I chose was to page the query and after every time I ran through the rows and filtered them into pre-made arrays.

So after the query finished I was left with the information already stored and partially organized inside corresponding arrays. This allowed me to process over 300,000 records without reaching any memory limit.

I hope this will help.

Average of ratings: Useful (1)
In reply to lior gil

Re: Fatal ERROR in Moodle

by Ahmed Eissa -

Hi lior gil,

Many thanks for your reply; could you please give me a code example so I could understand more?

 

Thanks in advance.

 

Ahmed

Average of ratings: -
In reply to Ahmed Eissa

Re: Fatal ERROR in Moodle

by lior gil -
Picture of Core developers

Don't know if this is the best approach but this is what I came to.

I wrote it for 1.9 so here is a small converted snippet:

 

if (!$rows_num = $DB->count_records_sql($sql_count))

return -1;

$page_start = 0;

$page_limit = 2000;

while ($page_start < $rows_num) {

if ($rows = $DB->get_records_sql($sql, $params, $page_start, $page_limit) {

foreach ($rows as $row)

filter_function($row);

}

$page_start+=$page_limit;

}

 

To save time, I decided to loop only once through the results after every query.

I don't think writing the function itself will help you because it's designed for my own uses. I had no need for detailed data like you do so I mainly used arrays with counters and some of the information.

I can only suggest that you'll try to minimize the stored data.

For example, every row contains the user details. You can just ask for the user id and ip address and afterwards run a separate query for the users to be stored in another array and you'll be able to use it later on. This alone will clear the memory of 4 fields X 21599 rows.

There are plenty of other optimizations to think of. It does require a little bit more coding but if you need all of this data at hand this will definitely be worth it.

Average of ratings: Useful (1)
In reply to lior gil

Re: Fatal ERROR in Moodle

by Ahmed Eissa -

Hi lior gil,

 

Many thanks for your reply; I will try it and will let you know.

 

Thanks.

 

Ahmed

Average of ratings: -