Hello all. Polybase newbie here, so hoping that I'm just missing something simple.
I've configured Polybase between SQL Server 2019 CU9 and MySQL (5.7.12) via a generic ODBC driver. Able to create the external table without issue and can do a basic select statement on the external table without issue.
However, once I start to join that table with other tables and do where clauses, things start to get funky.
External table config code here:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
GO
CREATE DATABASE SCOPED CREDENTIAL mySQL_creds WITH IDENTITY = '', SECRET = '';
GO
CREATE EXTERNAL DATA SOURCE [DataSource]
WITH ( LOCATION = 'odbc://somecoolMySQLdbGoesHere.us-east-1.rds.amazonaws.com:3306'
, CONNECTION_OPTIONS = 'DSN=MySQLODBCConn'
, PUSHDOWN = on
, CREDENTIAL = mySQL_creds
);
GO
CREATE EXTERNAL TABLE [p].[Account]
([accountID] nvarchar(32), [accountNumber] int
, firstname nvarchar(255) lastname nvarchar(255)
)
WITH ( LOCATION = 'account',DATA_SOURCE = [DataSource] ) ;
My internal table looks like this:
CREATE TABLE [dbo].[Accounts]
([account_id] [numeric](20, 0) NOT NULL
, [sponsor_id] [numeric](20, 0) NULL
, [roman_name_2] [nvarchar](320) NULL
CONSTRAINT [PK_Distributors] PRIMARY KEY CLUSTERED ([account_id] ASC)
)
Now, when I join, I start to have challenges. This works:
select top 10 pa.accountNumber, pa.firstname, pa.lastname, da.roman_name
from p.account as pa
inner join dbo.account as da on pa.accountNumber = da.account_id
But this doesn't:
select top 10 pa.accountNumber, pa.firstname, pa.lastname, da.roman_name
from p.account as pa
inner join dbo.account as da on pa.accountNumber = da.account_id
where da.account_id = 123456
SQL Returns the error:
"Msg 7320, Level 16, State 110, Line 16
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 110802;
An internal DMS error occurred that caused this operation to fail. Details: 105082;
Generic ODBC error: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.12] ."
Similar issues occur regardless of how I format the WHERE clause or what side the WHERE clause is filtering.
The polybase logs do not appear to offer any additional information.
Polybase_DMS_Errors.log shows:
5/14/2021 12:14:33 AM [Thread:780] [AbstractReaderWorker:ErrorEvent] (Error, High): QueryId QID449 PlanId 491d85e2-61e4-4c4a-8f0b-c07d6ec09f13 StepId 3:
Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException[105082:1]: 105082;Generic ODBC error: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.12] .
at Microsoft.SqlServer.DataWarehouse.DataMovement.Common.ExternalAccess.OdbcUtil.ThrowMppSqlException(String message, NativeOdbcConnection connection)
at Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.DataReader.OdbcBufferReader.ReadBuffer(MemoryBuffer buffer)
at Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.RoundRobinBufferReaderWorker.ReadAndSendData()
at Microsoft.SqlServer.DataWarehouse.DataMovement.Workers.AbstractReaderWorker.Execute(Object status)
Attached is the snippet from the DWEgine_errors.log.
Anyone got a clue as to what is going on or a way to track it down so I can figure this out? I have a hard time believing we've created a system where we can't filter it, but can magically join to it. Makes no sense. Appreciate any assistance you can provide.
Sincerely,
Tenagra
