Error Migrating Large Table from MySQL to SQL Server 2012

MySQLsql-server-2012

I'm trying to convert several mySQL tables to Microsoft SQL Server 2012 tables using the SQL Server Migration Assistant for MySQL. The small tables get converted but when it comes to the big one (slightly less than 4 GB), after about 1:30 hour of processing, it gives the following error:

(Elapsed Time = 00:01:24:32:197). Cannot truncate target table.
Reason: ExecuteReader requires an open and available Connection. The
connection's current state is closed.

Here is the output:

Migrating data...
Analyzing metadata...
Preparing table `big fmail user`.conversionlog...
Preparing data migration package...
Starting data migration Engine
Starting data migration...
The data migration engine is migrating table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 87489673 rows total
Table `big fmail user`.conversionlog data migration: 8760542 rows processed.
Table `big fmail user`.conversionlog data migration: 17510722 rows processed.
Table `big fmail user`.conversionlog data migration: 26249995 rows processed.
Table `big fmail user`.conversionlog data migration: 34998808 rows processed.
Table `big fmail user`.conversionlog data migration: 43757368 rows processed.
Table `big fmail user`.conversionlog data migration: 52494656 rows processed.
Table `big fmail user`.conversionlog data migration: 61254458 rows processed.
Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
 Errors: Cannot truncate target table. Reason: ExecuteReader requires an open and available Connection. The connection's current state is closed.
Table `big fmail user`.conversionlog data migration: 0 rows processed.
Completing migration of table `big fmail user`.conversionlog...
Migration complete for table '`big fmail user`.conversionlog': > [big fmail user].dbo.conversionlog, 0 rows migrated (Elapsed Time = 00:01:24:32:197).
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Information: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Data migration operation has finished.
    0 table(s) successfully migrated. 
    0 table(s) partially migrated. 
    1 table(s) failed to migrate.

Any suggestions as to what might cause this error, and how I should resolve it?

Best Answer

I solved the issue. The problem was the value of the Data migration timeout in minutes property.

Go to Tools > Project Settings > General and in the left pane click on Migration and then in the right pane, in the section Misc, change Data migration timeout in minutes property to something larger (the default value was 15; I changed it to 300).

Related Solutions

Mysql – Query performance with subquery and IN clause

Refactor the query as follows:

SELECT
    readings.*
FROM
    (
        SELECT boxsn FROM readings
        WHERE (time >= 1325404800) 
        AND (time < 1326317400) 
        ORDER BY `time` ASC
    ) readings_keys
    LEFT JOIN
    (
        SELECT id AS boxsn FROM boards WHERE siteId = '1'
    ) boards
    USING (boxsn)
    LEFT JOIN readings
    USING (boxsn)
;

Make sure you have the following indexes:

ALTER TABLE boards ADD INDEX siteId_id_ndx (siteId,id);
ALTER TABLE readings ADD INDEX time_boxsn_ndx (time,boxsn);

You can drop the other index

ALTER TABLE readings DROP INDEX boxsn_time_ndx;

You should definitely see a dramatic improvement in performance as the tables grow.

In your case,

The first EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a list of value in memory
The second EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a table.

UPDATE 2012-01-12 14:03 EDT

I refactored it again to make sure the readings keys and boards keys are combined correctly before retrieving the data from the readings table:

SELECT 
    readings.* 
FROM 
    ( 
        SELECT A.* FROM
        (
            SELECT boxsn FROM readings 
            WHERE (time >= 1325404800)  
            AND (time < 1326317400)  
            ORDER BY `time` ASC
        ) A
        LEFT JOIN
        (
            SELECT id AS boxsn
            FROM boards
            WHERE siteId = '1'
        ) B
        USING (boxsn)
        WHERE B.boxsn IS NOT NULL
    ) readings_keys 
    LEFT JOIN readings 
    USING (boxsn) 
;

Sql-server – transactional replication, failing with communication link failure half way through bcp

If the link is bad, you must overrride -QueryTimeOut and -KeepAliveMessageInterval parameters. Check the SQL Server Agent job, identify the replication Job, then right-click Properties, then Steps, Edit and at the end of Commend just add -LoginTimeOut 120 -QueryTimeOut 7200 -KeepAliveMessageInterval 600

Best Answer

Related Solutions

Mysql – Query performance with subquery and IN clause

UPDATE 2012-01-12 14:03 EDT

Sql-server – transactional replication, failing with communication link failure half way through bcp

Related Question