Power BI problem, I think that is ODBC Driver.
hamacker opened this issue · 22 comments
Hi Devs,
I am using Firebird 4, and there is a time that use PowerBI to create reports that I am helping, this clients have lastest version of Firebrid ODBC installed (2.00.05.156).
So, not good when I select a single table to create a report and show me an exception:
DataSource.Error: ODBC: ERROR [HY000] [ODBC Firebird Driver][Firebird]arithmetic exception, numeric overflow, or string truncation
string right truncation
expected length 31, actual 63
Detalhes:
DataSourceKind=Odbc
DataSourcePath=dsn=teste1
OdbcErrors=[Table]
This error not in all tables, just few.
So, I choose one problematic table to discover whats the problem.
After many tests, I decide to replicate a problematic table and data (except indexes and FKs) into a new table, and guess? No more problem! I can select a replicate table with the same data, no errors found. Then I create indexes for replicate table, no problem again. But when I create FK (just one) for replicate table then errors come back. Initially I think thats because FK name has almost 31 chars, then rename to 'xxx' (3chars), but the same problem.
I think that PowerBI read not only table names but same metadata around objects was linked to my table and some names has more than 31 chars and crash because ODBC Driver was created to Firebird 2.x where object names are limited to 31 chars and now is much more.
PowerBI is very popular in my country - Brazil -, is too bad not use it.
To workaround, I transform my table to a view, it´s fine. But I would like to know If ODBC will be updated to Firebird newer version.
Thanks a lot,
H.
Hi @hamacker
You can try the beta-version ODBC driver here: https://github.com/FirebirdSQL/firebird-odbc-driver/wiki
As far as I remember I fixed the names length problem in it.
Regards
Well, good and bad news.
The good news is that 31 limit chars is OVER!
Bad news is, some corruption memory (protect memory) when I try using powerBI. I put log bellow.
I try devard odbc without problem.
Using Driver Firebird Beta 3.x from PowerBI:
Feedback Type:
Frown (Error)
Error Message:
Tentativa de leitura ou gravação em memória protegida. Normalmente, isso é uma indicação de que outra memória está danificada.
(Attempt to read or write protected memory. Typically, this is an indication that other memory is damaged.)
Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
em Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
em Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
em Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
em Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
em Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
em Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
em Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunkAndCheckIfClosed()
em Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunk()
em Microsoft.Mashup.Common.ChunkedInputStream.ReadByte()
em System.IO.BinaryReader.FillBuffer(Int32 numBytes)
em Microsoft.Mashup.Evaluator.RemotePageReader.PageReader.NextResult()
em Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.NextResult()
em Microsoft.Mashup.Evaluator.SoftCancellingDocumentEvaluator.DataReaderSourceEvaluation.SoftCancellingDataReaderSource.SoftCancellingPageReader.NextResult()
em Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.GetNextReader()
em Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.Read(IPage page)
em Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.Read(IPage page)
em Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.Read(IPage page)
em Microsoft.OleDb.Serialization.PageReaderRowset.ReadNextPage()
em Microsoft.OleDb.Serialization.PageReaderRowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hchapter, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
em Microsoft.OleDb.Rowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hReserved, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
em Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hReserved, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
Stack Trace Message:
Tentativa de leitura ou gravação em memória protegida. Normalmente, isso é uma indicação de que outra memória está danificada.
Invocation Stack Trace:
em Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
em Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
em Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
em Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
em Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.b__0()
em Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_0.b__0(Object null)
em System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
em System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
em System.Delegate.DynamicInvokeImpl(Object[] args)
em System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
em System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
em System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
em System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
em System.Windows.Forms.Control.InvokeMarshaledCallbacks()
em System.Windows.Forms.Control.WndProc(Message& m)
em System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
em System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
em System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
em System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
em System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
em System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
em System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
em Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
em Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction) em Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.<>c__DisplayClass10_0.<Microsoft.Mashup.Client.UI.Shared.Ux.IWindowService.ShowDialog>b__0() em Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.Trace[T](String action, String title, String details, Func1 showDialog, Func2 getResult) em Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.Trace(String action, String title, String details, Func1 showDialog)
em Microsoft.PowerBI.Client.Windows.Services.PowerBIWindowService.Microsoft.Mashup.Client.UI.Shared.Ux.IWindowService.ShowDialog(IDialog dialog)
em Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.ShowQueryLoadDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryErrorEditor queryErrorEditor, IDesktopModelingHost modelingHost, LocalizedString title, LoadToModelContext loadToModelContext, ILegacyUIHost legacyUIHost)
em Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass17_0.b__0()
em Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass72_01.<SynchronizeLooselyWithQueries>b__0() em Microsoft.PowerBI.Client.Windows.Report.<SynchronizeLooselyWithQueriesAsync>d__731.MoveNext()
em System.Runtime.CompilerServices.AsyncTaskMethodBuilder1.Start[TStateMachine](TStateMachine& stateMachine) em Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func1 getTask)
em Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func1 action) em Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialogForQueries(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryErrorEditor queryErrorEditor, IEnumerable1 queriesToApply, String loadReason, LocalizedString title, QueryLoadMode queryLoadMode, Action1 beforeSchemaSyncCallback, Action1 afterSchemaSyncCallback, Boolean synchronizeLooselyWithQueries)
em Microsoft.PowerBI.Client.Windows.Services.QueriesEditorWindowManager.ApplyQueryChanges(IEnumerable1 queriesToLoad) em Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action) em System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) em System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) em System.Delegate.DynamicInvokeImpl(Object[] args) em System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme) em System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj) em System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) em System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme) em System.Windows.Forms.Control.InvokeMarshaledCallbacks() em System.Windows.Forms.Control.WndProc(Message& m) em System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) em System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) em System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) em System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) em System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) em System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) em System.Windows.Forms.Form.ShowDialog(IWin32Window owner) em Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction)
em Microsoft.PowerBI.Client.Program.<>c__DisplayClass6_0.b__0()
em Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.b__0()
em Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
em Microsoft.PowerBI.Client.Program.RunApplication(String[] args)
em Microsoft.PowerBI.Client.Program.Main(String[] args)
PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.124.1805.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.EvaluationHost","Error":"Microsoft.Mashup.Host.Document.SerializedException - Microsoft.Mashup.Evaluator.Interface.ErrorException","MethodDef":"OnException","ErrorOffset":"","ErrorCode":""}
Snapshot Trace Logs:
C:\Users\gsantana\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShotc505de41-8c64-496e-a091-de03efadbb8e.zip
Model Default Mode:
Empty
Model Version:
PowerBI_V1
Performance Trace Logs:
C:\Users\gsantana\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Enabled Preview Features:
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_setLabelOnExportPdf
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
PBI_newCard
PBI_buttonSlicerAuthoring
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_angularRls
PBI_onObject
PBI_backstageUI
PBI_useModernPublishDialogs
PBI_gitIntegration
PBI_modelExplorer
PBI_daxQueryView
PBI_aiNarrativesVisual
PBI_qnaImproveLsdlCopilot
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
PowerBINonFatalError_ErrorDescription:
Microsoft.Mashup.Evaluator.Interface.ErrorException
PowerBIUserFeedbackServices_IsReported:
True
Formulas:
section Section1;
shared BI_CV = let
Fonte = Odbc.DataSource("dsn=teste1", [HierarchicalNavigation=true]),
BI_CV_View = Fonte{[Name="BI_CV",Kind="View"]}[Data]
in
BI_CV_View;
shared BI_PROCESSOS = let
Fonte = Odbc.DataSource("dsn=teste1", [HierarchicalNavigation=true]),
BI_PROCESSOS_View = Fonte{[Name="BI_PROCESSOS",Kind="View"]}[Data]
in
BI_PROCESSOS_View;
Oops) Well, I thought I'd cleared all the memory issues but now I see it was a very optimistic view))
Will take a look tomorrow.
What's puzzling is that I can't find any trace of the ODBC driver in the backtrace you posted...
If you could explain to me how I can reproduce this, it would help.
Thank you
@hamacker
I can not reproduce any memory issues in simple PBI scenarios(
Maybe it's related to your db data/metadata somehow. Could you send me a kind of test db (medatata script & some test data to insert), and the corresponding PowerBI project (pbix file) to load?
May I also request you to tell me the version/build of your PowerBI and your OS vesion/build please.
Regards.
There is a view "BI_Process" that use 4 tables.
"BI_PROCESS" is an only source for powerBI.
Using original database, powerBI crash with previous message error.
But when I export only 4 tables and 1 view to a new database, no errors appears!
There tables have the same row count, just less columns, because I selected only columns called by view.
I try to see if my original database was corrupted than I did backup/restore and everything is OK, but powerBI keep crashing, why, why...? but not crash in new database with only tables(with only fields) I need.
power-bi-fails.mp4
I still trying to cut and copy to send only necessary tables.
Mission complete!
I attach .fbk with tables and views. Please use view BI_PROCESSOS to view an error like showed in video and reported.
Enjoy.
I can´t uload here because the file .zip has 46MB and limit here is 25MB. So I put in wetransfer site, follow link:
https://we.tl/t-M4m6x2u3nC
This link will be free fot 7 days.
@hamacker
Great!)
Can you also give me your ODBC DSN settings? I mean this window screenshot:
Of curse!
I try several variants options, all tests fails.
Using Firebirdf 4 Database.
This week I will upgrade to Firebird 5 in my machine, then I will test again.
To test all scenarios, I discover that powerBI read metadata a lot. When I choose table "A", if there is FK to link table "B". PowerBI read metadata from "B" too. I think that powerBI create a spider web when you touch any table. My database is not big, but has several FK, Constraints, Indexes and all fields type are domains. When I create a new database to test ODBC driver, I did convert all field types domains to raw types(d_valor2->numeric(18,2), remove constraints and FKs...does not show error anymore!
So, I think the problem is metadata reading, maybe when exists domains.
Aha. Seems like I got it)
The root of all evil is table CLIENTES, field OBSERVACAO, it's a BLOB, the first one is null, but the others are not null but empty (size==0). And the ODBC driver returns a garbage for them. Looks very similar to #201
Issue 201 solution was not ported to ODBC v3 yet. I will make an investigation tomorrow and return back to you.
Regards
Hi @hamacker
Can you please try the last (3.0.0.7) build from here https://github.com/FirebirdSQL/firebird-odbc-driver/wiki
Up!
It´s over yesterday.
I like carnival because of hollydays, not for samba.
I tested minutes a go, well done, good job!
Sucessfully in Firebird 4.0.4 and Firebird 5.0, booth 64 bits.
Hi @hamacker
Great to here it!
Test it please at leisure, let's not rush to close the ticket. Two or three days won't matter.
Regards
it´s closed!