Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
OK_Packet and EOF_Packet have 2 bytes indicating server status.
This flag indicates different things like if a transaction is currently active.
Problem is ERR_Packet doesn't have this information.
Query that result in ERR_Packet can create a transaction, but driver cannot know if there is really a transaction in progress or not.
example :
create table tx_prim_key (id int not null primary key) engine=innodb; |
create table tx_fore_key (id int not null primary key, id_ref int not null, foreign key (id_ref) references tx_prim_key(id) on delete restrict on update restrict) engine=innodb; |
insert into tx_prim_key(id) values(32); |
insert into tx_fore_key(id, id_ref) values(42, 32); => server status :2 |
set autocommit=0; => server status :0 |
DO 1 => server status :0 |
delete from tx_prim_key where id = 32 => Error due to foreign key constraint - server status :0 |
DO 1 => server status :1 |
ROLLBACK => server status :0 |
in this example, delete result in an ERR_packet. At this time there is some locks :
SELECT * FROM information_schema.INNODB_TRX \G
|
*************************** 1. row *************************** |
trx_id: 9472648 |
trx_state: RUNNING
|
trx_started: 2018-02-16 08:57:03 |
trx_requested_lock_id: NULL
|
trx_wait_started: NULL
|
trx_weight: 4 |
trx_mysql_thread_id: 16 |
trx_query: NULL
|
trx_operation_state: NULL
|
trx_tables_in_use: 0 |
trx_tables_locked: 2 |
trx_lock_structs: 4 |
trx_lock_memory_bytes: 1136 |
trx_rows_locked: 2 |
trx_rows_modified: 0 |
trx_concurrency_tickets: 0 |
trx_isolation_level: REPEATABLE READ
|
trx_unique_checks: 1 |
trx_foreign_key_checks: 1 |
trx_last_foreign_key_error: `testj`.`tx_fore_key`, CONSTRAINT `tx_fore_key_ibfk_1` FOREIGN KEY (`id_ref`) REFERENCES `tx_prim_key` (`id`)
|
trx_adaptive_hash_latched: 0 |
trx_is_read_only: 0 |
trx_autocommit_non_locking: 0 |
Having server status in ERR_Packet would permit to really rely on that information.
Underlying problem that this would solve :
in java work, there is different framework layer, one not always knowing other state, causing multiple call of JDBC connection.rollback().
Java driver actually relies on the flag SERVER_STATUS_IN_TRANS of server status to know if there is really a transaction to know if COMMIT/ROLLBACK command must really be sent to the server (to avoid a useless round-trip)