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)