SQL Operational Error on datetime ?
Opened this issue · 5 comments
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 ?
My server timezone is CEST, maybe that is related somehow ?
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
Here's an idea to test. Trades are created in the accounting system here:
gryphon/gryphon/lib/models/order.pyx
Lines 93 to 104 in 8963d07
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.
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...
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