Problem In Retrieving Index Information Fron Sybase To Mysql Using Sql

[sridevi]

when i am accessing the data in sybase 12 installed on sunsparc workstation wih solaris as the OS using migration toolkit of sqlyog I found a problem like :

Error Code : 1072

Key column '' doesn't exist in table

(0 ms taken)

this the ddl generated by sqlyog

CREATE TABLE `test`.`CMLoginInfo`( `LoginRecId` int(10) NOT NULL ,

`LoginId` smallint(5) NOT NULL ,

`WorkStnId` tinyint(3) UNSIGNED NOT NULL ,

`LoginTime` timestamp(23) NOT NULL ,

`LogoutTime` timestamp(23) NOT NULL ,

`WorkStnMode` tinyint(3) UNSIGNED NOT NULL ,

PRIMARY KEY ( `LoginRecId` ) ,

UNIQUE KEY `CMLoginInfo_pk` (`LoginRecId` ASC ,“ DESC ) )Type=InnoDB

As u can see that in the last line of the ddl statement it refers to some column name '' with sort order as DESC.

this is the table schema of the cmmogininfo table when quried at sybase.

Name Owner Type Data_located_on_segment When_created

CMLoginInfo dbo user table default Feb 21 2007 11:14AM

Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity

LoginRecId int 4 NULL NULL 0 NULL NULL 0

LoginId smallint 2 NULL NULL 0 NULL NULL 0

WorkStnId tinyint 1 NULL NULL 0 NULL NULL 0

LoginTime datetime 8 NULL NULL 0 NULL NULL 0

LogoutTime datetime 8 NULL NULL 0 CMLoginInf_Logout_1081054887 NULL 0

WorkStnMode tinyint 1 NULL NULL 0 NULL NULL 0

index_name = CMLoginInfo_pk

index_description = clustered, unique located on default

index_keys = LoginRecId

index_max_rows_per_page = 0

index_fillfactor = 0

index_reservepagegap = 0

keytype = primary

object = CMLoginInfo

related_object = none

object_keys = LoginRecId, *, *, *, *, *, *, *

related_keys = *, *, *, *, *, *, *, *

AS U SEE NO WHERE THE SORT ORDER OF THE INDEX IS MENTIONED AS ASC OR DESC.

BUT THE ddl GENERATED BY MIGRATION TOOLKIT MENTIONS THE SORT ORDER AS asc AND ALSO MANTIONS A COL '' WITH SORT ORDER AS desc .

IT AWS SUGGESTED BY ADARSH OF WEBYOG THAT THIS IS BECAUSE I AM USING DATADIRECT DRIVER FOR SYBASE. BUT THIS PROBLEM EXISTS FOR SYBASE ODBC DRIVER ALSO.

HENCE I RAN THE THE FOLLOWING DDL STATEMENT WITH OUT SORT ORDER DESC IN THE QUERY WINDOW OF SQLYOG AND THE TABL EWAS CREATED.

CREATE TABLE `test`.`CMLoginInfo`( `LoginRecId` int(10) NOT NULL ,

`LoginId` smallint(5) NOT NULL ,

`WorkStnId` tinyint(3) UNSIGNED NOT NULL ,

`LoginTime` timestamp(23) NOT NULL ,

`LogoutTime` timestamp(23) NOT NULL ,

`WorkStnMode` tinyint(3) UNSIGNED NOT NULL ,

PRIMARY KEY ( `LoginRecId` ) ,

UNIQUE KEY `CMLoginInfo_pk` (`LoginRecId` ASC ) )Type=InnoDB

PLEASE REPL;Y SOON.

this PROBLEM I INITIALLY POSTED UNDER ODBC IMPORT AND CREATED A TICKET #2614 . FOR THE LAST 3 DAYS I GOT NO REPLY .

ITS URGENT

[adarsh]

Hi,

We have installed that Data Direct ODBC driver and we tried to import we got same error, it is importing tables without keys.

After this we tried using ASE 15.0 ODBC driver it is importing all the tables with keys.

So this is a driver issue, please try to find someother driver or you can do one thing, you install that ASE 15.0 in your Win2k system with ODBC driver through custom install and then create a DSN it will work.

Adarsh

[sridevi]

hi

i tried on the internet and got two types of drivers one with ase15 and the other one is pcclient 1252 from sybase sites

1. I installed Adaptive Server Enterprise 15 by custom install and also the Adaptive Server Enterprise odbc driver. I created a datasource called system121 and tested the connection. which gave the message “connection succeeded”. but when i used the driver in the migration tool then no tables from sybase database were listed.

2. I had also downloaded pcclient1252 from the net. the sybase driver that comes with it is Sybase Ase odbc driver . with this driver the tables from the source sybase database are listed. but I am able to import the tables without any primary key or indexes.

did u try retrivig data from sybase on a workststion to mysql on a pc as i mentioned earlier to get the data? Also my source database is ase 12. the drivers i use either ase 12.5 .2 or ase 15 . does this create any problem

more over

as u see the error

Sql:CREATE TABLE `ccotrial`.`names`( `ssn` varchar(9) NOT NULL , `name` varchar(20) NOT NULL , PRIMARY KEY ( `ssn` ) , UNIQUE KEY `names_pk` (`ssn` ASC ,“ DESC ) )Type=InnoDB

is there with the third party driver as well as the sybase odbc drivers that come with sybase products.

so please analyse the problem with ur seniors.

I am sending u the listing of the table schema as well as data as an attachment

Could it be because my source database is sybase 12 and the ase driver is fron sybase ase 15?

[peterlaursen]

We have no opton to test with that specific version and not on Solaris either.

Could you arrange remote acces to some (silly) database?

[sridevi]

peterlaursen wrote on Apr 13 2007, 05:29 PM:

We have no opton to test with that specific version and not on Solaris either.

Could you arrange remote acces to some (silly) database?

First,Tell me is sqlyog capable of replication of data from sybase to mysql. By replication i mean what ever updates occur at sybase have to be executed at mysql . All unchanged data need not be written again and again. If so which versions of sybase and mysql are supported.

second, Could u please visit our organisation which is in Hydrabad to analyse the problem.

reply soon

[peterlaursen]

1)

The Migration tool is a Migration Tool – not a synchronisation tool. With the use of triggers and where-conditions it can be used for synchronisation.  It is all explained in the doucmentation.

2)

We do not offer 'on the spot' support as you suggest.  Both our sales and support concepts are 100% based on the Internet.

[sridevi]

peterlaursen wrote on Apr 17 2007, 07:08 PM:

1)

The Migration tool is a Migration Tool – not a synchronisation tool. With the use of triggers and where-conditions it can be used for synchronisation.  It is all explained in the doucmentation.

2)

We do not offer 'on the spot' support as you suggest.  Both our sales and support concepts are 100% based on the Internet.

Please read my first two posts pertaing to this topic dated 11th apr and 13th apr and tell me whether the extraction of maetadata or the generation of the DDL statements are done by ODBC driver for sybase which i am using or by the migration toolkit software. identify the problem area.

please reply soon

[peterlaursen]

We asked for remote access.  We have no option to create a setup with exactly the same OS and Sybase version.

We need to try and import ourselves from a debugging environment.

We have read your posts of course, but words alone won't do anymore.  We need to debug this if we shall do any progress.  But of course the ODBC driver is supposed to send the metadata.  But maybe there is an issue between the server and driver version.  We cannot tell without debugging the stream of data.

If you have a licensed Sybase, can't you contact Sybase and asked for the 'perfect driver' for this version?

[Author]

Posts