Bug #11359 INFORMATION_SCHEMA.ROUTINES doesn't exist in source
Submitted: 15 Jun 2005 19:36 Modified: 26 Aug 2005 9:43
Reporter: Keith Blackwell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S1 (Critical)
Version:1.0.10 OS:Windows (Windows2000)
Assigned to: Michael G. Zinner CPU Architecture:Any

[15 Jun 2005 19:36] Keith Blackwell
Description:
Trying to migrate database from SQL Server 7.0 to mySql 5.0.6 beta.
I have verified username/password, IP/Port of SQL Server, am able to connect via ODBC just fine.
After filling in source and destination connection info, click next, get error.  Viewing log shows sql, which is trying to get data from INFORMATION_SCHEMA.ROUTINES object; error says invalid object.  I checked, and indeed yes, there is no such object on the SQL Server. Here's body of message log:
----------------------------------
Connecting to source database and retrieve schemata names.
Initializing JDBC driver ... 
Driver class net.sourceforge.jtds.jdbc.Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://10.0.0.16:1433;user=sa;password=
Fetching schemata list.
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA
The list of schema names could not be retrieved (error: 0).
Invalid object name 'INFORMATION_SCHEMA.ROUTINES'.
----------------------------------

How to repeat:
go through steps to migrate from MS SQL Server, provide info, etc.

Suggested fix:
?
[6 Jul 2005 4:48] Jorge del Conde
Hi

