Smaug and MySQL

Posted by Gohan_TheDragonball on Thu 02 Mar 2006 04:16 PM — 18 posts, 63,559 views.

Gohan_TheDragonball USA Thu 02 Mar 2006 04:16 PM #0

I am trying to connect my mud to my sql server to update statistics which are being displayed on our website. I got the code working from a snippet, and everything compiles fine. However it keeps loosing the connection before its able to perform the queries. Can anyone tell me whats wrong with the following.


#define MYSQL_SERVER    '127.0.0.1'
#define MYSQL_DB        'thedragonball'
#define MYSQL_USER      '****'
#define MYSQL_PWD       '******'

int stats_update_mkills(CHAR_DATA *attacker)
{
    MYSQL *hnd;
    char sql[1000];
    if ( (hnd = mysql_init(NULL)) == NULL ) {
        return -1;
    }
    if ( mysql_real_connect(hnd,MYSQL_SERVER,MYSQL_USER,MYSQL_PWD,MYSQL_DB,1210,NULL,0) == NULL ) {
        char buf[MAX_STRING_LENGTH];
        sprintf( buf, 'Stats_Update_Mkills: Error: %s', mysql_error(hnd) );
        monitor_chan( buf, MONITOR_GEN_IMM, LEVEL_IMMORTAL );
        mysql_close(hnd);
        return -2;
    }
    sprintf(sql,'update mud_player_stats set mkills = %d where player = '%s'', attacker->pcdata->mkills, attacker->name );
    if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
        monitor_chan( 'Stats_Update_Mkills: stage 2 failed', MONITOR_GEN_IMM, LEVEL_IMMORTAL );
        mysql_close(hnd);
        return -3;
    }
    if ( mysql_affected_rows(hnd) == 0 ) {
        sprintf(sql,'insert into mud_player_stats values( '%s', 0, %d, 0, 0, 0, 0 )', attacker->name, attacker->pcdata->mkills );
        if ( mysql_real_query(hnd, sql, strlen(sql)) != 0 ) {
            monitor_chan( 'Stats_Update_Mkills: stage 3 failed', MONITOR_GEN_IMM, LEVEL_IMMORTAL );
            mysql_close(hnd);
            return -4;
        }
    }
    mysql_close(hnd);
    return 0;
}

This is what I keep getting: Error: Lost connection to MySQL server during query

David Haley USA Thu 02 Mar 2006 05:31 PM #1

sprintf(sql,'update mud_player_stats set mkills = %d where player = '%s'', attacker->pcdata->mkills, attacker->name );

Is that truly what you have in your code? How does that even compile -- don't you need double-quotes?

Nick Cash USA Thu 02 Mar 2006 05:38 PM #2

You might do better to go with a global one time connection (presumably on startup) rather than connecting to the DB each time you want to do a query (supposing you plan to use MySQL in any significant form).

Gohan_TheDragonball USA Thu 02 Mar 2006 06:34 PM #3

Well, if I can't connect even once, how will changing it to a global connection and ran at startup change it. While I do agree, I would prefer to only connect once and point to that connection rather than multiple connection and disconnects, I first need to figure out why it won't connect.

David Haley USA Thu 02 Mar 2006 06:47 PM #4

Did you check my post? Your posted code just isn't right, so I suspect you didn't post exactly what your code is.

In fact, all of your strings use single quotes and not double quotes, so I'm not sure quite what you're doing.

Gohan_TheDragonball USA Thu 02 Mar 2006 07:47 PM #5

The forum changed it to single quotes.

David Haley USA Thu 02 Mar 2006 07:54 PM #6

"I am using double quotes with 'single' quotes inside"

I'm not sure what the problem with double quotes is. If you could post your code again, with double-quotes so I can see exactly what's going on, that would be helpful.

Gohan_TheDragonball USA Thu 02 Mar 2006 08:06 PM #7

