cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sahaya
Frequent Visitor

Non-negative number required error while appending tables

Hello,

 

I'm trying to append 2 large datasets (22M & 30M) of same table format in Power Bi using Append Queries option. But I'm recceiving the below error. Not sure what this error means. Please assist.

append error.PNG

18 REPLIES 18
CharlieBI
Frequent Visitor

Hi, having this issue too with MySQL, any fix?

boylec
Advocate I
Advocate I

NOTE:

I decompiled the assemblies that Power BI runs on and hooked up my debugger to drill down and find out whats going on and I was correct. The culprit is a bug in the Microsoft.MashupEngine.dll assembly, not something wrong with any data source. It will occur when the report is big enough.

 

Specifically:

The ReadPage and WritePage methods of the Microsoft.Mashup.EngineHost.Services.TransformingPagedStream class both use the following line (or equivalent because the decompiler may not be accurate as far as semantics)

 

this.inputStream.Seek((long) (page * this.inputPageSize), SeekOrigin.Begin);

 

'page' is a 32-bit integer here and so is 'this.inputPageSize'. So when the page number of the stream seek operation gets high enough, the page*this.inputPageSize multiplication operation eventually gets larger than the max 32-bit integer value of 2,147,483,647, which causes the operation to return a negative number due to the overflow.

 

That value is *then* converted to a long. What needs to happen is that the 'page' or 'this.inputPageSize' 32-bit integer needs to be a long in the first place so that the multiplication operation does not cause an overflow (or one of them needs to be cast at the time of the multiplication operation).

 

This negative number result is passed down through a few other functions until it hits the spot where we are seeing this exception in the stack-trace provided by the original poster.

 

Hope a dev can see this and patch it - seems like a pretty big bug.

 

Layman's terms:

This is a PBI Desktop bug, not a data source bug or something wrong with the way you built your report. The only thing you can really do until it gets patched is try and shrink your data source(s) so none of them hit this limitation.

Anonymous
Not applicable

Thanks everyone for your hard work in trying to figure out this problem. Based on this Power BI Administrator's recommendation, I submitted a support ticket to Power BI support and included the information from this post as well as a link to this post for reference. The support site is telling me that I should hear back from Power BI support within one day. I'll let everyone know what I hear.

Anonymous
Not applicable

Power BI support responded the same day that I submitted the support ticket (yesterday) and reached out to me to set up a screen sharing session to go through the issue we've been experiencing. The support person was very nice to work with. We captured some screenshots and gathered some log files, which will be passed on to the Power BI product development team for review.

Hello, we are having the same issue. Did you get to solve it?

 

B rgds,

I’m working with a pro support rep on it the last two days. He said the latest October release of PBI Desktop should have patched the bug. I’ve yet to try it. Are you using the latest Power BI Desktop?

I am using PoweryQuery plugin for Excel 2016

 

Anybody know how I can get this update?

 

Excel version 16.0.4738.1000 64 Bit

Anonymous
Not applicable

While the Power BI team was working on this fix, I modified my query to avoid loading such a large amount of data.

 

Recently, though, the Power BI support person I was working with followed up with me to let me know this problem has been fixed by the product team in the latest version of Power BI Desktop (December release).

 

I went back to replicate what I was originally trying to do, and I am no longer having the problem.

Any news if this will be pushed to PowerQuery for Excel as well?

Anonymous
Not applicable

I am having this same problem with my report. All I'm trying to do is load data into my report from SQL Server using a query. The query runs just fine in Management Studio, but Power BI gives me the error message.

 

Can we please get a fix for the mashup engine?

 

Here is my error message:

 

