MySQL Bugs: #87647: MS Access DB Migration fails

Bug #87647	MS Access DB Migration fails
Submitted:	3 Sep 2017 20:18	Modified:	25 Sep 2017 7:16
Reporter:	Michael Hardy	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S2 (Serious)
Version:	6.3.9,8.0.19,8.0.21	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any
Tags:	accdb, access, MDB, migration, reverse engineering

Description:
Attempted migration of a simple MS Access 16 DB fails using MySQL Workbench 6.3.9's Migration Wizard.

How to repeat:
Begin with an MS Acceess 2016 DB.  Verify Admin using ? CurrentUser.  Execute: CurrentProject.Connection.Execute “GRANT SELECT ON MSysRelationships TO Admin”
to allow access to a protected table.  SAve DB and exit MS Access 2016.

Open MySQL Workbench 6.3 (6.3.9).  Select Database | Migration Wizard...
In Overview select "Start Migration".  In Source Selection use the following parameters -> Database System: Microsoft Access, Connection Method: ODBC Datasource, in the Parameters Tab, use the following -> featdb(Microsoft Access (*.mdb, *.accb)), Default Character Set: cp1252.  Select the Test Connection button.  Dialog box indicates "MySQL Workbench" and has a message of Test Source DBMS Connection  Connection succeeded.  Click OK and thren select the Next button (bottom right).  Target Selection is now chosen with Target RDBMS COnnectionParameters Stored Connection: Local Instance MySQL57, Connection Method:  Standard (TCP/IP).  Parameters Tab has Hostname: localhost, Port: 3306, Username: root.. Select Test Connection - succeeded as with Source.  Click the Next button and Fetch Schema List succeeds.  Click Next and the Connect to Source DBMS has a Red x in the radio button to the left of the text.  Log File:
Starting...
Connect to source DBMS...
- Connecting...
Connecting to ...
Opening ODBC connection to DSN=featdb...
Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_msaccess_re_grt.py", line 697, in connect
    return MsAccessReverseEngineering.connect(connection, password)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_msaccess_re_grt.py", line 148, in connect
    con = db_driver.connect(connection, password)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\workbench\db_driver.py", line 91, in connect
    connection = library.connect(connection_string, password=password)
pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\workbench\wizard_progress_page_widget.py", line 66, in run
    self.func()
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\migration_schema_selection.py", line 164, in task_connect
    if not self.main.plan.migrationSource.connect():
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\migration.py", line 201, in connect
    self._rev_eng_module.connect(self.connection, self.password or "")
SystemError: Error("('HY000', 'The driver did not supply an error!')"): error calling Python module function DbMsAccessRE.connect
ERROR: Error during Connect to source DBMS: Error("('HY000', 'The driver did not supply an error!')"): error calling Python module function DbMsAccessRE.connect
ERROR: Exception in task 'Connect to source DBMS': SystemError('Error("(\'HY000\', \'The driver did not supply an error!\')"): error calling Python module function DbMsAccessRE.connect',)

Failed

Suggested fix:
Pass the appropriate parameters that the workbench utilized in testing the connection to the Source (MS Access) DB into the Python script(s) which are failing to connect at the beginning of the Reveres Engineering step.

Attempted again using older mdb in MySQL workbench.  Tested with and without supplying administrator's uname pwd combination -> same results as with the accb - fails in Python file attempting to connect to the source DB, even after testing connection was successful.

Mr. Battula,
Have you made any progress researching this issue?  I have a very old database and wish to modernize without disrupting the design.  I will attempt to recreate the DB in the meantime.  Please let me know if this edge case is suitable for your energies in attempting to solve the python issue.  My opinion is that if the DB source and destination connections succeed in the workbench then the migration should complete without the error I found.
Regards,
-Michael

Hello Michael,

Thank you for the bug report.
Verified this behavior on MS Access 2016 64bit and MySQL Workbench in 6.3.9 version.

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_87647.JPG (image/jpeg, text), 169.10 KiB.

I have the same issue. 64bit Access 2016, ODBC and MySQL Workbench 6.3.9 64bit

Attachment: mysql_migration_error1.png (image/png, text), 272.61 KiB.

While this bugged me with Workbench 6.x, since installing 8.0 and Access ODBC drivers 14.x (64bit version), I can migrate Access 2016 Databases again. Testing in 6.x revealed that connection parameters were all passed correctly and you could make connections using those settings as long as the step where Workbench retrieved the schema information did not run.

Having this bug currently, on windows 7. I was able to successfully migrate one Access DB, but when trying to migrate another I encountered this bug. (workbench 8.0.12 and Access 2016, most recent drivers.)

Update: I regressed back to the 2010 Access Database Engine from 2016 and is migrating properly now.

Is there any resolution to this bug?  May run properly a few times but the error always returns

Using:
 - MySQL Workbench 8.0.17 (build 14960816).
 - Microsoft Access Driver (*.mdb, *.accdb) Version 14.00.7010.1000 (ACEODBC.DLL) (64 bit).

Have same problem that others have described here, when attempting to migrate an .ACCDB database.

Using same versions that Felix Pahlow describes as the solution to fix the issue for him--not fixing the issue for me though.

Would like to try Morgan Whittemore's solution without uninstalling the latest version of Access. Have installed old 2010 Access Database Engine but not sure how to use old ODBC drivers rather than the newer ones (that appear to be automatically selected in spite of having installed the old engine).

Can anyone point me in the direction for how to use the old drivers even though the latest Access be installed?

UPDATE

Have now tried migration using Microsoft Access Driver (*.mdb, *.accdb) Version 16.00.4513.1000 (ACEODBC.DLL) (64 bit) in conjunction with MySQL Workbench 8.0.17. Having same problem with both 14.00.4513.1000 (actually this version is the one for 2010 Access Database Engine) and 16.00.4513.1000 versions of the Access ODBC driver.

