[SOLVED] PHP: Oracle *and* SQL

Rahb · 10-03-2012, 02:03 PM

Hi.

I've been handed a Linux (2.6.18) server running Apache(2.2.3)/PHP(5.3.16) that can successfully connect to and retrieve info from an Oracle database. I have confirmed all of this.

I now need to include a local mysql database in the mix. Mysql(5.0.77) is installed and have re-compiled PHP thus;

Code:

/configure --enable-soap --with-mysql=mysqlnd

I can still connect to and retrieve info from the Oracle database but I'm having issues connecting to mysql.

I'm using this in a file named msql.php to test;

Code:

<html>
<head>
<title> PHP Test Script </title>
</head>
<body>
<?php
mysql_connect("localhost", "user", "password") or die(mysql_error());
echo "Connected to MySQL<br />";
?>
</body>
</html>

...I get;

Code:

Fatal error: Call to undefined function mysql_connect() in /var/www/html/msql.php on line 7

I've been through a lot of the standard troubleshooting and have added this to php.ini;

Code:

; Enable mysql extension module
extension=mysql.so

I'm still a little concerned about changes in php.ini not being in the correct location but I'm wondering if I can even do mysql in the same Apache/PHP/Oracle environment.
If so, can someone give me a hand up with the mysql connection?

Thanks, R

jlinkels · 10-03-2012, 02:43 PM

There is no reason why you cannot make calls to MySQL on an Oracle system. A MySQL call is simply that, regardless of what more is installed.

How did you test your PHP code? From within a web page? Did you restart Apache?
Did you try to run your PHP code in a terminal? php -f msql.php
It should be clear that PHP is not recognizing the mysql function call. This has nothing to do yet with the mysql database.

I am wondering why you compiled PHP. Doesn't your package manager provide PHP? If you just recompiled PHP, did you install it? And even if you installed it, if your distro expects something else than a standard make install it won't run your version of PHP. What distro are you running anyway?

jlinkels

Rahb · 10-03-2012, 03:52 PM

Quote:

Originally Posted by jlinkels

How did you test your PHP code? From within a web page? Did you restart Apache?
Did you try to run your PHP code in a terminal? php -f msql.php

Yes, I restarted Apache and ran something I knew worked before; the initial call to Oracle VIA PHP from a test page. It still works.
Running it from the terminal like you describe, I see a little more info (probably just more of the same);

Code:

Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in /var/www/html/rob_msql.php on line 7

Quote:

Originally Posted by jlinkels

I am wondering why you compiled PHP. Doesn't your package manager provide PHP? If you just recompiled PHP, did you install it? And even if you installed it, if your distro expects something else than a standard make install it won't run your version of PHP. What distro are you running anyway?

The initial install was by compile. Rather than mixing modes I figured I'd stick with it.
Yep; configure, make, make test, make install. It all looked good.

This is on Oracle Linux and yeah, it's a PHP problem. Or more accurately "this guy is very new to PHP problem".
I do still think I have some php.ini location issues;
I see a copy in /etc/init.d
phpinfo( ); says it's in /usr/local/lib but it's not.

Definitely a puzzle.

jlinkels · 10-03-2012, 04:13 PM

That new error message is telling something really different. Since it is closer to MySQL, without Apache in between I would concentrate on this error first.

What that error message tells is that the mysql_connect function is found in PHP now. But it cannot connect to mysql.

I have checked on my own system running your script, and when I try to connect I get the identical error message when MySQL is not running.

So either you MySQL is not running or you cannot connect because of credentials or a different or conflicting port. Do you know by any change which port is used by Oracle?

Can you try to connect to MySQL using the client: mysql -uusername -ppassword.

jlinkels

Rahb · 10-03-2012, 04:26 PM

Interesting.

Quote:

Originally Posted by jlinkels

So either you MySQL is not running or you cannot connect because of credentials or a different or conflicting port. Do you know by any change which port is used by Oracle?
Can you try to connect to MySQL using the client: mysql -uusername -ppassword.

No, I don't know the port.

Code:

[root@owbsljtsc01:html]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Does it make a difference that I can't pass the password on the first string? The msql prompt requires that I enter it separately.

jlinkels · 10-03-2012, 06:11 PM

The problem might be that PHP is trying to connect to mysql here unix:///tmp/mysql.sock. While most likely mysql is listening on another socket by default.

Try to find out where mysql is listening on: netstat -a | egrep 'mysql|3306'. MySQL might be listening on /var/run/mysqld/mysqld.sock and that is a clear mismatch.

Using phpinfo(); gives a lot of information. Look which config files PHP is using, and especially look at the mysql settings, constants and variables.

