Request for Transaction Support in manticoresearch-php

[rostislav-pn]

Hello,

I'd like to inquire if there are any plans to add transaction support to the manticoresearch-php library in the future, considering that transactions are supported according to the official ManticoreSearch documentation (https://manual.manticoresearch.com/Data_creation_and_modification/Transactions#Transactions). Having this functionality would significantly enhance the capabilities of the library and provide a more convenient way to work with commands like INSERT, REPLACE, and DELETE.

Thank you for the information.

[sanikolaev]

In theory it's possible as it's supported in the JSON interface. It's just that the BEGIN, ROLLBACK and COMMIT commands have to be executed via the /sql endpoint (https://github.com/manticoresoftware/manticoresearch-php/blob/master/docs/sql.md).

BEGIN + JSON insert + COMMIT:

➜  ~ mysql -P9306 -h0 -e "drop table if exists t; create table t;"; curl -X POST -v 'http://localhost:9308/sql?mode=raw' -d 'query=begin' --next 'http://localhost:9308/insert'  -d '{"index":"t", "id":3}' --next 'http://localhost:9308/sql?mode=raw' -d 'query=commit'; echo; mysql -P9306 -h0 -e "select * from t"
Note: Unnecessary use of -X or --request, POST is already inferred.
*   Trying 127.0.0.1:9308...
* Connected to localhost (127.0.0.1) port 9308 (#0)
> POST /sql?mode=raw HTTP/1.1
> Host: localhost:9308
> User-Agent: curl/7.85.0
> Accept: */*
> Content-Length: 11
> Content-Type: application/x-www-form-urlencoded
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: 6.2.0 45680f95d@230804 (columnar 2.2.0 dc33868@230804) (secondary 2.2.0 dc33868@230804)
< Content-Type: application/json; charset=UTF-8
< Content-Length: 37
<
* Connection #0 to host localhost left intact
[{"total":0,"error":"","warning":""}]* Found bundle for host: 0x600000638900 [serially]
* Can not multiplex, even if we wanted to
* Re-using existing connection #0 with host localhost
* Connected to localhost (127.0.0.1) port 9308 (#0)
> POST /insert HTTP/1.1
> Host: localhost:9308
> User-Agent: curl/7.85.0
> Accept: */*
> Content-Length: 21
> Content-Type: application/x-www-form-urlencoded
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: 6.2.0 45680f95d@230804 (columnar 2.2.0 dc33868@230804) (secondary 2.2.0 dc33868@230804)
< Content-Type: application/json; charset=UTF-8
< Content-Length: 69
<
* Connection #0 to host localhost left intact
{"_index":"t","_id":3,"created":true,"result":"created","status":201}* Found bundle for host: 0x600000638900 [serially]
* Can not multiplex, even if we wanted to
* Re-using existing connection #0 with host localhost
* Connected to localhost (127.0.0.1) port 9308 (#0)
> POST /sql?mode=raw HTTP/1.1
> Host: localhost:9308
> User-Agent: curl/7.85.0
> Accept: */*
> Content-Length: 12
> Content-Type: application/x-www-form-urlencoded
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: 6.2.0 45680f95d@230804 (columnar 2.2.0 dc33868@230804) (secondary 2.2.0 dc33868@230804)
< Content-Type: application/json; charset=UTF-8
< Content-Length: 37
<
* Connection #0 to host localhost left intact
[{"total":0,"error":"","warning":""}]
+------+
| id   |
+------+
|    3 |
+------+

BEGIN + JSON insert + ROLLBACK:

➜  ~ mysql -P9306 -h0 -e "drop table if exists t; create table t;"; curl -X POST -v 'http://localhost:9308/sql?mode=raw' -d 'query=begin' --next 'http://localhost:9308/insert'  -d '{"index":"t", "id":3}' --next 'http://localhost:9308/sql?mode=raw' -d 'query=rollback'; echo; mysql -P9306 -h0 -e "select * from t"
Note: Unnecessary use of -X or --request, POST is already inferred.
*   Trying 127.0.0.1:9308...
* Connected to localhost (127.0.0.1) port 9308 (#0)
> POST /sql?mode=raw HTTP/1.1
> Host: localhost:9308
> User-Agent: curl/7.85.0
> Accept: */*
> Content-Length: 11
> Content-Type: application/x-www-form-urlencoded
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: 6.2.0 45680f95d@230804 (columnar 2.2.0 dc33868@230804) (secondary 2.2.0 dc33868@230804)
< Content-Type: application/json; charset=UTF-8
< Content-Length: 37
<
* Connection #0 to host localhost left intact
[{"total":0,"error":"","warning":""}]* Found bundle for host: 0x600001fe4990 [serially]
* Can not multiplex, even if we wanted to
* Re-using existing connection #0 with host localhost
* Connected to localhost (127.0.0.1) port 9308 (#0)
> POST /insert HTTP/1.1
> Host: localhost:9308
> User-Agent: curl/7.85.0
> Accept: */*
> Content-Length: 21
> Content-Type: application/x-www-form-urlencoded
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: 6.2.0 45680f95d@230804 (columnar 2.2.0 dc33868@230804) (secondary 2.2.0 dc33868@230804)
< Content-Type: application/json; charset=UTF-8
< Content-Length: 69
<
* Connection #0 to host localhost left intact
{"_index":"t","_id":3,"created":true,"result":"created","status":201}* Found bundle for host: 0x600001fe4990 [serially]
* Can not multiplex, even if we wanted to
* Re-using existing connection #0 with host localhost
* Connected to localhost (127.0.0.1) port 9308 (#0)
> POST /sql?mode=raw HTTP/1.1
> Host: localhost:9308
> User-Agent: curl/7.85.0
> Accept: */*
> Content-Length: 14
> Content-Type: application/x-www-form-urlencoded
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Server: 6.2.0 45680f95d@230804 (columnar 2.2.0 dc33868@230804) (secondary 2.2.0 dc33868@230804)
< Content-Type: application/json; charset=UTF-8
< Content-Length: 37
<
* Connection #0 to host localhost left intact
[{"total":0,"error":"","warning":""}]

@rostislav-pn can you give it a shot? If it works, then we probably just need to add dedicated functions instead of sql('BEGIN', true), sql('COMMIT', true) and sql('ROLLBACK', true).

[hatemjaber]

@sanikolaev can you do that with the mysql client? if so, how do you do that? I tried this but it didn't work:

begin; insert into products (id) values (500); insert into orders (id, order__id) values (100,500);commit;rollback;

order__id is really order_id with a single underscore. I was forcing the second query to fail to see if the transaction will rollback or not. It this case the products did have an item with the id of 500 when I queried after running that block;

when I ran this:

begin; insert into products (id) values (500); insert into orders (id, order__id) values (100,500);rollback;

it didn't insert the 600 into the products table. when I tried to run it again, this time fixing it and removing the extra underscore, it gave a message: current txn is working with another table ('products')

What is the proper syntax for running a transaction and failing all queries in the event something goes wrong?

[hatemjaber]

just for extra measure I tried to run a transaction that worked on the same index rather than two separate indexes which did not work either.

[sanikolaev]

What is the proper syntax for running a transaction and failing all queries in the event something goes wrong?

If by "all queries" you mean queries to multiple tables - it won't work. The transactions don't support multiple tables. Here's a basic rollback example:

mysql> drop table if exists t; create table t(f text); begin; insert into t values(1, 'abc'); rollback; select * from t;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(f text)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
begin
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(1, 'abc')
--------------

Query OK, 1 row affected (0.01 sec)

--------------
rollback
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
select * from t
--------------

Empty set (0.00 sec)
--- 0 out of 0 results in 1ms ---