MySQL on MudOS/FluffOS

[Squid]

I had to make some changes to packages/db.c etc to get mysql compiled into FluffOS; I just thought I'd leave a bread crumb trail for other people looking to do the same.

http://wiki.desolation.org/MUD_Driver#Adding_MySQL_Support_to_MudOS.2FFluffOS

[wodan]

I'll test this and include it in FluffOS if it seems to work

[dlloyd]

Thanks, got this to work and communicating with Mysql 5.0.45 flawlessly.

[shadyman]

WOO! It works! 

And with NO CALLBACKS! Zomg.

Time to SQL-ize everything!

[shadyman]

Diff for the DB.c patch: (incase I borke the CVS)

Code:

Index: db.c
===================================================================
RCS file: /home/cvsroot/fluffos/packages/db.c,v
retrieving revision 1.1.1.1
diff -u -r1.1.1.1 db.c
--- db.c 19 Jul 2008 22:11:24 -0000 1.1.1.1
+++ db.c 18 Aug 2008 19:14:11 -0000
@@ -144,7 +144,7 @@
 {
     int ret = 0;
     db_t *db;
-   
+
     valid_database("close", &the_null_array);
 
     db = find_db_conn(sp->u.number);
@@ -648,9 +648,9 @@
  case FIELD_TYPE_VAR_STRING:
                     if (field->flags & BINARY_FLAG) {
 #ifndef NO_BUFFER_TYPE
-         v->item[i].type = T_BUFFER;
-         v->item[i].u.buf = allocate_buffer(field->length);
-         write_buffer(v->item[i].u.buf, 0, target_row[i], field->length);
+                       v->item[i].type = T_BUFFER;
+                       v->item[i].u.buf = allocate_buffer(field->max_length);
+                       write_buffer(v->item[i].u.buf, 0, target_row[i], field->max_length);
 #else
                         v->item[i] = const0u;
 #endif
@@ -679,13 +679,14 @@
 
 static int MySQL_connect (dbconn_t * c, const char * host, const char * database, const char * username, const char * password)
 {
-    int ret;
-    MYSQL *tmp;
-
-    tmp = ALLOCATE(MYSQL, TAG_DB, "MySQL_connect");
-    *(c->mysql.errormsg) = 0;
+   int ret;
+   MYSQL *tmp;
+   tmp = ALLOCATE(MYSQL, TAG_DB, "MySQL_connect");
+   tmp = mysql_init(tmp);
+   *(c->mysql.errormsg) = 0;
 
-    c->mysql.handle = mysql_connect(tmp, host, username, password);
+   c->mysql.handle = mysql_real_connect(tmp, host, username, password, database, 0, MYSQL_SOCKET_ADDRESS, 0);
+   //c->mysql.handle = mysql_connect(tmp, host, username, password);
     if (!c->mysql.handle) {
  strncpy(c->mysql.errormsg, mysql_error(tmp), sizeof(c->mysql.errormsg));
  c->mysql.errormsg[sizeof(c->mysql.errormsg) - 1] = 0;

[shadyman]

Keep in mind now that you supposedly need to do a mysql_config and:
1) copy the contents of --libs  [ stuff from here ] to the "system_libs" file, I guess in "packages/".
2) Make sure the path in --include is what's in the edit_source.c for

Code:

|| check_include("INCL_MYSQL_MYSQL_H", "/usr/include/mysql/mysql.h"))) {

  Change /usr/include/mysql/mysql.h as necessary  (Optional: Or... Find out which one yours is, there's a bunch of incl_mysql_'s in there, and #define it in the next step instead of INCL_MYSQL_MYSQL_H)


You still need to change your local_options to include this: (Don't forget to remove any #undef PACKAGE_DB)

Code:

#define PACKAGE_DB
#ifdef PACKAGE_DB
#define USE_MYSQL 2
#define DEFAULT_DB USE_MYSQL
#define INCL_MYSQL_MYSQL_H
#define MYSQL_SOCKET_ADDRESS    "/tmp/mysql.sock"
#endif

You might have to change the MYSQL_SOCKET_ADDRESS, mine was actually /var/lib/something/something/mysqld.sock

[shadyman]

Urgh, power went out, and I now I can't edit 

Anyways, here is what I *wanted* to say.

Keep in mind now that you supposedly need to do a mysql_config and:
1) copy the contents of --libs  [ stuff from here ] to the "system_libs" file, I guess in "packages/".
2) Optional: if there is no "INCL_MYSQL_{WHATEVER}" (See "defines" code below) that matches your mysql.h location (location is given in --include), Change /usr/include/mysql/mysql.h in edit_source.c to whatever you need, and #define INCL_MYSQL_MYSQL_H in the defines below.

Code:

|| check_include("INCL_MYSQL_MYSQL_H", "/usr/include/mysql/mysql.h"))) {

  Change /usr/include/mysql/mysql.h as necessary 

You still need to change your local_options to include this:

Code:

#define PACKAGE_DB //Enable/disable the Database package

#ifdef PACKAGE_DB

#define USE_MYSQL 2 //Newfangled, working, MySQL code
#define DEFAULT_DB USE_MYSQL //Use MySQL code as opposed to MSQL, etc

/* Define whichever of the following gives your proper mysql.h location: Only define one, though.
 * The rest aren't needed, so it's safe to comment them out.
 * /
//#define INCL_LOCAL_MYSQL_H    // "/usr/local/include/mysql.h"
//#define INCL_LOCAL_INCLUDE_MYSQL_MYSQL_H    // "/usr/local/include/mysql/mysql.h
//#define INCL_LOCAL_MYSQL_MYSQL_H    // "/usr/local/mysql/include/mysql.h"
#define INCL_MYSQL_MYSQL_H    // "/usr/include/mysql/mysql.h"

//Change this to the location of your mysql.sock (or mysqld.sock, if you don't have a mysql.sock)
#define MYSQL_SOCKET_ADDRESS    "/tmp/mysql.sock"

#endif

[shadyman]

[REmoved.]

[shadyman]

Sorry for the spam, but here's a relatively generic example daemon to handle chatter to/from the SQL package. It makes SQL access that much easier.

Code:

/* /secure/daemon/SQL.c
 * Author: Shadyman@QS, using code, comments, and some
 *         examples from FluffOS DB Package
 * Date: 18-Aug-2008
 * Redistribute as needed, but please leave headers intact
 *
 * This file acts as an intermediary between the lib and
 * the driver's DB package to make things a little easier
 * to use/more foolproof/implementable with less code.
 */

#include <db.h>

inherit LIB_DAEMON;

/* varargs int SQL_CONNECT (string server, string db,
 *                          string username)
 * Connects to the specified server and database, with
 *   the given username, or uses defaults if none given.
 * All arguments are optional. Defaults from db.h
 * Returns the connection handle (int >= 1). 0 is FAIL.
 */
varargs int sql_connect (string server, string db, string username) {
    mixed foo;

    if (!server || server == "") server = CONFIG_DB_HOST;
    if (!db || db == "") db = CONFIG_DB;
    if (!username || username == "") username = CONFIG_DB_USER;

    foo = db_connect(server, db, username);

    if (!intp(foo)) {
log_file(SQL_LOG,"db_connect returned: "+foo);
      return 0;
    } else {
      return foo;
    }
}

/* mixed SQL_QUERY (int handle, string query)
 *   Returns 0 on fail, ({}) on 0 rows, string for error message
 *   Returns array of arrays for resultset:
 *     ({ / * sizeof() == 105 * /
 *        ({ / * sizeof() == 7 * /
 *           1,
 *           "dirk",
 *           "Dirk the Tired",
 *           "/domains/town/npc/dirk",
 *           "male",
 *           "0",
 *           "human"
 *        }),
 *        ...
 *     })
 */
mixed sql_query (int handle, string query) {
    string *res = ({});
    string *ret = ({});
    mixed rows;
    int i;

    if (handle < 1) {
       log_file(SQL_LOG,"Handle < 1... Not properly connected to DB");
       return 0;
    }

    rows = db_exec(handle, query);
    if( !rows ) {
        return ({});
    } else if( stringp(rows) ) { //Error out
        log_file(SQL_LOG,rows);
        return rows;
    } else {
        for(i=1; i<=rows; i++) {
            res = db_fetch(handle, i);
            ret += ({ res });
        }
        return ret;
    }


}

/* Not yet implemented (maybe?) at the driver level.
 *
 * Commits the last set of transactions to the database
 * NOTE: MSQL does not have transaction logic, but MySQL DOES,
 *   so you CAN allow commits if you disable
 *   the AUTOCOMMIT (SET AUTOCOMMIT=0) feature.
 *   See http://dev.mysql.com/doc/refman/5.0/en/commit.html for more.
 * Saving a "commit" until after you spam commands keeps
 *   the unnecessary disk writes to a minimum, and you can
 *   "Undo" with a rollback if something goes wrong.
 *
 * Returns 1 on success, 0 on failure
 */
int sql_commit(int handle) {
   return db_commit(handle);
}

/* Rolls back all db_exec() calls back to the last db_commit() call
 * for the named connection handle.
 * NOTE: MSQL does not support rollbacks, but MySQL CAN, if you
 *   disable the AUTOCOMMIT (SET AUTOCOMMIT=0);
 *   See http://dev.mysql.com/doc/refman/5.0/en/commit.html for more.
 *
 * Returns 1 on success, 0 on failure
 */
int sql_rollback(int handle) {
   return db_rollback(handle);
}

/*
 * Returns a string describing the database package's current status
 * May be "" if nothing connected, or like "MYSQL->1" for MYSQL
 * operating on handle 1.
 */
string sql_status() {
   return db_status();
}

/*
 * Closes the database named by 'handle'.
 */
int sql_close(int handle) {
    return db_close(handle);
}

/* varargs mixed SQL_QUICKQUERY (string query, string server,
 *                               string db, string username)
 *
 * A quick, clean-code way to insert, update or delete a
 * record or recordset, since insert/update/delete queries
 * have no resultset.
 *
 * Returns 1 on Success or 0 on Failure
 *
 * sql_quickquery can process multiple inserts/updates/deletes
 * by making the "query" an array of queries. IE:
 * ({ "INSERT INTO FOO WHERE..", "DELETE FROM BLAH WHERE.." })
 * however, string queries will work also. IE:
 *   "INSERT INTO FOO WHERE..."
 */
varargs mixed sql_quickquery (mixed query, string server,
               string db, string username) {
   mixed handle;
   int ret,i;

   // if (!server || server == "") server = CONFIG_DB_HOST;
   // if (!db || db == "") db = CONFIG_DB;
   // if (!username || username == "") username = CONFIG_DB_USER;

   handle = sql_connect(server, db, username); //Connect

   if ( !intp(handle) || handle < 1 )
return 0;

   if ( arrayp(query) ) {
      for(i=0; i<=sizeof(query)-1; i++) {
   //ret should normally be 0 or ({}) for no-return queries
   ret = db_exec(handle, query[i]);
         if ( ret != 0 && ret != ({}) ) {
//Log funny results.
            log_file(SQL_LOG,"Query '"+query[i]+" returned something "
                 "funny: "+sprintf("%O",ret));
            //Rollback all queries. (Maybe)
            //sql_rollback(handle);
//FAIL. (Maybe)
            return 0;
         }
      }
   } else if ( stringp(query) ) {
ret = db_exec(handle, query);
   } else { //WTF?
      sql_close(handle); //Disconnect
      return 0;
   }

   sql_commit(handle); //Not yet implemented
   sql_close(handle); //Disconnect
}

And in DB.h:

Code:

#ifndef DB_H
#define DB_H
/**
 * Set up Database Log
 */
#define SQL_LOG "/log/sql"

/**
 * Set up Database Access
 */
//Default database name
#define CONFIG_DB "mud"
//General database user (least priviledges)
#define CONFIG_DB_USER           "mud"
//General database user's password. General database user probably doesn't need a password.
#define CONFIG_DB_PASS ""

/**
 * Set up Database Connection
 */
//If your sql server is on another machine, enter its IP here.
#define CONFIG_DB_HOST           "127.0.0.1"
#endif /* DB_H */

[wodan]

Urgh, power went out, and I now I can't edit 

Anyways, here is what I *wanted* to say.

Keep in mind now that you supposedly need to do a mysql_config and:
1) copy the contents of --libs  [ stuff from here ] to the "system_libs" file, I guess in "packages/".
2) Optional: if there is no "INCL_MYSQL_{WHATEVER}" (See "defines" code below) that matches your mysql.h location (location is given in --include), Change /usr/include/mysql/mysql.h in edit_source.c to whatever you need, and #define INCL_MYSQL_MYSQL_H in the defines below.