If you see that PHP tries to read a config file that doesn't exist (either in CLI mode or in web mode) PHP might insert unwanted default values.

Did you insert a space between -p and password? Then it will prompt you.

jlinkels

Rahb · 10-04-2012, 08:49 AM

Quote:

Originally Posted by jlinkels

Try to find out where mysql is listening on: netstat -a | egrep 'mysql|3306'. MySQL might be listening on /var/run/mysqld/mysqld.sock and that is a clear mismatch.

Code:

> netstat -a | egrep 'mysql|3306'
tcp        0      0 *:mysql                     *:*                         LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     14188  /var/lib/mysql/mysql.sock

...and /var/lib/mysql/mysql.sock is empty;

Code:

[root@owbsljtsc01:mysql]$ ll  /var/lib/mysql/mysql.sock
srwxrwxrwx 1 mysql 27 0 Oct  3 11:56 /var/lib/mysql/mysql.sock

Quote:

Originally Posted by jlinkels

Using phpinfo(); gives a lot of information. Look which config files PHP is using, and especially look at the mysql settings, constants and variables.
If you see that PHP tries to read a config file that doesn't exist (either in CLI mode or in web mode) PHP might insert unwanted default values.

In the output of phpinfo();, Configuration File (php.ini) Path is set to /usr/local/lib, which does not exist.

phpinfo(); doesn't seem to be calling out mysql by name, which could be a big clue unless SQLite means the same thing...

EDIT: Actually I DO see mysql on a second look. I'll have to confirm, but should settings like these be managed in /etc/my.cnf?

Code:

jlinkels · 10-04-2012, 09:23 AM

Well, this explains a few things.

First you are not loading a php.ini file. Don't ask me why not. You can specify which .ini file to use on the command line using -c. If you have an .ini file somewhere on your system it might be easier to use it than to create one from scratch. Or search for it on the internet.

The result of not loading the .ini file is that the mysql connector does not know where to connect to mysql. Default apparently is /tmp/mysqld.sock, but your mysql installation has it opened at var/run/mysqld/mysqld.sock. The two will never find each other.

Look at your config:

Code:

var/run/mysqld/mysqld.sock

and mine:

Code:

mysql.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock

Strange is that in my php.ini file there is no default socket given. That means the built-in default is used. I assume it is specified during php compile time. Since you can override it, it might be easier to override it in the php.ini instead of recompiling php with this setting correct.

I know I am concentrating on command line behaviour of PHP while you need the Apache interface. I am sure once this is solved on the command line, it is either solved for Apache as well, or it is easy to solve because you know the mechanism. One problem at a time.

jlinkels

Rahb · 10-04-2012, 12:48 PM

Quote:

Originally Posted by jlinkels

If you have an .ini file somewhere on your system it might be easier to use it than to create one from scratch.

I pulled the one from /etc into the location that PHP expects it; /usr/local/lib/

Quote:

Originally Posted by jlinkels

The result of not loading the .ini file is that the mysql connector does not know where to connect to mysql. Default apparently is /tmp/mysqld.sock, but your mysql installation has it opened at var/run/mysqld/mysqld.sock. The two will never find each other.
Look at your config:

Code:

var/run/mysqld/mysqld.sock

and mine:

Code:

mysql.default_socket => /var/run/mysqld/mysqld.sock => /var/run/mysqld/mysqld.sock

I'm not sure what to make of that. The only thing I see is;

Code:

[root@owbsljtsc01:html]$ ls /var/run/mysqld/mysqld.pid
-rw-rw---- 1 mysql 27 5 Oct  4 10:49 /var/run/mysqld/mysqld.pid

That said, I've updated the default socket in php.ini to;

Code:

mysql.default_socket = /var/lib/mysql/mysql.sock

Interestingly enough, now when I submit my script from the command line I get;

Code:

[root@owbsljtsc01:html]$ /usr/local/bin/php -f rob_msql.php
<html>
<head>
<title> PHP Test Script </title>
</head>
<body>
mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

I fixed the offending passwords with some good information at this link;
http://stackoverflow.com/questions/1...authentication

And lo, I was able to connect to the local database. Life is good.

Quote:

Originally Posted by jlinkels

I know I am concentrating on command line behaviour of PHP while you need the Apache interface. I am sure once this is solved on the command line, it is either solved for Apache as well, or it is easy to solve because you know the mechanism. One problem at a time.

The mark of a veteran troubleshooter for sure. Now, looking at the differences between Apache and CLI submission;

Apache;
The Oracle test page works. (Configure Command = './configure' '--with-apxs2' '--with-oci8')
The SQL test page does not.
MySql does not appear in the phpinfo(); test page

