One site can't connect to database via apache but via cli

pingu · 09-09-2014, 08:58 AM

After moving some websites to another server one site throws an error "failed to connect to the database".
The 8 other sites connects fine with same connection details.
I have moved both webserver & mysql - they resided on different servers earlier, now on same.

The problematic site is running "Lazy8", an accounting application.
There is a file "protected/config/main.php" that contains connection information:

Code:

		'db'=>array(
//		    'connectionString'=>'mysql:host=192.168.1.11;port=3306;dbname=bok8',
			'connectionString'=>'mysql:host=localhost;port=3306;dbname=bok8',
			'username'=>'clerk',
			'password'=>'password',			 
		),

If I change 'connectionString' to old db-server (192.168.1.11) it works fine.

Connecting from cli works fine:

Code:

mysql -p -h localhost -u clerk
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bok8               |
+--------------------+

Checking permissions for my user:

Code:

mysql -p -e "show grants for 'clerk'@'localhost'"
+--------------------------------------------------------------------------------------------------------------+
| Grants for clerk@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'clerk'@'localhost' IDENTIFIED BY PASSWORD '*<password>' |
| GRANT ALL PRIVILEGES ON `bok8`.* TO 'clerk'@'localhost'                                                      |
+--------------------------------------------------------------------------------------------------------------+

(I did set GRANT ALL just for testing.)

I have checked all log files - apaches access.log & error.log and mysql error.log, there is nothing written anywhere when the error occurs.

Both old & new servers runs Ubuntu 12.04.4 LTS.
Old servers are vm guests, OpenVZ under Proxmox, new server is a physical Dell PE 2950.

