Strange MySQL problem - localhost connection

Openfire Openfire Support
1

I have openfire (latest rpm from website) already working on server ‘john’ which is a FC4 with mysqld 5.0.51a but it was a test environment. I’ve decided to move it to a production env, so I’m trying to re-install openfire on a particular linux server named ‘james’ with mysqld 5.0.51a. I did the same exact steps as before, however, when running the web setup for the first time, when it gets to database setup and I enter the appropriate mysql settings, it just hangs. I’ve done some extensive testing, and here is what I’ve found:

  1. I’m sure i disabled iptables (for troubleshooting)

  2. On james, if I point to localhost database, it hangs - however, if i point to the database on john it goes through without problem.

  3. On john, if i re-run the setup and point to db on james, it also works without problem. This leads me to believe the db on james is fine.

  4. On james, I can connect via mysql client to localhost using both -S (socket) as well as network (-h) and works fine using the openfire username and password to connect to db. This is further indication that mysql connectivity works fine to localhost, and further isolates openfire as specifically the problem.

  5. When I do a tcpdump, I see that the web admin hangs when trying to “SHOW.SESSION.VARIABLES” from local mysql.

  6. I’ve double checked that the grants for ‘openfire’@’%’ as well ‘as’openfire’@‘localhost’ both have same password and have ALL privileges to openfire.*.

I’m not sure what else I can do to figure out the problem, it’s driving me crazy! Any help would be much appreciated.

2

Hi,

are you already connected and authenticated? The query should not block, or it should produce a timeout.

Are there any errors in the MySQL log files?

LG

3

I didn’t see any errors in mysql log. I am not yet authenticated, it is when you first click the button after entering the mysql settings and it tests the connection/authentication but yet doesn’t come back it just hangs.

4

Hi,

it could help to add openfire@hostname as another user, I usually do this when using MySQL.

LG

5

That sounds like a good idea. I went ahead and added ‘openfire’@‘hostname’ (hostname being equal to what the hostname command comes back with). Unfortunately it did not seem to help. This is the strangest problem because the mysql utilities all work fine from localhost. This would make me believe it has somethign to do with the java mysql driver that openfire uses but I don’t know enough about java to troubleshoot it much further.

6

Hi,

you could try to replace openfire/lib/mysql.jar with another version, maybe with http://www.igniterealtime.org/fisheye/browse/~raw,r=3978/svn-org/wildfire/trunk/ build/lib/dist/mysql.jar (see http://www.igniterealtime.org/fisheye/browse/svn-org/openfire/trunk/build/lib/di st/mysql.jar) which is quite old.

Or try to download the current version from the MySQL web site.

LG

7

Another very good suggestion! However, neither the one you linked nor the one from mysql website worked

Although I have more information. Looking closer at my mysql query log, here are the comparison of queries between the successful (JOHN server -> JAMES’s mysql) versus the UNsuccessful (JAMES server -> itself MYSQL):

  1. JOHN SERVER CONNECTION TO JAMES MYSQL IP

080703 14:17:22 9 Connect openfire@192.168.100.43 on openfire

10 Connect openfire@192.168.100.43 on openfire

9 Query SHOW SESSION VARIABLES

10 Query SHOW SESSION VARIABLES

10 Query SHOW COLLATION

9 Query SHOW COLLATION

9 Query SET character_set_results = NULL

10 Query SET character_set_results = NULL

9 Query SET autocommit=1

10 Query SET autocommit=1

10 Query SET sql_mode=‘STRICT_TRANS_TABLES’

9 Query SET sql_mode=‘STRICT_TRANS_TABLES’

9 Query SELECT @@session.tx_isolation

9 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

9 Query USE openfire

10 Query select 1

11 Connect openfire@192.168.100.43 on openfire

11 Query SHOW SESSION VARIABLES

11 Query SHOW COLLATION

11 Query SET character_set_results = NULL

11 Query SET autocommit=1

