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

Cogitate

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.

Smog

I'm puzzled as to how "preparing" is relevant. Whether prepared or not, "SELECT ..." is eventually sent to the server, at which point the server will record it if requested. – Toombs

@RickJames I am confused to but it seems vikash is correct. After making this change query do seems to start appearing in digest. [bugs.mysql.com/bug.php?id=80173] seems to state the same as well. – Cogitate

there official reply "after comparing the current behaviour of instrumentation of prepared statements from SQL prompt and from C API, we figured out that in both these cases, the instrumentation of the actual query used in prepared statement is not being done" – Cogitate

@vikash-pathak I read somewhere that if I do that change all fields will be returned as string. A little worried if that could break our existing application somehow. But for the question I had, your solutions seems to be working. So accepting – Cogitate