Medoo ignores SET SQL_MODE command for MySQL database connections

[scr4bble]

Information

• Version of Medoo: v2.1.4
• Version of PHP: 8.1.6
• Type of Database: MySQL
• System: Linux

Describe the Problem Medoo ignores SET SQL_MODE command when used in options during Medoo object creation. It is probably executed but then there is the same command executed again in Medoo code and overwrites whatever user has set. Result is then that no matter the user sets, SQL_MODE always equals "ANSI_QUOTES".

Detail Code The detail code you are using causes the problem.

new Medoo([
	'command' => [
		'SET SQL_MODE="ANSI_QUOTES,ONLY_FULL_GROUP_BY"'
	]
]);

SQL_MODE gets overwritten by this code in Medoo.php:

case 'mysql':
    // Make MySQL using standard quoted identifier.
    $commands[] = 'SET SQL_MODE=ANSI_QUOTES';
    break;

Expected output I would expect Medoo to parse SET SQL_MODE (if entered by the user), check if ANSI_QUOTES is in the modes that user specified, if not then add it to the list, if yes, then just continue and let the command execute. Instead of adding another SET SQL_MODE command into $commands array that overwrites the user specified SQL modes.

[catfan]

You are right. I will fix that.

[scr4bble]

Thank you for fast reply!

[orware]

I had a related issue to this one to share and add to the conversation.

An observation I noticed in Medoo#1044 yesterday is that the default line referenced by @scr4bble above: https://github.com/catfan/Medoo/blob/master/src/Medoo.php#L243

Ends up overriding the SQL_MODE in MySQL completely, even if there are other settings already present within SQL_MODE.

For example, with PlanetScale the default SQL_MODE is the following:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

So after the Medoo line referenced above is executed for the current connection, it would change it to simply be ANSI_QUOTES instead.

A potential alternative approach that would simply add in the ANSI_QUOTES option to the existing list would be maybe something like this:

SET SQL_MODE = IF(FIND_IN_SET('ANSI_QUOTES', @@sql_mode), @@sql_mode, CONCAT(@@sql_mode, ',ANSI_QUOTES'));

Which should leave the SQL_MODE unchanged if ANSI_QUOTES is already in the list, or it will add it to the end if it isn't.

It still doesn't make ANSI_QUOTES work for PlanetScale databases, but this may help allow users to avoid needing to define a custom command in some cases if Medoo is currently overriding their server defaults.