Link to home
Create AccountLog in
Avatar of Daniele Brunengo
Daniele BrunengoFlag for Italy

asked on

phpmyadmin memory error

Hello, I have a problem on my CentOS 6.5 server related to phpmyadmin.

If I click on Export (not for a particular database, but for all the databases) I get a 500 error, which translated into this error in the Apache log:
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 256 bytes) in /var/www/html/phpmyadmin/libraries/dbi/DBIMysqli.class.php on line 299 

Open in new window


I have tried increasing memory limit up to 512MB in php.ini, but I keep getting this error after restarting apache.

I have also updated phpmyadmin to the latest version my php version allows, but I still get the error.

There are no errors listed in phpmyadmin home page, the configuration should be ok.

Versions: phpmyadmin 4.4.15.10, php 5.4.45, mysql 5.5.48

I managed to export the databases from the command line, so that's no problem, but I'd like to get rid of this error.

There is a ton of php scripts running on the server, but the only memory error I get is this one.

Does anyone have any suggestion?
Avatar of arnold
arnold
Flag of United States of America image

The issue is a restriction by Apache not php. Apache or the OS is preventing or unable to allocate the additional memory phpmyadmin needs or needed by MySQL to respond to the query you have.

What are the resources on your system?

Top -n 1
Vmstat
.
Make sure you are editing the right php.ini

CLI:
php --ini

Open in new window


Should tell you which php.ini is in use.
You could try

<?php ini_set("memory_limit", "512M"); ?>

Open in new window


In your code too.
Avatar of Daniele Brunengo

ASKER

Yes, it's the correct php.ini, I've edited it many times before actually.

About the resources, I don't see anything wrong.

top shows very few resources being used, and this is vmstat's output:

User generated image
About the <?php ini_set("memory_limit", "512M"); ?>, I could do that but I'd have to modify a phpmyadmin core file. I'd rather understand the reason behind this error.
How much memory does your system have? What is the swap set to? Looks like 16GB

Lets try a different way to achieve what you want without the use of export from phpmyadmin which you already have done.
Either export a db at a time using phpmyadmin . I suspect the issue is a combination that includes MySQL db and that might be the cause of the .........
I.e. If you choose two DBS presuming you have more than two,
Do you have a config file configured for PHPMyAdmin, if so there may be the following found in it:

$cfg['MemoryLimit'] = xxx;

Open in new window


Where xxx is a figure.

If this is the case it may be overriding your php.ini.
Swap and memory are 16GB.

I have already exported via command line, my aim here is not to export databases but to understand the reason for the error and, most of all, if I should be worried or not.

Let's remember that the error comes up as soon as I click on the export tab. I don't even get to the export button, because I can't load the page.

I will try to export each database anyway, to see if there's a particular database causing problems. I have A TON of them.
About
$cfg['MemoryLimit'] = xxx;

Open in new window

I had checked that out but there is no such line in the config file.

I also tried to add such a line using 256M or 512M, but this didn't fix the error.
For the sake of debugging you could try to set the memory_limit variable to -1 to disable it.

It's a risky thing to do, but might shed some light on the situation if you're not in a production environment.
Also, this may be a silly point to make, but you are definitely restarting the apache process every time you edit the memory limit?
Yes, I restarted it every time.

Trouble is, this is very much a production environment, so I must tread carefully.
ASKER CERTIFIED SOLUTION
Avatar of Chris Jones
Chris Jones
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Also consider adjusting the max_execution_time variable?
Yeah, I have another server and I can also replicate it on local.

I'll try out your new suggestions as soon as I get respite from work.

I had thought about the .htaccess, but wouldn't that need a full url pointing to phpmyadmin? Right now I only access it using my server's IP, for security reasons mostly.
.htaccess shouldn't be dependant on URL in the sense that you're suggesting unless you are specifying domains.
But to make .htaccess work I usually create a file in /etc/httpd/sites-available (and then link it in sites-enabled) where I tell Apache to let that particular folder, associated with an url, override stuff with .htaccess.

Can I do this with a folder that's not associated with an url, too? Never tried that.
At the moment you're just running off 000-default?

I think it should still work, but honestly I haven't tried. If I understand correctly the topology you have I think it should function by default.

I can't offer any guarantees though :)
This is a sample virtual host I'm using:

<VirtualHost *:80>
	ServerName www.example.it
	ServerAlias example.it
		DocumentRoot /var/www/html/example/

        <Directory />
                Options FollowSymLinks
                AllowOverride None
                Order Deny,Allow
                Deny from All
        </Directory>
        <Directory /var/www/html/example/>
                Options -Indexes FollowSymLinks MultiViews
                AllowOverride All
                Order allow,deny
                allow from all
        </Directory>