I should also mention its failing at the mysql_real_connect() part, so the queries are not whats failing.

David Haley USA Thu 02 Mar 2006 09:32 PM #8

OK... it's important to know that it wasn't the queries.

The usual questions:
- are you sure that the DB is up and running?
- are you sure that you have the right user/pass?
- are you sure that the DB has privileges set up correctly to allow your user/pass from local connections?
- is there some strange firewall configuration that would somehow be blocking local connections on that port?
- can you connect to the DB otherwise e.g. from perl or php?

Gohan_TheDragonball USA Fri 03 Mar 2006 04:47 AM #9

Quote:

The usual questions:
- are you sure that the DB is up and running?
- are you sure that you have the right user/pass?
- are you sure that the DB has privileges set up correctly to allow your user/pass from local connections?
- is there some strange firewall configuration that would somehow be blocking local connections on that port?
- can you connect to the DB otherwise e.g. from perl or php?

i am sorry i guess i should have mentioned those things as well, i tend to assume too much. my website which is also local running php can connect and query the database just fine. the password/user is correct as well. the mud is located in the same shell as the website, so that is not an issue as well. the only thing i can assume is because i am trying to connect to it from a MUD.

One of my theories is that the database is trying to write back to the mud, the mud is not correctly handling it, and it is failing. Should that be the case I would not in a million years know how to fix it.

Nick Cash USA Fri 03 Mar 2006 03:17 PM #10

Try changing the port number in your mysql_real_connect call to 0.

It would then look like:


mysql_real_connect(hnd,MYSQL_SERVER,MYSQL_USER,MYSQL_PWD,MYSQL_DB,0,NULL,0)

Gohan_TheDragonball USA Fri 03 Mar 2006 06:10 PM #11

Changing the port from 1210 to 0 gave me this: [IMM_GEN] Stats_Player_Killed_Monster: Error: Can't connect to MySQL server on '127.0.0.1' (111)

Another thing I tried was changing MYSQL_SERVER to "127.0.0.1:1210" and that didn't work either, got this: [IMM_GEN] Stats_Player_Killed_Monster: Error: Unknown MySQL Server Host '127.0.0.1:1210' (3)

David Haley USA Fri 03 Mar 2006 08:25 PM #12

No, you can't put the port into the IP address when making the connection call.

Are you *sure* that the connection is failing? Looking through the error messages for mysql_real_connect:
http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html
doesn't list that error. It does however list a number of interesting things, such as mismatching protocol versions. Are you sure you have matching/compatible protocol/library versions?

If you could get the error name as opposed to error text (e.g. get CR_CONNECTION_ERROR) that would help pinpoint the problem.

Gohan_TheDragonball USA Sat 04 Mar 2006 09:06 AM #13

Error: 2013 (CR_SERVER_LOST)

Thats what I'm getting.

David Haley USA Sat 04 Mar 2006 11:30 AM #14

OK. Have you checked all the connection options? Namely, init-command.
The documentation is here:
http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html

There should be a link somewhere there, or not far away, that shows how to get the current options.

Obviously PHP is connecting slightly differently from the C library...

Gohan_TheDragonball USA Tue 07 Mar 2006 04:04 AM #15

WhiteKnight had it right from the start, i switched over to global definitions and an open_db_connect() at startup and now it works just fine, i can connect and query the database. wierd huh, don't know why that would be any different but learn something every day.

David Haley USA Tue 07 Mar 2006 04:56 AM #16

Maybe you weren't closing it correctly, so it worked the first time you updated the database but any subsequent call failed because the first one was still around. That's usually a danger with opening it every time.

(besides, you don't really need to, and in fact it's arguably much more efficient to open it only once, as Whiteknight suggested)

Gohan_TheDragonball USA Sat 25 Mar 2006 07:56 AM #17

actually no, it was not working at any time until i switched it to a global definition. its strange i know, but since i switched it i have had no problems at all.