app_addon_sql_mysql fails connect

Hello all again,
I am really frustrated… I have a test 800Mhz Athalon test box and a production PowerEdge dual Xeon 2.0Ghz box. I am running the following on both -exact same setup-:

MySQL-shared-compat-4.1.21-1.glibc23
MySQL-devel-4.1.21-0.glibc23
MySQL-client-4.1.21-0.glibc23
MySQL-server-4.1.21-0.glibc23
asterisk-1.2.12.1
asterisk-addons-1.2.5

The mysql realtime and mysql cdr functions work perfectly on both.
The mysql command in the dialplan fails to connect no matter what I put in as a username and password (even root).

It seems somthing is broken here… Can anyone help?? Thanks!!
-Cheers, Peter.

A. what do you have in your host line in cdr_mysql.conf?

B. are you sure your socket line is appropriate for your machine?

Dear colbec,
Yes, the socket is correct… but I also tried commenting out the socket and using just the localhost IP and port… My CDR is working perfectly… just not the dialplan access… Here is my cdr_mysql.conf

    hostname = 127.0.0.1
    port = 3306

; sock = /data/var/lib/mysql/mysql.sock
timeout = 8
dbname = ast_cdr
table = cdr
user = ast-cdr
password = xxxxxxxxxx
userfield = 1
spool = /data/var/log/asterisk/cdr_mysql.spool

Thanks! :smile:
-Cheers, Peter.

OK so the problem is with the basic mysql function as described here:

http://www.voip-info.org/wiki/view/Asterisk+cmd+MYSQL

which you have followed in detail? If you simply connect to the db and then disconnect (ie no other operations) you still have a problem?

Yup… that is the function. :smile:
The failure happens right on the connect function.
‘exten => s,1,MYSQL(Connect connid dhhost dbuser dbpass dbname)’

In the code it fails at the stage where it attempts to connect to the mysql server. It is line 229 of ‘app_addon_sql_mysql.c’.

Thanks! -Cheers, Peter.

this might be a dumb question … have you allowed access for this user in MySQL from this host ?

Not a dumb question. Also, has he allowed access for for that user from 127.0.0.1, not only from localhost.

Let’s assume that the permissions on 127.0.0.1 are ok for this user since cdr_mysql works.

Next is an odd typo you have in your mysql(Connect connid d!h!host…

does that have any relevance?

How about punctuation inside the parentheses? Any odd marks (you have a hyphen and an underscore to deal with)? do you have any parameter substitution?

exten => s,1,MYSQL(Connect connid 127.0.0.1 ast-cdr xxxxxxxx ast_cdr)

Good points…

I only have the localhost setup for the host on the user account in MySQL… but the root account which has no restrictions also did not work, so I ruled out user privileges…

Just a check, but these connects are using the MySQL shared libraries correct… so the new password hashing system should not affect this?

I am fairly certain that my command is correct as it works on my test box, just not the production system. Both with the same MySQL, asterisk, and asterisk-addon versions…

Thanks so much!! :smile:
-Cheers, Peter.


Here are my sql commands btw… they reside in a macro:
exten => s,3,MYSQL(Connect dbConn localhost ast-dp xxxx ast-pbx_r2)
exten => s,4,MYSQL(Query resultid ${dbConn} SELECT\ in_out\ FROM\ exen_status\ WHERE\ stat_exten=’${EXT}’)
exten => s,5,MYSQL(Fetch foundRow ${resultid} OOO)
exten => s,6,MYSQL(Clear ${resultid})
exten => s,7,MYSQL(Disconnect ${dbConn})

hosts file contains

localhost 127.0.0.1

?

Yup…

sPBX-1:/ # ping localhost
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.071 ms
64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms

I am seriously baffeled…

Any possibility of difference in kernel between smp and non-smp??
test box = non-smp
prod box = smp

Thanks! -Cheers, Peter.

What error log files do you have available? When s,3 is activated do you see anything in /var/log/messages for example? Is there an access log file activated for mysql?

Sadly not… Nothing additional appears in the /var/log/asterisk/messages file either.

As of last night, I have started to rewrite parts of the module and have added better error messages so this may help the situation.

I will report back after I get the opportunity to reload the server tonight.

Thanks! -Cheers, Peter.

Eureka!!
Well, I got my answer… I rewrote parts of the add-on… I hope to submit them for integration into the trunk… Since then it works.

And I did notice on my test box, instead of a dash I used an underscore in the username. But at this point I have been fiddling so much I can not be certain that is what did it.

Either way, I have moved the username, password, and connect junk into a config file and out of the dialplan (much better in my opinion). There was a lot of inspiration that came from the work of the cdr_mysql authors… Thanks!!

And Thank You to yusuf, baconbuttie, and especially colbec for your help. Sometimes you just need to talk over a problem to find the answer.

Cheers all! -Peter