search

pBlueG / SA-MP-MySQL

MySQL plugin for San Andreas Multiplayer
BSD 3-Clause "New" or "Revised" License
196 stars 80 forks source link

Kill query support #165

Closed Misiur closed 7 years ago

Misiur commented 7 years ago

Hi! I've looked at your login implementation and noticed the race condition checks. I would prefer not having to do that for all player queries. Quick googling tells me there exists KILL QUERY. Would you consider adding support for it? I think mysql_*query functions would have to return query id for that.

maddinat0r commented 7 years ago

You can get the query ID by executing SHOW processlist. A better solution would be setting a query timeout hint (MySQL 5.7+ only):

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ....
Misiur commented 7 years ago

Hm, but that would kill legitimate heavy queries (though 1 second is a massive amount of time even when dealing with full server of players). However I'm using MariaDB so I have to send additional SET STATEMENT max_statement_time=1000.

I'll report with my findings in future.

maddinat0r commented 7 years ago

That timeout hint is per-query only. On MariaDB 10.1.2+ it would look like this (also per-query only; timeout is in seconds):

SET STATEMENT max_statement_time=1 FOR SELECT field1 FROM table_name ORDER BY field1
Misiur commented 7 years ago

Oh, I didn't notice the FOR and assumed there was a semicolon. That certainly makes things easier