The procedure for moving my sites:
The new database configured as slave.
All sites /var/www/sites/ copied over to new server.
Changed config-files on new server.
Copied over /etc/apache2/sites-enabled/ Paths changed from /var/www/* to /data/www/* - checked carefully.
Stop apache on old server.
Stop mysql slave on new server.
Start apache on new server
Point port 80 & 443 in firewall to new server.

Habitual · 09-09-2014, 10:38 AM

looks ok.

Code:

does mysql -u clerk -p -h 192.168.1.11

connect?

shows us the entry in /etc/hosts for 192.168.1.11

and bind-address value from /etc/my.cnf

pingu · 09-09-2014, 11:32 AM

Quote:

Originally Posted by Habitual

looks ok.

Yepp, I know.

Everything looks ok it just doesn't work.

Quote:

Code:

does mysql -u clerk -p -h 192.168.1.11

connect?

Yep, connects fine.

Quote:

shows us the entry in /etc/hosts for 192.168.1.11
and bind-address value from /etc/my.cnf

Nothing in /etc/hosts for 192.168.1.11
Here's complete /etc/hosts:

Code:

127.0.0.1       localhost 
172.16.21.2     myservername.tuxoffice.lan     myservername

# The following lines are desirable for IPv6 capable hosts
::1     ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

Bind-address: tried without setting any (works for all other sites), also tried with bind-address=localhost.
Old site did not have bind-address specified.

Additional info:
I also tried connecting to 127.0.0.1, and tried specifying socket instead of port.
No difference.

And just to make sure there's no misunderstanding:
I need to connect to mysql at localhost, that's the problem.
I can connect to old database but that one is to be removed.

Habitual · 09-09-2014, 11:53 AM

Quote:

Originally Posted by pingu

And just to make sure there's no misunderstanding:
I need to connect to mysql at localhost, that's the problem.
I can connect to old database but that one is to be removed.

you try

Code:

bind-address 127.0.0.1

in my.cnf?

also

Code:

netstat -plaunt | grep 3306

shows what?

pingu · 09-09-2014, 12:00 PM

Yes, I tried setting "bind-address 127.0.0.1" in my.cnf, then connect to both localhost and 127.0.0.1. No change.
netstat -plaunt | grep 3306
[sudo] password for pingu:
tcp6 0 0 :::3306 :::* LISTEN 4010/mysqld

And remember, all other sites connects fine to mysql@localhost.

pingu · 09-09-2014, 12:05 PM

I also checked for typos, used that string

Code:

//		    'connectionString'=>'mysql:host=192.168.1.11;port=3306;dbname=bok8',

which connects fine to old database.
Then I changed the host-address to 'localhost' to make sure there is no typo in second string but it still fails.
So there has to be something that prevents this application to connect to database@localhost. I'm just out of ideas as where to search!

Habitual · 09-09-2014, 01:00 PM

tcp6 0 0 :::3306 :::* LISTEN 4010/mysqld

It's only listening for tcp6 connections?

pingu · 09-09-2014, 01:10 PM

That is strange! Didn't notice that, thanks for you sharp eyes!
Why is that, why does all other sites work, why does cli access work - now there's something to investigate! It'll probably take the rest of the evening (just a few hours to bedtime in my part of the world), I'll be back tomorrow then.

pingu · 09-09-2014, 01:13 PM

Hmmm... https://bugs.debian.org/cgi-bin/bugr...cgi?bug=685382

Quote:

a socket listening on :::3306 listens on v4 *and v6

However, I'll still check it out, it could be something.

Habitual · 09-09-2014, 01:55 PM

tomorrow then...

Nighty night.

pingu · 09-11-2014, 05:31 AM

Further researching:
1. The new db-server surely listens on both ipv4 & ipv6 - otherwise no other application would work.
2. I changed connection info for lazy8 on old web-server, pointed it to database on new db-server. This works fine, which tells me that there is nothing wrong with the database it self, also definitely says statement no 1 is correct as I'm using an ipv4 address to connect.
3. On new web-server I pointed lazy8 to an older db-server, this also failed.
4. Running "sudo tcpdump -i any -n dst port 3306" on new server while trying to connect to db on it's localhost outputs nothing! Same trying to connect to old db-server, running tcpdump there - nothing!
Connecting from old web-server outputs lots, as it should be.

So it seems that for some reason the db-server is never reached, something must be blocking connections, but only for this application!???
No firewall active:

Code:

sudo iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination         
Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         
Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Something that might be very interresting though, Apache is same version on both servers, but php differs:
Old has 5.4 branch (PHP Version => 5.4.22-1+debphp.org~precise+1)
New has 5.3 branch (PHP Version => 5.3.10-1ubuntu3.9)
(The new server was installed long time ago, it's just to be used temporarily while upgrading new server & changing virtualization system.)
Seems rather strange though if this very old application runs under php-5.4 but not under php-5.3...
Anyway, I believe this is what I shall investigate further.

Habitual · 09-11-2014, 07:15 AM

Bizarre indeed. Wish I had something for you.

selinux involved?

pingu · 09-13-2014, 09:32 AM

No selinux, Ubuntu don't have it afaik.
And now the last thing I could think of is tested, could the different php branches be the cause?
On a third server I installed php-5.4.30, and of course it doesn't help. This installation also can't access any database.
tcpdump again shows nothing on the db-servers.

Now I'm completely out of ideas.

---
(edit)
Let me also add that the old web-server connects fine to database on new server. All three servers are on the same subnet.

pingu · 09-14-2014, 12:39 PM

This is crazy.
It's completely, totally, utterly insane! Please - *please*! - someone explain this to me!:

First, about Lazy8 & database:
When the web application Lazy8 can't connect to a database configured in [home]/protected/config/main.php it redirects to a page where you enter connection details - see image.
This is also used when installing Lazy8. The database has to exist but if it's empty it is filled automatically with default tables. If it contains valid data, the database is used as is.

What I did:
I have, as you've seen, tried everything.
For the rest of this post, I am working on the new server holding both apache and mysql.
Last tries was to copy the whole Lazy8 to another directory, now I could try both http & https. None could connect to the db. I always ended up on that "baddatabase" page, typing in connection info just returned me.
Next try: Create a new, empty database, then use the web interface you see on the image to connect to new, empty database.
It did not work - BUT!!! that tcpdump command suddenly outputs stuff! ("tcpdump -i any -n src port 3306")
Now I change, on the web interface, to old database - and it connects... !???!!?
Finally, using Chrome instead of Firefox, tries to access the webapp normally - it works...

As I have 2 installations, http & https, I can test this. Above was using https, which has DocumentRoot /var/www/sites/upplin.se/bok/, so I do the same thing but now using http - it has DocumentRoot /opt/www/sites/upplin.se/bok/ .

My steps - guaranteed, I'm not doing anything else:
Try to access application using Chrome: "baddatabase"
Try co access application in Firefox: "baddatabase"
In Firefox, type in connection info for empty db: "baddatabase"
In Firefox, type in connection info for old db: it works.
Access application from Chrome: it works.

I'm happy it works of course, but seriously I need an explanation! If I can't get/find one I'll probably go nuts and spend the rest of my life experimenting with this while my hair grows longer and longer and mice will start to nest in my beard...

pingu · 09-15-2014, 05:13 AM

There is one small difference:
When a db-connections is successful, the file protected/config/main.php is updated with connection info.
Now look at this, running "file" against working main.php & not working:

Code:

file lazy8/protected/config/main.php
lazy8/protected/config/main.php: PHP script text
lazy8/protected/config/main.php: PHP script, ASCII text

This could explain why lazy8 can't connect to db initially, but it's still a mystery why connecting via web-gui didn't work until I connected to a different database.