Unexpected error: Non-negative number required.
    Parameter name: value
    Details:
        Microsoft.Mashup.Evaluator.Interface.ErrorException: Non-negative number required.
    Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
    Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
    Parameter name: value
       at System.IO.FileStream.set_Position(Int64 value)
       at Microsoft.Mashup.EngineHost.Services.StreamSharer.MoveTo(SubStream subStream, Int64 position)
       at Microsoft.Mashup.EngineHost.Services.StreamSharer.ReadAt(SubStream subStream, Int64 position, Byte[] buffer, Int32 offset, Int32 count)
       at Microsoft.Mashup.EngineHost.Services.StreamSharer.SubStream.Read(Byte[] buffer, Int32 offset, Int32 count)
       at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
       at Microsoft.Mashup.EngineHost.Services.TransformingPagedStream.ReadPage(Int32 page, Byte[] buffer)
       at Microsoft.Mashup.EngineHost.Services.PagedStream.SeekToPage(Int32 page)
       at Microsoft.Mashup.EngineHost.Services.PagedStream.Write(Byte[] array, Int32 offset, Int32 count)
       at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(String key, TemporaryStorage storage)
       at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(Object obj)
       at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
       at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
       --- End of inner exception stack trace ---
       at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
       at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
       at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
       at Microsoft.Mashup.Evaluator.EvaluationHost.TryHandleException(Exception exception)
       at Microsoft.Mashup.Evaluator.SafeThread2.HandleException(Exception e)
       at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart(Object obj)
       --- End of inner exception stack trace ---
       at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
       at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
       at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
       at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
       at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
       at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

 

boylec
Advocate I
Advocate I

Looking at the stack trace it looks like this is an issue with the mashup engine. It looks like it uses a bunch of file streams under the cover to merge queries or something of that nature. In other words, its storing information in files on the hard disk while generating the preview and reading from those files while mashing everything up.

 

This exception appears to be occurring because the value being passed to "set_Position(Int64 value)" - referring to a spot in a filestream, is a negative number.

 

When an integer increases beyond its maximum value, it wraps back to a negative number. For example if the max value of an int was 10, and you tried to increment it up to 11, it would become -10 instead of 11.

 

The actual maximum value of a 64-bit integer (int64) is high.

 

 

 

 

 

 

18,446,744,073,709,551,615 to be exact.

 

This many bits would be, in gigabytes:

18,446,744,073,709,551,615/8/1000/1000/1000

=2,305,843,009.21 GB

 

Guaranteed none of the filestreams the mashup engine is using are large enough to cause that to happen.

 

However, if the value variable is being derived from any other 32-bit integers somewhere along the code path up to that point: The max value of an int32 is 2,147,483,647. In gigabytes thats:

2,147,483,647/8/1000/1000/1000.

= roughly .268 GB or 268 MB.

 

If any of the file stream position calculations are being stored in a 32 bit integer in the PBI code, and the size of that filestream is exceeding 268 MB, there is a good chance it is causing that 'value' integer shown in the stack trace to wrap around to -2,147,483,647. Then by the time it hits that line of code an exception gets thrown, because code-based file reader can't find a negative position in a file.

 

I would experiment with getting rid of columns that you don't need in any of the base data sources being used by the mash up engine in order to try and shrink the size of any file streams. Perhaps it won't reach the 2,147,483,647 bits size and trigger this bug....

 

I know its a bit of a shot in the dark, but that is my best educated guess of what is occurring.

 

Anonymous
Not applicable

