Bug #70731 SQL Anywhere 9 to Mysql Migration Problem
Submitted: 26 Oct 2013 13:36 Modified: 26 Feb 2014 13:31
Reporter: Aji Mon Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S2 (Serious)
Version:workbench 6.0 OS:Microsoft Windows (XP)
Assigned to: CPU Architecture:Any
Tags: Migration Problem

[26 Oct 2013 13:36] Aji Mon
Description:
Reverse engineer selected schemata error..

Error Log shown Below....
==========================

Starting...

Connect to source DBMS...

- Connecting...

Connecting to ...

Opening ODBC connection to DSN=wms...

Connected

Connect to source DBMS done

Reverse engineer selected schemata....

Reverse engineering DBA from Wms

- Reverse engineering catalog information

Traceback (most recent call last):

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_sqlanywhere_re_grt.py", line 460, in reverseEngineer

    return SQLAnywhereReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_sqlanywhere_re_grt.py", line 169, in reverseEngineer

    catalog = super(SQLAnywhereReverseEngineering, cls).reverseEngineer(connection, '', schemata_list, context)

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_generic_re_grt.py", line 228, in reverseEngineer

    catalog = cls.reverseEngineerCatalog(connection, catalog_name)

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_generic_re_grt.py", line 379, in reverseEngineerCatalog

    cls.reverseEngineerUserDatatypes(connection, catalog)

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_sqlanywhere_re_grt.py", line 41, in wrapped_method

    res = method(cls, connection, *args)

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_sqlanywhere_re_grt.py", line 194, in reverseEngineerUserDatatypes

    for type_name, sql_definition, parent_type in cls.execute_query(connection, query):

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\db_generic_re_grt.py", line 76, in execute_query

    return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)

pyodbc.ProgrammingError: ('42S22', "[42S22] [Sybase][ODBC Driver][Adaptive Server Anywhere]Column not found: Column 'base_type_str' not found (-143) (SQLExecDirectW)")

Traceback (most recent call last):

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\workbench\wizard_progress_page_widget.py", line 192, in thread_work

    self.func()

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\migration_schema_selection.py", line 160, in task_reveng

    self.main.plan.migrationSource.reverseEngineer()

  File "C:\Program Files\MySQL\MySQL Workbench 6.0 CE\modules\migration.py", line 335, in reverseEngineer

    self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)

SystemError: ProgrammingError("('42S22', "[42S22] [Sybase][ODBC Driver][Adaptive Server Anywhere]Column not found: Column 'base_type_str' not found (-143) (SQLExecDirectW)")"): error calling Python module function DbSQLAnywhereRE.reverseEngineer

ERROR: Reverse engineer selected schemata: ProgrammingError("('42S22', "[42S22] [Sybase][ODBC Driver][Adaptive Server Anywhere]Column not found: Column 'base_type_str' not found (-143) (SQLExecDirectW)")"): error calling Python module function DbSQLAnywhereRE.reverseEngineer

Failed

How to repeat:
Migrate to mysql from ASA 9
[19 Jan 2014 4:09] David Matheson
Hi Aji!  Did you ever find a way around this?  I'm having a similar issue.
[24 Jan 2014 0:38] Alfredo Kojima
Please try the following:
1- find the db_sqlanywhere_re_grt.py file and open it in an editor
2- go to line 189 and change the following line:

        query = """SELECT UPPER(st.type_name), UPPER(base_type_str), UPPER(sd.domain_name )

to

        query = """SELECT UPPER(st.type_name), UPPER(st.base_type_str), UPPER(sd.domain_name )

3- delete the db_sqlanywhere_re_grt.pyc file if it exists
4- retry migration
[27 Jan 2014 12:08] nirmal jingar
Hello,

I am also facing the similar issue. I am trying to migrate from sql anywhere 11 to MySQL. It is showing me the following error. 
@Alfredo: I tried your solution, but "SYSUSERTYPE st" table does not have the base_type_str column.

SystemError: ProgrammingError("('42S22', "[42S22] [Sybase][ODBC Driver][SQL Anywhere]Column 'base_type_str' not found (-143) (SQLExecDirectW)")"): error calling Python module function DbSQLAnywhereRE.reverseEngineer

ERROR: Reverse engineer selected schemata: ProgrammingError("('42S22', "[42S22] [Sybase][ODBC Driver][SQL Anywhere]Column 'base_type_str' not found (-143) (SQLExecDirectW)")"): error calling Python module function DbSQLAnywhereRE.reverseEngineer

Failed
[28 Jan 2014 9:53] nirmal jingar
Got the solution!
"base_type_str" column in introduced in ver 12. for previous versions you need to make your own base_type_str column. use below sample query to generate this column, replace it with line 189 query in db_sqlanywhere_re_grt.py. Remove db_sqlanywhere_re_grt.pyc file and start mysql workbench.

query = """SELECT UPPER(st.type_name), case when st.type_name = 'money' then UPPER(sd.domain_name+'(19,4)')
		when st.type_name = 'smallmoney' then UPPER(sd.domain_name+'(10,4)')
		when st.type_name = 'sysname' then UPPER(sd.domain_name+'(30)') 
		when st.type_name = 'uniqueidentifierstr' then UPPER(sd.domain_name+'(36)') 
		else UPPER(sd.domain_name) end AS base_type_str,
		UPPER(sd.domain_name ) FROM SYSUSERTYPE st LEFT JOIN SYSDOMAIN sd ON st.domain_id=sd.domain_id"""
[3 Feb 2014 17:57] Aji Mon
Dear all,

I found one way to solve the issue.I Migrated my ASA 9 to ASA 16 and i used workbench 6..please try this....ASA 16 developer version[free licence from Sybase] is enough for this...
[11 Feb 2014 14:49] Milosz Bodzek
Confirmed
[26 Feb 2014 1:55] David Matheson
Tried Nirmal's suggestion.  Now it is complaining about SYSTAB not existing.  Will try replacing with SYSTABLE.  I am on ASA 8.0.2.4308 (at least, that's what SELECT @@VERSION reports).  Not an option to migrate to ASA 16 for me.
[26 Feb 2014 13:31] Aji Mon
Please install the ASA 16 there you can find the option that upgrade from  earlier version of asa like that.please check it
[10 Jul 13:50] Mauricio Rivas
I can also confirm that by migrating my Sybase Adaptive Server Anywhere 9 database to SAP SQL Anywhere 17, the migration works perfectly! First step to finally rid myself of proprietary software.