CLI;
The Oracle test page does not work. (Configure Command => './configure' '--enable-soap' '--with-mysql=mysqlnd')
The SQL test page works.
MySql appears throughout the phpinfo(); test page.

I have a sinking feeling that I am dealing with two builds of PHP and that my next task is going to be burning one down without burning down the other.

jlinkels · 10-04-2012, 01:09 PM

Now * I * have the sinking feeling that you need not just to connect to MySQL on a machine which runs Oracle. but you need to connect to both Oracle and MySQL from within PHP. That was not clear to me before.

And yes, as it seems Apache uses the PHP-with-Oracle-included and CLI uses PHP-with-MySQL-included.

Of course MySQL call in PHP is just the inclusion of additional libraries. I don't see why they have to be mutually exclusive. Have you tried to compile your PHP version with both Oracle and MySQL support? If you do that for your last version (the one which includes MySQL) you can see if compiling is succesful, and test it CLI. If that functions, it should be easy (ha!) to tell Apache to use your compiled version.

jlinkels

Rahb · 10-04-2012, 01:45 PM

Quote:

Originally Posted by jlinkels

Now * I * have the sinking feeling that you need not just to connect to MySQL on a machine which runs Oracle. but you need to connect to both Oracle and MySQL from within PHP. That was not clear to me before.

No, simultaneous functionality is not the goal. It's just gravy, but I like gravy and figured I'd reach for it if it was handy.

Quote:

Originally Posted by jlinkels

Of course MySQL call in PHP is just the inclusion of additional libraries. I don't see why they have to be mutually exclusive. Have you tried to compile your PHP version with both Oracle and MySQL support? If you do that for your last version (the one which includes MySQL) you can see if compiling is succesful, and test it CLI. If that functions, it should be easy (ha!) to tell Apache to use your compiled version.

Yeah, I think I'll take a shot at that. From what I can see it seems like it should be as "simple" as making the correct libphp5.so available in the right place (in my case /usr/lib64/httpd/modules).

Rahb · 10-04-2012, 04:33 PM

Curious.

I've recompiled with;

Code:

./configure --enable-soap --with-mysql=mysqlnd  --with-oci8 --with-zlib-dir=/usr/include/
make
make test
make install

The datestamp on libphp5.so shows it's been updated and httpd.conf is pointing at it correctly.

In the web based PHP info page it still only shows;

Code:

Configure Command	 './configure' '--with-apxs2' '--with-oci8'

There's something basic I must be missing here or maybe I've just been looking at this too long this today.

jlinkels · 10-04-2012, 07:10 PM

I think it was too long.

You already drew the conclusion that Apache uses a different PHP version from your compiled version. That is not different now you compiled again.

Using the command line you should see both MySQL and Oracle working. (Crossing fingers....)

Now somewhere in the Apache config you should see that Apache points to a different PHP executable.

jlinkels

Rahb · 10-05-2012, 09:47 AM

Quote:

Originally Posted by jlinkels

Using the command line you should see both MySQL and Oracle working. (Crossing fingers....)

This is the case.

Quote:

Originally Posted by jlinkels

Now somewhere in the Apache config you should see that Apache points to a different PHP executable.

Hrm. Maybe this is sinking in.
The PHP executable I use at the command line is in /usr/local/bin;

_SERVER and _ENV look good here;

Code:

[root@owbsljtsc01:html]$ /usr/local/bin/php -f rob_php.php |grep /usr/local/bin/
_ => /usr/local/bin/php
_SERVER["_"] => /usr/local/bin/php
_ENV["_"] => /usr/local/bin/php

VIA Apache, they're set incorrectly.

Code:

_SERVER["PATH"]	/sbin:/usr/sbin:/bin:/usr/bin
_ENV["PATH"]	/sbin:/usr/sbin:/bin:/usr/bin

To me this almost seems to be the endgame if I can understand how to fix that but that's a leap.
I'm curious, where does Apache learn about these variables? I don't see any reference to them in httpd.conf or php.ini.

Rahb · 10-05-2012, 11:01 AM

Okay, it's a heinous hack but I've temporarily symlinked the executable;

Code:

[root@owbsljtsc01:/]$ ln -s /usr/local/bin/php /usr/sbin/php
[root@owbsljtsc01:/]$ service httpd restart
Stopping httpd:                                            [  OK  ]
Starting httpd:                                            [  OK  ]

...and everything works from both side (MySQL/Oracle, CLI/Apache).

FWIW, I'd still like to know where Apache pulls that info (and do the right thing).