Skip to content

Further enhancement in prepared statement logging #4861#4922

Merged
renecannao merged 6 commits intov3.0from
v3.0-4861_fixes1
May 8, 2025
Merged

Further enhancement in prepared statement logging #4861#4922
renecannao merged 6 commits intov3.0from
v3.0-4861_fixes1

Conversation

@renecannao
Copy link
Contributor

@renecannao renecannao commented Apr 25, 2025

Proper use of DATETIME/TIME/DATE/TIMESTAMP

For columns DATETIME/TIME/DATE/TIMESTAMP , in MYSQL_BIND they are set as length = 0 , but their real size is sizeof(MYSQL_TIME) (40 bytes) .

For reference, MYSQL_TIME is defined as:

typedef struct st_mysql_time
{ 
  unsigned int  year, month, day, hour, minute, second;
  unsigned long second_part;
  my_bool       neg;
  enum enum_mysql_timestamp_type time_type;
} MYSQL_TIME;

It is also worth to notice that during the execution of COM_STMT_EXECUTE the client library may choose a datatype different than the column type. This because the client library specifies the format of the data being sent.
This means that client library could use a STRING format when sending DATETIME / TIME / DATE / TIMESTAMP , or always specify DATETIME because the encoding is always the same.

Use hex encoding for string

STRING and BLOB are encoded in hex format. This is useful especially for blob, but blob are often sent as string, thus the same encoding is applied for both

Adding new variable mysql-eventslog_stmt_parameters #4923

Because of the overhead of logging prepared statements parameters , especially in JSON format and in the presence of large blob, new variable mysql-eventslog_stmt_parameters controls if parameters need to be logged or not

## Adding event_type PROXYSQL_METADATA

When creating an event log in binary format, a metadata packet is written.
The metadata is in JSON format.
It currently only provide the ProxySQL version.
This allows application reading the binary format file to identify the ProxySQL version thus know what format is used.

For illustration purposes, tool eventlog_reader_to_json.cpp supports it too.

In bufferTypeInfoMap , use sizeof(MYSQL_TIME) for the size of these types:
* MYSQL_TYPE_TIMESTAMP
* MYSQL_TYPE_DATE
* MYSQL_TYPE_TIME
* MYSQL_TYPE_DATETIME

Notes:
* sizeof(MYSQL_TIME) is 40
* client library may use MYSQL_TYPE_DATETIME for all of these types
When logging the parameters of COM_STMT_EXECUTE , use sizeof(MYSQL_TIME)
for these types:
* MYSQL_TYPE_TIMESTAMP
* MYSQL_TYPE_DATE
* MYSQL_TYPE_TIME
* MYSQL_TYPE_DATETIME

Notes:
* sizeof(MYSQL_TIME) is 40
* client library may use MYSQL_TYPE_DATETIME for all of these types
When parameters are strings or blobs, they are always converted to hex
Variable mysql-eventslog_stmt_parameters configures the logging of parameters in prepared statements:
- 0 : logging disabled
- 1 : logging enabled
…ters

Now that variable `mysql-eventslog_stmt_parameters` is present and disabled by default,
`mysql-eventslog_stmt_parameters` needs to be enabled to run test `test_ps_logging-t`
@renecannao renecannao changed the title Proper use of DATETIME/TIME/DATE/TIMESTAMP in prepared statement logging #4861 Further enhancement in prepared statement logging #4861 Apr 25, 2025
When creating an event log in binary format, a metadata packet is written.
The metadata is in JSON format.
It currently only provide the ProxySQL version.

For illustration purposes, tool eventlog_reader_to_json.cpp supports it too.

This commit includes also some reformatting
@renecannao renecannao merged commit edc84ea into v3.0 May 8, 2025
30 of 162 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant