Does TiDB support Power BI?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: tidb是否支持powerbi

| username: 付先生Mr

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v7.5.1
[Reproduction Path] What operations were performed to cause the issue
[Encountered Issue: Problem Phenomenon and Impact]
Does TiDB support PowerBI? Currently, when saving the data source, an error occurs: Connection must be valid and open.

image
image769×312 22.6 KB

Detailed Information
Feedback Type:
Frown (Error)

Error Message:
Connection must be valid and open.

Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
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.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunkAndCheckIfClosed()
at Microsoft.Mashup.Evaluator.MessageBasedInputStream.ReadNextChunk()
at Microsoft.Mashup.Common.ChunkedInputStream.ReadByte()
at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
at Microsoft.Mashup.Evaluator.RemotePageReader.PageReader.NextResult()
at Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.NextResult()
at Microsoft.Mashup.Evaluator.SoftCancellingDocumentEvaluator.DataReaderSourceEvaluation.SoftCancellingDataReaderSource.SoftCancellingPageReader.NextResult()
at Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.GetNextReader()
at Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.Read(IPage page)
at Microsoft.Mashup.Engine.Interface.Tracing.TracingPageReader.Read(IPage page)
at Microsoft.Mashup.Common.IPageReaderExtensions.NotifyingEndOfAllResultsPageReader.Read(IPage page)
at Microsoft.OleDb.Serialization.PageReaderRowset.ReadNextPage()
at Microsoft.OleDb.Serialization.PageReaderRowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hchapter, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
at Microsoft.OleDb.Rowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hReserved, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)
at Microsoft.Mashup.OleDbProvider.TracingRowset.Microsoft.OleDb.IRowset.GetNextRows(HCHAPTER hReserved, DBROWOFFSET lRowsOffset, DBROWCOUNT cRows, DBCOUNTITEM& countRowsObtained, HROW** pRows)

Stack Trace Message:
Connection must be valid and open.

Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo…ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_0.b__0(Object null)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Delegate.DynamicInvokeImpl(Object args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
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.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Windows.DialogWindowBase.ShowModal(IWindowHandle windowHandle)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func1 showModalFunction) at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.TryShowDialog(Report report, IExceptionHandler exceptionHandler, IPowerBIWindowService windowService, IPowerBIQueryUIService queryServices, IDesktopModelingHost modelingHost, LocalizedString title, LoadToModelContext loadToModelContext) at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass16_0.<TryShowDialogForQueries>b__0() at Microsoft.PowerBI.Client.Windows.Report.<>c__DisplayClass68_01.b__0()
at Microsoft.PowerBI.Client.Windows.Report.d__691.MoveNext() at System.Runtime.CompilerServices.AsyncTaskMethodBuilder1.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueriesAsync[T](Func1 getTask) at Microsoft.PowerBI.Client.Windows.Report.SynchronizeLooselyWithQueries[T](Func1 action)
at Microsoft.PowerBI.Client.Windows.Commands.ApplicationCommands.DataImportCommands.RefreshAllQueries(IPowerBIWindowService windowService, IExceptionHandler exceptionHandler, RefreshAllQueriesParameters parameters)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.Practices.Prism.Commands.DelegateCommandBase.<>c__DisplayClass4_0.<.ctor>b__0(Object arg)
at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(ICommand command, Object parameter) at Microsoft.Practices.Prism.Commands.CompositeCommand.Execute(Object parameter) at Microsoft.PowerBI.Client.Windows.Commands.CompositeCommand1.Execute(T parameter)
at Microsoft.PowerBI.Client.Windows.CommandStoreHostService.d__14.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.CommandStoreHostService.ExecuteCore(String commandId, String parameterJson, Int64 promiseHandle)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.d__1.MoveNext()
at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine)
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAwaitableAsyncExceptions(IExceptionHandler exceptionHandler, Func1 asyncFunc) at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<HandleAsyncExceptions>d__0.MoveNext() at System.Runtime.CompilerServices.AsyncVoidMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine) at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.HandleAsyncExceptions(IExceptionHandler exceptionHandler, Func1 asyncFunc)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Delegate.DynamicInvokeImpl(Object args)
at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
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.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass6_0.b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.RunApplication(String args)
at Microsoft.PowerBI.Client.Program.Main(String args)

