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