Getting "null" From Ranking Query

[Tarheel]

I have been using the following query to rank records in a MySQL 4.1 database:

set @N = 0;

SELECT @N := @N +1 as Rank, …..

Under the column Rank, it gave me the values 1, 2, 3, etc.

I recently moved to another server with MySQL 5.0 and now I get (NULL) under the Rank column using the same query. However, if I use the same query using phpMyAdmin, it works as it is supposed to.

What has happened? What do I need to do to get SQLyog to give me the appropriate ranking values?

[peterlaursen]

Are you using HTTP tunnel? If so is PHP version the same? Was SQLyog/the tunneler upgraded recently?

User variables only live as long as the connection. With HTTP tunnel a new connection is created for every statement (unless persistent connection is ON in PHP configuration and supported by webserver configuration)

So when you execute “SELECT @N := @N +1” @N is NULL and NULL + 1 = NULL

As it works with phpMyAdmin it seems that pesrsistent connection is supported on the host. You can edit the tunneller file and replace mysql_connect() with mysql_pconnect.

This is the only reason I can see. If that is not the issue, I think we need more details

[Tarheel]

peterlaursen wrote on Aug 16 2007, 03:58 AM:

Are you using HTTP tunnel? If so is PHP version the same? Was SQLyog/the tunneler upgraded recently?

User variables only live as long as the connection. With HTTP tunnel a new connection is created for every statement (unless persistent connection is ON in PHP configuration and supported by webserver configuration)

So when you execute “SELECT @N := @N +1” @N is NULL and NULL + 1 = NULL

As it works with phpMyAdmin it seems that pesrsistent connection is supported on the host. You can edit the tunneller file and replace mysql_connect() with mysql_pconnect.

This is the only reason I can see. If that is not the issue, I think we need more details

Yes, I am using HTTP tunnel on the new server (I did not have to use it on the old server). The PHP version is also an upgrade to 5.1.6. I used the SQLyogTunnel.php file from SQLyog version 6.05.

I made the following change to the tunneller file but it has not corrected the problem: $ret = mysql_pconnect ($host.':'.$port, $username, $password);

Is this correct? Is there anything else I can try?

Thanks for your help!

[peterlaursen]

Can you tell if php_mysql() or php_mysqli() extension is available/used? There is no mysqli_pconnect().

If php_mysql is used, Is 'Allow_persistent = ON´ in php configuration ([mysqld] section of php.ini)? But even if it is it is not always sufficient. I have experienced on some servers that it is not and I think that also webserver configuration has influence. But I do not know details.

What is the phpMyAdmin version and can you attach phpMyAdmin configuration file here?

Also the returns of phpinfo() could be useful! I am a little surprised that phpMyAdmin seems to have no problem and would like to find out how it does!

[Tarheel]

peterlaursen wrote on Aug 16 2007, 09:24 AM:

Can you tell if php_mysql() or php_mysqli() extension is available/used? There is no mysqli_pconnect().

If php_mysql is used, Is 'Allow_persistent = ON´ in php configuration ([mysqld] section of php.ini)? But even if it is it is not always sufficient. I have experienced on some servers that it is not and I think that also webserver configuration has influence. But I do not know details.

What is the phpMyAdmin version and can you attach phpMyAdmin configuration file here?

Also the returns of phpinfo() could be useful! I am a little surprised that phpMyAdmin seems to have no problem and would like to find out how it does!

I am not sure about whether php_mysql() or php_myhsqli() extension is available/used. The phpMyAdmin program is accessed via CPanel 11 at BlueHost.com so the only information I have is as follows:

phpMyAdmin – 2.10.0.2

MySQL client version: 4.1.21

Used PHP extensions: mysql

'Allow_persistent = ON' is in the php.ini file which I can access. Sorry, I cannot provide more info – perhaps, technical support at http://www.BlueHost.com can help.

[peterlaursen]

If phpmyadmin can use persistent connection I do not see any reason why SQLyog should not!

I will ask the developer who maintains the tunneller file to help with this tomorrow!

[Sabyasachi Ruj]

Hi,

The change that you have to do in php.ini is in [MySQL] section. for example:

[MySQL]

; Allow or prevent persistent links.

mysql.allow_persistent = On

And as more information:-

---

If you are using mysql_pconnect(), and say five persistent connections are in pool for that client.

Now mysql_pconnect can return any one of them.

So, If you are expecting that mysql_pconnect will return always use the same persistent connection, that is not the case.

As a result, say you are executing the following query after connecting with mysql_pconnect:

set @aa=6;

And after one more mysql_pconnect, you executed:

select @aa;

That will not necessarily return '6' as the result!

Because the second mysql_pconnect can take another persistent connection from that server's connection pool.

[peterlaursen]

addtional:

Connections are pooled on the server on the basis of: client IP, user name, password. so if there are five connections for say 192.168.16, root, rootpwd, mysql_pconnect can return any one of them. This is important when comparing with phpMyadmin, becuase phpMyAdmin has its own (mysql)user that is always used.

Refer to http://php.net/function.mysql-pconnect

If persistent is ON and if phpMyAdmin can use it the SQLyog/HTTP tunnelling can too. But don't create more than one persistent connection for every combination of client IP and user name!

[peterlaursen]

I am sorry but I have to correct myself here. We have been researching and discussing this on Friday and there were something that I was not aware of.

SQLyog reconnects for every statement when connected with HTTP tunnel. The reason that we need this is that we support multiple queries returning multiple resultsets. Without reconnection we would only be able to retrieve one of the resultsets. As phpMyADmin does not support multiple resultsets it won't have to reconnect.

This reconnect is problematic with user variables, temporary tables and transactions.

Now as I wrote before persistent connections are pooled on the basis of ip, user etc. A new connection will (randomly) select one existing from the pool if it exists. If only one connection for each combination exists and is kept alive a new one will 'pick up' that one, if none such exists a new connection thread will be created . However there is no guarantee that the connection thread will not have been killed in between.

A practical experience is that persistent connection sometimes works and sometimes not with user variables, temporary tables and transactions. But this is NOT the purpose of persistent connections as defined by the PHP developer community. I cannot define the circumstances where it works and where it does not either!

Refer to: http://php.net/manual/en/features.persistent-connections.php

“People who aren't thoroughly familiar with the way web servers work and distribute the load may mistake persistent connects for what they're not. In particular, they do not give you an ability to open 'user sessions' on the same link, they do not give you an ability to build up a transaction efficiently, and they don't do a whole lot of other things. In fact, to be extremely clear about the subject, persistent connections don't give you any functionality that wasn't possible with their non-persistent brothers.”

and

“An important summary. Persistent connections were designed to have one-to-one mapping to regular connections. That means that you should always be able to replace persistent connections with non-persistent connections, and it won't change the way your script behaves. It may (and probably will) change the efficiency of the script, but not its behavior!”

I think we should discuss if we could possibly only reconnect when we need to in order to retrieve multiple results (that is I think for every SELECT and SHOW statement only).

[Author]

Posts