PowerBINonFatalError:
{“AppName”:“PBIDesktop”,“AppVersion”:“2.117.984.0”,“ModuleName”:“”,“Component”:“”,“Error”:“Microsoft.Mashup.Host.Document.SerializedException - Microsoft.Mashup.Evaluator.Interface.ErrorException”,“MethodDef”:“”,“ErrorOffset”:“”}

Snapshot Trace Logs:
C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop SSRS\FrownSnapShot4ad26fae-ef38-4726-98db-6e9b114be36f.zip

Model Default Mode:
Import

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop SSRS\PerformanceTraces.zip

Enabled Preview Features:
PBI_scorecardVisual
PBI_NlToDax
PBI_horizontalFusion
PBI_setLabelOnExportPdf

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_sparklines
PBI_fieldParametersSuperSwitch
PBI_angularRls
PBI_onObject
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBINonFatalError_ErrorDescription:
Microsoft.Mashup.Evaluator.Interface.ErrorException

PowerBIUserFeedbackServices_IsReported:
True

Formulas:

section Section1;

shared WorkOrderDataReport = let
Source = MySQL.Database(“10.10.10.53:4000”, “dscmall”, [ReturnSingleDatabase=true, Query="select a.*#(lf),ifnull(aur.user_name, a.CustomerName_2) ‘CustomerName’, ifnull(aur.mobile_phone, a.CustomerPhone_2) ‘CustomerPhone’#(lf)from #(lf)(#(lf)-- explain#(lf)SELECT#(lf)dwos.id,#(lf)dwos.serial_number ‘WorkOrderSerialNumber’,#(lf)max(dwode.order_sn) ‘WorkOrderNumber’,#(lf)dwody.dict_name ‘Project’,#(tab)#(lf)dwody2.dict_name ‘ServiceType’,#(tab)#(lf)dwody3.dict_name ‘WorkOrderType’,#(tab)#(lf)dwody4.dict_name ‘WorkOrderSubType’,#(lf)case dwos.status#(lf)when 1 then ‘PendingTransfer’#(lf)when 2 then ‘Transferred’#(lf)when 3 then ‘Other’#(lf)when 4 then ‘ReturnRequest’#(lf)when 5 then ‘Other’#(lf)when 6 then ‘CancelledByStation’#(lf)when 7 then ‘Assigned’#(lf)when 8 then ‘Accepted’#(lf)when 9 then ‘Other’#(lf)when 10 then ‘Contacted-Appointment’#(lf)when 11 then ‘Contacted-Departure’#(lf)when 12 then ‘Contacted-Reschedule’#(lf)when 13 then ‘Other’#(lf)when 14 then ‘CancelledByEngineer’#(lf)when 15 then ‘OnSite’#(lf)when 16 then ‘Other’#(lf)when 17 then ‘ServiceCompleted’#(lf)when 18 then ‘Closed’#(lf)when 19 then ‘Returned’#(lf)when 20 then ‘SettlementRejected’#(lf)when 21 then ‘SettlementApproved’#(lf)when 22 then ‘CustomerArrived’#(lf)when 23 then ‘InStoreService’#(lf)when 24 then ‘InProgress’#(lf)when 25 then ‘RemoteService’#(lf)when 26 then ‘Locked’#(lf)when 27 then ‘Approved’#(lf)when 28 then ‘PendingUserAppointment’#(lf)when 29 then ‘PendingDelivery’#(lf)when 30 then ‘PendingAssignment’#(lf)when 31 then ‘SFService’#(lf)when 32 then ‘UserCancelled’#(lf)when 33 then ‘WorkOrderTerminated’#(lf)else ‘Other’#(lf)end ‘WorkOrderStatus’,#(lf)case dwos.status#(lf)when 1 then ‘ActiveWorkOrder’#(lf)when 2 then ‘ActiveWorkOrder’#(lf)when 3 then ‘Other’#(lf)when 4 then ‘ReturnedWorkOrder’#(lf)when 5 then ‘Other’#(lf)when 6 then ‘HistoricalWorkOrder’#(lf)when 7 then ‘ActiveWorkOrder’#(lf)when 8 then ‘ActiveWorkOrder’#(lf)when 9 then ‘Other’#(lf)when 10 then ‘ActiveWorkOrder’#(lf)when 11 then ‘ActiveWorkOrder’#(lf)when 12 then ‘ActiveWorkOrder’#(lf)when 13 then ‘Other’#(lf)when 14 then ‘Cancelled’#(lf)when 15 then ‘ActiveWorkOrder’#(lf)when 16 then ‘Other’#(lf)when 17 then ‘ServiceCompleted’#(lf)when 18 then ‘HistoricalWorkOrder’#(lf)when 19 then ‘HistoricalWorkOrder’#(lf)when 20 then ‘HistoricalWorkOrder’#(lf)when 21 then ‘HistoricalWorkOrder’#(lf)when 22 then ‘CustomerArrived’#(lf)when 23 then ‘ActiveWorkOrder’#(lf)when 24 then ‘ActiveWorkOrder’#(lf)else ‘Other’#(lf)end ‘WorkOrderStatus-MajorCategory’,#(lf)case dwos.order_to #(lf)when ‘xian-niu’ then ‘XianNiu’#(lf)when ‘soc’ then ‘SOC’#(lf)when ‘byo’ then ‘BYO’#(lf)when ‘sf’ then ‘sf’#(lf)when ‘auto’ then ‘AutoAssignment’#(lf)else ‘Other’#(lf)end ‘AssignmentChannel’,#(lf)dwos.third_party_id,#(lf)-- dus.user_name ‘CustomerName’,#(lf)-- maur.name ‘CustomerName’,#(lf)-- dus.mobile_phone ‘CustomerPhone’,#(lf)-- maur.telephone ‘CustomerPhone’,#(lf)dler.real_name ‘EngineerName’,#(lf)dwos.create_user_name ‘Creator’,#(lf)if(dwos.create_user_name = ‘System’, ‘AutoAssignment’, #(lf)case dwos.assigned_way #(lf)when 1 then ‘AutoAssignment’#(lf)when 2 then ‘ManualAssignment’#(lf)else if(dwos.create_user_name = ‘System’, ‘AutoAssignment’, ‘’)#(lf)end) ‘AssignmentMethod’,#(lf)dmsi2.rz_shopName ‘EngineerServiceProviderName’,#(lf)case dmsi2.shop_type#(lf) when 1 then ‘ServiceProvider’#(lf) when 2 then ‘ServiceStation’#(lf) when 3 then ‘Supplier|NationalDistributor’#(lf) when 4 then ‘SaaSProvider’#(lf) when 5 then ‘IndividualSupplier-ProvincialDistributor’#(lf) when 6 then ‘IndividualServiceProvider’#(lf) when 7 then ‘IndividualSupplier-Regular’#(lf) when 8 then ‘ProvincialDistributor’#(lf) when 9 then ‘Supplier(YingTongSheType)’#(lf) when 10 then ‘SelfOperated’#(lf) when 11 then ‘T2/CityDistributor’#(lf) when 13 then ‘SmartBusiness-Provider’#(lf) when 14 then ‘SmartBusiness-Station’#(lf) else ‘Other’#(lf) end ‘ServiceProviderType’,#(lf)died.station_name ‘EngineerServiceStationName’,#(lf)case dmsi.shop_type#(lf) when 1 then ‘ServiceProvider’#(lf) when 2 then ‘ServiceStation’#(lf) when 3 then ‘Supplier|NationalDistributor’#(lf

2
| username: zhaokede | Original post link

TiDB supports Power BI, and Power BI supports connecting to data sources through the MySQL driver. Theoretically, it should be able to connect to TiDB.

3
| username: Kongdom | Original post link

In theory, everything that MySQL supports, TiDB also supports. :yum:

4
| username: 付先生Mr | Original post link

The issue is not resolved yet. Printing the TiDB logs.
堡垒机_2024-05-30_17-15-35.log (10.0 KB)

5
| username: TiDBer_QYr0vohO | Original post link

Supported.

6
| username: 付先生Mr | Original post link

Looking at the last log, a kill was executed before attempting to connect, and then it immediately tried to connect but timed out.

7
| username: Kongdom | Original post link

I haven’t used it, but I found some similar posts.

8
| username: 付先生Mr | Original post link

My situation is quite strange. The already published data is not affected, but there is an error during data modeling. There is no issue when the data volume is small, and there are no problems when directly connecting to native MySQL.

9
| username: 濱崎悟空 | Original post link

Sure.

10
| username: 我是人间不清醒 | Original post link

We used FineBI and SmartBI, and did not find any issues when switching to TiDB, using the MySQL driver.

11
| username: MrSylar | Original post link

Similar logs, so is it possible that the database connection pool is not configured for keep-alive? waitTimeout=28800 is the default database setting, indicating session idle timeout.