Sql-server – MySQL client out of memory – Linked Table MsSQL to MySQL

linked-serverMySQLreplicationsql server

I'm currently having an issue on 2 out of 3 SQL servers when copying large amounts of data to a MySQL table. The MySQL table currently houses ~6 million records, and the query that is running is trying to update / delete / insert another 100,000 records.

I've attempted to use OPENQUERY on the commands I'm running to attempt to do this but that's not working either and I am just presented with the error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.6.35]MySQL client ran out of memory"

I thought it could be a driver issue so updated to the latest ODBC driver (5.2) but that did not work either.

The two servers i am using are both 32 bit operating systems which may have a great deal to do with it (the 64 bit server hasn't had an issue as yet).

Is there another setting i can have on the server that will let it pool values and process with what memory it has available or am I just stuck with an issue of needing more memory for processing a result list that is this large?

UPDATE: the third server is now not working either, all showing same results

My cnf settings just have:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 512K
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
join_buffer_size = 128K
innodb_buffer_pool_instances = 1

Have to setup the symbolic-links section still, but at the moment this is still a non-production server so not a big issue.

The two statements that are failing are inserting and deleting rows from a MySQL table that is > 4 million rows. Both are inside a stored procedure that reads values from a transfer table (transfer table is filled via triggers on the main tables which read in whether the query should send a delete or insert request to the mysql table)

    DELETE
        a
    FROM
        [LinkServerTable]...Table1 as a
    INNER JOIN
        [dbo].[TransferTable] as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        a.Branch = 'Databases Branch'
    AND
        (b.QueryType = 'Delete' OR b.QueryType = 'Update')
    AND
        b.DateQueried < @DateNowVar

    INSERT INTO [LinkServerTable]...Table1
    (
            Columns,
            Branch
        )
    SELECT
        b.Columns,
        Branch = 'Databases Branch'
    FROM
        [dbo].[TransferTable] as a
    INNER JOIN
        [dbo].OriginalTable as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        (QueryType = 'Insert' OR QueryType = 'Update')
    AND
        DateQueried < @DateNowVar
    AND
        NOT EXISTS
            (SELECT
                1
             FROM
                Transfertable as c
             WHERE
                a.Key1 = c.Key1
             AND
                a.Key2 = c.Key2
            AND
                a.Key3 = c.Key3
            AND
                a.Key4 = c.Key4
            AND
                a.Key5 = c.Key5
             AND
                a.DateQueried < c.DateQueried)

That extra statement at the end of the insert section is so it won't process multiple updates / deletes if not required (if they've been updated multiple times in the time it takes to run the procedure)

Best Answer

Do the DELETE in chunks. More discussion .

Related Question