PHP Fatal error: Allowed memory size

Published 7 months ago by YoTosho

Hi everyones I know this error is common asked, I read every post ans discussion that I found but nothing did work for me. The problem is the next:

When I try to migrate from Laravel to SQL Server 2016 I can get one of two error:

  1. When I set my memory_limit parameter in the php.ini file with a value >= 1000M the CLI crashes without any massage, only crashes

  2. When the memory_limit is lower than 1000M then I get the next error

PHP Fatal error:  Allowed memory size of 402653184 bytes exhausted (tried to allocate 4096 bytes) in C:\xampp\htdocs\inteligencia\vendor\laravel\framework\src\Illuminate\Database\Grammar.php on line 96
PHP Fatal error:  Allowed memory size of 402653184 bytes exhausted (tried to allocate 32768 bytes) in C:\xampp\htdocs\inteligencia\vendor\symfony\debug\Exception\FatalErrorException.php on line 1
PS C:\xampp\htdocs\inteligencia> php memory_limit

The most curious is that isnt matter the memory_limit value the error is the same with the same values on its description

(tried to allocate 4096 bytes) (tried to allocate 32768 bytes)

I wasted hours with this error without results. I really hope you can help me with this one.

Thanks

lostdreamer_nl

Depending on how you are doing the migration it might take all data in memory and that can kill your memory_limit.

See what the current memory_limit is for the CLI

php -i | grep memory_limit

Just to make sure that you're editting the correct php.ini file

You can remove the memory limit in your migration script as well

ini_set('memory_limit', -1)

But eventually, it could be that you need to change the way you migrate your data to make sure you do not load to much data into memory at once.

YoTosho

Hi @lostdreamer_nl, I was checking

Cantidad total de memoria física:          8.089 MB
Memoria física disponible:                 4.876 MB
Memoria virtual: tamaño máximo:            9.625 MB
Memoria virtual: disponible:               6.273 MB
Memoria virtual: en uso:                   3.352 MB

And with the ini_set the CLI delay some minutes but in the end crashes

ini_set('memory_limit', -1)

The database is new, doesnt have any table, its totally empty.

click
click
7 months ago (74,930 XP)

What are you doing when this happens? With normal usage you should not need 400mb of memory.

update: nvm, I see you are trying to run a migration.

The culprit could be a slow (or huge) query you are running. Try to select and update in chunks. Or another cause could be that you collect a lot of data during the migration.

Or... an endless loop in your code

lostdreamer_nl

I'm guessing that you have something like the following in your migration code:

// pseudo code

$oldData = \DB::table('old_table')->get();
// do some magical data processing here
foreach($oldData as $row)  {
    Model::create($row);
}

First getting all old data in memory, then restructuring it, then re-importing it into the DB in the new way.

But when you get all that data into memory, at some point the script overflows your (physical) memory.

Without knowing the migration script, it's hard to say for sure.

If this is the case, you could try to change the way you migrate, for instance: getting the old data in chunks, processing them, inserting them in the new DB, and then loop back for the next chunk.

It might also be that you keep data in memory while it's not needed anymore. Something like:

$oldData = \DB::table('old_table')->get();
// do some magical data processing here
foreach($oldData as $row)  {
    Model::create($row);
}

$moreOldData = \DB::table('other_old_table')->get();
// do some magical data processing here
foreach($moreOldData as $row)  {
    DifferentModel::create($row);
}

If you do not unset() the $oldData variable when getting the next dataset, your memory will start climbing rapidly.

If you cant figure it out, try to anonymize the code if needed and post it here, we could have a look at how to refactor it.

YoTosho

@m-rk I'm only creating the two basics tables that came by default with laravel

  1. 2014_1012000000_create_users_table.php
  2. 2014_10_12_100000_create_password_resets_table.php

I wrote this and get the error

php artisan migrate

My database config is

'default' => env('DB_CONNECTION', 'sqlsrv'),

.
.
.

'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', '10.10.xx.xxx'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'WebService'),
            'username' => env('DB_USERNAME', 'laravel'),
            'password' => env('DB_PASSWORD', 'tG)P{JWf2Gt43L,Y'),
            'charset' => 'utf8',
            'prefix' => 'dbo.',
        ],

and the .env file

DB_CONNECTION=sqlsrv
DB_HOST=10.10.xx.xxx
DB_PORT=1433
DB_DATABASE=WebService
DB_USERNAME=laravel
DB_PASSWORD=tG)P{JWf2Gt43L,Y

This is the code from a migration file

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{    
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        ini_set('memory_limit', -1);
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}
click
click
7 months ago (74,930 XP)

Ok, that should work out of the box you would think. I do not have any experience with sql server.

Does your application even run in the browser? Are you able to create a simple controller that does not do any database calls?

And can you confirm that you can make a connection to your sql server?

To test this create a manual table on in your sql server database (if you do not have one already).

# enter in your console:
php artisan tinker

# now run this (change your table name)
\DB::selectOne('SELECT * FROM your_table'));
YoTosho

@m-rk the connection is succeed

PS C:\xampp\htdocs\inteligencia> php artisan tinker
Psy Shell v0.8.17 (PHP 7.2.1 — cli) by Justin Hileman
>>> \DB::selectOne('SELECT * FROM dbo.Test');
=> {#760
     +"ID": "92838",
   }
>>>   
djkevino

How much data are you trying to migrate? Is it just a small amount? Can you see what the last executed query was and find out where it stopped?

click
click
6 months ago (74,930 XP)

@djkevino as far as I understand now he is not running any big migrations. Only the two default migrations described in an earlier comment.

@YoTosho Ok, I am clueless now. Are you also able to run a route to a controller in your browser (read as: is your laravel application even working)? My suggestion would be to get xdebug running and step through the code and figure out what is eating all the memory.

lostdreamer_nl

ok, I think i might know whats happening here... We were all looking at 'migration' as migrating data from DB1 --> DB2

But you're just running the laravel migrations to create new tables in MSSQL and it errors out already.

This is definately a server issue instead of laravel.

Check your PDO SQLSRV drivers to see if they're up to date, I believe they will be outdated creating endless loops for you.

Chris_Aus

@YoTosho did you have any luck with a solution for this?

I'm getting the same issue php 7.2 commenting to SQL 2017 Express on Win Server 2016

Please sign in or create an account to participate in this conversation.