Fighting with mysql,byond+db (found solution)

ID:170663
 
Dec 29 2004, 6:14 am (Edited on Dec 30 2004, 1:22 pm)
okay so I am trying to get a test box up and running and I am wondering if I am missing something. I fail everytime I try to connect to the database. Yet perl using almost the same dbi string can connect fine. Has anyone else got db working? Heres the test code...
#define DBI             "dbi:mysql:test:localhost:3306"
#define DB_USER         "MY_USER_NAME"
#define DB_PASSWORD     "MY_PASSWORD"
CGI/Topic(href)
        var/ok
        var/DBConnection/dbcon = new()
        ok=dbcon.Connect(DBI,DB_USER,DB_PASSWORD)
        content_type = "text/html"
        if(!ok)
                usr << "failed:[dbcon.ErrorMsg()]!"
        else
                usr << "connected!"
                dbcon.Disconnect()
        ..()
Yea I know its pretty poor quality code, but I am just trying to test it. What I am wondering is if I am putting in the wrong thing for my_server (I replaced that with localhost). database is right, ports right (checked with netstat -an). user and password are right. I also emerged libdbi + libdbi-drivers. Thought maybe it was a library issue. And finally dbcon.ErrorMsg() returns a null string also, so that wasnt any help. Any thoughts?

MySQL 4.0.22
Byond 341.877
Dec 29 2004, 9:09 am
been a while since i tinkered with MySQL with BYOND. have you checked out the stuff at http://www.byond.com/hub/Dantom/DB ? compare your stuff with what Dan made- maybe there is a descrepancy?

i'd have to check into how i did it way back when to be of any more help.
Dec 29 2004, 9:14 am
In response to digitalmouse
yea. My code is basically the first couple lines of his code (in db.html). Thats why I am wondering if there is something wrong with my dbi string sense his reads: 
#define DBI "dbi:mysql:my_database:my_server:server_port"
and I am wondering if its looking for something else for my_server than what I have put there.
Dec 29 2004, 1:14 pm
In response to TrashHalo
hmm... have you tried 127.0.0.1 instead of 'localhost' for the server? or maybe your machine's ip address (the local network ip address if you are in a home LAN for example - or your internet ip address if you are alone connected to the internet). otherwise, is 'test' the right name for the database (and not a table in the database i mean)?

the last thing off the top of my head is whether you have a proper 'localhost' user access setting in your mysql.user table. there should be something that looks like:
Host            User           Password       Select_priv     Insert_priv    Update_priv...
localhost   (username)     4e2cd4073c7608a0      Y                 Y             Y

making sure that there is an entry for a user for localhost.

otherwise i'm stumped at the moment, myself.
Dec 29 2004, 1:52 pm
In response to digitalmouse
Have you tried connecting with the mysql (mysql.exe for me) client, and does that work?
Usually: mysql -u (username) -p
*Edit: Have you attempt logging in using the above with the login user/pass you'll be using in-game?
Dec 29 2004, 1:52 pm
In response to digitalmouse
Thanks for the suggestions. I just tried 127.0.0.1 and the lan ip for the machine. Both failed. Using the same user_name and password I can use perl's dbi to connect to the database and do basic queries so I dont think its the username. Though just to make sure I went ahead and checked to make sure that test was a database and the username existed: 
betty test # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23 to server version: 4.0.22

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

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| monsters       |
+----------------+
1 row in set (0.00 sec)

mysql> select * from monsters;
+-------+--------+
| monid | name   |
+-------+--------+
|     0 | goblin |
|     1 | slime  |
|     2 | orc    |
+-------+--------+
3 rows in set (0.01 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.01 sec)

mysql> select Host,User from user;
+-----------+----------+
| Host      | User     |
+-----------+----------+
| betty     | ******** |
| betty     | ******** |
| localhost | ******** |
| localhost |(username)|
| localhost | ******** |
+-----------+----------+
5 rows in set (0.00 sec)