11 Query SET sql_mode=‘STRICT_TRANS_TABLES’

12 Connect openfire@192.168.100.43 on openfire

12 Query SHOW SESSION VARIABLES

12 Query SHOW COLLATION

12 Query SET character_set_results = NULL

12 Query SET autocommit=1

12 Query SET sql_mode=‘STRICT_TRANS_TABLES’

13 Connect openfire@192.168.100.43 on openfire

13 Query SHOW SESSION VARIABLES

13 Query SHOW COLLATION

13 Query SET character_set_results = NULL

13 Query SET autocommit=1

13 Query SET sql_mode=‘STRICT_TRANS_TABLES’

080703 14:17:23 10 Query UPDATE jiveVersion SET name=‘openfire’ WHERE name=‘wildfire’

10 Query SELECT version FROM jiveVersion WHERE name=‘openfire’

10 Query select 1

9 Query select 1

9 Query SELECT * FROM jiveID

9 Query select 1

  1. JAMES SERVER FIRST ATTEMPT - TO ITS OWN MYSQL LOOPBACK/LOCALHOST IP

080703 14:14:26 8 Connect openfire@localhost on openfire

7 Connect openfire@localhost on openfire

8 Query SET NAMES latin1

7 Query SET NAMES latin1

8 Query SET character_set_results = NULL

7 Query SET character_set_results = NULL

8 Query SHOW VARIABLES

7 Query SHOW VARIABLES

  1. JAMES SERVER SECOND ATTEMPT - TO ITS OWN MYSQL - THIS TIME USING NON-LOOPBACK IP

080703 14:19:25 15 Connect openfire@192.168.100.41 on openfire

14 Connect openfire@192.168.100.41 on openfire

14 Query SET NAMES latin1

15 Query SET NAMES latin1

14 Query SET character_set_results = NULL

15 Query SET character_set_results = NULL

14 Query SHOW VARIABLES

15 Query SHOW VARIABLES

NOTE: I have no idea why it’s try to set Query SET NAMES to latin1. I very specifically chose “English (En)” from the first question prompt.

8

I’m running into the samething.

I can connect fine to a remote MYSQL dbase but not to one on localhost

I can access the localhost dbase fine using commandline.

9

Hi,

could you try to run “netstat -an | grep 3306” to display the IP addresses MySQL is listening. Maybe you did specify 192.168.22.33:3306 in my.cnf so trying to connect to 127.0.0.1:3306 will not work.

LG

10

That’s a good suggestion this is what I’ve got.

my.cnf DOES have my ip address and not 127.0.0.1

tcp 0 0 205.226.5.28:3306 0.0.0.0:* LISTEN

As you were aluding to … 127.0.0.1 does not work

  1. mysql -p -h 127.0.0.1 -D openfire

Enter password:

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)

But I can do this.

  1. mysql -p -h localhost -D openfire

Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 271

Server version: 5.0.45-Debian_1ubuntu3.3-log Debian etch distribution

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

mysql> show tables;

*----

Tables_in_openfire

*----

jiveExtComponentConf

| jiveGroup |

<snip>

Let me go mess with MYSQL config a bit and I’ll get back to you.

Thanks for the suggestion.

11

That was the trick.

I commented out bind-address in my.cnf and restarted mysql

netstat -an | grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

Thanks for the great and fast response.

g$

12

Glad that fixed the problem for you, but for me the problem still persists.

I’m sure my problem is not related to networking because from the sql dump above you can see it does initially connect to MySQL and fetches some info.

  1. netstat -an|grep LIST|grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

13

what happens when you do something like the following?

mysql -p -h 127.0.0.1 -D openfire

14
  1. mysql -u openfire -p -h 127.0.0.1 -D openfire

Enter password:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 22 to server version: 5.0.51a-log

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

mysql>

15

yup … as you already know … working like it should.

Sorry. I’ve got nothing.

g$