Can you please give us step by step instructions that tell us how to reproduce this bug ?  I was unable to reproduce it using a random database in SQL2k
[6 Jul 2005 18:31] Keith Blackwell
Hi
Please note, this is Sql Server 7.0, not 2000.
The steps to reproduce this are to launch the migration toolkit and follow instructions to import from MS Sql Server.  I do not have access to the system at this time, so I can't step through it right now, but can later.
Thanks
Keith
[11 Jul 2005 17:13] Keith Blackwell
Hi
Here are the exact steps taken.
1. Launch Migration toolkit through shortcut
2. At the "Welcome to...." screen, initialized runtime system and initialized java loader are both checked, so I click Next at the bottom...
3. Direct Migration is checked... click Next at the bottom...
4. Source.... Database System dropdown... choose MS SQL Server
5. Hostname... I provide IP to server... 10.0.0.16, port 1433, Username and password are known good Administrator 
6. Target database... localhost, username/password, etc provided
7. Click Next at bottom... it starts doing whatever, then Connecting to Servers page comes up, red X's next to Connecting to the source database system and Retrieve schema information from source database system
8. Click Advanced at the bottom and see body of error as described previously.
[12 Jul 2005 4:46] Jorge del Conde
Thanks for your bug report.  Verified using your last comment as a reproducible test case.
[19 Jul 2005 22:29] Keith Blackwell
Hi
I have discovered that this also occurs in the latest version, 1.0.10.
Here's the body of the "Message Log":
Connecting to source database and retrieve schemata names.
Initializing JDBC driver ... 
Driver class MS SQL JDBC Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://10.0.0.16:1433/DIALYSIS;user=sa;password=xxxxxx
Fetching schemata list.
SELECT TABLE_SCHEMA AS SCHEMANAME,  max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA
The list of schema names could not be retrieved (error: 0).
Invalid object name 'INFORMATION_SCHEMA.ROUTINES'.
Details: 
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2708)
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2150)
net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:587)
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:323)
net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1041)
com.mysql.grt.modules.ReverseEngineeringMssql.getSchemata(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

I followed mostly the same steps as described for version 1.0.7; the gui is a little different though (i.e., port number pre-populated w/1433 in 1.0.10).

FYI.
Thanks
Keith
[26 Jul 2005 2:30] Ryan Lathouwers
This is also happening in 1.0.11 (same error message as above).  Trying to migrate from MSSQL7 -> MySql 4.1.x on Windows 2003 Server.
[2 Aug 2005 15:34] Michael G. Zinner
We did not yet have the chance to test against a SQL Server 7, we only had a SQL Server 2k. We will address this later, when we get access to a SQL Server 7.
[8 Aug 2005 9:54] Andreas Götz
I have the same problem connecting with MT 1.0.12 to Sybase's SQL Anywhere database using JDBC-ODBC bridge:

Driver class Generic Jdbc
Opening connection ... 
Connection jdbc:odbc:SEAW Samp Db default instance;user=SADMIN;password=SADMIN
The list of schema names could not be retrieved (error: 0).
Column not found
Details: 
sun.jdbc.odbc.JdbcOdbcResultSet.findColumn(Unknown Source)
sun.jdbc.odbc.JdbcOdbcResultSet.getString(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.getSchemata(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)
[9 Aug 2005 16:18] Andreas Götz
I've verified that the same thing is happening on Sybase when connecting and DBA:

Connecting to source database and retrieve schemata names.
Initializing JDBC driver ... 
Driver class Generic Jdbc
Initializing JDBC driver ... 
Driver class Generic Jdbc
Opening connection ... 
Connection jdbc:odbc:SEAW Samp Db default instance;user=DBA;password=SQL
The list of schema names could not be retrieved (error: 0).
Column not found
Details: 
sun.jdbc.odbc.JdbcOdbcResultSet.findColumn(Unknown Source)
sun.jdbc.odbc.JdbcOdbcResultSet.getString(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.getSchemata(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)
[21 Aug 2005 13:16] Gary Chernega
Here is some information that may help the resolver.
Obtained from master database on SQL Server 7 running Windows 2k using:
   SELECT * FROM INFORMATION_SCHEMA.TABLES

TABLE_CATALOG  TABLE_NAME                                   TABLE_TYPE
------------------------------------------------------------------------
master         dbo                 MSreplication_options    BASE TABLE
master         dbo                 spt_committab            BASE TABLE
master         dbo                 spt_datatype_info        BASE TABLE
master         dbo                 spt_datatype_info_ext    BASE TABLE
master         dbo                 spt_fallback_db          BASE TABLE
master         dbo                 spt_fallback_dev         BASE TABLE
master         dbo                 spt_fallback_usg         BASE TABLE
master         dbo                 spt_monitor              BASE TABLE
master         dbo                 spt_provider_types       BASE TABLE
master         dbo                 spt_server_info          BASE TABLE
master         dbo                 spt_values               BASE TABLE
master         dbo                 sysalternates            VIEW
master         dbo                 sysconstraints           VIEW
master         dbo                 syslogins                VIEW
master         dbo                 sysoledbusers            VIEW
master         dbo                 sysremotelogins          VIEW
master                             syssegments              VIEW
master         INFORMATION_SCHEMA  CHECK_CONSTRAINTS        VIEW
master         INFORMATION_SCHEMA  COLUMN_DOMAIN_USAGE      VIEW
master         INFORMATION_SCHEMA  COLUMN_PRIVILEGES        VIEW
master         INFORMATION_SCHEMA  COLUMNS                  VIEW
master         INFORMATION_SCHEMA  CONSTRAINT_COLUMN_USAGE  VIEW
master         INFORMATION_SCHEMA  CONSTRAINT_TABLE_USAGE   VIEW
master         INFORMATION_SCHEMA  DOMAIN_CONSTRAINTS       VIEW
master         INFORMATION_SCHEMA  DOMAINS                  VIEW
master         INFORMATION_SCHEMA  KEY_COLUMN_USAGE         VIEW
master         INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS  VIEW
master         INFORMATION_SCHEMA  SCHEMATA                 VIEW
master         INFORMATION_SCHEMA  TABLE_CONSTRAINTS        VIEW
master         INFORMATION_SCHEMA  TABLE_PRIVILEGES         VIEW
master         INFORMATION_SCHEMA  TABLES                   VIEW
master         INFORMATION_SCHEMA  VIEW_COLUMN_USAGE        VIEW
master         INFORMATION_SCHEMA  VIEW_TABLE_USAGE         VIEW
master         INFORMATION_SCHEMA  VIEWS                    VIEW

My error information appeared slightly different, so here it is:
NOTE: Connection string server info, database name, userid, and password removed, but were correct.

Connecting to source database and retrieve schemata names.
Initializing JDBC driver ... 
Driver class MS SQL JDBC Driver
Opening connection ... 
Connection jdbc:jtds:sqlserver://(server_name):1433/(database_name);user=(user_name);password=(password)
Fetching schemata list.
SELECT TABLE_SCHEMA AS SCHEMANAME,  max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA
The list of schema names could not be retrieved (error: 0).
Invalid object name 'INFORMATION_SCHEMA.ROUTINES'.
Details: 
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2708)
net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2150)
net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:587)
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:323)
net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1041)
com.mysql.grt.modules.ReverseEngineeringMssql.getSchemata(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)
[21 Aug 2005 13:31] Gary Chernega
Also found this (INFORMATION_SCHEMA.ROUTINES isn't available pre-SQL2k):
from here - http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-159.html

(NOTE section on what query to use for SQL Server 7)

INFORMATION_SCHEMA.ROUTINES view

Requirements: Microsoft SQL Server 2000 or later

You can use the INFORMATION_SCHEMA.ROUTINES view to retrieve information about stored procedures. This view contains one row for each stored procedure accessible to the current user in the current database.

For example, the following query returns owner, name and definition text of stored procedures in the current database:

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'

The INFORMATION_SCHEMA.ROUTINES view was introduced in SQL Server 2000. This view is based on the sysobjects, syscomments and other system tables.

To retrieve the same information about stored procedures from a Microsoft SQL Server 7.0 database, you can use the following query:

select su.name, so.name, sc.text
from sysobjects so, syscomments sc, sysusers su
where xtype='P' and so.id=sc.id and so.uid=su.uid
order by su.name, so.name, sc.colid

The query above may return several rows per each stored procedure if the definition text is longer than 4000 characters.
[21 Aug 2005 15:55] Gary Chernega
**** WORKAROUND v1.0 ****

  I believe this workaround should resolve most issues. I was able to migrate a database and all its data. Only getting errors on the stored procedures.

IMPORTANT UPDATE: After I did this I began having issues with the MySQL service crashing (MySQL v5.0.11beta, WinXP). Appears to be only with the database instance I migrated. Investigating.

  Below is the SQL to create a INFORMATION_SCHEMA.ROUTINES view that meets the requirements of the migration tool. I run it from the Query Analyzer on each database as I migrate. It only needs to be run once for each database.
  In order to remove the view (if you so wish), you need to have AdHoc system table editing enabled, and change sysobjects uid entry to the uid for dbo. You can then DROP VIEW RESOURCES

if exists (select * from sysobjects where id = object_id(N'[INFORMATION_SCHEMA].[ROUTINES]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [INFORMATION_SCHEMA].[ROUTINES]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE VIEW dbo.ROUTINES
AS
SELECT DB_NAME() AS ROUTINE_CATALOG, 
    su.name AS ROUTINE_SCHEMA, 
    so.name AS ROUTINE_NAME, 
    'PROCEDURE' AS ROUTINE_TYPE, 
    so.name AS MODULE_NAME, 
    sc.text AS ROUTINE_DEFINITION
FROM sysobjects so, syscomments sc, sysusers su
WHERE xtype = 'P' AND so.id = sc.id AND so.uid = su.uid

GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

EXEC sp_changeobjectowner 'ROUTINES', 'INFORMATION_SCHEMA'
GO
[21 Aug 2005 17:11] Michael G. Zinner
Hi. Thanks for you valuable informations. I have modified the java code to not use the ROUTINE view.

Please find a patch at
 ftp://ftp.mysql.com/pub/mysql/download/mysql-migration-toolkit-patch2-1.0.13-rc-win32.zip

Extract the zip file into [INSTALLDIR]\java\com\mysql\grt\modules, overwriting the existing mssql files there.

Please note that you have to use this patch for the version 1.0.13, not 1.0.10 as mentioned in the bug metadata.

It would be nice if you could report if that patch works for you.

Thanks,
Mike
[22 Aug 2005 1:04] Gary Chernega
My MySQL server continually crashes after doing my workaround. So I can't try your patch yet. It crashes anytime I open the Query Browser or Administrator. Specifically when it refreshes the table schemas. I've been looking for a way to reset the system tables (I can lose any user tables as there aren't any).

  As soon as I can get MySQL stable again, I will try your patch to the Java code.

Here is a log file:
..\..\bin\mysqld, Version: 5.0.11-beta-log. started with:
Tcp port: 3306  Unix socket: MySQL
Time                 Id Command    Argument
050821 14:30:12	      1 Connect     root@localhost on 
		      1 Query       SET SESSION interactive_timeout=1000000
		      1 Query       SELECT @@sql_mode
		      1 Query       SET SESSION sql_mode=''
		      1 Query       SET NAMES utf8
		      1 Init DB     test
		      1 Init DB     test
050821 14:30:13	      1 Query       show databases
		      1 Init DB     test
		      1 Query       SHOW FULL TABLES
		      1 Query       SHOW COLUMNS FROM `ads`
		      1 Query       SHOW COLUMNS FROM `test`
		      1 Query       SHOW COLUMNS FROM `userids`
		      1 Init DB     test
		      1 Init DB     test
		      1 Query       SHOW PROCEDURE STATUS
[22 Aug 2005 3:08] Gary Chernega
Since the MySQL server instance was crashing, I removed and reinstalled MySQL Server.

  I couldn't find any way to reset it (I did run the instance reinitialization (MySQLInstanceConfig.exe), but it still crashed everytime I tried to view schema information. So I DO NOT recommend following my Workaround listed above. Although it does get the table and data, it causes MySQL to crash.. I suspect it may have copied too much information (system tables or something)

  I DO suggest installing the patch Michael put above. It copied the tables and data over to MySQL. It did not however copy over any of the stored procedures. They need BEGIN END statements added, the CREATE statement needs to be modified, etc.. so more support for automatically doing that would be appreciated as some of my databases have hundreds of stored procedures.

  I will continue to work with the patched migration tool.. something called sleep seems to need me. :-)

A big thanks to Michael for the patch.. just needs the Stored Procedure correction on the fly and it'd be perfect.
[23 Sep 2005 14:16] Thorsten Rottgardt
Hello, 

if tried to migrate to MySQL5.0 with the Migration toolkit 1.018rc from a MSSQL7 but I got the same error "SELECT COUNT(*) AS NUM FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA=?
Stored procedures cannot be fetched.
Ungültiger Objektname 'INFORMATION_SCHEMA.ROUTINES'." Do I have to use the Version 1.0.10 with the patch mentioned in here ? Many thanx Thorsten
[14 Mar 2008 6:07] Ashutosh kumar
i am using mysql(5.0) stored procedure in asp.net. i face a error message INFORMATION_SCHEMA.ROUTINES doesn't exist. when i execute inline query it runs fine.please help me.