Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries. So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page? Refs: https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht... https://mariadb.com/kb/en/com_query/ Thanks! Eli
Hi, Eli, It's incompatibility. MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text. On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs: https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht... https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Sergei thanks for the information! You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL. Thanks, Eli Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,
It's incompatibility.
MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text.
On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs:
https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht...
https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hey Sergei et al., I have been trying to find information on how to do pipelining, I guess I am not looking in the right place or using the right terms, the thing closest to it that I found was compressed packets that can contain multiple packets. Any hints would be appreciated. Thanks! Eli Op do 23 jan 2025 om 20:42 schreef E.S. Rosenberg < es.rosenberg+lists.mariadb.org@gmail.com>:
Sergei thanks for the information!
You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL.
Thanks, Eli
Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,
It's incompatibility.
MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text.
On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs:
https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht...
https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hi, Eli, do you mean, use pipelining in python via PyMySQL? I don't know if that's possible, better ask on PyMySQL list? Or do you mean how to use it on the protocol level? Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org On Jan 26, E.S. Rosenberg wrote:
Hey Sergei et al., I have been trying to find information on how to do pipelining, I guess I am not looking in the right place or using the right terms, the thing closest to it that I found was compressed packets that can contain multiple packets. Any hints would be appreciated. Thanks! Eli
Op do 23 jan 2025 om 20:42 schreef E.S. Rosenberg < es.rosenberg+lists.mariadb.org@gmail.com>:
Sergei thanks for the information!
You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL.
Thanks, Eli
Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,
It's incompatibility.
MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text.
On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs:
https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht...
https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hey Sergei, I mean on the protocol level so that I can see about writing a PoC and maybe creating a PR for pymysql eventually. Thanks, Eli Op ma 27 jan 2025 om 13:28 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,
do you mean, use pipelining in python via PyMySQL? I don't know if that's possible, better ask on PyMySQL list?
Or do you mean how to use it on the protocol level?
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
On Jan 26, E.S. Rosenberg wrote:
Hey Sergei et al., I have been trying to find information on how to do pipelining, I guess I am not looking in the right place or using the right terms, the thing closest to it that I found was compressed packets that can contain multiple packets. Any hints would be appreciated. Thanks! Eli
Op do 23 jan 2025 om 20:42 schreef E.S. Rosenberg < es.rosenberg+lists.mariadb.org@gmail.com>:
Sergei thanks for the information!
You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL.
Thanks, Eli
Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org :
Hi, Eli,
It's incompatibility.
MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text.
On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs:
https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht...
https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
this is documented in https://mariadb.com/kb/en/mariadb-protocol-differences-with-mysql/#pipelinin... to simplify if capability is supported by server, you can send COM_STMT_PREPARE then a COM_STMT_EXECUTE with statement ID -1
Hi, PyMySQL doesn't support MariaDB's protocol extension for bulk operations, this is afaik only supported by MariaDB Connector/Python when using cursors executemany() method. If you have only INSERT statements PyMySQL will transform executemany to multi value statement which also reduces roundtrips: cursor.executemany("INSERT INTO t1 VALUES (%s)", [("foo",),("bar,),("john",),("doe",)]) will be sent as INSERT INTO t1 VALUES ("foo"),("bar"),("john"),("doe") This approach is a little bit slower than using the binary protocol implementation but it is limited to SELECT statements and doesn't work with UPDATE, REPLACE or DELETE. /Georg On Sun, Jan 26, 2025 at 2:33 PM E.S. Rosenberg via developers < developers@lists.mariadb.org> wrote:
Hey Sergei et al., I have been trying to find information on how to do pipelining, I guess I am not looking in the right place or using the right terms, the thing closest to it that I found was compressed packets that can contain multiple packets. Any hints would be appreciated. Thanks! Eli
Op do 23 jan 2025 om 20:42 schreef E.S. Rosenberg < es.rosenberg+lists.mariadb.org@gmail.com>:
Sergei thanks for the information!
You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL.
Thanks, Eli
Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,
It's incompatibility.
MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text.
On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs:
https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht...
https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
_______________________________________________ developers mailing list -- developers@lists.mariadb.org To unsubscribe send an email to developers-leave@lists.mariadb.org
-- Georg Richter, Staff Software Engineer Client Connectivity MariaDB Corporation Ab
Hey all, The reason I am focusing on pymysql is that it is the library that underlies community.mysql.* in ansible. Based on an issue opened in 2013 with the pymysql project on github the maintainer has been refraining from implementing prepared statements because it adds another roundtrip. Instead the library relies on it doing all the escaping properly, given that this imho opens potential security issues the size of the titanic (that is only smaller because it should be that only me a "trusted party" runs my ansible and edits my variables) I would like to not pile on an 12 year old issue but actually show up with a constructive PR. Though I can imagine that even that will be rejected because it would need to add logic to check which MySQL/MariaDB server is running. The other solution would be parameterized queries but as stated previously by Sergei this is an incompatibility and essentially pipelining a prepared statement and a parameterized query do the same thing [except that the statement would stick around for whatever timeout you set]. Based on what I understand from the docs you referenced I would still need to send 2 packets, I would just not wait for a reply to the 1st before sending the next or is it possible to send both commands in the same packet? Hope that explains my goals more and thanks for all your input! Eli refs: https://github.com/PyMySQL/PyMySQL/issues/202 https://github.com/PyMySQL/PyMySQL/issues/202#issuecomment-243348826 Op ma 27 jan 2025 om 14:46 schreef Georg Richter <georg@mariadb.com>:
Hi,
PyMySQL doesn't support MariaDB's protocol extension for bulk operations, this is afaik only supported by MariaDB Connector/Python when using cursors executemany() method.
If you have only INSERT statements PyMySQL will transform executemany to multi value statement which also reduces roundtrips:
cursor.executemany("INSERT INTO t1 VALUES (%s)", [("foo",),("bar,),("john",),("doe",)]) will be sent as INSERT INTO t1 VALUES ("foo"),("bar"),("john"),("doe")
This approach is a little bit slower than using the binary protocol implementation but it is limited to SELECT statements and doesn't work with UPDATE, REPLACE or DELETE.
/Georg
On Sun, Jan 26, 2025 at 2:33 PM E.S. Rosenberg via developers < developers@lists.mariadb.org> wrote:
Hey Sergei et al., I have been trying to find information on how to do pipelining, I guess I am not looking in the right place or using the right terms, the thing closest to it that I found was compressed packets that can contain multiple packets. Any hints would be appreciated. Thanks! Eli
Op do 23 jan 2025 om 20:42 schreef E.S. Rosenberg < es.rosenberg+lists.mariadb.org@gmail.com>:
Sergei thanks for the information!
You also immediately hit the nail on the head as to the issue of the extra round trip, I will have a look at pipelining prepared statements, specifically in PyMySQL.
Thanks, Eli
Op do 23 jan 2025 om 19:08 schreef Sergei Golubchik <serg@mariadb.org>:
Hi, Eli,
It's incompatibility.
MariaDB only supports parameters in COM_STMT_PREPARE/COM_STMT_EXECUTE. You can pipeline them and avoid an extra round-trip, but it still be binary protocol, not text.
On Jan 23, E.S. Rosenberg via developers wrote:
Hi, Recently I found out about parameterized queries in MySQL, it seems to have been supported at least since 8.0.x but it is not listed as an incompatibility between mariadb and mysql yet the package spec for COM_QUERY on mariadb does not mention any support for parameterized queries.
So is this an incompatibility, some out of date docs or am I just looking at the wrong com_query page?
Refs:
https://dev.mysql.com/doc/dev/mysql-server/8.0.40/page_protocol_com_query.ht...
https://mariadb.com/kb/en/com_query/
Thanks! Eli
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
_______________________________________________ developers mailing list -- developers@lists.mariadb.org To unsubscribe send an email to developers-leave@lists.mariadb.org
-- Georg Richter, Staff Software Engineer Client Connectivity MariaDB Corporation Ab
Identifying a mariadb / mysql server recommendation is documented in the same document : https://mariadb.com/kb/en/mariadb-protocol-differences-with-mysql/#identifyi... and about being constructive, you can se another reason of using prepared statement with matadata skipping (https://mariadb.com/kb/en/mariadb-protocol-differences-with-mysql/#prepare-s...). This is interesting for second execution: metadata are then only send any when if they have changed.
participants (4)
-
diego.dupin＠mariadb.com -
E.S. Rosenberg -
Georg Richter -
Sergei Golubchik