mysql Archives - Kyriakos's Den

Frequently I find the need to see what SQL queries are actually generated magically by Eloquent. It really helps when I need to debug a query or figure out why its not performing well. Since I keep referring back to this very useful code snippet I thought its good to keep it here so its readily available for myself and anyone else. Add the following code in the boot() method of AppServiceProvider.php:

    public function boot() {
        if (\App::environment('local')) {
            DB::listen(
                static fn($query) => logger(
                    Str::replaceArray('?', array_map(fn($binding) => '\'' . $binding . '\'', $query->bindings),
                                      $query->sql
                    )
                )
            );
        }
    }

If you haven’t changed Laravel’s default logging configuration all queries executed will be logged in storage/logs/laravel.log. The full query including its parameter values will be included which makes it dead easy to then copy/paste and run EXPLAIN so you can see how you could optimize it. Note that this will only execute if APP_ENV is set to local so no sensitive information will be logged on your production logs.

In order to run PHPUnit tests I use a boostrap file which uses mysqldump to export the ever-changing database schema without any data, and import it into the testing database. After using this method for a while I stumbled on a problem with the auto-increment values for the table primary keys. There is no easy way to get mysqldump to avoid including the next auto-increment value in the dump. The problem this causes is that if your tests depend on specific primary key values things will go wrong the second time you run your test suite. There is a way to fix this by piping mysqldump’s output into sed. Here’s the actual command:

mysqldump -d [devDB] | sed 's/AUTO_INCREMENT=[0-9]*//' >

This will work under Linux but sadly not on Windows which doesn’t have sed but there’s a solution for that. Read More

Tag: mysql

Log Laravel Eloquent SQL queries with parameter values

How to mysqldump without auto increment values in Windows