Lost connection to MySQL server during query

Codes here !

Moderators: macek, egami, gesf

jesusdmz
New php-forum User
New php-forum User
Posts: 4
Joined: Wed Oct 10, 2012 6:37 pm

Lost connection to MySQL server during query

Postby jesusdmz » Wed Oct 10, 2012 6:55 pm

hello everyone i've been having this issue from months now and i cant seem to find the solution...
i hope i can explain my problem and someone can help me.

i keep getting this error on some queries i have trough out my site, some of my queries are very complex and some are very simple as "select field1, field2 from table1".

so in my php files i have written my queries something like this:

Code: Select all

$sql = "select t1.field1, t1. field2, t2.field1, t2.field2
       from table1 as t1
       join table2 as t2 on t2.id2 = t1.id1
       where t1.field3 = 'Smith'";

and i do it in this form so the queries can be more understandable. i do some tests and it works like charm, the problem is that sometimes just out of the blue i get the error: "Lost connection to MySQL server during query" at first i spent hours trying to figure out what the problem was until i re-arrange the query to look like this

Code: Select all

$sql = "select t1.field1, t1. field2, t2.field1, t2.field2 from table1 as t1
       join table2 as t2 on t2.id2 = t1.id1
       where t1.field3 = 'Smith'";

and voilà works like a charm again. but then days later i get the same error as before and i have to re-arrange the query again, and again, and again, sometime i just need to add an extra white space after/before any word and it works again. anybody has had the same issue or knows a solution for this
i have used set_time_limit(0) at the beginning of all my files i open/close all my connections correctly but i just can figure out what the problem is... can someone please help me with this its driving me crazy.

PS. the rows found can vary from 1-1000, so i think the amount of rows returned has nothing to do with my queries either.

seandisanti
php-forum Fan User
php-forum Fan User
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Lost connection to MySQL server during query

Postby seandisanti » Thu Oct 11, 2012 8:30 am

I think you're actually getting a couple of different errors (that are requiring different fixes) but would have to see the offending queries to be sure. Here are a few ways to avoid sql errors, and correctly debug them when you do get them.

1) make sure you use mysqli_real_escape_string or the mysql_ equivalent if you're using mysql_ for each of your variables.

2) if you build your query in parts, make sure you have a trailing space between sections, so it doesn't turn into "SELECT fieldFROM tableWHERE condition"

3) when you get an error, verify your query before you try to rewrite it etc. usually i'll just add

Code: Select all

die($query);
right after the query string is built, before it's attempted. A lot of times your problem will be obvious as soon as you see the parsed query.

jesusdmz
New php-forum User
New php-forum User
Posts: 4
Joined: Wed Oct 10, 2012 6:37 pm

Re: Lost connection to MySQL server during query

Postby jesusdmz » Wed Oct 17, 2012 7:07 am

i think i might have to change some php or mysql settings because i keep getting the error over and over again and sometimes i dont change anything and all i do is wait a few minutes and it works again, without changing anything. and for the options you're giving me :
1.- when i got the error i decided to give it a try to "mysqli_real_escape_string" since im using mysqli and nothing chance i kept getting the same error.

2.- my queries are not built in parts they're all defined as a whole. $sql = "select fields from table";

3.- i use the die function more than i should, normally i run the output thrown from die,
and i dont know if i mentioned this but if i run the query exactly as it is defined in php, the query works as expected in the mysql workbench.

im 99.9% sure that there's a php configuration i need to change/add.

as im writing this reply im having an issue with a query, this is my query:

Code: Select all

$sql = "select s1.c00001desc, s1.c00001name from sit00008 s8
      join sit00040 s40 on s40.n00040userid = s8.n00040userid
      join sit00001 s1  on s1.n00001Id = s8.n00001Id
      where s40.n00040userid = $userid
          UNION
          select s1.c00001desc, s1.c00001name from sit00009 s9
      join sit00040 s40 on s40.n00040userid = s9.n00040userid
      join sit00001 s1  on s1.n00001Id = s9.n00001Id
      where s40.n00040userid = $userid";

im getting the same error "Lost connection to MySQL server during query"

i run the die function and this is the output: "select s1.c00001desc, s1.c00001name from sit00008 s8 join sit00040 s40 on s40.n00040userid = s8.n00040userid join sit00001 s1 on s1.n00001Id = s8.n00001Id where s40.n00040userid = 148 UNION select s1.c00001desc, s1.c00001name from sit00009 s9 join sit00040 s40 on s40.n00040userid = s9.n00040userid join sit00001 s1 on s1.n00001Id = s9.n00001Id where s40.n00040userid = 148" i run the query exactly as it is, in the mysql workbench i get the results i expect.

so i modify my query to look like this

Code: Select all

