1.8.2 MySQL Support Fails

Replying to loki

A cygwin build using 1.8.3p5 yields similar results. Lack of any sql entries in the logs isn't helpful.

First time round: sql() function twice returned "No SQL database connection" though I have a working database and user set up in mySQL and mush.cnf.

After that: this and @sql command closed down the MUSH without warning.

Replying to loki:

Anything like think sql(show tables) crashes the MUSH (and loses outdb).

Erroneous requests like think sql(twit) return: "SQL Error: You have an error in your SQL syntax ...", suggesting that we actually did make contact with the MySQL server.

Replying to loki:

Server AMD Athlon 64 Bit PennMUSH: Attempted 1.8.2p1 and 1.8.2p8 versus 1.8.0p13. Symptoms: * PennMUSH compiles in all versions with no errors or warnings. * Config setup for MySQL is the same across all tested versions. * @config compile shows The MUSH was compiled with MySQL support. * @sql SELECT 1 returns "No SQL database connection." on all tested 1.8.2 versions. * @sql SELECT 1 returns "1" on all tested 1.8.0 versions. * There are no log records related to SQL in any file across all versions.

Crashes seem to happen where results are returned or results are to be displayed.

The query think sql(update test set name='changed' where id=1) silently succeeds.

With good wishes, eric.

Replying to loki:

Server AMD Athlon 64 Bit PennMUSH: Attempted 1.8.2p1 and 1.8.2p8 versus 1.8.0p13. Symptoms: * PennMUSH compiles in all versions with no errors or warnings. * Config setup for MySQL is the same across all tested versions. * @config compile shows The MUSH was compiled with MySQL support. * @sql SELECT 1 returns "No SQL database connection." on all tested 1.8.2 versions. * @sql SELECT 1 returns "1" on all tested 1.8.0 versions. * There are no log records related to SQL in any file across all versions.

I put some log points in cmd_log, like:

do_rawlog(LT_ERR, "@SQL %s", "1\0");

=======

The query was "select * from test"

The data in the test database was:

1, name1, 2, name2

ie two rows containing two columns.

=======

This is the trace from the log file:

... @SQL 1 @SQL 2 @SQL 3 @SQL 4 @SQL 5 @SQL 6 @SQL 7 @SQL 7-1 @SQL 8 @SQL 9 @SQL 7 @SQL 7-1 @SQL 8 @SQL 9 @SQL 5 @SQL 6 @SQL 7 @SQL 7-1 @SQL 8 @SQL 9 @SQL 7 Crash ... (consistent, though may have prevented subsequent log entry/ies. I once caught "i5i" in place of "name2")

========

And this cmd_log with the log points in it:

