| Bug #110855 | Error occurs when calling MySQL 8.0 stored procedure from MS SQL via linked serv | ||
|---|---|---|---|
| Submitted: | 28 Apr 2023 2:44 | Modified: | 15 Sep 2023 8:02 |
| Reporter: | Akira Morishita | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 8.0.30, 8.0.33 | OS: | Any (alma linux 8、Windos10) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | connector, MSSQL, MySQL, ODBC | ||
[5 May 2023 7:34]
MySQL Verification Team
Hello Akira, Thank you for the bug report. Verified as described. Regards, Ashwini Patil
[8 May 2023 5:33]
Akira Morishita
Thank you for your reply. If you want more infomation, feel free to tell me. I'm looking foward to fix this bug.
[15 Sep 2023 8:02]
Rafal Somla
Posted by developer:
After further analysis we decided this issue is not feasible to fix in MySQL server. Here is our reasoning.
In a query like this
```
select * from openquery([LINKSRV_MYSQL8],'call foo()')
```
one requests rows from the result of a call to stored procedure `foo()` on the linked MySQL server. This implies that `call foo()` returns a result set but when the statement is being prepared (*) the returned meta-data gives no information about a result set. This confuses MS SQL logic and leads to the error
```
The OLE DB provider "MSDASQL" for the linked server "LINKSRV_MYSQL8" indicates that either the object has no columns or the current user does not have permissions on that object.
```
Again, this error happens in the prepare phase, before the query is executed.
Note (*): Prepare step happens when the statement is handled by `sp_executesql`.
Note: It is possible to correctly execute `call foo()` on the linked server using `EXECUTE ... AT ...` construct, without going through prepare phase.
The reason why MySQL server is reporting no result meta-data when preparing an SQL statement of the form `CALL ...` is because in general it is not possible to know whether and what kind of data will be returned when the call is executed. This might depend on parameters of the stored procedure, like in this example:
```
ALTER PROCEDURE [dbo].[sample_mssql]
@InputValue INT
AS
BEGIN
IF @InputValue > 1
BEGIN
SELECT 'Three columns' as Col1, CURRENT_TIMESTAMP as Col2, 456 AS Col3
END
ELSE
BEGIN
SELECT 123 as Col1, 'Two columns' AS Col2
END
END
```
In the example statement `CALL [dbo].[sample_mssql] ?` will return 3 or 2 columns depending on the value of the parameter.
Your scenario works with MS SQL server because the latter makes heuristic guesses about possible result of a `CALL ...` statement based on stored procedure definition. However such guess can not be always correct and in the example above, where MS SQL reports a result with 3 columns, execution will fail with error if statement is executed with the parameter set to 1 (and return 2 columns instead of expected 3).
In case of MySQL we opt for giving no information rather than trying to guess and give possibly incorrect information. For that reason, even though in your scenario you know that the stored procedure will always return a result set of the same shape, MySQL server has no way of knowing this and the construct you try to use can not work with it.
Note: This means that we no longer consider it a bug that MySQL server does not give meta-data when preparing a `CALL ...` statement. It is an inherent limitation of how `CALL ...` statements work, in particular the fact that in general you can not know the shape of the result before actually executing the call.
[20 Sep 2023 1:52]
Akira Morishtia
Thank you for your investigation. I consider changing the logic to call mysql procedures directly from the program.
Description: Hi, I'm developing an application that connects to MySQL8.0 from MS SQL via linked server. using MySQL Community Server 8.0.30 mysql-connector-odbc-8.0.32 SQLServer Express 12.0.2000.8 I can successfully crate a linked server in MS SQL. But, an error always occurs, when I call a stored procedure which is registered in MySQL, from MS SQL linked server. For example, when I call a stored procedure from the SQLServer Management Studio as follows: ------------------------------------------------------------ declare @sql nvarchar(max) set @sql='select * from openquery([LINKSRV_MYSQL8],''call [linked_server].[sample()]'')' exec sp_executesql @sql ------------------------------------------------------------ * [LINKSRV_MYSQL8]=Linked Server Name * [linked_server]=MySQL Database Name * [sample()]=Stored Procedure Name which is registered in MySQL Database MS SQL is returning error message as follows: ------------------------------------------------------------ Object "call linked_server.sample()" cannot be processed. The OLE DB provider "MSDASQL" for the linked server "LINKSRV_MYSQL8" indicates that either the object has no columns or the current user does not have permissions on that object. ------------------------------------------------------------ * The above is a translation of the error message from the Japanese version of SQL Server Management Studio, so the error message in the English version of SQL Server Management Studio may differ slightly. By the way, if I run a raw SQL as followings, something similer to the above stored procedure via a linked server, its work fine. ------------------------------------------------------------ DECLARE @sql1 NVARCHAR(MAX) SET @sql1 = N'select * from OPENQUERY (LINKSRV_MYSQL8, ''select * from linked_server.user'')' exec sp_executesql @sql1 ------------------------------------------------------------ And, if I run a stored procedue directly from MySQL clinent as followings, its work fine too. ------------------------------------------------------------ $ mysql -u **** -p -h ***.***.***.*** mysql > use linked_server; mysql > call sample(); ------------------------------------------------------------ Furthermore, if I change the MySQL version 8.0 to 5.7 (without changing any other configurations), its work fine too. From the error massage, I suspected that the permission settings were insufficient, but I confirmed that "Execute" was granted to the sql execution user. Based on the above, I guess that the error will occurs under limited conditions, as follows: 1. Error occurs when running stored procedue. 2. Error occurs when running via linked server 3. Error occurs only at the version of MySQL is 8.0. I understand the above, but I do not know which setting(s) to change to avoid this error. How to repeat: 1. Creating a database Creating a following database on MySQL server. ------------------------------------------------------------ mysql> CREATE DATABASE linked_server; USE linked_server; CREATE TABLE `user` ( `id` int DEFAULT NULL, `name` varchar(60) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO user VALUES (1, 'ichiro yamada'); INSERT INTO user VALUES (2, 'hanako suzuki'); create user 'TestUser'@'%' identified by '*********'; grant select, insert, update, delete, alter, alter routine, create, create routine, create view, create temporary tables, drop, execute on *.* to 'TestUser'@'%'; ------------------------------------------------------------ Registering a following stored procedue to Database: linked_server by User: TestUser. ------------------------------------------------------------ mysql> USE linked_server; create procedure sample() begin select * from user; end ------------------------------------------------------------ 2. ODBC data source Creating a data source with a following settings using the 'ODBC Data Source (64-bit)' in Windows administrative tools. ------------------------------------------------------------ Driver: MySQL ODBC 8.0 Ucicode Driver Data Source Name： linked_server_mysql8 TCP/IP Server： ***.***.***.*** User： TestUser Password： ********* Database： linked_server ODBC driver： mysql-connector-odbc-8.0.32-winx64.msi ------------------------------------------------------------ 3. Linked server Creating a linked server with a following settings using the Microsoft SQL Server Manage Studio 2014. ------------------------------------------------------------ [General] Provider: Microsoft OLE DB Provider for ODBC Drivers Product name: MySQL Linked server name: LINKEDSRV_MYSQL8 Server type: other data source Provider: Microsoft OLE DB Provider for ODBC Drivers Data source: linked_server_mysql8 Provider string: DRIVER={MySQL ODBC 8.0 UNICODE Driver};SERVER=***.***.***.***;PORT=3306;DATABASE=linked_server;USER=TestUser;PASSWORD=*********;OPTION=3; Catalog: linked_server [Security] Use this security context: Put a check Remote login: TestUser Password: ********* ------------------------------------------------------------ 4. Running a stored procedue Running a following SQL with the Microsoft SQL Server Manage Studio 2014. ------------------------------------------------------------ declare @sql nvarchar(max) set @sql='select * from openquery(LINKEDSRV_MYSQL8,''call linked_server.sample()'')' exec sp_executesql @sql ------------------------------------------------------------ Then MS SQL is returning error message as follows: ------------------------------------------------------------ Object "call linked_server.sample()" cannot be processed. The OLE DB provider "MSDASQL" for the linked server "LINKSRV_MYSQL8" indicates that either the object has no columns or the current user does not have permissions on that object. ------------------------------------------------------------ * The above is a translation of the error message from the Japanese version of SQL Server Management Studio, so the error message in the English version of SQL Server Management Studio may differ slightly.