$sql = "select s1.c00001desc,s1.c00001name from sit00008 s8
      join sit00040 s40 on s40.n00040userid = s8.n00040userid
      join sit00001 s1  on s1.n00001Id = s8.n00001Id
      where s40.n00040userid = $userid
          UNION
          select s1.c00001desc, s1.c00001name from sit00009 s9
      join sit00040 s40 on s40.n00040userid = s9.n00040userid
      join sit00001 s1  on s1.n00001Id = s9.n00001Id
      where s40.n00040userid = $userid";

i removed the blank space between the fields from the first part of the UNION i run the php page again and it works as expected, and i think to myself that its not possible that this could be the solution so i add the blank space again and i get the same error again, so i have to leave the query without the blank space so i can continue working.

the thing is that the problem doesn't ends there. after a few days i know ill get the exact same error "Lost connection to MySQL server during query" with this same query and ill have to add/remove another blank space for the query to work. i think it really is a weird problem, i dont know if someone has experienced this before.

seandisanti
php-forum Fan User
php-forum Fan User
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Lost connection to MySQL server during query

Postby seandisanti » Wed Oct 17, 2012 7:29 am

How long does it take the query to run from the console?

jesusdmz
New php-forum User
New php-forum User
Posts: 4
Joined: Wed Oct 10, 2012 6:37 pm

Re: Lost connection to MySQL server during query

Postby jesusdmz » Fri Oct 19, 2012 6:37 am

Nothing really:

Affected rows: 0 Found rows: 3 Warnings: 0 Duration for 1 query: 0.016 sec

johnj
php-forum Super User
php-forum Super User
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: Lost connection to MySQL server during query

Postby johnj » Fri Oct 19, 2012 10:14 pm

Check those warnings.

Check your application and db server logs.

jesusdmz
New php-forum User
New php-forum User
Posts: 4
Joined: Wed Oct 10, 2012 6:37 pm

Re: Lost connection to MySQL server during query

Postby jesusdmz » Wed Oct 24, 2012 7:17 am

There are no warning.
"Affected rows: 0 Found rows: 3 Warnings: 0 Duration for 1 query: 0.016 sec"

Affected rows: 0
Found rows: 3
Warnings: 0
Duration for 1 query: 0.016 sec.

and also i've been using jqGrid

and it gives me the same error

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]:
General error: 2013 Lost connection to MySQL server during query' in /var/www/html/desarrollo/gridv2/php/jqGridPdo.php:104
Stack trace:
#0 /var/www/html/desarrollo/gridv2/php/jqGridPdo.php(104): PDO->query('select n00714ti...')
#1 /var/www/html/desarrollo/gridv2/php/jqGrid.php(3176): jqGridDB::query(Object(PDO), 'select n00714ti...')
#2 /var/www/html/desarrollo/taller/sitmto0003actividadesgrid.php(63): jqGridRender->setSelect('n00714tipoactiv...', 'select n00714ti...')
#3 /var/www/html/desarrollo/taller/sitmto0003actividades.php(158): include('/var/www/html/d...')
#4 {main} thrown in /var/www/html/desarrollo/gridv2/php/jqGridPdo.php on line 104

it throws out a lot of crap but, basically what i have to fix its error #2 which is a simple query as
"select n00714tid, c00714telephone from sit00714" and all i have to do again is either put a blank space somewhere in the query or add somethine else to the query like

"select distinct n00714tid, c00714telephone from sit00714"
or
"select n00714tid, c00714telephone from sit00714 where 1 = 1"
or
"select n00714tid, c00714telephone from sit00714 where n00714tid"

and the apache log gives me this, which basically is the same thing:
"[Wed Oct 24 10:01:39 2012] [error] [client 172.16.1.66] PHP Fatal error:
Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]:
General error: 2013 Lost connection to MySQL server during query' in /var/www/html/desarrollo/gridv2/php/jqGridPdo.php:104
\nStack trace:
\n#0 /var/www/html/desarrollo/gridv2/php/jqGridPdo.php(104): PDO->query('select n00714ti...')
\n#1 /var/www/html/desarrollo/gridv2/php/jqGrid.php(3176): jqGridDB::query(Object(PDO), 'select n00714ti...')
\n#2 /var/www/html/desarrollo/taller/sitmto0003act.php(63): jqGridRender->setSelect('n00714tipoactiv...', 'select n00714ti...')
\n#3 /var/www/html/desarrollo/taller/sitmto0003actividades.php(158): include('/var/www/html/d...')
\n#4 {main}
\n thrown in /var/www/html/desarrollo/gridv2/php/jqGridPdo.php on line 104, referer: http://192.168.10.30/desarrollo/taller/sitmto0003.php"


Return to “mySQL & php coding”

Who is online

Users browsing this forum: Bing [Bot] and 1 guest