Level 122
does this only happen in CI or also in prod?
Dec 3, 2024
10
Level 3
Where to store init sql query with databsase mode settings
Hi there, I need to run this sql
DB::statement("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"); // This is copy from MySqlConnector.php
It is modification of MySqlConnector.php::configureConnection() without ONLY_FULL_GROUP_BY. I want to use strict mode but without ONLY_FULL_GROUP_BY. ChatGPT lead me to add it to the AppServiceProvider::boot() method. Everything works BUT when I try to run our Bitbucket pipeline which contains command php artisan cache:clear it fail on error:
RUN cd /application && php artisan cache:clear
#21 sha256:1b4a053901e3fd6704ba3b6c9e8095bf143bba4784f432ebd7cb6e6bfe8b2fda
#21 0.513
#21 0.513 Illuminate\Database\QueryException
#21 0.513
#21 0.513 SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION')
#21 0.513
#21 0.513 at vendor/laravel/framework/src/Illuminate/Database/Connection.php:825
#21 0.518 821▕ $this->getName(), $query, $this->prepareBindings($bindings), $e
#21 0.518 822▕ );
#21 0.518 823▕ }
#21 0.518 824▕
#21 0.518 ➜ 825▕ throw new QueryException(
#21 0.518 826▕ $this->getName(), $query, $this->prepareBindings($bindings), $e
#21 0.518 827▕ );
#21 0.518 828▕ }
#21 0.518 829▕ }
#21 0.518
#21 0.518 1 [internal]:0
#21 0.518 Illuminate\Foundation\Application::Illuminate\Foundation\{closure}()
#21 0.519 +16 vendor frames
#21 0.519
#21 0.519 18 app/Providers/AppServiceProvider.php:36
It seems the connection is not resolved yet when cache:clear is called. The question is what is better place where to store this command to avoid this behaviour? Thanks.
Level 3
@Snapey It happened when pipeline try to run artisan cache:clear so the production never happened cause the pipeline died..
Level 73
I strongly advice against playing with these settings, it's much better to write correct SQL. If you move from MySQL to any other RDBMS you will need to rewrite all your group by queries, and not adding the fields you need in a group by, is just plain laziness.
Level 50
@Tray2 It's not about laziness. Partial grouping will produce different results. Here's a one use case:
There's an orders table with an id, customer_id, product_id, and timestamps. You want to get the latest order of each customer. If ONLY_FULL_GROUP_BY is off, you can run this:
SELECT id, customer_id, product_id, MAX(created_at) AS created_at
FROM orders
GROUP BY customer_id
ORDER BY created_at DESC
That gives you the latest order of each customer. If all the selected columns were in GROUP BY, you'd get every record in the table. MySQL and MariaDB don't have a convenient method for this when ONLY_FULL_GROUP_BY is enabled, at least that I know of. Postgres has the DISTINCT ON syntax.
Here's what the MySQL docs say: https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html
1 like
Level 3
@Tray2 There is nothing like correct sql. I know pretty well what I am doing so I will go through this.
Level 3
@Tray2 I never in my 15+ career migrarte from one databse to another. It does not make sense. You did?
Level 3
@Tray2 Slow down. It is not lazines I dont want end up with garbage in the code. I like spageti but Carbonara not in PHP.
Level 73
@JussiMannisto I don't agree, there are other ways around the issue, for example why do you select the product_id in your example, you shouldn't unless you are going to use it, and in your case you are only interested in the latest order, and you could use subqueries to handle the order id if needed.
Level 50
They go in the connection definitions in config/database.php, in a modes array:
'mysql' => [
(...)
'modes' => [
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
(...)
],
],
1 like
Level 3
@JussiMannisto Yes I did it exactly this way. Thanks for help.
Please or to participate in this conversation.