mahara-contributors team mailing list archive

[Aaron Wells <1529775@xxxxxxxxxxxxxxxxxx>]

The main gotcha to this, is that if you're debugging a Mahara instance
that's running in MySQL, and you copy out one of the SQL queries
generated by Mahara and try to run it manually in a separate MySQL
client.

If you do that, and the query uses ||, it'll error out unless you have
first manually run

    SET SQL_MODE='POSTGRESQL';

The full list of things we do when setting up a connection to a MySQL
DB, is in the "configure_dbconnection()" function in htdocs/lib/dml.php.
It's actually:

    SET NAMES 'utf8';
    SET SQL_MODE='POSTGRESQL';
    SET CHARACTER SET utf8;
    SET SQL_BIG_SELECTS=1;

And if you're using $CFG->dbtimezone, we also do

    SET time_zone='{$CFG->dbtimezone}';

So if you notice discrepancies when running Mahara-generated SQL queries
in a MySQL client, one thing to try is to run all of those in the client
and see if it makes a difference.

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1529775

Title:
  MySql concat string needs to be used instead of ||

Status in Mahara:
  Won't Fix

Bug description:
  Mahara 15.10
  OS: Ubuntu 14.04
  DB: Mysql 5.5
  Browser: any

  I've noticed that in htdocs/search/internal/lib.php, the SQL used to
  concatenate strings is '||'.

  For example, line 275:
  $sql = $alias . '.' . $field . ' ' . db_ilike() . " '%' || ? || '%'";

  
  Unfortunately, this doesn't always work with Mysql. In order for this to work we would need to set PIPES_AS_CONCAT

  Please refer to the documentation:
  http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_pipes_as_concat

  Otherwise, strings need to be concatenated using: 'CONCAT'.

  This function is also available in Postgres.

  So, perhaps we should be using CONCAT instead of '||'.

  So, the above line 275 would be:
  $sql = $alias . '.' . $field . ' ' . db_ilike() . " concat('%', ? , '%')";

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1529775/+subscriptions