CODEWITHSUNDEEP
mysql
Lauren S
Lauren S

Reputation: 11

MySQL Query Not Logging to Performance Schema Tables

I am running a simple bash script that reads select statements from a file and executes the query so that I can gather performance statistics on the query. 

while read -r line || [[ -n $line ]]; do
    echo ${line//\\n/ }
    mysql ${MYSQL_CONN} -ANe"${line//\\n/ }" > select-output.txt 2>&1
done < ./test-stmts.txt

When I run my script, the query is successfully executed, but it isn't being logged to the performance_schema.events_statement_current table or the performance_schema.events_statement_history table. When I execute the same query from MySQL Workbench, the query is captured in both the events_statement_current and events_statement_history tables.

The performance_schema is enabled and the appropriate consumers are also enabled:

events_statements_current   YES
events_statements_history   YES
events_statements_history_long  NO
statements_digest   YES

Running SHOW VARIABLES LIKE 'performance_schema'; from my script returns performance_schema ON.

Do I need to add something to my script so that the queries will log to the performance_schema?

Thanks

Upvotes: 1

Views: 875

Answers (2)

Vikash Pathak
Vikash Pathak

Reputation: 3572

If you are using PDO, you must have to verify that such PDO options is set to true.

 PDO::ATTR_EMULATE_PREPARES => true

Here is some more details.

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. Requires

Upvotes: 1

Heiko Ebens
Heiko Ebens

Reputation: 496

I suggest verifying that the appropriate performance schema actors are enabled in case you are running the bash script with credentials (that differ when connecting to MySQL via Workbench and) that are not enabled in the performance_schema.setup_actors table.

To see what actors are enabled, use: SELECT * FROM performance_schema.setup_actors;.

Further details and instructions how to enable different users can be found here (assuming you are using MySQL 5.7).

While unlikely based on your description, I believe specific configurations of the performance_schema.setup_objects table can also cause the issue you are describing. Therefore, you might also want to check SELECT * FROM performance_schema.setup_objects;.

Upvotes: 1

Related Questions