Query Locked All Other MySQL Processes

[Resentful]

Hello, everyone. I've recently switched hosts and the same script I was running in the past is now causing the rest of my MySQL queries to be locked, and it seems the query never completes.

The code is:

<?
    $con = mysqli_connect('localhost','user','password', 'database');
    $Q = "DELETE FROM `Table1` WHERE `Field1` IN (SELECT `Field2` FROM `Table2`)";
    if(mysqli_query($con,$Q)){
    echo 'entries deleted from table1';
    }else{
    echo 'delete failed: '.mysqli_error($con);
    }
?>

The code is used to delete entries from a table if they exist in another table. I've researched the problem and tried every solution I could find. I've changed my MySQL settings and reset the SQL server several times without any luck. I am running a VPS with 2 gigabytes of RAM and plenty of processing power.

If anyone has any suggestions or knows another method, I'd love to hear it.

Thanks for taking the time to read this,

Resentful

[veedeoo]

Hi,

On your vps account, what do you have? WHM panel ? or something else?

What is your setting value for

    mysql.max_persistent 

You can read more of the most common settings here. Anything where it says php_ini system under changeable column can be adjusted or changed on your servers loaded configuration file. loaded configuration file can be found by running

phpinfo();   

[Resentful]

I have cPanel/WHM on my VPS. My mysql.maxpersistent is set to -1.

I appreciate the answer. Is there anything you would suggest checking/changing now?

[Resentful]

It appears the issue has stopped occuring for now. I'll close this for now and hope for the best.

Thank you Veedeoo and everyone who stopped by.

Best wishes,

Resentful

[veedeoo]

You may want to check the values assigned to max_input_time, max_input_vars, memory_limit, post_max_size , max_execution_time. You can either change the values of these items in WHM panel under domain management or account management. However, if you would like an independent changes for each domains hosted in your VPS server, you can changed them by adding a new php.ini file in the root of the domain executing the script in question.

Before we can change anything, we need to know the Server API of your server and the Loaded Configuration File (php.ini). Create a new document and save as info.php, paste codes below unto this document

<?php

phpinfo();

?>

Upload the info.php to your server. Direct your browser to this page. Look for the values of Server API and the loaded configuration file. Once we know the location of the loaded configuration file, we can change the values as required by your script.

1. You can either access this via black box or terminal just by typing (terminal only)

   sudo -s
   ## type in your password
   ## then type gedit or whatever is the installed editor on your server
   cd location of your loaded configuration file
   v /location of your loaded configuration file/ 
   

Or, if you have a windows desktop, then you will need a free SSH program called Putty. Open putty, and log on to your remote server.. I believe we can do this on putty

    cd /directory of your loaded configuration file normally it is etc/
    v php.ini

That should load the php.ini file on the monitor view editor in linux, ubuntu server. Once the php.ini file is loaded look on those values listed above and change them accordingly. Below is an example on how to set values.. you can always increase, but only to the maximum allowed by your hosting company.

    max_execution_time = 1500
    max_file_uploads = 200M
    max_input_time = 1500
    max_input_vars = 1000
    memory_limit = 250M
    post_max_size = 200M

the max_execution_time have the same value as max_input_time, and the max_file_uploads have the same value as the post_max_size. The memory_limit can be 30% of your server's resources, depending on how much memory your php scripts needed to run smoothly.

Save your changes.

1. If you don't like looking at the black box, look for the value of the Server API if it says **CGI/FastCGI **, then all you have to do is to create a new php.ini file, paste the your changes as shown on my example. Upload this file in the root directory of your domain. Reload the the info.php on your browser and make sure the changes are included.

Warning! Security issues on option Two. Make sure you delete the info.php file after you are done changin values on the php.ini file.

IMPORTANT SECURITY ISSUES! Option two has a vulnerability, because php.ini file can loaded or viewed on any browser. So, to protect this file from the noty ones, we need to create .htaccess file.

copy, paste code below, and then upload .htaccess to the root of your domain or any directory where there is php.ini file

                <Files php.ini> 
                order allow,deny 
                deny from all 
                </Files> 

That's pretty much it...: I am pretty sure you have fastCGI.. most servers today have this configuration.. With one exemption, is my very own pre-configured, modified nginx server ( fast, fast, and blazing fast, faster than I could say hi :)).

[scaiferw]

Line 1 suggests you are using shorttags, which can easily break a site if you move servers.

It may be that this has nothing at all to do with the database. Replace <? with <?php and see if that helps. May take some hunting to do it, but worthwhile. I don't think too many PHP servers today support shorttags.

Hope that helps.

[veedeoo]

Let me add something, because I just noticed you are using mysqli, so the php.ini file settings for the mysqli should be something like this

    ;this is the entire site using mysql 3 for three seconds time out in seconds. If you have other applications running on the native mysql, then increase this to the number of seconds you want before timing out. -1 is no timeout at all.

    mysql.connect_timeout = 3

    ; by the way this -> ; is how you comment on the php.ini file

    ;for the mysqli settings,
    mysqli.max_persistent = -1
    ; Maximum number of persistent links.  -1 means no limit.
    ; http://php.net/mysqli.max-persistent
    mysqli.max_persistent = -1

    ; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
    ; http://php.net/mysqli.allow_local_infile
    mysqli.allow_local_infile = On

    ; Allow or prevent persistent links.
    ; http://php.net/mysqli.allow-persistent
    mysqli.allow_persistent = On

    ; Maximum number of links.  -1 means no limit.
    ; http://php.net/mysqli.max-links
    mysqli.max_links = -1

    ; If mysqlnd is used: Number of cache slots for the internal result set cache
    ; http://php.net/mysqli.cache_size
    mysqli.cache_size = 2000

If you want to make sure that the short tags is allowed on your server change this value

    ;short_open_tag = Off

to this

    short_open_tag = on

If you want to write your php codes with ASP opening and closing style, for example these

    <?php

    echo "hello";

    ?>

Can be written also as and the php handler will parse this..

    <%

    echo "hello! I am written like ASP";

    %>

change this

    asp_tags = Off

to this

    asp_tags = on

[Larry_Singleton]

the OP also needes to restart his server after all of the suggested adjustments as prescribed by Mr. Veedeoo.