Laravel API's mysql queries not getting tracked in performance_schema.events_statements_summary_by_digest

Question

I am facing some very weird issue here. We have a laravel API hosted on AWS EC2 and we using RDS (mysql 5.6). I recently enabled performance_schema on RDS. Following is the behavior I am noticing

1. We have two databases on our RDS instance. One for wordpress and one for our laravel API. Wordpress database query are getting digested fine. But queries run from our laravel app are not.
2. For some reason when I connect MySql Workbench to RDS Instance and executes queries on our Laravel database then they appear in statement summary fine.
3. I logged into my EC2 machine, connected to MySQL on RDS and executes some queries and they are getting tracked in statement summary.

So it looks like only when queries are executed from our Laravel App, they do not get tracked.

Our Laravel version is 4.2. I am trying to identify the reason from last two days, any help will be a relief.

The user that I used in all above steps is same and have all privileges on all databases.

--EDIT--

I performed many other tests and they all point out to only one conclusion that it has something to do with Laravel. I created a simple php file on the same server where laravel is hosted. In this file, I connected to the same instance/database with same user/password. Only thing I did in this file was to run a very simple Query on $pdo like this.

$stmt = $pdo->query('SELECT name FROM trades');
        while ($row = $stmt->fetch())
        {
            echo $row['name'] . "\n";
        }

and it appears in query analytics [https://prnt.sc/j3ochd] (I manually checked performance_schema.events_statements_summary_by_digest as well)

But then I can hit our laravel api which infact returns the entries from trade table itself ( very much like the query I run above). but that would appear in my query analytics reports ( Percona PMM) or in events_statements_summary_by_digest

Answer 1

You must have to pass the options parameter in database > connection settings as PDO::ATTR_EMULATE_PREPARES => true

config > database.php

'connections' => [

    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false,
        'engine' => null,
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => true
        ]
    ],

]

By default laravel marks this options as PDO::ATTR_EMULATE_PREPARES => false to improve query performance.

Here is the quick details aboout it.

PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE and emulated prepares are supported by the driver), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query.