search

garethdmm / gryphon

Powerful, proven, and extensible framework for building trading strategies at any frequency, with a focus on crypto currencies. Battle-tested with billions traded.
http://www.gryphonframework.org
Other
1.05k stars 150 forks source link

SQL Operational Error on datetime ? #44

Open asmodehn opened 5 years ago

asmodehn commented 5 years ago

I started trading on kraken recently and I got this (on first order passed I think...) : 

ERROR:gryphon.execution.live_runner:[OperationalError] (_mysql_exceptions.OperationalError) (1292, "Incorrect datetime value: '2019-07-04 15:48:46+00:00' for column `gryphon`.`trade`.`time_created` at row 1")
Traceback (most recent call last):
  File "/opt/Projects/gryphon/gryphon/execution/live_runner.py", line 281, in live_run
    harness.tick()
  File "harness.pyx", line 157, in gryphon.execution.harness.harness.Harness.tick (/home/alexv/.pyxbld/temp.linux-x86_64-2.7/pyrex/gryphon/execution/harness/harness.c:3155)
  File "harness.pyx", line 193, in gryphon.execution.harness.harness.Harness.consolidate_ledgers (/home/alexv/.pyxbld/temp.linux-x86_64-2.7/pyrex/gryphon/execution/harness/harness.c:3879)
  File "exchange_coordinator.pyx", line 77, in gryphon.execution.harness.exchange_coordinator.ExchangeCoordinator.consolidate_ledger (/home/alexv/.pyxbld/temp.linux-x86_64-2.7/pyrex/gryphon/execution/harness/exchange_coordinator.c:1941)
  File "exchange_coordinator.pyx", line 133, in gryphon.execution.harness.exchange_coordinator.ExchangeCoordinator._run_accounting (/home/alexv/.pyxbld/temp.linux-x86_64-2.7/pyrex/gryphon/execution/harness/exchange_coordinator.c:2754)
  File "exchange_coordinator.pyx", line 361, in gryphon.execution.harness.exchange_coordinator.ExchangeCoordinator._save_order (/home/alexv/.pyxbld/temp.linux-x86_64-2.7/pyrex/gryphon/execution/harness/exchange_coordinator.c:6591)
  File "/opt/Projects/gryphon/gryphon/lib/session.py", line 74, in commit_mysql_session
    raise e
OperationalError: (_mysql_exceptions.OperationalError) (1292, "Incorrect datetime value: '2019-07-04 15:48:46+00:00' for column `gryphon`.`trade`.`time_created` at row 1") [SQL: u'INSERT INTO trade (trade_type, unique_id, exchange_trade_id, time_created, fee, fee_currency, price, price_currency, volume, volume_currency, meta_data, fee_buyback_transaction_id, order_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: ('BID', '--snip--', '--snip--', datetime.datetime(2019, 7, 4, 15, 48, 46, tzinfo=<UTC>), Decimal('0.0843'), 'EUR', Decimal('52.6820'), 'EUR', Decimal('0.0050000000'), 'BTC', '{}', None, 219L)] (Background on this error at: http://sqlalche.me/e/e3q8)
INFO:gryphon.execution.harness.harness:[DynamicMarketMaking] (04/07/19 15:49:01 UTC) Winding Down

Any hint ?

asmodehn commented 5 years ago

My server timezone is CEST, maybe that is related somehow ?

garethdmm commented 5 years ago

Hmm, this same message appears as a warning on other installs of gryphon, but not an exception. My guess is that different database drivers have different behaviour. Do you know what mysql version you are on?

The issue has to do with details about how mysql stores and retrieves datetime values. I believe the string '2019-07-04 15:48:46+00:00' should actually be going into the database without the timezone information in it (the part after the plus sign). Gryphon operates only in UTC so we don't actually need to be storing timezone data anyway. You can see someone with a similar issue here: https://dba.stackexchange.com/questions/48704/mysql-5-6-datetime-incorrect-datetime-value-2013-08-25t1700000000-with-er

garethdmm commented 5 years ago

Here's an idea to test. Trades are created in the accounting system here: https://github.com/garethdmm/gryphon/blob/8963d079e432b886d571efc085181a8ce3b0b39f/gryphon/lib/models/order.pyx#L93-L104

I think that last line is causing the issue because it's creating a timezone-aware datetime. Try replacing it with this:

new_trade.time_created = epoch(trade['time']).datetime.replace(tzinfo=None)

There's probably a more elegant way to do this but this should explicitly strip the timezone information from the datetime object.

asmodehn commented 5 years ago 
alexv@pop-os:~$ mysql --version
mysql  Ver 15.1 Distrib 10.3.13-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
alexv@pop-os:~$ mariadb --version
mariadb  Ver 15.1 Distrib 10.3.13-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

That change seems to have fixed the issue as I could do a trade, and i believe it was registered as expected...

This probably needs a more thorough investigation though, as my dashboard is not super clean, and I am not yet familiar enough with gryphon to be able to spot other places where things don't work as expected...

matteosantama commented 5 years ago

I had the same issue.

matteo@matteo-pi:~$ mysql --version mysql Ver 14.14 Distrib 5.7.26, for Linux (armv7l) using EditLine wrapper

Gareth's change seems to have fixed the issue