Re: [PHP-DB] Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
One thing to be careful is if you are relying on 'transactions' to handle anything. Obviously the transaction has to be in the same connection just to work. Despite what others have said, the PDO connection will be different to the generic mysql connection as it is a separate process. Persistent connections will only reuse an already open connection, you can't connect two processes to the same connection. You would not know which process was accessing things. According to the documentation: First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. Therefore, providing you've configured PHP's --with-mysql and --with-pdo-mysql options with the same MySQL library, then as long as the host, username and password are the same, the same connection will be re-used for both the native (mysql_pconnect) connection and the PDO connection. I'm not sure about the mysqli library, but I believe that lies beneath the mysql_* family of functions anyway so it shouldn't make a difference whether you're using libmysqlclient or mysqli. Regards, Andy
Re: [PHP-DB] Combing PDO with mysql_pconnect connections in one application -- will this degrade performance?
As long as you test it (hopefully using some sort of automated tests) you should be right. Since you're using persistent connections (make sure you set pdo to do the same), I don't think you're really doubling the number of connections. Yeah, I think PDO uses the underlying mysql/pgsql functions anyway - it just provides a common API layer across all database types. Therefore if you call mysql_pconnect outside of PDO, then call the same database server with PDO, technically it should realise the connection has already been made to that server and use your previous connection. As Chris pointed out, make sure PDO will also use persistent connections. Regards, Andy
Re: [PHP-DB] Multiple instances of mysql_connect() in single PHP document.
Hi, If the databases are in the same mysql server, then you could qualify the table select with the database name and simply re-use the connection select db_name.table_name.field from db_name.table_name [where] No offence, but if I saw this in an application's source code, I'd run a mile. The negligible overhead of simply making two connections is far better in my opinion than having to rewrite each query if the database name changed, or to point an application at a copy of the original database with a different name for testing. At least with the dual-connection approach, you'd only have to change it once. Of course in a full application, you'd want the two database connection details in a config file, so the databases could even be located on separate servers, and you wouldn't need to make a source code change to point the application to another database. Regards, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] losing MySQL resource
Hi, I got around this by creating a database wrapper class which gets passed the credentials from the app's config file. An instance of the class is created and saved in the session, and every query to the database runs through the class's Query() wrapper method which checks if the connection is alive and valid - if it isn't, it reconnects it before running the query. Andy On 9 November2009, at 16:46, Stan wrote: How do I make an Object persistant for the duration of a Session? Thanks, Chris dmag...@gmail.com wrote in message news:4AB6B16C. 8...@gmail.com... Niel Archer wrote: I'm maintaining a session. I successfully connect to a database ($DBConnect = mysql_connect()). I save the connection resource in a session variable ($_SESSION['connection'] = $DBConnect) ... to use in subsequent queries. It remains valid while the user is on the current page. print_r($_SESSION['connection']) yields 'Resource id #3', for instance. User browses to a new page ... in the same session. $_SESSION['$DBConnect'] no longer references a valid mysql resource. print_r($_SESSION['connection']) yields '0'. Other ordinary values saved in $_SESSION variables remain valid. Is there something special about a mysql resource? Not about the resource itself, no. PHP closes connections to a Db when a script ends, unless the connection is persistent, so while the resource IS saved, the connection to which it refers no longer exists. No resources (whether they are persistent or not) can be stored in the session. http://www.php.net/manual/en/intro.session.php The big pink box has more info. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] losing MySQL resource
Hi Stan, Are you saving the instance of your class in $_SESSION? Class instances can be persisted across sessions, however resources (i.e. MySQL and other DB connections) cannot. What you need to do, is every method in your wrapper class that uses the MySQL resource needs to check if it's a valid resource (is_resource() springs to mind.) If it isn't valid, you need to re-create it (using mysql_connect). The reason for this is that PHP isn't running between session requests, so it cannot maintain and monitor the database connection across requests. Andy On 9 November2009, at 21:11, Stan wrote: I got as far as creating a class to be the wrapper ... instantiating an object of that class ... saving the reference in a $_SESSION variable ... but my object reference seems to be invalid upon the next request (in the same session) from the client browser. What have I missed? Thanks, Stan Andy Shellam (Mailing Lists) andy-li...@networkmail.eu wrote in message news:fd8200b0-e18a-4afd-8ffc-f51080621...@networkmail.eu... Hi, I got around this by creating a database wrapper class which gets passed the credentials from the app's config file. An instance of the class is created and saved in the session, and every query to the database runs through the class's Query() wrapper method which checks if the connection is alive and valid - if it isn't, it reconnects it before running the query. Andy On 9 November2009, at 16:46, Stan wrote: How do I make an Object persistant for the duration of a Session? Thanks, Chris dmag...@gmail.com wrote in message news:4AB6B16C. 8...@gmail.com... Niel Archer wrote: I'm maintaining a session. I successfully connect to a database ($DBConnect = mysql_connect()). I save the connection resource in a session variable ($_SESSION['connection'] = $DBConnect) ... to use in subsequent queries. It remains valid while the user is on the current page. print_r($_SESSION['connection']) yields 'Resource id #3', for instance. User browses to a new page ... in the same session. $_SESSION['$DBConnect'] no longer references a valid mysql resource. print_r($_SESSION['connection']) yields '0'. Other ordinary values saved in $_SESSION variables remain valid. Is there something special about a mysql resource? Not about the resource itself, no. PHP closes connections to a Db when a script ends, unless the connection is persistent, so while the resource IS saved, the connection to which it refers no longer exists. No resources (whether they are persistent or not) can be stored in the session. http://www.php.net/manual/en/intro.session.php The big pink box has more info. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Very old question: Mysql and old authentication
The mysqlnd driver doesn't support MySQL 4.1 or earlier - it looks like the server that you think is 5.0.45 is actually a lot older, or your user account on MySQL has been set up using the old authentication method. The solution would be to re-compile PHP 5.3 with the original MySQL client library (libmysql) instead of the newer mysqlnd. Andy On 29 October2009, at 20:05, SuNcO wrote: Recently we update our Local Server to this: Apache Version : 2.2.11 PHP Version : 5.3.0 MySQL Version : 5.1.36 All works fine locally, but when we want to connect to a Remote Mysql Server 5.0.45 just can't, we got the mysqlnd cannot connect to MySQL 4.1+ using old authentication msg The solution as i see is to use the OLD_PASSWORD trick, but in this case we can't do that because the Server is a Shared server and yada yada yada.. change some in the Remote Mysql Server is not an option I think Php must provide a solution for this but we can't found it We use SqlYog 6.05 to connect to Mysql Server's. Why it works with NEW and OLD algorithm and Php don't ? Any help on this please ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Very old question: Mysql and old authentication
-Remote Server old_passwords ON version 5.0.45 That's probably why - as I said in my previous e-mail, the mysqlng driver doesn't support the old authentication mechanism. If you cannot change the remote server, you need to re-compile PHP with the MySQL client library (libmysql) instead of the PHP 5.3 native driver (mysqlng.)
Re: [PHP-DB] DB interface problem
Hi Giff, I want to have a look at this for you, but you'll need to put the file on your server as plain-text because your server is parsing the PHP code and only sending us the output, not the source code. Append *.txt to your file on the server and we should be able to see it better! Andy On 27 October2009, at 12:48, Giff Hammar wrote: I started having trouble with a DBI interface to my PostgreSQL database after I built a new Ubuntu machine. The Postgres version is 8.3 and the DBI was written several years ago (by someone else). I'm using PHP version 5.2.6-3ubuntu4.2 with apache2. The problem is that I get the error: Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /var/www/lib/dbi_pgsql.php on line 202 I don't know what to check next to figure out why this is happening. Here is what I have done so far: - Updated the old PHP commands in the dbi file to reflect new ones var - public pg_numrows - pg_num_rows pg_cmdtuples - pg_affected_rows pg_exec - pg_execute - Connected to the database as the same user by using straight PHP calls vs the DBI. It worked fine. - Connected to the database via psql and that worked fine, too. There is something with the DBI class that is not right, but I am a novice with objects and classes, so I can't pin it down. The whole file is here: http://www.sv-phoenix.com/dbi_pgsql.php - it's 205 lines so I didn't want to put the whole thing in this post. If anyone can take a look at it and let me know what to check next, I would really appreciate it. Giff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] DB interface problem
Hi Giff, No worries - I know the feeling well! At first glance this looks OK, however I notice that there is no res property defined in the STH class, like there is session and query. $this-res on line 202 is what's causing the issue - PHP is saying it's not a valid PostgreSQL result resource, so if you trace back to where that comes from, you get to line 175: $this-res = @pg_execute($this-session, $query); PHP is therefore trying to assign a query result to a property that doesn't exist. I would have expected PHP to throw an error at this but it may have been obscured with your logging settings, I'm not sure. Try adding public $res; after line 85 and see what you get. You could also add var_dump($this-res); between lines 175 and 176 which should tell you what PHP thinks $this-res is. And lastly, you could get a back-trace of the rows() method call to see if it's being called before $this-res has been assigned - just add the line var_dump(debug_backtrace()); between lines 201 and 202. Hope this gives you a starting pointer! Regards, Andy On 27 October2009, at 19:36, Giff Hammar wrote: Andy, Sometimes I'm too fast for my own good. I added .txt to the file and it should be visible as http://www.sv-phoenix.com/dbi_pgsql.txt. Just appending the .txt didn't work. Thanks for taking a look! Giff On Tue, 2009-10-27 at 17:49 +, Andy Shellam (Mailing Lists) wrote: Hi Giff, I want to have a look at this for you, but you'll need to put the file on your server as plain-text because your server is parsing the PHP code and only sending us the output, not the source code. Append *.txt to your file on the server and we should be able to see it better! Andy On 27 October2009, at 12:48, Giff Hammar wrote: I started having trouble with a DBI interface to my PostgreSQL database after I built a new Ubuntu machine. The Postgres version is 8.3 and the DBI was written several years ago (by someone else). I'm using PHP version 5.2.6-3ubuntu4.2 with apache2. The problem is that I get the error: Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /var/www/lib/dbi_pgsql.php on line 202 I don't know what to check next to figure out why this is happening. Here is what I have done so far: - Updated the old PHP commands in the dbi file to reflect new ones var - public pg_numrows - pg_num_rows pg_cmdtuples - pg_affected_rows pg_exec - pg_execute - Connected to the database as the same user by using straight PHP calls vs the DBI. It worked fine. - Connected to the database via psql and that worked fine, too. There is something with the DBI class that is not right, but I am a novice with objects and classes, so I can't pin it down. The whole file is here: http://www.sv-phoenix.com/dbi_pgsql.php - it's 205 lines so I didn't want to put the whole thing in this post. If anyone can take a look at it and let me know what to check next, I would really appreciate it. Giff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] DB interface problem
Hi Chris, $this-res = @pg_execute($this-session, $query); PHP is therefore trying to assign a query result to a property that doesn't exist. I would have expected PHP to throw an error at this but it may have been obscured with your logging settings, I'm not sure. Close - the '@' before pg_execute is hiding the notice/warning. I'd say check your database username/password are correct. Yeah I noticed that, but the line below it does a check to see if the pg_execute command succeeded, and if it didn't it kills the script, but that's not happening - it's getting further down the line, which is why I think $this-res is not being persisted across method calls. Andy
Re: [PHP-DB] DB interface problem
Hi Giff, Glad you got it sorted! pg_exec() is the old (and deprecated) name for pg_query - this behaves differently to pg_execute. pg_execute runs a SQL statement that has previously been prepared with a call to pg_prepare, while pg_query runs a SQL query directly. http://www.php.net/manual/en/function.pg-prepare.php http://www.php.net/manual/en/function.pg-query.php After a quick glance through your code, I think pg_query is what you want. HTH, Andy On 27 October2009, at 22:06, Giff Hammar wrote: Andy, Thanks for your help! The var_dump after the pg_execute showed that the execute() method wasn't being called (I left a line out of the calling script). Once I fixed that, I changed pg_execute back to pg_exec and it worked as expected. Now I have to figure out the difference between pg_exec and pg_execute... From what I can tell, pg_exec isn't supported any more and I should be using pg_execute instead... Thanks again. Giff On Tue, 2009-10-27 at 19:51 +, Andy Shellam (Mailing Lists) wrote: Hi Giff, No worries - I know the feeling well! At first glance this looks OK, however I notice that there is no res property defined in the STH class, like there is session and query. $this-res on line 202 is what's causing the issue - PHP is saying it's not a valid PostgreSQL result resource, so if you trace back to where that comes from, you get to line 175: $this-res = @pg_execute($this-session, $query); PHP is therefore trying to assign a query result to a property that doesn't exist. I would have expected PHP to throw an error at this but it may have been obscured with your logging settings, I'm not sure. Try adding public $res; after line 85 and see what you get. You could also add var_dump($this-res); between lines 175 and 176 which should tell you what PHP thinks $this-res is. And lastly, you could get a back-trace of the rows() method call to see if it's being called before $this-res has been assigned - just add the line var_dump(debug_backtrace()); between lines 201 and 202. Hope this gives you a starting pointer! Regards, Andy On 27 October2009, at 19:36, Giff Hammar wrote: Andy, Sometimes I'm too fast for my own good. I added .txt to the file and it should be visible as http://www.sv-phoenix.com/dbi_pgsql.txt. Just appending the .txt didn't work. Thanks for taking a look! Giff On Tue, 2009-10-27 at 17:49 +, Andy Shellam (Mailing Lists) wrote: Hi Giff, I want to have a look at this for you, but you'll need to put the file on your server as plain-text because your server is parsing the PHP code and only sending us the output, not the source code. Append *.txt to your file on the server and we should be able to see it better! Andy On 27 October2009, at 12:48, Giff Hammar wrote: I started having trouble with a DBI interface to my PostgreSQL database after I built a new Ubuntu machine. The Postgres version is 8.3 and the DBI was written several years ago (by someone else). I'm using PHP version 5.2.6-3ubuntu4.2 with apache2. The problem is that I get the error: Warning: pg_num_rows(): supplied argument is not a valid PostgreSQL result resource in /var/www/lib/dbi_pgsql.php on line 202 I don't know what to check next to figure out why this is happening. Here is what I have done so far: - Updated the old PHP commands in the dbi file to reflect new ones var - public pg_numrows - pg_num_rows pg_cmdtuples - pg_affected_rows pg_exec - pg_execute - Connected to the database as the same user by using straight PHP calls vs the DBI. It worked fine. - Connected to the database via psql and that worked fine, too. There is something with the DBI class that is not right, but I am a novice with objects and classes, so I can't pin it down. The whole file is here: http://www.sv-phoenix.com/dbi_pgsql.php - it's 205 lines so I didn't want to put the whole thing in this post. If anyone can take a look at it and let me know what to check next, I would really appreciate it. Giff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
