We have designed a Country, State, City Drop-down function on our registration form but our Host have put a block on our DB Code Answer

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of We have designed a Country, State, City Drop-down function on our registration form but our Host have put a block on our DB without wasting too much if your time.

The question is published on by Tutorial Guruji team.

We have designed a Country, State, City Drop-down function on our registration form but our Host (Host Gator) have put a block on our DB due to apparent SQL overload in processes.I think this has something to do with testing the drop down, all I did was chafe country to make sure state changed accordingly and same with city a few times.

We have a shared hosting account and are allowed to use up to 25% of the CPU and RAM which is like a 32core AMD and 64GB Ram Server. I really can’t see how a three tier drop down can possibly cause such an issue.

This is the email they sent us:

Hello,

This message is to advise you of a temporary block placed on your database. The database was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system’s CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case. Requests to this database may become degraded by limiting the maximum number of queries or connections for a limited amount of time, or if there are sustained issues, ultimately we may be forced to block access to this database until the issue has been resolved.

Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.

If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.

~~~ Excessive MySQL activity is caused by (a) a long-running process that locks a table, causing other queries to back up, (b) a query that is not optimized ][example: select all from … and involving a large or complex query], (c) huge table copies/maintenance during peak hours.

NOTE:, the following are just possible fixes or suggestions, and are not endorsed or supported by HostGator. They are included in the hope that they may apply to your situation, and/or help you reduce the amount of resources your SQL queries consume. As always, it’s best to backup any data before making any changes or adjustments.

First and foremost, you may need to optimize your tables. The frequency depends on the size and usage of the database, but most databases would benefit from doing something like this on a yearly basis: a) Enter your phpMyAdmin/MySQL control panel. Click on the database (not the table, the database name), and on the right hand column your tables should be listed. Scroll down till you see the .Check all. link. Click on that link, make sure all database tables are checked and then from the drop-down next to it, and carefully select .Optimize table..

Additionally, adding indexes to your table(s) may improve performance. If you’re not sure what you’re doing, it’s best not to modify any table; caution is recommended. There are various articles

