[Home] [Downloads] [Search] [Help/forum]


Register forum user name Search FAQ

Gammon Forum

[Folder]  Entire forum
-> [Folder]  SMAUG
. -> [Folder]  SMAUG coding
. . -> [Subject]  Smaug and MySQL

Smaug and MySQL

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page


Pages: 1 2  

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Thu 02 Mar 2006 04:16 PM (UTC)

Amended on Thu 02 Mar 2006 04:17 PM (UTC) by Gohan_TheDragonball

Message
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
[Go to top] top

Posted by David Haley   USA  (3,881 posts)  [Biography] bio
Date Reply #1 on Thu 02 Mar 2006 05:31 PM (UTC)
Message
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?

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top

Posted by Nick Cash   USA  (626 posts)  [Biography] bio
Date Reply #2 on Thu 02 Mar 2006 05:38 PM (UTC)
Message
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).

~Nick Cash
http://www.nick-cash.com
[Go to top] top

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Reply #3 on Thu 02 Mar 2006 06:34 PM (UTC)
Message
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.
[Go to top] top

Posted by David Haley   USA  (3,881 posts)  [Biography] bio
Date Reply #4 on Thu 02 Mar 2006 06:47 PM (UTC)
Message
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.

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Reply #5 on Thu 02 Mar 2006 07:47 PM (UTC)
Message
The forum changed it to single quotes.
[Go to top] top

Posted by David Haley   USA  (3,881 posts)  [Biography] bio
Date Reply #6 on Thu 02 Mar 2006 07:54 PM (UTC)
Message
"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.

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Reply #7 on Thu 02 Mar 2006 08:06 PM (UTC)
Message
I should also mention its failing at the mysql_real_connect() part, so the queries are not whats failing.
[Go to top] top

Posted by David Haley   USA  (3,881 posts)  [Biography] bio
Date Reply #8 on Thu 02 Mar 2006 09:32 PM (UTC)
Message
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?

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Reply #9 on Fri 03 Mar 2006 04:47 AM (UTC)

Amended on Fri 03 Mar 2006 04:49 AM (UTC) by Gohan_TheDragonball

Message
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.
[Go to top] top

Posted by Nick Cash   USA  (626 posts)  [Biography] bio
Date Reply #10 on Fri 03 Mar 2006 03:17 PM (UTC)
Message
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)

~Nick Cash
http://www.nick-cash.com
[Go to top] top

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Reply #11 on Fri 03 Mar 2006 06:10 PM (UTC)
Message
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)
[Go to top] top

Posted by David Haley   USA  (3,881 posts)  [Biography] bio
Date Reply #12 on Fri 03 Mar 2006 08:25 PM (UTC)
Message
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.

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top

Posted by Gohan_TheDragonball   USA  (183 posts)  [Biography] bio
Date Reply #13 on Sat 04 Mar 2006 09:06 AM (UTC)
Message
Error: 2013 (CR_SERVER_LOST)

Thats what I'm getting.
[Go to top] top

Posted by David Haley   USA  (3,881 posts)  [Biography] bio
Date Reply #14 on Sat 04 Mar 2006 11:30 AM (UTC)
Message
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...

David Haley aka Ksilyan
Head Programmer,
Legends of the Darkstone

http://david.the-haleys.org
[Go to top] top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


38,760 views.

This is page 1, subject is 2 pages long: 1 2  [Next page]

It is now over 60 days since the last post. This thread is closed.     [Refresh] Refresh page

Go to topic:           Search the forum


[Go to top] top

Quick links: MUSHclient. MUSHclient help. Forum shortcuts. Posting templates. Lua modules. Lua documentation.

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.

[Home]


Written by Nick Gammon - 5K   profile for Nick Gammon on Stack Exchange, a network of free, community-driven Q&A sites   Marriage equality

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( https://gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Hosted at HostDash]