BTW, to use the 2010 Access Database Engine rather than the 2016 Access Database Engine, you just need to uninstall all engines installed, and then just selectively install the engine you want to use--don't need to uninstall Access.

Okay, I think I have semi-identified the issue.

It appears that Workbench is not disconnecting its connection for the source (Access) ODBC database, after it connects for the Fetch Schema List step. The close method for the connection is executed but it doesn't appear to work. So it appears it is an issue to do with the pyodbc Python package, since the close method is part of that package. The pyodbc package comes in the form of a compiled python file (extension '.pyd'). I tried using a later version of the package but Workbench wouldn't let me. It appears likely that Workbench is actually using a fairly dated version of the package. What is quite disturbing is that it appears that MySQL is using a version of Python that is soon to be deprecated (in January of next year), and that the Python version is quite dated. I'm not impressed with this, and perhaps it points to a more general problem that Workbench (and the community edition of MySQL) is not being levelly improved and maintained in a timely manner.

One of the easy ways to see that the issue is likely to do with the connection not being closed, is to execute the Fetch Schema List step, to then press the '< Back' button and then finally again press the 'Next >' button to re-execute the Fetch Schema List step. The second time you run the step, you will find that it will fail with the error. So performing the exact same step, in sequence, fails the second time round, even though it succeeds on the first time round.

I don't actually know Python, so it's been a bit of a struggle debugging these issues. Was tripped up by their indentation / spacing system when I was trying to debug this issue.

Does anyone know anything about Python and / or pyodbc, such that they can advise as to whether newer versions of these pieces of software can be used with Workbench, to get this migration wizard to work?

Thanks,

Mark

Have managed to resolve this bug.

So it appears that both the Fetch Schema List and Reverse Engineering steps include tasks that don't properly close the source database connection. Consequently, re-opening the connection causes problems (causes this bug in particular).

More specifically, closing the database connection is contained in a task labelled threaded task, whereas other similar tasks don't have the label 'threaded'. My analysis is that closing the connections is likely failing due to multi-threading being used for closing the connections. What might be happening is that the connection is being kept open / alive because the thread for closing the connection isn't dying on time or at all.

FIX

STEP 1
Around line 163 of the 'migration_schema_selection.py' file contained in the 'MySQL\MySQL Workbench 8.0 CE\modules' folder, find the following line contained in the __init__ procedure of the ReverseEngineerProgressView(WizardProgressPage) class:
        self.add_threaded_task(self.task_reveng, "Reverse engineer selected schemas", "Reverse Engineer Selected Schemas")
Replace line with the following line (notice that the main difference is that the 'threaded' text disappears):
        self.add_task(self.task_reveng, "Reverse engineer selected schemas", "Reverse Engineer Selected Schemas")

STEP 2
Around line 423 of the 'migration_source_selection.py' file contained in the 'MySQL\MySQL Workbench 8.0 CE\modules' folder, find the following line contained in the __init__ procedure of the FetchProgressView(WizardProgressPage) class:
        self.add_threaded_task(self.task_fetch_schemata, "Retrieve schema list from source", "Retrieve Schema List From Source")
Replace line with the following line (notice that the main difference is that the 'threaded' text disappears):
        self.add_task(self.task_fetch_schemata, "Retrieve schema list from source", "Retrieve Schema List From Source")

How do you edit the .py file? I keep getting errors when saving.

Hello Adam,

I used Notepad++ under Windows 8.1 to edit the Python files. I think the editor automatically asked me whether I wanted to re-open the Python files in Administrator mode, when I tried to save them. When in Administrator mode, the file saving worked.

Alternatively, you could try opening Windows Notepad with Administrator privileges and then try saving that way. There should be plenty of help on the internet letting you know how to do this.

Mark,

I tried notepad with admin privileges to no avail. However I did revert back to the Access Database Engine 2010 and it migrated the tables just fine. Unsure why, but that's all I needed as I'm using Access as our front end.

Worked for me the solution to update PY files in module folder and remove "threaded". Thanks. I suggest Oracle to update this in the next workbench release.

Bug #98641 marked as duplicate of this one.

I followed the instruction from Mark Fernandes. However, I got several syntax errors as the code seems to be using syntax that seems not valid in current version of Python. With my very basic knowledge of Python, I could not correct those. I would appreciate Oracle providing a working code in next release of MySQL.

Thank you Mark Fernandes, that fix worked for me. For those noting they can't save the file it is likely windows permissions blocking you from editing files in the Program Files directory. Run your text editor as Admin and it should allow the save.

I have this exact issue as well. I kept getting errors following the steps (I have ODBC 16 driver for MS Access). Details are also there 
https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-create-an-access-db-diagr...

Thank you Mark Fernandes, your solution worked for me.

After getting the error I also got an "unhandled exception get.dict key must be a string" when I tried again. However that has also been fixed by your solution.

I am using the current version of the Microsoft Access driver and MySQL workbench, I am surprised the bug hasnt been officially fixed.

Bug #101632 marked as duplicate of this one.

go to control panel /uninstall all access 2016 or 2010 engine
open ms access 
start migration it worked

https://bugs.mysql.com/bug.php?id=102093 marked as duplicate of this one.

Bug #106972 marked as duplicate of this one.

2024 this solution still work. thank you so much.

I am having this issue with 8.4 and see that it's not listed above. When I follow this solution, the migration wizard no longer shows up as an option. Instead I see two horizontal grey lines.Is there a different solution for this version or do I need to revert back to an older version?

Wanted to update that my version appears to be 8.0.36.