MySQL Bugs: #70731: SQL Anywhere 9 to Mysql Migration Problem

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:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	Migration Problem

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

Hi Aji!  Did you ever find a way around this?  I'm having a similar issue.

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

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

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"""

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...

Confirmed

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.

Please install the ASA 16 there you can find the option that upgrade from  earlier version of asa like that.please check it

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.