ANSI Mode right syntax to use near '". mshop_service"

[rhand]

Team mate installing Aimeos / doing migrations is getting this error

Code: Select all

Migrated: vendor/aimeos/aimeos-core/setup/Product.php (4.34ms)
Migrating: vendor/aimeos/ai-laravel/setup/CustomerClearPropertyKeyLaravel.php
Migrated: vendor/aimeos/ai-laravel/setup/CustomerClearPropertyKeyLaravel.php (14.06ms)
Migrating: vendor/aimeos/aimeos-core/setup/ServiceUniqueCode.php
Aimeos\Base\DB\Exception
SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check th e manual that corresponds to your MySQL server version for the right syntax to use near '".
mshop_service"
GROUP BY "code" HAVING COUNTC"code") > 1' at line 1:
SELECT "code" FROM "mshop_service" GROUP BY "code" HAVING COUNT"code") > 1
at vendor/aimeos/aimeos-base/src/DB/Statement/DBAL/Simple.php:75

I did not have this locally with my MariadDB database so trying to figure out why this is. Could be syntax issue or issue with syntax in version he is using. He is using Laravel Herd and our Laravel 9 Vue 2 app.

When I do research I am told this:

The problem is that the query in the vendor/aimeos/aimeos-core/setup/ServiceUniqueCode.php file uses double quotes for identifiers, which is not standard in MySQL but works in MariaDB with certain configurations.

and

And then we have "double quotes" which could be used for one of above purposes at a time depending on MySQL server's sql_mode:

In stock configuration, the " character can be used to enclose string literals just like '
In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `
The following query will produce different results (or errors) depending on SQL mode:

Code: Select all

SELECT "column" FROM table WHERE foo = "bar"

ANSI_QUOTES disabled
The query will select the string literal "column" where column foo is equal to string "bar"
ANSI_QUOTES enabled
The query will select the column column where column foo is equal to column bar

https://stackoverflow.com/a/14123649/460885

Not sure about his setup yet, but mine is mariadb from 11.2.2-MariaDB, client 15.2 for osx10.19 (arm64) using EditLine wrapper and all works well. So perhaps he did not get MysQL mode to run properly with

Code: Select all

'stmt' => config('database.default', 'mysql') === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"] : [],

Did he perhaps use the wrong `config/shop` settings not running that line for the database he chose for Aimeos? I guess portability.. ? Linked Stackoverflow answer linked to earlier recommends NOT using a MYSQL mode though comments remind us of portability .

Our servers use:

Code: Select all

mysql --version
mysql  Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

So we do need to make sure this all will work. Turning on ANSI mode server wide won't fly I think as Laravel does not work that way as far as I know.

Question remains what colleague did worn with Laravel Herd and MySQL and how to remedy this..

[aimeos]

Is the default connection not "mysql" in his case?
https://github.com/aimeos/aimeos-larave ... op.php#L59

[rhand]

We use a different database name / connection for Aimeos. Will have to ask what settings he used for the connection

Code: Select all

'stmt' => config('database.default', 'mysql') === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"] : [],

Guess he will need to use

Code: Select all

'stmt' => config('database.default', 'mysql_aimeos') === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"] : [],

Or am I wrong?

[aimeos]

Code: Select all

'stmt' => config('database.default', 'mysql_aimeos') === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"] : [],

This is wrong, it should be like this if you use MySQL only:

Code: Select all

'stmt' => ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"],