Ignite Realtime: Strange MySQL problem

Up to Discussions in Openfire Support

4102 Views 14 Replies Latest reply: Jul 8, 2008 4:41 PM by g money

vectorz

6 posts since
Jul 2, 2008

Currently Being Moderated

Jul 2, 2008 11:49 AM

Strange MySQL problem - localhost connection

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:

I'm sure i disabled iptables (for troubleshooting)
On james, if I point to localhost database, it hangs - however, if i point to the database on john it goes through without problem.
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.
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.
When I do a tcpdump, I see that the web admin hangs when trying to "SHOW.SESSION.VARIABLES" from local mysql.
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.

Like (0)

LG
5,935 posts since
Dec 13, 2005

Currently Being Moderated

Jul 2, 2008 1:44 PM (in response to vectorz)
Re: Strange MySQL problem - localhost connection

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

Report Abuse

Like (0)
- vectorz
  6 posts since
  Jul 2, 2008
  
  Currently Being Moderated
  
  Jul 2, 2008 4:28 PM (in response to LG)
  Re: Strange MySQL problem - localhost connection
  
  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.
  
  Report Abuse
  
  Like (0)
  - LG
    5,935 posts since
    Dec 13, 2005
    
    Currently Being Moderated
    
    Jul 3, 2008 12:04 PM (in response to vectorz)
    Re: Strange MySQL problem - localhost connection
    
    Hi,
    
    it could help to add openfire@hostname as another user, I usually do this when using MySQL.
    
    LG
    
    Report Abuse
    
    Like (0)
    - vectorz
      6 posts since
      Jul 2, 2008
      
      Currently Being Moderated
      
      Jul 3, 2008 12:19 PM (in response to LG)
      Re: Strange MySQL problem - localhost connection
      
      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.
      
      Report Abuse
      
      Like (0)
      - LG
        5,935 posts since
        Dec 13, 2005
        
        Currently Being Moderated
        
        Jul 3, 2008 12:50 PM (in response to vectorz)
        Re: Strange MySQL problem - localhost connection
        
        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
        
        Report Abuse
        
        Like (0)
        
        vectorz
        6 posts since
        Jul 2, 2008
        
        Currently Being Moderated
        
        Jul 3, 2008 2:29 PM (in response to LG)
        Re: Strange MySQL problem - localhost connection
        
        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):
        
        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
        
        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
        
        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.
        
        Report Abuse
        
        Like (0)
        
        g money
        5 posts since
        Jul 7, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 12:43 PM (in response to vectorz)
        Re: Strange MySQL problem - localhost connection
        
        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.
        
        Report Abuse
        
        Like (0)
        
        LG
        5,935 posts since
        Dec 13, 2005
        
        Currently Being Moderated
        
        Jul 8, 2008 1:09 PM (in response to vectorz)
        Re: Strange MySQL problem - localhost connection
        
        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
        
        Report Abuse
        
        Like (0)
        
        g money
        5 posts since
        Jul 7, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 2:15 PM (in response to LG)
        Re: Strange MySQL problem - localhost connection
        
        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
        
        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.
        
        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.
        
        Report Abuse
        
        Like (0)
        
        g money
        5 posts since
        Jul 7, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 3:43 PM (in response to g money)
        Re: Strange MySQL problem - localhost connection
        
        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$
        
        Report Abuse
        
        Like (0)
        
        vectorz
        6 posts since
        Jul 2, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 3:49 PM (in response to g money)
        Re: Strange MySQL problem - localhost connection
        
        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.
        
        netstat -an|grep LIST|grep 3306
        tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN
        
        Report Abuse
        
        Like (0)
        
        g money
        5 posts since
        Jul 7, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 4:08 PM (in response to vectorz)
        Re: Strange MySQL problem - localhost connection
        
        what happens when you do something like the following?
        
        mysql -p -h 127.0.0.1 -D openfire
        
        Report Abuse
        
        Like (0)
        
        vectorz
        6 posts since
        Jul 2, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 4:22 PM (in response to g money)
        Re: Strange MySQL problem - localhost connection
        
        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>
        
        Report Abuse
        
        Like (0)
        
        g money
        5 posts since
        Jul 7, 2008
        
        Currently Being Moderated
        
        Jul 8, 2008 4:41 PM (in response to vectorz)
        Re: Strange MySQL problem - localhost connection
        
        yup ... as you already know ... working like it should.
        
        Sorry. I've got nothing.
        
        g$
        
        Report Abuse
        
        Like (0)

Go to original post

Legend

Correct Answers - 10 points
Helpful Answers - 5 points

Jul 2, 2008 11:49 AM

Strange MySQL problem - localhost connection

Actions

More Like This

Bookmarked By (0)

Legend