</VirtualHost>

Open in new window


I'm not running off 000-default, I changed stuff. In httpd.conf I have AllowOverride None, while the default I think is AllowOverride All.
If you are the only person to use the PHPMyAdmin on the server you could do a simple test.

Create an .htaccess in the PHPMyAdmin folder and have it contain the following:
DENY FROM ALL

Open in new window


Try to load the phpmyadmin application, if it gives you a 403 then you can say that .htaccess is almost certainly working.

From there you can test the memory_limit override.
As I thought, htaccess doesn't work. DENY FROM ALL doesn't stop me from entering the application.
Can you create a page called memtest-EE.php in your phpmyadmin directory with the following contents:

<?php echo ini_get("memory_limit"); ?>

Open in new window


and call it in your browser, what is the figure returned?
I managed to use .htaccess creating a different virtual host file.

"deny from all" worked, so I tried increasing memory with
php_value memory_limit 512M

Open in new window

(there is a typo in your command above)
and it worked!

Still don't get why increasing php memory for all processes didn't, though. But I'm positive I was modifying the correct php.ini.
Sorry about the typo :)

Glad you got it working!
Thanks a lot for your help. I learned stuff in the process, too.
You're welcome, me too!
.htaccess controls Apache httpd which is what the setting does.
PHP is not the same as Apache, if PHP were not installed, the .htaccess line would have no bearing on Apache, as it states it is a php_value.

Are you thinking about the LimitRequestBody Directive?:
http://httpd.apache.org/docs/2.4/mod/core.html#limitrequestbody
You can have the entry and it will gave no impact.
The htaccess tells Apache how to behave in regards to php meaning Apache which is the overseer of all items running under it, Apache httpd child processes are the ones that spawn php they are the ones that restrict the amount of space ...

Let's try it this way, you ave an office manager (Apache httpd)
Comes tenant one and asks for an office with a space of 600k. The office is allocated
Comes tenant 2 (php) asking for 135MB, there are no offices of that size, so the request is declined.
tenant 2 goes to office owner, (the asker) , saying they need the space. Office owner tells the office manager whenever tenant 2 or anyone like tenant 2 was larger space office, make appropriate adjustments to provide the office space"
After this directive the office manager makes the requisite adjustment to provide the space requested.

With this directive, if you have a php page with a runaway query, as much memory upto 512MB will ve allocated to the process while before as phpmyadmin that page would have been terminated once the memory resources it needed reached 128MB or there a about.
So what solution would you have implemented? Perhaps the user would be better off with an apache based answer.

I'm assuming we're not talking about recompilation of apache pre-fork/worker type low level solution?
The solution you provided with the link, combines the various settings.
 the htaccess directive is processed by apache and passed to php it launches to handle the page.
since the asker made an attempt to update the php.ini
memory_limit=512MB from the default 128MB (original crash reference)  that is usually set and it did not resolve the issue, but the .htaccess with a memory allocation adjustment did. .......

not clear why the change did not take effect, while the .htaccess change whicih is passed on access when a php process is started ......
Yes, that last thing remains unclear. I'll check out some other configuration files to see if I can understand it, but I'll need to have time. The original problem is fixed though. I'll post here any new information I get about this.
The .htaccess php_value overrides php.ini set parameters, this suggests that Apache may have a default restriction somewhere within its configuration


One option to test
Create a single page
<?php
Phpinfo();
?>
place this file anywhere and see what info it includes on memory_limits compared to this file located within the phpmyadmin location.

I.e. Since Apache passes arguments/parameters/environment variables to the PHP that it starts when needed which overrides the php.ini set parameter.
Actually, I found out that the php.ini being used has been different since I installed a web application in april.

I get this:

Configuration File (php.ini) Path	/etc
Loaded Configuration File	/var/www/html/[application folder]/php.ini

Open in new window


How the heck did the loaded ini file change like that?
In my original post, I suggested you did "php --ini", but you indicated when you did that the ini file was normal. Had this value changed since then, or was this an oversight?
No, actually this is php --ini output, which is different from the above:

 php --ini
Configuration File (php.ini) Path: /etc
Loaded Configuration File:         /etc/php.ini
Scan for additional .ini files in: /etc/php.d
Additional .ini files parsed:      /etc/php.d/curl.ini,
/etc/php.d/dom.ini,
/etc/php.d/fileinfo.ini,
/etc/php.d/gd.ini,
/etc/php.d/gmp.ini,
/etc/php.d/json.ini,
/etc/php.d/mbstring.ini,
/etc/php.d/mcrypt.ini,
/etc/php.d/mysql.ini,
/etc/php.d/mysqli.ini,
/etc/php.d/odbc.ini,
/etc/php.d/pdo.ini,
/etc/php.d/pdo_mysql.ini,
/etc/php.d/pdo_odbc.ini,
/etc/php.d/pdo_sqlite.ini,
/etc/php.d/phar.ini,
/etc/php.d/sqlite3.ini,
/etc/php.d/wddx.ini,
/etc/php.d/xmlreader.ini,
/etc/php.d/xmlwriter.ini,
/etc/php.d/xsl.ini,
/etc/php.d/zip.ini

