MySQL connection issue

[milksnake12]

I'm having trouble connecting to a remote db. I setup a MySQL db on my blue host account that I'm hoping I can get asterisk to save the CDR to. I've entered what I think are the correct parameters but am getting an error message.

My odic.ini looks like this:
[asterisk-connector]
Description = MySQL connection to 'asterisk' database
Driver = MySQL
Database = snapfoot_asterisk
Server = 69.xx.xx.xxx
UserName = xxxxxxx
Password = xxxxxxx
Port = 3306
Socket = /var/lib/mysql/mysql.sock

I get this error when I type command: isql -v asterisk-connector
[S1000][unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user 'root'@'174-XX-X-XX.spkn.qwest.net' (using password: YES)
[ISQL]ERROR: Could not SQLConnect

I've also just logged into the phpMyAdmin in my Bluehost panel with the exact user and password, so those are good.

And thanks for all the help you guys have been giving, I've been in the world of Asterisk/ Linux for only about two weeks now, a lot to wrap my head around. You guys have been great!

[jpsharp]

You can log in via phpmyadmin because that connection, since it is originating from the web server process rather than an externalclient, appears to be coming from "localhost/127.0.0.1" or across a unix domain socket. MySQL, by default, allows the root user to connect only from localhost. You'll need to either allow remote root access (BAD IDEA!!!!) or create a separate MySQL user that has the right permissions to twiddle that table.

[milksnake12]

I made another user for the db and gave it some permissions, then stuck it in the odbc.ini and got the same result.

[milksnake12]

I also added my IP address to the Remote MySQL on my Bluehost.

Still nothing.

Any other ideas? Or maybe I didn't make a new user as jpsharp suggested correctly.

[milksnake12]

I also read to try ping'ing the Bluehost server from my box with asterisk on it. I did and got no response. Unfortunately I'm not familiar enough with Linux to know what this really means >.< or if it's related to my main issue from above...

[dalenoll]

Try using the mysql client with the credentials that you put into the odbc.ini file.
Perhaps it will give you a better error message than isql.

[milksnake12]

Well I've tried everything I can think of to solve this and can't get it to connect, so I'm going to try a different route. So originally I was trying to get Asterisk to send the db info to a db on my web server as you seen above. I am now trying to access my db on my Asterisk server with a php script. Great and all, but I still can't get a connection. Could someone with some seasoned eyes give the erroe below a quick once over just to make sure there is nothing obviously wrong. I can't seem to get around this snag.
I put the php testing script from http://www.ardamis.com/2008/05/26/a-php ... onnection/ in a .php and put it on my web server and put in my info to the db on my Asterisk server. It came back with this:
___________________________________________
Could not connect to the server '192.XXX.XXX.XXX' ;I am using the inet addr from the ifconfig command. Is that correct?

Lost connection to MySQL server at 'reading initial communication packet', system error: 110Access denied for user 'root'@'localhost' (using password: NO)
____________________________________________
I entered in the correct user, password and db. I took iptables down. I also entered in info to a db that is on the webservers localhost and it connected fine, I just can't for the life of me find a way to get connected to my Asterisk server's db.

Is there any other files that need something added to connect to the .php. What am I missing >.<

[tomdemoor]

___________________________________________
Could not connect to the server '192.XXX.XXX.XXX' ;I am using the inet addr from the ifconfig command. Is that correct?

Lost connection to MySQL server at 'reading initial communication packet', system error: 110Access denied for user 'root'@'localhost' (using password: NO)
____________________________________________
I entered in the correct user, password and db. I took iptables down. I also entered in info to a db that is on the webservers localhost and it connected fine, I just can't for the life of me find a way to get connected to my Asterisk server's db.

<

You still are trying to access witht the root account (and without password

110Access denied for user 'root'@'localhost' (using password: NO)

Are you absolutly sure that you have remote access the the mysql-server-daemon?

Try this in a php file

<?
$hostname = '192.x.x.x';
$username = 'the user you created';
$password = 'the very secret word;
$database = 'the database name';
mysql_connect("$hostname", "$username", "$password") or die ('No connection'.mysql_error());
mysql_select_db("$database") or die ('No access to database'.mysql_error());
?>


What does it give as result?

[milksnake12]

No I'm not absolutely sure that I have remote access the the mysql-server-daemon? How would I check for sure?

The above .php gave me error:
No connectionLost connection to MySQL server at 'reading initial communication packet', system error: 110

Thanks for the help!

[dalenoll]

From the Linux command line, try this....


mysql -h192.x.x.x -uuseranme -ppassword dbname

Does it connect? If not, what is the error?

[jimtom]

I can't remember exactly what error I was getting, but I solved a similar problem by renaming the odbc connection to something without the dash in it (i.e. the [asterisk-connector]).

[tomdemoor]

For me it seems that the asterisk server can't communicate to mysql-server.

A manual to let mysql listen on the ip-address instead of localhost only:
http://www.cyberciti.biz/tips/how-do-i- ... erver.html

[milksnake12]

Thanks for the reply's guys. Jim and Tom, I'm new to this so I may be wrong, but I think your posts are having to do with Asterisk writing to the db. I can get Asterisk to write to the db on my localhost (Asterisk and db are on one server, website that will be accessing the db on another). Let me know if that is not correct.
Dalenoll, I ran that command and it gave me a ton back, the first half was just different things I could do with the mysql command, the second half is below:
_________________________________
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
auto-rehash TRUE
character-sets-dir (No default value)
column-type-info FALSE
comments FALSE
compress FALSE
debug-check FALSE
debug-info FALSE
database (No default value)
default-character-set latin1
delimiter ;
vertical FALSE
force FALSE
named-commands FALSE
ignore-spaces FALSE
local-infile FALSE
no-beep FALSE
host 192.1XX.XXX.XXX
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 0
prompt mysql>
quick FALSE
raw FALSE
reconnect TRUE
socket (No default value)
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
table FALSE
user asterisk
safe-updates FALSE
i-am-a-dummy FALSE
connect_timeout 0
max_allowed_packet 16777216
net_buffer_length 16384
select_limit 1000
max_join_size 1000000
secure-auth FALSE
show-warnings FALSE
[root@localhost smurray]#
[root@localhost smurray]# cd
[root@localhost ~]# cd /etc/
[root@localhost etc]# ls
__________________________
I dont know if this means it connected or not.
Any other files you would need to see?

[dalenoll]

The looks like the output of 'mysql --help'

Make sure on the command line that you fill in the proper values after the -h, -u and -p, and make sure you actually have those three argument switches.

It should look something like this

mysql -h192.168.1.1 -udale -pmypass my_database

Some possible errors are:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.1' (113)
(No database running or port 3306 not open)

ERROR 1045 (28000): Access denied for user 'dale'@'host.example.com' (using password: YES)
(Database is running and connection is available, but user is not authorized from the source machine or the password is wrong)

[milksnake12]

Ok, I ran it again double checking my info and it connected and went to the mysql> prompt. I was able to see the tables etc. So it looks like my issue is on the connecting the .php part. Any reason my web server would not be allowed to connect? It's hosted on Bluehost. Do I need to add their IP anywhere?

[dalenoll]

OK, we have verified the the MySQL database is accepting connections.

Unfortunately, at this point I am a little confused. You have your Asterisk server with the mysql database on it. You have a system out on the Internet where you have a web server running. Is this correct?

Which server were you issuing the mysql command line on? Is it the same server as the web server or are they different?
If they are different, you will need to grant access to the mysql account for the address of the server you are connecting from.

If you ran the previous mysql command from the Asterisk server, then you need to run the test again from the remote host. If that fails, we will need to talk about firewalls, NAT and other such topics.

Dale

[milksnake12]

Unfortunately, at this point I am a little confused. You have your Asterisk server with the mysql database on it. You have a system out on the Internet where you have a web server running. Is this correct?

That is correct.

Which server were you issuing the mysql command line on? Is it the same server as the web server or are they different? If they are different, you will need to grant access to the mysql account for the address of the server you are connecting from.

I originally ran it from a vm on the same box as Asterisk and the db, which connected (only when I took iptables down). After your post, I connected to my web server via ssh and issued the mysql connect command and got this error:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.1XX.XXX.XXX' (110)

You mentioned that I may need to grant access to my web server to connect to my db, how would I go about doing that?

Thanks again for taking the time to help, it's really appreciated!

[dalenoll]

The ERROR 2003 indicates that the client cannot connect to the database at the server address. This is different than and 'Access denied (which you may stiff have, but we are not there yet).

Is your Asterisk server behind a router that is providing NAT?
Is the address of your Asterisk box 192.168.x.x?

If the answer to either of these is yes, then you will need to make a change to the router in order to allow the connection from your remote server to be forwarded to your Asterisk server. The concept of port forwarding is common, but different routers have different ways of doing that so you may have to do a bit of research.

Next you will need to know your public IP address. You can get that from your router as well. There are websites you can go to from within you home network that will tell you, or you could check your remote server to see what IP address you are connecting from.

Once that is all in place, then you can worry about putting the IP address of you remote server into the your local mysql database to allow access.

[milksnake12]

Is your Asterisk server behind a router that is providing NAT?

I don't think so. I logged into my router, Linksys WRT54GS, and didn't see anything saying NAT.

Is the address of your Asterisk box 192.168.x.x?

When I pull up ifconfig, yes it is.

If the answer to either of these is yes, then you will need to make a change to the router in order to allow the connection from your remote server to be forwarded to your Asterisk server. The concept of port forwarding is common, but different routers have different ways of doing that so you may have to do a bit of research.

Next you will need to know your public IP address. You can get that from your router as well. There are websites you can go to from within you home network that will tell you, or you could check your remote server to see what IP address you are connecting from.{/quote]
I did some reading on these and I added a port forward in my router settings for port 3306 (read this is the mysql port (y/n)), and entered to ip of my server (the 192.168.x.x). I then switched the 192.168.x.x ip to my public one in the php script you gave me above (still didn't connect, but not sure it will yet).

Once that is all in place, then you can worry about putting the IP address of you remote server into the your local mysql database to allow access.

Sorry for the time between posts, I was waiting for my actual server to get here. I was running the server off a vm in the earlier posts. Just wanted to see if putting all this on a actual server would change anything, it didn't. My setup has DSL coming into a modem, then to a router that has my main (personal) computer and the server plugged into it.

Did I do all that correctly to where we can add the web servers ip to allow access to the db?

Thanks again!

[tomdemoor]

Ok,

Some short theory on ip-addresses.
Each device connected to an ip-based network has an ipaddress.

You have public ip-adresses, those are accessible from the internet. Because there are not enough ip-addresses to give each device in the world a public address we have private addresses. Private addresses are used in internal networks. They can speak to the other internal ipaddresses in the internal network and to the internet through a router. The router does Net Address Translating (NAT) to make connections going to the internet and bring back the answers to the right internal device.

So, your webserver can't talk to the internal ipaddress of your mysql/asterisk server. So what must you do...
On your router, you can setup port-forwarding so that connections to your public ip are forwarded to your internal server (you can do that through the webinterface).

Then you let your webserver make a connection to your public ip-address.

Success!