If you reply back to this with your IP address (http://www.hostgator.com/ip.shtml) we will be more than happy to go ahead enable HTTP access for you, so that you can safely work on the script without it causing further issues. Please let us know how you would like to proceed.

CPU_TIME:138 table_rows_read:402659370 SELECTS:134266 ROWS_UPDATED:66854 ROWS_FETCHED:200708 BUSY_TIME:141 ONNECTED_TIME:159 BYTES_SENT:0 BYTES_RECEIVED:16653735 WAIT_TIME:3

Top table row reads:

DB_USER: innovate — TOTAL_CONNECTIONS: 279 — CONNECTED_TIME: 159 — CPU_TIME: 138 — TABLE_ROW_READS: 402664343 — SELECT_COMMANDS: 134301 — UPDATE_COMMANDS: — BUSY_TIME: 141 — BYTES_SENT: 0 — BYTES_RECEIVED: 16658133 — WAIT_TIME (IO): 3

Top WAIT (IO) TIME:

DB_USER: innovate — TOTAL_CONNECTIONS: 279 — CONNECTED_TIME: 159 — CPU_TIME: 138 — TABLE_ROW_READS: 402664343 — SELECT_COMMANDS: 134301 — UPDATE_COMMANDS: — BUSY_TIME: 141 — BYTES_SENT: 0 — BYTES_RECEIVED: 16658133 — WAIT_TIME (IO): 3

innovate 10050 0.0 0.0 17964 1340 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 10200 0.0 0.0 17768 1084 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 10381 0.0 0.0 18040 1604 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 10539 0.0 0.0 18036 1584 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 21266 15.0 0.0 295808 15884 ? RN 02:31 0:00 /usr/bin/php /home3/innovate/public_html/developer/innovativeresearch/admin/addtotable.php

Thu Nov 14 02:31:25 CST 2013

Running Processes:

innovate 10050 0.0 0.0 17964 1340 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 10200 0.0 0.0 17768 1084 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 10381 0.0 0.0 18040 1604 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 10539 0.0 0.0 18036 1584 ? SN 02:29 0:00 imap [anthonysinclair@creavation.com 90.220.91.59]

innovate 21266 15.0 0.0 295808 15884 ? RN 02:31 0:00 /usr/bin/php /home3/innovate/public_html/developer/innovativeresearch/admin/addtotable.php

Running Queries:

************* 1. row *************

USER: innovate

DB: innovate_research

STATE:

TIME: 0

COMMAND: Sleep

INFO: NULL

Open connections

Current Site Requests:

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

99.231.106.237 creavation.com /developer/innovativeresearch/admin/addtotable.php?addprod=

This is where we purchased the country , state, city database from:

http://www.worldcitiesdatabase.com/world-cities.aspx

Any help on this would be much appreciated 🙁

<?php //require_once('inc/config.php');?>
<?php
//check();          //check admin login

$link = mysql_connect('localhost', 'innovate', '4HIDDENW8n');
$db_selected = mysql_select_db('innovate_research', $link);

/*$sql = "select * from country";
$res=mysql_query($sql);
while($row=mysql_fetch_array($res))
{
    $sql=mysql_query("INSERT INTO `innovere_country` (`country_name` ,`country_code`) VALUES ('".$row['country_name']."', '".$row['country_code']."');");
}*/


/*$sql = "select * from states";
$res=mysql_query($sql);
while($row=mysql_fetch_array($res))
{

    $sql_country = mysql_fetch_array(mysql_query("select * from innovere_country where `country_code`='".$row['country_code']."'"));

    $sql=mysql_query("INSERT INTO `innovere_state` (`country_id` ,`state_name` ,`state_code`) VALUES ('".$sql_country['country_id']."',  '".addslashes($row['state_name'])."',  '".$row['state_code']."');");
}*/

ini_set('max_execution_time', 0);
$sql_count = mysql_fetch_array(mysql_query("select count(*) as num_c from weblocations"));

if(empty($_REQUEST['startpaging']))
{
    $_REQUEST['startpaging']=0;
}

$startpaging = $_REQUEST['startpaging']+240;    
echo "<br>add : ".$addprod=$_REQUEST['addprod'];

echo $sql = "select * from weblocations limit $startpaging,240";
$res=mysql_query($sql);

while($row=mysql_fetch_array($res))
{
    $addprod++;
    if(!empty($row['country_code']))
    {
    $sql_country = mysql_fetch_array(mysql_query("select * from innovere_country where `country_code`='".$row['country_code']."'"));
    }

    if(!empty($row['state_code']))
    {
    $sql_state = mysql_fetch_array(mysql_query("select * from innovere_state where `state_code`='".$row['state_code']."' and `country_id`='".$sql_country['country_id']."'"));
    }

    $sql=mysql_query("INSERT INTO `innovere_city` (`state_id` ,`country_id` ,`city_name`) VALUES ('".$sql_state['state_id']."',  '".$sql_country['country_id']."',  '".addslashes($row['city_name'])."');");
    echo "<br>"."INSERT INTO `innovere_city` (`state_id` ,`country_id` ,`city_name`) VALUES ('".$sql_state['state_id']."',  '".$sql_country['country_id']."',  '".$row['city_name']."');";
}

echo "<br>total : ".$sql_count['num_c'];
echo "<br>add : ".$addprod;

if($sql_count['num_c']>$addprod)
{
echo "<script>document.location.href='addtotable.php?addprod=".$addprod."&startpaging=".$startpaging."'</script>";
}

        /*ini_set('max_execution_time', 0);
        $country_code = "AF";

        $sql_count = mysql_fetch_array(mysql_query("select count(*) as count_num from weblocations where `       country_code`='".$country_code."'"));

        echo "<br>Count: ".$sql_count['count_num'];

        echo $sql = "select * from weblocations where `country_code`='".$country_code."'";
        $res=mysql_query($sql);

        while($row=mysql_fetch_array($res))
        {
        if(!empty($row['country_code']))
        {
        $sql_country = mysql_fetch_array(mysql_query("select * from innovere_country where `      country_code`='".$row['country_code']."'"));
        }

        if(!empty($row['state_code']))
        {
        $sql_state = mysql_fetch_array(mysql_query("select * from innovere_state where `      state_code`='".$row['state_code']."' and `country_id`='".$sql_country['country_id']."'"));
        }

        $sql=mysql_query("INSERT INTO `innovere_city` (`state_id` ,`country_id` ,`city_name`) VALUES ('".$sql_state['state_id']."',  '".$sql_country['country_id']."',  '".addslashes($row['city_name'])."');");
        echo "<br>"."INSERT INTO `innovere_city` (`state_id` ,`country_id` ,`city_name`) VALUES    ('".$sql_state['state_id']."',  '".$sql_country['country_id']."',  '".$row['city_name']."');";
    }*/

    ?>

This is a list of tables we have:

Table 1: innovere_city Rows = 1,604,640 MyISAM utf8_general_ci 73.8 MiB, Table 2: innovere_country Rows= 247 MyISAM utf8_general_ci 10.7 KiB Table 3: weblocations Rows = 2,449,838 MyISAM utf8_persian_ci 127.1 MiB Table 4: innovere_state Rows: 3,874 MyISAM utf8_general_ci 185.1 KiB Table 5: country Rows = 247 MyISAM utf8_persian_ci 6.3 KiB

Ive just realised we have two country tables?

Answer

If I was betting, I suspect the problem is that you are returning way too many rows, and what you need to use is a cascacading dropdown. Searching on ‘javascript cascading dropdrown” will show lots of examples, for jquery and other populate script libraries.

ADDED

If you understand “cascading dropdowns” you will know what you need to change – I’m going to assume jquery for simplicity. The idea is to simply serve the web page initially with only top level (country) SELECT populated. They, via a jquery event handling on country SELECT changing, you make the AJAX call to you server to collect the “states” when they select USA or provinces when then select Canada, etc. You then populate the “State/region” SELECT from this and do likewise for each level of cascading dropdown.

JQuery (and other script libraries) have everthing you need to make this simple — If you don’t use a script library — take the time to learn JQuery immediately — It will be some of the best time you ever spend learning about website development. JQuery is very widely used for good reason.

We are here to answer your question about We have designed a Country, State, City Drop-down function on our registration form but our Host have put a block on our DB - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji