MySQL Linked Server can't update longtext

Question

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.

Roanne

is meta_value null or not null by definition? – Trothplight

It's not null. Would that affect it? For now we got around it by making a 2nd column called meta_value_overflow (of type text), but it would be ideal if we could just have one longtext column. – Roanne

Sorry I can't get what you are doing, can you provide the diagram? To me it seem you don't need to SELECT meta_value you don't use it anywhere. Can't you just: UPDATE OPENQUERY (woocommerce, 'SELECT meta_id FROM woocommerce.wp_postmeta WHERE meta_id = 9465078') SET meta_value = 'outofstock'; . ? – Chenay

Answer 1

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.

Carlstrom