1. One of the ways we found around this bug (it's not an issue) was by changing the order of the Appended Queries, for eg:

 

If you're appending Table 2, 3 and 4 to Table 1 (one being the main) - change the order to append Table 1, 3 and 4 to Table 2. 

 

That solved our issue in one of our reports - we are appending more than 100m rows between 4 different datasets. This is not consistent - sometimes it still gives the error but eventually, we were able to make it through. 

 

2. If the error happens around the same row count - for eg: the error always happens around 19m rows - and you know that on 19m rows your dataset is changing from Table 1 to Table 2, then there might be something wrong a part of your Table 2. This is how we bypassed this bug on another case. In theory, nothing should be wrong with the dataset - we are 100% sure of that - but somehow, somewhere, Power BI is interpreting the data the wrong way. Once we deleted the first millions on Table 2 the query ran as expected.

 

This shouldn't be happening and it appears to be happening a lot - only on large datasets apparently. 

 

3. On another thread this came up:

 

"Realizing this problem is bigger than me, I decided to try and get around it by creating the final form of the data table on the SQL Server side, thus removing the heavy workload (Which was mostly merged operations) from the mashup engine. This resulted in a successful report refresh."

 

We tried this but PowerBI couldn't handle the 200m rows dataset.

 

Hope this helps.

 

I too am running into this with only 2 million rows total from two seperate databases.

 

Unfortunately, swapping the order of the imports did not help me. 

 

 

Anonymous
Not applicable

I'm having this issue, too, and *in Power Query within Excel*.  The query worked just fine on as recently as July 24 - now it won't ever refresh correctly, and I get the same error messages shown in other posts above. 

 

Interestingly, it seems to have refreshed the data in the query editor (I can tell because a new season's data is now appearing in the preview), so it seems to be crashing as it tries to load the results - I've never seen behavior like this before!  I've rebooted, pulled down the latest Power BI update (August ?14?), looked for Excel updates (I have Excel 2016 on Windows 7 and found none pending), rebooted again, etc. etc... 

 

This query does, as others have described, involve merged queries.  I have another merged query to the same large table in this same workbook (again, this is primarily an Excel issue for me - I'm posting here because it's identical error language and that seems relevant!), so there are two merged queries pulling different subsets of the same table - the first merge does still run, with about 3m records post-join (in terms of how many rows it says it's loading as it refreshes), with 1,300 rows in the final output.  The merged query that won't run is about 9m records post-join, with about 12,000 rows in the final output to the workbook back when it would successfully run a few weeks ago!

 

 

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Sahaya,

 

Could you please show the Details message?

 

In addition, Please check if there is any negative number which is not expected in your value.

 

If you still need help, please share the dummy table structure of your tables, so that I could have test in my environment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I am getting the same error.

 

I am running the exact same structure datasets (created duplicate and changed the date range in one of the datasets). When I refresh each of the datasets seperately, it refreshes with no issues, but when I am appending it, thats when I am coming across this issue. This is the first time I came across this. Does this have anything to do with the latest Power BI Desktop update?

 

Unexpected error: Non-negative number required.
Parameter name: value
Details:
Microsoft.Mashup.Evaluator.Interface.ErrorException: Non-negative number required.
Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
Parameter name: value ---> System.ArgumentOutOfRangeException: Non-negative number required.
Parameter name: value
at System.IO.FileStream.set_Position(Int64 value)
at Microsoft.Mashup.EngineHost.Services.StreamSharer.MoveTo(SubStream subStream, Int64 position)
at Microsoft.Mashup.EngineHost.Services.StreamSharer.ReadAt(SubStream subStream, Int64 position, Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.Mashup.EngineHost.Services.StreamSharer.SubStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.Mashup.EngineHost.Services.TransformingPagedStream.ReadPage(Int32 page, Byte[] buffer)
at Microsoft.Mashup.EngineHost.Services.PagedStream.SeekToPage(Int32 page)
at Microsoft.Mashup.EngineHost.Services.PagedStream.Write(Byte[] array, Int32 offset, Int32 count)
at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(String key, TemporaryStorage storage)
at Microsoft.Mashup.EngineHost.Services.WriteBehindPersistentCache.AsyncCommitStorage(Object obj)
at Microsoft.Mashup.Evaluator.EvaluatorThreadPool.EvaluatorThread(Object state)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass7.<TryReportException>b__6()
at Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
at Microsoft.Mashup.Evaluator.EvaluationHost.TryHandleException(Exception exception)
at Microsoft.Mashup.Evaluator.SafeThread2.HandleException(Exception e)
at Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass15.<CreateAction>b__14(Object o)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart(Object obj)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

marouf
Frequent Visitor

Would anybody know how to resolve this issue?

I am experiencing the same error when refreshing my dataset, however this dataset has been refreshing without issue for over a year. I just updated to the August PowerBI yesterday and this is the first time I've tried refreshing it with the new update (I was using the July 2018 version previously).

 

I'm also running into a further issue where I can't select the window after the failure and have to kill the task in task manager. It's acting as though there is an extra window open when the failure occurs while I don't have the query editor open, but I can't actually see any extra windows and tabbing between available windows does not show any options available ot me.

 

If I trigger the failure while query editor is open I get the large failure block that looks very similar if not the same to the one pasted in the comments above.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors