qbcore-framework/qb-crypto

SQL Issue

milotiro95 opened this issue · 8 comments

milotiro95 commented

Hey everyone.

I got a lot of warning from the DB coming from QB-crypto while trying to update value. I'm using an API to have the real price of Bitcoin on my city and also add an update when ressources start into.

Every time it update I got this warning is this normal or need to be optimize in some way ? I have updated my oxmysql for the lastest version.
1

OMikkel commented

Insert this into your server.cfg set mysql_slow_query_warning 10000
This will ensure that only queries which exceed 10000ms will be printed in the console

milotiro95 commented

Not a bad idea but that's just delocalize the problem ^^". I put 800 and seems ok for now.

  • 👍1
unejamzezak commented

Yeah I'm receiving the same message even with the slow query warning at 10000
image

edit - have not changed anything in the config/client/server configs/luas
Every 10 mins the price stays hard fixed at a value of $1000 and everytime I do /setcryptoworth qbit $price it spams the log with the same mysql error that there's a duplicate key

milotiro95 commented

I don't have this type of error but that shoud the past update of updatecryptogrowth() 20 days ago

OMikkel commented

@unejamzezak your problem is not due to a warning, but an error. The above mentioned fix will therefore not work, since your issue is completely different.
The syntax used at Line 89 and Line 128 looks weird to me, maybe I am wrong. However I am not able to test it and create a pull request any time soon.

unejamzezak commented

@unejamzezak your problem is not due to a warning, but an error. The above mentioned fix will therefore not work, since your issue is completely different. The syntax used at Line 89 and Line 128 looks weird to me, maybe I am wrong. However I am not able to test it and create a pull request any time soon.

No worries thank you for clearing that up honestly thought it was a MySQL issue, redownloaded the folders/files and threw it in now it works perfectly fine with no error code

MatthewRorke commented

The original post is a valid issue and occurs due to the size of the VARCHAR being pulled in the query. I will run a query analyser and post feedback.

  • 👍1
MatthewRorke commented

Some notes for this thread:

  1. Adjusting your slow log will only hide the issue (mysql_slow_query_warning).
  2. The slow query log occurs due to the INSERT query failing on the second time of initialising the resource and therefore triggering the CONSTRAINT TRIGGER (EG: ON DUPLICATE UPDATE ...).
  3. UPDATE is more likely to be successful than INSERT, therefore separating into two separate queries is actually faster and reduces the run time to as much as 22ms.

Refer to the above mention for a means to have optimised queries.

  • 👍1