mysql>
Dec 29 2004, 1:58 pm
In response to Nova2000
yes I have. mysql -u (username) -p (after I type in the pass) connects fine and I can access the database. Also as I said in the post above this I can use perl's dbi using the same user name and pass to connect.
Dec 29 2004, 3:52 pm
From what I've read I'm getting the idea your running this off of windows. "Currently, only MySQL access from Unix is supported."
Dec 29 2004, 5:46 pm
In response to Nick231
and what would give you that idea? I am on a windows machine (dual boot) but the one we are talking about is a dedicated gentoo box. Take a look a couple posts down in this topic where I dump my output the first line is: 
betty test # mysql
looks kinda like the shell to me ;) anyways I still have no luck finding whats wrong :( I am wondering if I am missing a library.
Dec 30 2004, 7:46 am
In response to TrashHalo
Its on the byond download page. I think this is your problem.
Dec 30 2004, 9:55 am (Edited on Dec 30 2004, 11:09 am)
In response to XxDohxX
Okay here are the steps to getting to getting db working and what was wrong.
First things first, there is a error in the documentation that comes with it. The default example initializes the database like this 
var/ok
var/DBConnection/dbcon = new()
ok = dbcon.Connect(DBI,DB_USER,DB_PASSWORD)
doing it like that, even using the EXACT code(while changing the dbi,user,pass) will result in a runtime error. The proper way to do it is like so 
var/ok
var/DBConnection/dbcon = new(DBI,DB_USER,DB_PASSWORD,Default_Cursor)
ok = dbcon.Connect()
so that was why I wasnt getting a failed. I couldnt see the runtime error because I was running it as a cgi. Thanks to the suggestion of jon88(I think) I ran it as a seeker and found that. But after I got passed that problem I got to a library issue. DB does NOT naturally work with mysql 4.*.*. It looks for a library called libmysqlclient.so.10 which ships with mysql 3.*.*. So your going to need to track down that. If your on redhat your going to need mysql-shared or mysql-shared-compat rpms. Or if your on another distro just extract the library you need into the lib dir (which is /usr/lib for me). Finally your going to need to make sure the mysql.sock file is in the right place. db wants it in /var/lib/mysql/mysql.sock but it could be many other places. to find out where its located go to /etc if the file my.cf isnt there go to /etc/mysql open the file my.cf in your fav text editor. There should be a line that looks like 
socket          = /var/run/mysqld/mysqld.sock
write down what it says after the socket=. Quit the editor and type in 
ln -s [what you just wrote down] /var/lib/mysql/mysql.sock
after that you should connect fine lol. ;)

Also: if at this point you are able to connect to the database using seeker under root but not via ByondCGI. you need to change the permissions of the mysql folder. At the prompt type 
cd /var/lib;chmod -R 777 mysql
but only do that if you fail to connect with cgi scripts. If you dont feel comfortable changing the premissions of the mysql folder you can also connect to the database by instead of placing localhost as the server place 127.0.0.1. You will connect over tcp/ip instead of sockets. You will take a proformence hit if you do this.
Dec 30 2004, 1:56 pm
In response to TrashHalo
thanks for the info!
Dec 30 2004, 2:02 pm
In response to TrashHalo
TrashHalo wrote:
and what would give you that idea? I am on a windows machine (dual boot) but the one we are talking about is a dedicated gentoo box. Take a look a couple posts down in this topic where I dump my output the first line is:
betty test # mysql
>
looks kinda like the shell to me ;) anyways I still have no luck finding whats wrong :( I am wondering if I am missing a library.

I was refering to byond itself, trying to access from a windows pc (to a server running linux) I couldn't get it working, didn't get it working from the linux box either, but had a little more luck. Anyway you got it working so it doesn't matter =)
Jan 15 2005, 5:02 pm
In response to TrashHalo
It seems that the Disconnect proc can cause a "BUG: Crashing do to an illegal operation!" error. Removing the disconnect line from my code fixed the problem.