02 August 2010, 0:36

Hi, I am trying to do a similar thing but am getting the same errors.  Did you ever resolve this?

[Mon Aug 02 15:10:25 2010] [error] [client ::1] [WAR] 44 (lib/dml.php:467) Failed to get a recordset: mysql error: [: ] in EXECUTE("SELECT * FROM "institution"  WHERE "name" = 'anzatsa'  LIMIT 2"), referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1] [WAR] 44 (lib/dml.php:467) Command was: SELECT * FROM "institution"  WHERE "name" = ?  and values was (anzatsa), referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1] Call stack (most recent first):, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * get_recordset_sql("SELECT * FROM "institution"  WHERE "name" = ? ", array(size 1), 0, 2) at /home/miles/webwork/anzatsa/mahara/lib/dml.php:306, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * get_record_sql("SELECT * FROM "institution"  WHERE "name" = ? ", array(size 1)) at /home/miles/webwork/anzatsa/mahara/lib/dml.php:288, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * get_record("institution", "name", "anzatsa") at /home/miles/webwork/anzatsa/mahara/lib/institution.php:110, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * Institution->findByName("anzatsa") at /home/miles/webwork/anzatsa/mahara/lib/institution.php:55, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * Institution->__construct("anzatsa") at /home/miles/webwork/anzatsa/mahara/auth/lib.php:1193, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * login_submit(object(Pieform), array(size 6)) at Unknown:0, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * call_user_func_array("login_submit", array(size 2)) at /home/miles/webwork/anzatsa/mahara/lib/pieforms/pieform.php:430, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * Pieform->__construct(array(size 8)) at /home/miles/webwork/anzatsa/mahara/auth/lib.php:474, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * auth_setup() at /home/miles/webwork/anzatsa/mahara/init.php:239, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1]   * require("/home/miles/webwork/anzatsa/mahara/init.php") at /home/miles/webwork/anzatsa/mahara/index.php:32, referer: http://localhost/miles/anzatsa/mahara/
[Mon Aug 02 15:10:25 2010] [error] [client ::1] , referer: http://localhost/miles/anzatsa/mahara/

The problem seems to be the double quote around table and column names

SELECT * FROM "institution" WHERE "name" = 'anzatsa' LIMIT 2

When I try this directly I get error

#1064 - 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 '"institution" WHERE "name" = 'anzatsa' LIMIT 2' at line 1

SELECT * FROM institution WHERE name = 'anzatsa' LIMIT 2

works fine - should the " be ` ?

Why is everything else working OK until I try my auth plugin?

02 August 2010, 16:28

Hi Miles,

When Mahara sets up a mysql connection it turns on postgres compatibility mode, which lets you use the double quotes.  It's in the configure_dbconnection function in lib/dml.php.

02 August 2010, 19:47

That's fine but why is my accessing another database resetting this for the next query in Mahara?

Tue Aug 03 10:43:40 2010] [error] [client ::1] [WAR] b8 (lib/dml.php:467) Failed to get a recordset: mysql error: [: ] in EXECUTE("SELECT * FROM "institution"  WHERE "name" = 'anzatsa'  LIMIT 2"), referer: http://localhost/miles/anzatsa/mahara/
[Tue Aug 03 10:43:40 2010] [error] [client ::1] [WAR] b8 (lib/dml.php:467) Command was: SELECT * FROM "institution"  WHERE "name" = ?  and values was (anzatsa), referer: http://localhost/miles/anzatsa/mahara/

I have also tried setting POSTGRESQL compatability in my script with no success.

public function authenticate_user_account($user, $password) {
        // $this->must_be_ready();
        $username = $user->username;
        // empty username or password is not allowed.
        $Midid = mysql_connect($this->myconfig['db_host'], $this->myconfig['db_user'], $this->myconfig['db_pass']);
        mysql_select_db($this->myconfig['db_database']);
        // create query
        $query = "SET SQL_MODE='POSTGRESQL'";
        $Midquery = mysql_query($query, $Midid);
        $search = array('/{username}/', '/{password}/');
        $replace = array($username, $password);
        $query = preg_replace($search, $replace, $this->myconfig['db_query']);
        log_debug('Login Query: '.$query);
        $Midquery = mysql_query($query, $Midid);
        if($this->myconfig['login_result'] = mysql_fetch_assoc($Midquery)) $return = true;
        else  $return = false;
        mysql_close($Midid);
        return $return;
    }

02 August 2010, 0:38

#1064 - 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 '"usr_activity_preference" WHERE "usr" = '3' "' at line 1