COMMAND(cmd_sql)
{
#ifdef HAVE_MYSQL
  MYSQL_FIELD *fields = NULL;
#endif
  void *qres;
  int affected_rows = -1;
  int rownum;
  int numfields;
  int numrows;
  char *cell = NULL;
  char *name = NULL;
  char tbuf[BUFFER_LEN];
  char *tbp;
  ansi_string *as;
  int i;

  do_rawlog(LT_ERR, "@SQL %s", "1\0");

  qres = sql_query(arg_left, &affected_rows);

  do_rawlog(LT_ERR, "@SQL %s", "2\0");

  if (!qres) {
    if (affected_rows >= 0) {
      notify_format(player, "SQL: %d rows affected.", affected_rows);
    } else if (!sql_connected()) {
      notify(player, "No SQL database connection.");
    } else {
      notify_format(player, "SQL: Error: %s", sql_error());
    }
    return;
  }

  do_rawlog(LT_ERR, "@SQL %s", "3\0");

  /* Get results. A silent query (INSERT, UPDATE, etc.) will return NULL */
  switch (sql_platform()) {
#ifdef HAVE_MYSQL
  case SQL_PLATFORM_MYSQL:
    affected_rows = mysql_affected_rows(mysql_connp);
    numfields = mysql_num_fields(qres);
    numrows = INT_MAX;          /* Using mysql_use_result() doesn't know the number
                                   of rows ahead of time. */
    fields = mysql_fetch_fields(qres);
    break;
#endif
#ifdef HAVE_POSTGRESQL
  case SQL_PLATFORM_POSTGRESQL:
    numfields = PQnfields(qres);
    numrows = PQntuples(qres);
    break;
#endif
#ifdef HAVE_SQLITE3
  case SQL_PLATFORM_SQLITE3:
    numfields = sqlite3_column_count(qres);
    numrows = INT_MAX;
    break;
#endif
  default:
    goto finished;
  }

  do_rawlog(LT_ERR, "@SQL %s", "4\0");

  for (rownum = 0; rownum < numrows; rownum++) {
#ifdef HAVE_MYSQL
    MYSQL_ROW row_p = NULL;
    if (sql_platform() == SQL_PLATFORM_MYSQL) {
      row_p = mysql_fetch_row(qres);
      if (!row_p)
        break;
    }
#endif

do_rawlog(LT_ERR, "@SQL %s", "5\0");

#ifdef HAVE_SQLITE3
    if (sql_platform() == SQL_PLATFORM_SQLITE3) {
      int retcode = sqlite3_step(qres);
      if (retcode == SQLITE_DONE)
        break;
      else if (retcode != SQLITE_ROW) {
        notify_format(player, "SQL: Error: %s", sql_error());
        break;
      }
    }
#endif

do_rawlog(LT_ERR, "@SQL %s", "6\0");

    if (numfields > 0) {
      for (i = 0; i < numfields; i++) {
        switch (sql_platform()) {
#ifdef HAVE_MYSQL
        case SQL_PLATFORM_MYSQL:
          cell = row_p[i];
          name = fields[i].name;
          break;
#endif
#ifdef HAVE_POSTGRESQL
        case SQL_PLATFORM_POSTGRESQL:
          cell = PQgetvalue(qres, rownum, i);
          name = PQfname(qres, i);
          break;
#endif
#ifdef HAVE_SQLITE3
        case SQL_PLATFORM_SQLITE3:
          cell = (char *) sqlite3_column_text(qres, i);
          name = (char *) sqlite3_column_name(qres, i);
          break;
#endif
        default:
          /* Not reached, shuts up compiler */
          break;
        }
        
        do_rawlog(LT_ERR, "@SQL %s", "7\0");
        
        if (cell && *cell) {
          do_rawlog(LT_ERR, "@SQL %s", "7-1\0");
          if (strchr(cell, TAG_START) || strchr(cell, ESC_CHAR)) {
            do_rawlog(LT_ERR, "@SQL %s", "7-2\0");
            /* Either old or new style ANSI string. */
            tbp = tbuf;
            do_rawlog(LT_ERR, "@SQL %s", "7-3\0");
            as = parse_ansi_string(cell);
            do_rawlog(LT_ERR, "@SQL %s", "7-4\0");
            safe_ansi_string(as, 0, as->len, tbuf, &tbp);
            do_rawlog(LT_ERR, "@SQL %s", "7-5\0");
            *tbp = '\0';
            do_rawlog(LT_ERR, "@SQL %s", "7-6\0");
            free_ansi_string(as);
            do_rawlog(LT_ERR, "@SQL %s", "7-7\0");
            cell = tbuf;
            do_rawlog(LT_ERR, "@SQL %s", "7-8\0");
            do_rawlog(LT_ERR, "@SQL cell=[%s] 7-8", cell);
            wait(10);
          }
        }
        
        do_rawlog(LT_ERR, "@SQL %s", "8\0");
        
        notify_format(player, "Row %d, Field %s: %s",
                      rownum + 1, name, (cell && *cell) ? cell : "NULL");
      
        do_rawlog(LT_ERR, "@SQL %s", "9\0");
        do_rawlog(LT_ERR, "@SQL cell=[%s] 9", cell);
      }
    } else
      {
      notify_format(player, "Row %d: NULL", rownum + 1);
      do_rawlog(LT_ERR, "@SQL row=[%s] 9", "NULL\0");
      }
  }

finished:
  free_sql_query(qres);
}

Sorry, the formatting messed up. Try this:

[06/09 22:44:35] @SQL 1
[06/09 22:44:35] @SQL 2
[06/09 22:44:35] @SQL 3
[06/09 22:44:35] @SQL 4
[06/09 22:44:35] @SQL 5
[06/09 22:44:35] @SQL 6
[06/09 22:44:35] @SQL 7
[06/09 22:44:35] @SQL 7-1
[06/09 22:44:35] @SQL 8
[06/09 22:44:35] @SQL 9
[06/09 22:44:35] @SQL cell=[1] 9
[06/09 22:44:35] @SQL 7
[06/09 22:44:35] @SQL 7-1
[06/09 22:44:35] @SQL 8
[06/09 22:44:35] @SQL 9
[06/09 22:44:35] @SQL cell=[name1] 9
[06/09 22:44:35] @SQL 5
[06/09 22:44:35] @SQL 6
[06/09 22:44:35] @SQL 7
[06/09 22:44:35] @SQL 7-1
[06/09 22:44:35] @SQL 8
[06/09 22:44:35] @SQL 9
[06/09 22:44:35] @SQL cell=[2] 9
[06/09 22:44:35] @SQL 7

