Weird Vanishing Orders And Database Errors

[anon84]

On sim testing I'm getting errors where orders are vanishing or getting stuck in orange initialize forever after reconnects, then stuck on cancel pending if I try to cancel.
I've hacked together an example based on a chart trader button addon.
Upon pressing the button 5 limit orders are submitted on the sim account.
Make sure you are connected and orders are higher than your current AMZN price so they don't execute. ($104 to $108)
All 5 orders display as working in the orders list, but order1 does NOT display in chart trader.
Now after disconnecting and reconnecting to Sim101, order5 and order1 will vanish from the order list, order5 from chart trader too.

Basically that's it. Why does manual creation of order5 fail to display in chart trader? why does only order1 vanish after reconnect?

This isn't the same error as in my real code, but they both give the same error in the NT log file, so I'm hoping your solution will help. My real code is long and I was unable to reproduce that same error. It places 1-4 orders using createorder(), which work fine until reconnect. Then they all vanish except for one in the order list which is stuck on orange initialize.
If I now run my test code it will add the orders as stated earlier, but now on reconnect ALL 5 will vanish except that orange one that is stuck on initialize.
So I'm thinking that stuck order blocks up your database somehow. You can repeat the test by resetting the Sim account, it's the only way to escape.

Log complains about the 'Kind' of time being wrong, that's why in my test file I have prints of that value.
From the debug prints it seems the Gtd time gets reset to 2099 after reconnect, and the 'Kind' becomes unspecified. Maybe that's causing a problem.

------------------------------------------------------------------------------------------------------------------------
Log Error:

2023-02-10 13:48:09:493|3|524288|Error on executing DB command: System.ArgumentException: The conversion could not be completed because the supplied DateTime did not have the Kind property set correctly. For example, when the Kind property is DateTimeKind.Local, the source time zone must be TimeZoneInfo.Local.
Parameter name: sourceTimeZone
at System.TimeZoneInfo.ConvertTime(DateTime dateTime, TimeZoneInfo sourceTimeZone, TimeZoneInfo destinationTimeZone, TimeZoneInfoOptions flags, CachedData cachedData)
at NinjaTrader.Cbi.Order.DbAdd()
at NinjaTrader.Cbi.DB.DBThread()

------------------------------------------------------------------------------------------------------------------------
Trace file has the same error, but also an SQLite error:

2023-02-10 13:31:34:504 (Db) Cbi.DB.DBThread.Exception: error=-1 [orderId='04b667aa3b114bae8c83fec0de4c9606' account='Sim101' name='name1' orderState=Working instrument='AMZN' orderAction=Sell orderType='Limit' limitPrice=101 stopPrice=0 quantity=100 tif=Gtd oco='' filled=0 averageFillPrice=0 onBehalfOf='' id=66 time='2023-02-10 13:31:24' gtd='2023-02-12' statementDate='2023-02-10', Update]
System.ArgumentException: The conversion could not be completed because the supplied DateTime did not have the Kind property set correctly. For example, when the Kind property is DateTimeKind.Local, the source time zone must be TimeZoneInfo.Local.
Parameter name: sourceTimeZone
at System.TimeZoneInfo.ConvertTime(DateTime dateTime, TimeZoneInfo sourceTimeZone, TimeZoneInfo destinationTimeZone, TimeZoneInfoOptions flags, CachedData cachedData)
at NinjaTrader.Cbi.Order.DbUpdate()
at NinjaTrader.Cbi.DB.DBThread()
2023-02-10 13:31:34:505 ERROR: Error on executing DB command: System.ArgumentException: The conversion could not be completed because the supplied DateTime did not have the Kind property set correctly. For example, when the Kind property is DateTimeKind.Local, the source time zone must be TimeZoneInfo.Local. Parameter name: sourceTimeZone at System.TimeZoneInfo.ConvertTime(DateTime dateTime, TimeZoneInfo sourceTimeZone, TimeZoneInfo destinationTimeZone, TimeZoneInfoOptions flags, CachedData cachedData) at NinjaTrader.Cbi.Order.DbUpdate() at NinjaTrader.Cbi.DB.DBThread()
2023-02-10 13:31:44:506 SQLite error (787): abort at 35 in [INSERT INTO OrderUpdates([Order],Nr,AvgFillPrice,Comment,[Error],Filled,LimitPrice,OrderId,OrderState,Quantity,Ser verName,StatementDate,StopPrice,Time) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)]: FO
2023-02-10 13:31:44:507 Cbi.Order.DbAddOrderUpdate.Failed.!PF!: id=638116326846563602 orderId='' orderId='' account='Sim101' name='name5' orderState=Submitted instrument='AMZN' orderAction=Sell limitPrice=105 stopPrice=0 quantity=100 orderType='Limit' filled=0 averageFillPrice=0 time='2023-02-10 13:31:24' statementDate='2023-02-10' error=NoError comment='' nr=0
i=0 orderId='' account='Sim101' name='name5' orderState=Submitted instrument='AMZN' orderAction=Sell limitPrice=105 stopPrice=0 quantity=100 orderType='Limit' filled=0 averageFillPrice=0 time='2023-02-10 13:31:24' statementDate='2023-02-10' error=NoError comment='' nr=0
i=1 orderId='' account='Sim101' name='name5' orderState=Accepted instrument='AMZN' orderAction=Sell limitPrice=105 stopPrice=0 quantity=100 orderType='Limit' filled=0 averageFillPrice=0 time='2023-02-10 13:31:24' statementDate='2023-02-10' error=NoError comment='' nr=-1
i=2 orderId='' account='Sim101' name='name5' orderState=Working instrument='AMZN' orderAction=Sell limitPrice=105 stopPrice=0 quantity=100 orderType='Limit' filled=0 averageFillPrice=0 time='2023-02-10 13:31:24' statementDate='2023-02-10' error=NoError comment='' nr=-1

