2

I'm making a second connection of my project in laravel with a view in an MsSql database, I configured my .env and config correctly, however this is an error of memory overflow:

$ php artisan tinker
Psy Shell v0.10.5 (PHP 7.3.24-3+ubuntu18.04.1+deb.sury.org+1 — cli) by Justin Hileman
>>> use App\Condinvest\BoletoPropCondominio as BPC
>>> BPC::first();
Illuminate\Database\QueryException with message 'SQLSTATE[HY001] Unable to allocate sufficient memory (meudominio.com.br:5000) (severity 8) (SQL: select top 1 * from [View_Boleto_Prop_Condominio])'
>>>

already changed in my php.ini:

memory_limit = 128M

but the error continues.

My models briefly look like this:

BaseView.php

<?php
    
namespace App\Condinvest;

use Illuminate\Database\Eloquent\Model;

class BaseView extends Model
{
    protected $connection = 'condinvest';
}

BoletoPropCondominio.php

<?php

namespace App\Condinvest;

class BoletoPropCondominio extends BaseView
{
    protected $table = 'View_Boleto_Prop_Condominio';

    protected $fillable = [
        'Id_Condo_lan',
        ...
        'Id_titular'
    ];
}

when I do the same query directly through the command terminal:

SELECT TOP 1 * FROM View_Boleto_Prop_Condominio;

returns my data successfully.

Can anyone tell me what may be happening, or how I can debug better to understand where the error is, please.

EDIT

>>> DB::connection('condinvest')->getConfig()['driver']
=> "sqlsrv"
Marcius Leandro
  • 775
  • 1
  • 11
  • 34
  • How much did you increase `memory_limit ` to? – dazed-and-confused Dec 21 '20 at 19:16
  • I put memory_limit = -1 – Marcius Leandro Dec 21 '20 at 19:22
  • When I had an issue with the memory, setting it to -1 did not resolve it even though I was expecting it to. I then tried something like 2G and the error cleared up. I wasn't trying to use the package you are but figured I'd at least share my experience with this error from a month ago. – dazed-and-confused Dec 21 '20 at 19:38
  • Not solve, I tried to restart with "apache2ctl restart" was not too, is there any chance of the error being on the server side of the database? – Marcius Leandro Dec 21 '20 at 20:55
  • 1
    @MarciusLeandro Never set the memory limit to -1, that means there is no limit anymore. I think it is not the php memory limit, because that would throw a php error and not an sql error. Anyway, the solution should not be increasing the memory limit, but decreasing the memory usage. – Gert B. Jan 28 '21 at 07:34
  • what is your mssql version? – bhucho Jan 28 '21 at 10:08
  • @bhucho I don't know, the database is from a third party, and I only have access to the route of a view – Marcius Leandro Jan 28 '21 at 10:28
  • The error is returned from the database itself. It appears to be some memory issue, so please share the specs and which services you're running inside the same environment. – sykez Jan 28 '21 at 18:14
  • 3
    Are other queries working? – Olivier Jan 29 '21 at 08:49
  • straight through the terminal yes, through the tinker none – Marcius Leandro Feb 03 '21 at 12:59
  • @MarciusLeandro , it sounds that the issue exists in either DB configuration or tinker if you have no problem to connect mssql DB from Laravel. Please check allocated memory in DB just in case. please refer to [check mssql server memory](https://www.mssqltips.com/sqlservertip/4182/setting-a-fixed-amount-of-memory-for-sql-server/). If enough memory is allocated to mssql, tinker might have compatibility issue with mssql. – John Feb 04 '21 at 04:05

2 Answers2

4

Since the error is apparently being reported by the database process (not the php process), I would not expect changes to memory limits in php.ini to have any effect.

I found this issue which mentions this specific error when using a deprecated driver with MSSQL Server. To check which driver Laravel is using, type DB::connection()->getConfig()['driver'] into your Tinker console. If you see sqlsrv then everything is ok here, but if you see dblib then this might be the source of the error. This problem was supposedly fixed in Laravel 5.7 to prefer the supported drivers if more than one is available, but it's also possible that your database.php config file uses the wrong one.

It is also possible that the memory limits of the database server or the system it resides on are actually being exceeded. Being able to run the query in a command prompt without getting the error suggests that this is not the case, but it may still be worth investigating. If the available memory is very low then it's possible that there is not enough to run both php and the database query at the same time. You can check the available system memory by running the free -h command in the terminal, as long as the database process is running on the same machine as your terminal. However, if you are using a shared hosting provider then it is possible that the database is on a separate machine.

Travis Britz
  • 5,094
  • 2
  • 20
  • 35
  • I saw in several places talking could be this, but I fixed it on the drive, and the error still persists – Marcius Leandro Feb 03 '21 at 12:56
  • @MarciusLeandro what version of Laravel are you running (`php artisan --version`)? Also, what were the results for checking the free system memory, and what was the database driver being used by the `DB` connection? – Travis Britz Feb 03 '21 at 17:36
-1

If it helps, i encoutered the same issue. What i did in order to fix it was to check my configuration in database.php, if you use sqlserver, make sure you have charset set to utf8 as follows. It was previously set to utf8mb4.

'sqlserver' => [
        'driver' => 'sqlsrv',
        'host' => env('DB_SQL_HOST'),
        'port' => env('DB_SQL_PORT'),
        'database' => env('DB_SQL_DATABASE', 'forge'),
        'username' => env('DB_SQL_USERNAME', 'forge'),
        'password' => env('DB_SQL_PASSWORD', ''),
        'charset' => 'utf8',
        'prefix' => '',
        'options' => [
            PDO::ATTR_TIMEOUT => 300
        ]
    ]
Florin Ciocirlan
  • 29
  • 3