Add enable_commenter support to mysql

[clesleycode]

Before opening a feature request against this repo, consider whether the feature should/could be implemented in the other OpenTelemetry client libraries. If so, please open an issue on opentelemetry-specification first.

Is your feature request related to a problem? Describe the solution you'd like.
Other SQL packages (like psycopg2) support SQL commenters for adding additional context -- I'd like to see this feature added to mysql

Describe alternatives you've considered
Which alternative solutions or features have you considered?
I didn't consider other alternatives since this feature already exists in other modules!

Additional context
Add any other context about the feature request here.
I think it'd be a simple change since it's already supported elsewhere. I have a potential draft PR here.

[rahuldimri]

@clesleycode can I give a try on this ?.

[tammy-baylis-swi]

Could trace context uniqueness in comments become an issue if mysql-connector supports prepared statements?: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorprepared.html

For example: if a parameterized statement is created then used for 3 queries (3 separate requests), then MySQL server may perform a PREPARE and EXECUTE the first time, then one EXECUTE for each subsequent query. Would the trace from each query be the same number of spans with correct parents?

A similar concern was brought up for Otel Java: open-telemetry/opentelemetry-sqlcommenter#32

[tammy-baylis-swi]

A correction to my previous comment: the number of spans isn't the issue!

I've experimented more with MySQL server-side prepared statements via mysql-connector-python and it might be that enabling sqlcommenter in instrumentation would have the same resource concerns are Marginalia and ActiveRecord: /~https://github.com/basecamp/marginalia/blob/master/README.md#prepared-statements

For example, I set up an application (db client) to use mysql-connector's cursor and use the same parameterized statement for 3 queries with different id param and a comment with unique timestamp added at each query. This would be similar to adding a unique traceparent comment each time:

SELECT * FROM city WHERE id = %s /* 1689198539.3315277 */
SELECT * FROM city WHERE id = %s /* 1689198539.3339512 */
SELECT * FROM city WHERE id = %s /* 1689198539.3353686 */

After queries, the MySQL general logs included these:

2023-07-12T21:48:59.333069Z	   15 Prepare	SELECT * FROM city WHERE id = ? /* 1689198539.3315277 */
2023-07-12T21:48:59.333236Z	   15 Reset stmt
2023-07-12T21:48:59.333563Z	   15 Execute	SELECT * FROM city WHERE id = 1818 /* 1689198539.3315277 */
2023-07-12T21:48:59.334429Z	   15 Close stmt
2023-07-12T21:48:59.334586Z	   15 Prepare	SELECT * FROM city WHERE id = ? /* 1689198539.3339512 */
2023-07-12T21:48:59.334777Z	   15 Reset stmt
2023-07-12T21:48:59.335062Z	   15 Execute	SELECT * FROM city WHERE id = 1819 /* 1689198539.3339512 */
2023-07-12T21:48:59.335743Z	   15 Close stmt
2023-07-12T21:48:59.335869Z	   15 Prepare	SELECT * FROM city WHERE id = ? /* 1689198539.3353686 */
2023-07-12T21:48:59.336064Z	   15 Reset stmt
2023-07-12T21:48:59.336268Z	   15 Execute	SELECT * FROM city WHERE id = 1820 /* 1689198539.3353686 */
2023-07-12T21:48:59.336887Z	   15 Close stmt

Since there is a Prepare for every Execute, there should be no issue with mismatched traceparent in logs and traces. But if the query were repeated many times with a unique traceparent, then system resources could become exhausted if prepared statements are used. Just something to keep in mind!