code = Constraint (19), message = System.Data.SQLite.SQLiteException (0x800027AF): constraint failed
FOREIGN KEY constraint failed
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCo mmand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(Com mandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(C ommandBehavior behavior)
at NinjaTrader.Cbi.Order.DbAddOrderUpdate(OrderEventA rgs e)
**********************
at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
at System.Environment.get_StackTrace()
at NinjaTrader.Cbi.Order.DbAddOrderUpdate(OrderEventA rgs e)
at NinjaTrader.Cbi.Order.DbUpdate()
at NinjaTrader.Cbi.DB.DBThread()
at System.Threading.ExecutionContext.RunInternal(Exec utionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

------------------------------------------------------------------------------------------------------------------------

In the SQLite exception there is '[Error]' for one of the parameters:
2023-02-10 13:31:44:506 SQLite error (787): abort at 35 in [INSERT INTO OrderUpdates([Order],Nr,AvgFillPrice,Comment,[Error],Filled,LimitPrice

This is possibly for order5 which is my manual submit attempt (see code), could be I've not set a value which is required.
Since I use createorder() in my real code I'd like to fix the issues with that method as priority. What's going on with that date Kind error and how to fix it.

Oh I almost forgot, If I place an order normally by right clicking the chart and selecting sell limit, then that order will persist between reconnects.
So it's just the orders placed via code which have the problem.

Edit: It got worse! In my manual submit I just realised I was setting the Order.Id instead of Order.OrderId. After switching to OrderId it's now completely broken everything. Now after reconnect EVERY order is gone, even ones I placed with right click. The SQLite error seems to be gone now, it's just that date 'Kind' error flooding the log and trace files.
I've reset the Sim account several times now and the vanishing behavior is consistent. In my real code I use the Gtd value and my own OrderIds, basically the 2 parameters which are seeming to cause problems.

Attached code is the completely broken version.

Notes: code might be buggy, I moved some things around and haven't made a safe implementation regarding the button.
If the button doesn't appear then try a recompile while a chart trader window is open.

Version 8.0.27.1

[NinjaTrader_Jesse]

Hello anon84,

I would suggest doing a reinstall and then re testing this. If you are getting database errors that generally means there is some kind of logical error, problem with the use case or a corruption happening with the database. That could also indicate other platform files are bad. After doing a re installation of the platform remove the database file from the folder: Documents\NinjaTrader 8\db. The database file should be named NinjaTrader.sqlite

After doing that start the platform and retest the situation to see if you are still getting database errors.

You mentioned this is also not the same error as your real code so this may not actually be a helpful observation with this new test. I would suggest testing your normal code to make sure that does not still have the problem. If it does you will need to reduce that code and make a sample that reproduces the actual error you are seeing so we can address that. There are situations where orders can be stuck in states like initialize or become invalid based on the logic being used.

[anon84]

Hi Jesse thanks for your attention,
I did a full reinstall of 8.0.27.1, mydocs and install folder completely removed.
1 clean boot up and exit. Then imported my addon and compiled in 2nd boot.
Had to change the code from .AddDays(2) -> (5) because order1 was auto cancelling. Probably because +2 would land on a weekend.
I'm seeing the initial behavior again of all displaying fine in the order list, but order5 not in chart trader.
Then on reconnect orders 1 & 5 vanish again.

SQLite constraint error is back and also the persistent date 'Kind' error.

I'd been developing my addon using NT v24 which used the old database technology. I remember seeing v27 changed the db technology so I was hoping it would fix these errors, but it seems the errors remain. Fair comment about it not being my real code, but I just want to confirm the absolute raw functionality of sending orders is working flawlessly first, then I can look to my more complex code for errors. When even a cut down basic version is having buggy behavior then it's a serious problem.

I can send the log and trace from the fresh attempt if you like.
Are you really not experiencing this on your end?

************************************************** Solution found ************************************************
● Short version:

To avoid database errors which cause stuck or disappearing orders, you must use 'DateTimeOffset.Utc/Now.AddDays().DateTime' in Account.CreateOrder(), NOT DateTime.Utc/Now.AddDays().

● Long version regarding the 2 orders which displayed unexpected behavior:

<= Order5 =>
This is the manual order created with new Order(), manually setting 17 attributes, followed by Account.Submit. The order will appear in the order list, but will NOT display in chart trader. On reconnect the order will vanish without warning. In VS debug I checked my manual order had the same attributes as an order created via CreateOrder(), I even Invoked hidden NT method SetUniqueId() to make sure I had an 'official' I'd, so I assume something else is going on under the hood during CreateOrder() which properly registers the order in the database.

Interesting to know, but not really a problem since CreateOrder() is how you're supposed to do it officially. Could potentially waste hours of a coder's time debugging if they are not aware you MUST use CreateOrder() for the Order object, since Submit simply says it accepts an Order object as input.

<= Order1 =>
For expiry all orders either use my DateTime.Now.AddDays() or Globals.MaxDate, but Order1 is the only one with TimeInForce == Gtd. Upon reconnect if the order is NOT TiF Gtd then the stored expiry date gets set to MaxDate. This indicates there is some date processing going on upon reconnect.

Using the database error as a clue, I assume that If the order IS TiF Gtd then it tries to convert the date to local timezone to see if the order is past expiry. If this fails then the expiry date gets set to MaxDate as a fallback, but by then the damage is done and the order won't show up in the order list. It's just gone forever without notifying the user. Note this can also 'block the pipe' as it were, and even cause other completely valid orders to not appear anymore.

Now for the cause of the problem. The database error mentions conversion, and the 'Kind' property of the DateTime being wrong. Here's where I assumed it needed to be set to something, either Local or UTC, but actually the Kind MUST be 'Unspecified'. The most convenient way of setting a time offset is simply DateTime.Utc/Now.AddDays() etc. The problem is that this sets the time 'Kind' to either Local or UTC, which causes a conversion error in Order.DbAdd() and leads to broken orders.

To get a 'Kind' of 'Unspecified' you can use:

DateTimeOffset.Now.AddDays().DateTime

Now the order will not vanish or block other orders upon reconnect.

This seems like a pretty dangerous error to be allowing. If someone happens to have orders stored locally due to paranoia about broker spying or something, then having orders vanish without warning on next reconnect could cause big problems. It could be a good or a bad thing that their order didn't execute, but really the issue shouldn't exist.

In CreateOrder() there should be a check if TimeInForce == Gtd && Kind != Unspecified -> remake the time with new DateTime(t.Year, t.Day.. etc which will result in a 'Kind' of 'Unspecified'.

During further testing I even stumbled upon a way to consistently recreate the frozen orange order issue from my real code. All I did was set Order2 as the TimeInForce.Gtd one, instead of Order1 which I set to TiF Day. For some reason having the bugged one occur 2nd makes ALL the orders vanish on reconnect, except leaving Order1 as stuck on orange 'Submitted'.

My real code is now working perfectly after the change to 'Unspecified' DateTimes. I wonder if there are other areas of NT that are susceptible to this error due to requiring that a specific kind of DateTime be fed in. Might be worth looking into.

[NinjaTrader_Jesse]

Hello anon84,

From the details it sounds like you may be generating an invalid DateTime. If you have use case with a valid DateTime and are seeing a problem you can provide a small sample that only includes the problematic code and steps to see the problem. We can review it and If a bug report if needed we can submit it based on that sample. A small sample would mean to exclude any GUI elements like buttons and make it specific to show an issue. That could be similar in code to the SampleMACrossover where you use specific code in OnBarUpdate to do an action one time to display an issue.

If your normal code is otherwise now working I will mark the post as resolved.