[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
As Robert mentioned, we do "SET SQL_MODE='POSTGRESQL'" when using MySQL.
This is equivalent to the "PIPES_AS_CONCAT" directive Ghada mentioned,
as well as several other options:
Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,
NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_postgresql
** Changed in: mahara
Status: Confirmed => Invalid
** Changed in: mahara
Milestone: 16.10.0 => None
** Changed in: mahara
Status: Invalid => Won't Fix
--
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
Back in commit 1a4c340b6959f7d1bd5bf2b6147686a0d627af8a (circa 2007)
the line
$db->_Execute("SET SQL_MODE='POSTGRESQL'");
was added to make mysql being used by Mahara act like postgres so that
it can do the pipes (||)
So this shouldn't be an issue.
I did not when I copied sql commands from code to try directly in mysql
I'd need to do SET SQL_MODE='POSTGRESQL'; first
--
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:
Invalid
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
As there is a fine alternative, namely CONCAT(), for both MySQL and
Postgres, which I personally find easier to understand what is going on,
we should use that.
--
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:
Confirmed
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
** Changed in: mahara
Status: Incomplete => Confirmed
** Changed in: mahara
Importance: Undecided => High
** Changed in: mahara
Milestone: None => 16.10.0
--
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:
Confirmed
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
Ghada wants to take another look before deciding on whether anything
needs to be fixed.
** Changed in: mahara
Status: Triaged => Incomplete
--
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:
Incomplete
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1529775] Re: MySql concat string needs to be used instead of ||
** Changed in: mahara
Status: New => Triaged
--
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:
Incomplete
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
___
Mailing list: https://launchpad.net/~mahara-contributors
Post to : mahara-contributors@lists.launchpad.net
Unsubscribe : https://launchpad.net/~mahara-contributors
More help : https://help.launchpad.net/ListHelp
