Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Linked Server can't update longtext

I have a Linked Server from SQL Server to my mySQL database (Windows Server using MySQL ODBC Driver 5.3). I have a unique situation where I can only get my longtext columns to return if I convert them in an OPEN QUERY:

SELECT * 
FROM
OPENQUERY (woocommerce, 'SELECT meta_id, CONVERT(meta_value using UTF8) as meta_value
                         FROM woocommerce.wp_postmeta WHERE meta_id = 9465078') 

If I don't convert/cast it I get the following error from the linked server:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "woocommerce" reported an error. The provider reported an unexpected catastrophic failure.

I need to update this longtext column, "meta_value", but I can't since it has been casted/converted.

    UPDATE 
    OPENQUERY (woocommerce, 'SELECT meta_id, CONVERT(meta_value using utf8) 
                             FROM woocommerce.wp_postmeta WHERE meta_id = 9465078') 
    SET meta_value = 'outofstock';

Is there any way to update a casted/converted column using OPENQUERY and a linked server? I've tried converting or casting it every which way.

like image 292
Paul D Avatar asked Aug 04 '18 15:08

Paul D


1 Answers

Driver seems have problem with LONGTEXT column.

You could limit the LONGTEXT column size with option:

Limit column size to signed 32-bit range

(Take care to not lose your data, but it's a rarely case, I don't think any String would longer than 1 billions characters XD)

The last comment here would help you how to do that

Now you can remove the cast and can proceed updating normally.

like image 124
Mạnh Quyết Nguyễn Avatar answered Nov 15 '22 23:11

Mạnh Quyết Nguyễn