0

I get an error while migrating a database from MySQL 8.0 to SQL Server 2019 using the SQL Server Migration Assistant.

The error message:

ExecuteReader requires an open and available Connection. The connection's current state is closed.

The same error happens on 95% of my tables, and occurred after I created a schema and ran the migrate data process.

Any idea for this?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Carlos Vaz
  • 11
  • 3

2 Answers2

1

I solved this with some steps.
My scenario is MySQL to SQL Server, and I presumed this error ExecuteReader occurred when the migration process tried to read MySQL. So, in MySQL (Windows installation) my.ini config, I made this changes, setting values in the edge of maximum.

table_open_cache
tmp_table_size
thread_cache_size
max_allowed_packet
innodb_open_files

In SQL Server Migration Assistant, I configured in Tools > Project Settings > Migration > Data Migration Timeout in minutes = 1000 I have tables with 20GB, the default timeout is 15 minutes and this caused the fall errors. In Tools > Default Project Settings > Synchronization = 1000 I´ve changed Query Timeout to 1000 in MySQL and SQL Server sections. This resolved the problem.

Ronaldo
  • 6,017
  • 2
  • 13
  • 43
Carlos Vaz
  • 11
  • 3
0

I've bene using the SSMA lately (DB2 to SQL Server) but I haven't incurred in such error. My suggestion is, before executing any SQL Query make sure that your connection of Application with the DataBase is Open.

The error is pretty clear and apparently is a custom error provided by C#:

  1. SQL server will hang up on you if you exceed the agreed upon timeout between requests.
  2. If you are non-local, the connection may be broken and might need to be re-established.
  3. You may not have successfully connected the first time (maximum number of connections exceeded).

Try to open a terminal and ping the remote server with the option -t while you use SSMA.

Otherwise you are clearly in this situation:

  1. https://stackoverflow.com/questions/38266349/mysql-5-6-to-mssql-server-2014-migration-executereader-requires-an-open-and-av/38266748
  2. Error migrating a big table from mySQL to SQL Server 2012
Francesco Mantovani
  • 1,695
  • 14
  • 28