Open in new window

Check if you have .htaccess under the root /var/www/html that includes directives for php/settings.

Presumably the phpinfo(); is what revealed this.


The application has their own crequirements/custom settings.
No, there's no .htaccess under that directory.
It's worth doing this:
find /var/www/html -type f | grep .htaccess

Open in new window

To identify any .htaccess anywhere in their directory hierarchy.
There are about fifty, the directory holds all the hosted websites on the server and most of them have a custom htaccess.

I have a local backup of the whole folder, I will take a look at those htaccess files.
Great, hopefully that will give you some insight :)

If you want to only check the directories you have concern with right now then you can just run the find command again but with /var/www/html changed to the base directory of your issue :)
when hosting multiple le sites, what is your setup consist of?
do you have a general standard httpd.conf with each site has its own domain.conf in the /etc/httpd/conf.d/ folder
usually, you would not place hosted sites in the /var/www/html which is the default site.
often to make the option available to access a site while the domain is registered transfer, part of your individual site conf file by including an <alias definition thast would use something akin to a customer ID under the root ...

This type of setup might prevent issues down the road where an app installed for one client, impacts all others if it makes changes to the main httpd.conf file.
I have a single conf file for each website. I'm using the sites available and sites enabled folders to store each website configuration.
Each file points to a different subfolder under the /var/www/html folder. I've been working like this for years, actually.
Usually this is done to avail the user with the option to see their site as http://www.yourdomain.com/mydomain

Though I have not seen an install of a web app i.e. CMS wordpress, jango, joomla, etc. that or a client app as you have...
I would make sure that the source of the redirect of the php.ini to a control by a client or within the web path as it can be abused...
No, actually you create a file for each domain and have each domain point to a different folder.

You can find a summary of the setup here. It's for Centos 7, but it's basically the same for Centos 6.5.

https://www.digitalocean.com/community/tutorials/how-to-set-up-apache-virtual-hosts-on-centos-7
Anyway, I can't find anything pointing to the "rogue" php.ini file. This is really baffling.
Since you mentioned that the php.ini was running via an application you installed, it might be worth either sharing the name of the application with us, or doing your own quick Google search to see if you can bring up any results indicating others being presented with the same issue?
It's here:
http://www.openstamanager.com/

The whole site is in italian though, no translation that I can see.
Unfortunately, the setting of the php file could be part of the httpd start script by altering the default search for php.

/etc/httpd/conf.d do you have any conf file that reflects the php changes?


Httpd.conf ...
I found it:
 
		 PHPINIDir /var/www/html/[application folder]/

Open in new window


It was in the app virtualhost file. I was positive this would have worked for the application only, looks like I was dead wrong.

But really, shouldn't this work just for that application? Shouldn't all the others be using /etc/php.ini?
Where was it it defined as part of the virtualHost S its own somesite.conf in /etc/httpd/conf.d or it is ...

Httpd.conf includes /etc/httpd/conf.d/*.conf
Where the PHPINIDir is within the
<virtualHost>
PHPINIDIr
</virtualHost>

If it is outside that block I think it applies to all no matter in which .conf file it is.
Since the .htaccess add the directive to use that phpconfig should be within the location where the app is.

As .htaccess proved, settings applied to Apache override system settings.

I've not gone through the site/info, I based on your experience, it does not seem that they expect that their web based app will be installed on a server that has multiple sites.
Always satisfying to hear something like that being identified! :)
It is inside the virtualhost file which connects the domain name for the application with the folder containing the app itself (which is the same folder with the rogue php.ini). The file itself is in the sites-enabled folder, like every other virtualhost in use on the server.

I will try to remove that voice from the virtualhost file and put it in an htaccess file inside the folder where the app resides.
Sorry, sites-available. The sites-enabled folder contains only links.
The distinction deals with whether the entry is

#exposed and is set on the Apache top level
PHPINDir

<virtualHost >
ServerName somesite


</virtualHost>

Or
Confined to the virtual host parameter

<virtualHost >
ServerName somesite
PHPINDir



</virtualHost>


You can make the change above, and run apachectl configtest
It will report if there are syntax error in the config versus restarting Apache and discovering the conflict.