Replying to EricT:

Sorry I don't have gdb working. I managed to catch the erroneous result in "select * from test". I was expecting the last cell in the database (row 2, column 2) to return "name2". It returned "i5i" followed by the crash:

...
[06/10 10:11:01] @SQL 1
[06/10 10:11:01] @SQL 2
[06/10 10:11:01] @SQL 3
[06/10 10:11:01] @SQL 4
[06/10 10:11:01] @SQL 5
[06/10 10:11:01] @SQL 6
[06/10 10:11:01] @SQL 7
[06/10 10:11:01] @SQL cell=[1] 7
[06/10 10:11:01] @SQL 7-1
[06/10 10:11:01] @SQL 8
[06/10 10:11:01] @SQL 9
[06/10 10:11:01] @SQL cell=[1] 9
[06/10 10:11:01] @SQL 7
[06/10 10:11:01] @SQL cell=[name1] 7
[06/10 10:11:01] @SQL 7-1
[06/10 10:11:01] @SQL 8
[06/10 10:11:01] @SQL 9
[06/10 10:11:01] @SQL cell=[name1] 9
[06/10 10:11:01] @SQL 5
[06/10 10:11:01] @SQL 6
[06/10 10:11:01] @SQL 7
[06/10 10:11:01] @SQL cell=[2] 7
[06/10 10:11:01] @SQL 7-1
[06/10 10:11:01] @SQL 8
[06/10 10:11:01] @SQL 9
[06/10 10:11:01] @SQL cell=[2] 9
[06/10 10:11:01] @SQL 7
[06/10 10:11:01] @SQL cell=[i5i] 7
[06/10 10:11:01] @SQL 7-1
[06/10 10:11:01] @SQL 8
[06/10 10:11:01] @SQL 9
[06/10 10:11:01] @SQL cell=[i5i] 9
Crash ...
(may have prevented subsequent log entry/ies)

Replying to EricT:

If I skip over the section in cmd_sql:

if (cell && *cell) {
   if (strchr(cell, TAG_START) || strchr(cell, ESC_CHAR)) {

I successfully read all cells in the datatable.

The thing then crashes after:

finished:
   free_sql_query(qres);

Cygwin / MySQL crash Replying to EricT:

Sorry, could be that this is my problem, trying to link to MySQL library libmysqlclient.a not compiled with Cygwin.

Instructions at http://cygwin.com/ml/cygwin/2006-01/msg00772.html suggest getting the MySQL linux source and using configure, make and make install on that.

WIP: Configure is telling me that the compiler can't convert between a long long and a float ... upgrade to egcs 1.0.3 or newer.

Replying to loki:

Server AMD Athlon 64 Bit PennMUSH: Attempted 1.8.2p1 and 1.8.2p8 versus 1.8.0p13. Symptoms: * PennMUSH compiles in all versions with no errors or warnings. * Config setup for MySQL is the same across all tested versions. * @config compile shows The MUSH was compiled with MySQL support. * @sql SELECT 1 returns "No SQL database connection." on all tested 1.8.2 versions. * @sql SELECT 1 returns "1" on all tested 1.8.0 versions. * There are no log records related to SQL in any file across all versions. I believe the MySQL changes between 1.8.0 and 1.8.2 (which are rather extensive) have caused this issue.

After building 1.8.3p5 with cygwin and building MySQL from Linux source with cygwin, I can confirm that MySQL is working:

@sql twit
SQL: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'twit' at line 1

a wandering dervish has arrived.

@sql update test set name='name1' where id=1
SQL: 1 rows affected.

@sql select 1
Row 1, Field 1: 1

@sql select * from test
Row 1, Field id: 1
Row 1, Field name: name1
Row 2, Field id: 2
Row 2, Field name: name2

fwiw, as a newbie to both cygwin and pennmush, I cobbled together readmes, howtos and my personal experiences and made a first draft of the install process here:

http://www.sarmouni.dyndns.org/mushkilgusha/building-pennmush.htm

Cheers, Eric T.