Code:

|| check_include("INCL_MYSQL_MYSQL_H", "/usr/include/mysql/mysql.h"))) {

  Change /usr/include/mysql/mysql.h as necessary 

You still need to change your local_options to include this:

Code:

#define PACKAGE_DB //Enable/disable the Database package

#ifdef PACKAGE_DB

#define USE_MYSQL 2 //Newfangled, working, MySQL code
#define DEFAULT_DB USE_MYSQL //Use MySQL code as opposed to MSQL, etc

/* Define whichever of the following gives your proper mysql.h location: Only define one, though.
 * The rest aren't needed, so it's safe to comment them out.
 * /
//#define INCL_LOCAL_MYSQL_H    // "/usr/local/include/mysql.h"
//#define INCL_LOCAL_INCLUDE_MYSQL_MYSQL_H    // "/usr/local/include/mysql/mysql.h
//#define INCL_LOCAL_MYSQL_MYSQL_H    // "/usr/local/mysql/include/mysql.h"
#define INCL_MYSQL_MYSQL_H    // "/usr/include/mysql/mysql.h"

//Change this to the location of your mysql.sock (or mysqld.sock, if you don't have a mysql.sock)
#define MYSQL_SOCKET_ADDRESS    "/tmp/mysql.sock"

#endif

ehm, there's code in edit_source.c to look up the right one which adds the proper define to configure.h, if yours is missing, add a test please!

[shadyman]

ehm, there's code in edit_source.c to look up the right one which adds the proper define to configure.h, if yours is missing, add a test please!

I was just going by Squid's instructions I guess I'll have to look deeper.

[shadyman]

Mine was in "/usr/include/mysql/mysql.h", so as far as I know, the tests work, so you shouldn't need the block of #defines.

[melkor]

One other thing that i find when try to use MySQL with TMI-2.
You need a function valid_database() in the master object or else the driver throws an error about security violation.
Here is my test function:

Code:

#define CONNECT_DB_PASS mypass
static mixed valid_database(string action, array info) {
    return CONNECT_DB_PASS;
}

The function valid_database() in the driver accepts the following results:
string - the password for the database.
int - 1 for connect without password, 0 for deny connection.

Hope this helps.

[zortek]

In the past I've preferred to include the object practicality:

Code:

static mixed valid_database(object ob, string action, mixed *info)
{
    switch(file_name(ob))
    {
         case "/adm/daemons/log_d":      return LOGD_DB_PASS;
         case "/adm/daemons/error_d":    return ERRORD_DB_PASS;
         case "/adm/daemons/econ_d":     return ECOND_DB_PASS;
         case "/adm/daemons/stellar_d":  return STELD_DB_PASS;
         case "/adm/daemons/tactical_d": return TACTD_DB_PASS;
         case "/adm/daemon/stocks_d":    return STOCD_DB_PASS;
         default:
      }
      return 0;  // Don't allow anyone/anything else in the lib to interact with MySQL
}

Perhaps its stylistic, but I prefer having the object in there to feel like I have more granular control over access.  It's not going to keep the blackhats away but I gave up "defending" a long time ago... it's more about pragmatism and segmentation of problems should there be a data integrity challenge later that needs to be chased.

Optionally, if you won't need to scale to support such complexity...just an if(file_name(ob) == "/secure/daemon/foo_d")  return MYPASS; might offer you a simple gate.

Humbly,
Z.

[wodan]

Using this significantly increased memory usage on my testserver (about 50MB more at startup).
Does anyone else see anything like that?