forums › forums › SQLyog › Sync tools, Migration, Scheduled Backup and Notifications › Primary Key Or Constraints Problem
- This topic is empty.
-
AuthorPosts
-
-
March 23, 2007 at 6:16 am #10255srideviMember
I had used the sqlyog's migration toolkit's evaluation of version 5.23 and 5.56 as well to copy data from sybase 12.0 to mysql 5.0.but i could get only tables without any primary keys and indexes not the ones with primary key or constraints. pl help me out.
sjasessionfile contents
–
–
ERROR: 1072, Key column '' doesn't exist in table Table:Assoc Sql:CREATE TABLE `cco3`.`Assoc`( `CCTrackNumber` smallint(5) NOT NULL , `GivenToAdjCC` tinyint(3) UNSIGNED NOT NULL , `AdjCCTrackNumber` smallint(5) NOT NULL , `CtLocOneLineFmt` char(15) NOT NULL , `ctLocLatitude` char(8) NOT NULL , `ctLocLongitude` char(8) NOT NULL , `CtLocElevation` smallint(5) NOT NULL , `CTRefineFlag` tinyint(3) UNSIGNED NOT NULL , `CTRefLocPosXCoord` int(10) NOT NULL , `CTRefLocPosYCoord` int(10) NOT NULL , `CTRefLocElevation` smallint(5) NOT NULL , `CTMovementField` tinyint(3) UNSIGNED NOT NULL , `VelocityVectorX` int(10) NOT NULL , `VelocityVectorY` int(10) NOT NULL , `CTAssocConfidence` tinyint(3) UNSIGNED NOT NULL , `CTThreatValue` tinyint(3) UNSIGNED NOT NULL , `CTTHAnalPriority` tinyint(3) UNSIGNED NOT NULL , `CTAnalysisField` tinyint(3) UNSIGNED NOT NULL , `CTActivePassive` tinyint(3) UNSIGNED NOT NULL , PRIMARY KEY ( `CCTrackNumber` ) , UNIQUE KEY `Assoc_pk` (`CCTrackNumber` ASC ,“ DESC ) )Engine=InnoDB ERROR: 1072, Key column '' doesn't exist in table Table:CMCCModeInfo Sql:CREATE TABLE `cco3`.`CMCCModeInfo`( `WorkStn1Mode` tinyint(3) UNSIGNED NOT NULL , `WorkStn2Mode` tinyint(3) UNSIGNED NOT NULL , `TimeStamp` timestamp(23) NOT NULL , PRIMARY KEY ( `TimeStamp` ) , UNIQUE KEY `CMCCModeInfo_pk` (`TimeStamp` ASC ,“ DESC ) )Engine=InnoDB sp_help assoc
2>
Name Owner
Type
Assoc dbo
user table
(1 row affected)
Data_located_on_segment When_created
default Feb 21 2007 11:14AM
Column_name Type Length Prec
Scale Nulls Default_name
Rule_name Identity
—-
CCTrackNumber smallint 2 NULL
NULL 0 NULL
Assoc_TrackNumber_check 0
GivenToAdjCC tinyint 1 NULL
NULL 0 NULL
NULL 0
AdjCCTrackNumber smallint 2 NULL
NULL 0 NULL
Assoc_AdjCC_Trk_No_check 0
CtLocOneLineFmt char 15 NULL
NULL 0 NULL
NULL 0
ctLocLatitude char 8 NULL
NULL 0 NULL
NULL 0
ctLocLongitude char 8 NULL
NULL 0 NULL
NULL 0
CtLocElevation smallint 2 NULL
NULL 0 NULL
Assoc_CtLocElevation_check 0
CTRefineFlag tinyint 1 NULL
NULL 0 NULL
Assoc_CTRefineFlag_check 0
CTRefLocPosXCoord int 4 NULL
NULL 0 NULL
NULL 0
CTRefLocPosYCoord int 4 NULL
NULL 0 NULL
NULL 0
CTRefLocElevation smallint 2 NULL
NULL 0 NULL
Assoc_CTRefLocElevation_check 0
CTMovementField tinyint 1 NULL
NULL 0 NULL
Assoc_CTMovementField_check 0
VelocityVectorX int 4 NULL
NULL 0 NULL
NULL 0
VelocityVectorY int 4 NULL
NULL 0 NULL
NULL 0
CTAssocConfidence tinyint 1 NULL
NULL 0 NULL
Assoc_CTAssocConfidence_check 0
CTThreatValue tinyint 1 NULL
NULL 0 NULL
Assoc_CTThreatValue_check 0
CTTHAnalPriority tinyint 1 NULL
NULL 0 Assoc_CTTHAn_32003145
Assoc_CTTHAnalPriority_check 0
CTAnalysisField tinyint 1 NULL
NULL 0 NULL
Assoc_CTAnalysisField_check 0
CTActivePassive tinyint 1 NULL
NULL 0 NULL
NULL 0
index_name index_description
index_keys
index_max_rows_per_page index_fillfactor index_reservepagegap
Assoc_pk clustered, unique located on default
CCTrackNumber
0 0 0
(1 row affected)
keytype object related_object
object_keys
related_keys
primary Assoc — none —
CCTrackNumber, *, *, *, *, *, *, *
*, *, *, *, *, *, *, *
(1 row affected)
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock
scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with
allpages lock scheme.
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
1 0 0 0 0
concurrency_opt_threshold
0
(return status = 0)
1> sp_help cmccmodeingo
2>
Msg 17461, Level 16, State 1:
Server 'WS2SYB', Procedure 'sp_help', Line 197:
Object does not exist in this database.
(return status = 1)
1> sp_help cmccmodeinfo
2>
Name Owner
Type
CMCCModeInfo dbo
user table
(1 row affected)
Data_located_on_segment When_created
default Feb 21 2007 11:14AM
Column_name Type Length Prec Scale Nulls Default_name
Rule_name Identity
—-
WorkStn1Mode tinyint 1 NULL NULL 0 NULL
NULL 0
WorkStn2Mode tinyint 1 NULL NULL 0 NULL
NULL 0
TimeStamp datetime 8 NULL NULL 0 NULL
NULL 0
index_name index_description
index_keys
index_max_rows_per_page index_fillfactor index_reservepagegap
CMCCModeInfo_pk clustered, unique located on default
TimeStamp
0 0 0
(1 row affected)
keytype object related_object
object_keys
related_keys
primary CMCCModeInfo — none —
TimeStamp, *, *, *, *, *, *, *
*, *, *, *, *, *, *, *
(1 row affected)
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock
scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with
allpages lock scheme.
exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
1 0 0 0 0
concurrency_opt_threshold
0
(return status = 0)
1>
Here is the sample data of the two tables
1> select * from assoc
2>
CCTrackNumber GivenToAdjCC AdjCCTrackNumber CtLocOneLineFmt ctLocLatitude
ctLocLongitude CtLocElevation CTRefineFlag CTRefLocPosXCoord
CTRefLocPosYCoord CTRefLocElevation CTMovementField VelocityVectorX
VelocityVectorY CTAssocConfidence CTThreatValue CTTHAnalPriority
CTAnalysisField CTActivePassive
1 0 0 56 K/6 597732 0174253
0781604 0 0 0
0 0 0 0
0 9 7 0
1 6
2 0 0 56 K/9 904769 0174501
0783328 0 0 0
0 0 0 0
0 9 5 0
1 6
3 0 0 56 K/13 157870 0175038
0784744 0 0 0
0 0 0 0
0 9 2 0
1 6
4 0 0 56 K/6 755672 0173941
0782504 0 0 0
0 0 0 0
0 9 4 0
0 6
5 0 0 56 K/9 026799 0174643
0784021 0 0 0
0 0 0 0
0 9 6 0
1 6
(5 rows affected)
1> select * from cmccmodeinfo
2>
WorkStn1Mode WorkStn2Mode TimeStamp
255 112 Mar 14 2007 11:39AM
255 112 Mar 14 2007 11:44AM
255 112 Mar 14 2007 11:47AM
255 112 Mar 14 2007 11:51AM
255 112 Mar 15 2007 10:04AM
255 112 Mar 16 2007 9:04AM
255 112 Mar 16 2007 12:28PM
255 112 Mar 16 2007 12:30PM
255 112 Mar 16 2007 2:19PM
255 112 Mar 20 2007 8:57AM
255 112 Mar 21 2007 9:05AM
(11 rows affected)
1>
-
March 23, 2007 at 6:50 am #23647peterlaursenParticipant
This was the correct place to post. so I'll delete th other post.
It is most practical to discuss only in one place.
We had quite a lot of new hardware recently and reisntalled most computers.
We therefore do not have our old Sybase install functional at the moment.
I'll have to ask you to have a little patience while we are setting it up again.
-
March 23, 2007 at 7:10 am #23648srideviMember' wrote on 'Mar:
This was the correct place to post. so I'll delete th other post.
It is most practical to discuss only in one place.
We had quite a lot of new hardware recently and reisntalled most computers.
We therefore do not have our old Sybase install functional at the moment.
I'll have to ask you to have a little patience while we are setting it up again.
[/quote
Dear Mr peterlaursen,
thank u
any help is appriciated
-
March 23, 2007 at 9:02 am #23649peterlaursenParticipant
we will try our best.
But it probably will not be before Monday that we have everything ready for trying to reproduce the issue
-
March 28, 2007 at 9:53 am #23650srideviMember
somebody please solve this problem.
its urgent.
-
March 28, 2007 at 2:11 pm #23651adarshMember
We have now installed Adaptive Enterprise Server 15.0 and the ODBC driver that ships with the installer. A fast setup with two small tables gave:
Code:SQLyog Job Agent Version 5.26
Copyright (c) Webyog Softworks Pvt. Ltd.. All Rights Reserved.Job started at Wed Mar 28 19:24:26 2007
DBMS Information: SQL SERVER
Importing table schema: Dept… Successful…
Importing table schema: Employees… Successful…
Importing table foreign keys: Dept… Successful…
Importing table foreign keys: Employees… Successful…
Importing table data: Dept…3 rows transferred!
Successful…Importing table data: Employees…
3 rows transferred!
Successful…
Total time taken – 1 sec(s)
There are PK's, Unique indexes in both tables and a Foreign Key.
We cannot reproduce, as you see. Everything imports.
It is not the same server version as yours. this is the one that we could get.
A google search tells about a lot of issues with ODBC drivers.
There are a lot of unoriginal drivers. And the original driver does not install as default. You will have to select from the 'custom install' option of the installer.
Are you perfectly sure that you can retrieve data with your ODBC driver to some other program (MS Access / Open Office Base for instance) ?
If you can give us a dump with a test case (schema, a few tables with a few rows of data) we will try to reproduce.
-
March 29, 2007 at 9:01 am #23652srideviMember
Dear Adarsh,
Actually I was trying to import data from sybase adaptive server 12.0 to Mysql 5. and I was using DATA DIRECT 'S SYBASE WIRE PROTOCOL DRIVERS which is freely available on net for evaluation as the odbc driver for sybase. I need to see if the odbc drivers for sybase are available with sybase adaptive server 12.0 or not.
the data of the two tables of sybase whose data i could not import is already mentioned in my first post. the data is not properly aligned. this is the output the isql editor generates.
is that suffficient or u r looking for some more information.
with regards
sridevi
-
March 29, 2007 at 9:12 am #23653srideviMember
Dear Adarsh,
Actually I was trying to import data from sybase adaptive server 12.0 to Mysql 5. and I was using DATA DIRECT 'S SYBASE WIRE PROTOCOL DRIVERS which is freely available on net for evaluation as the odbc driver for sybase. I need to see if the odbc drivers for sybase are available with sybase adaptive server 12.0 or not.
the data of the two tables of sybase whose data i could not import is already mentioned in my first post. the data is not properly aligned. this is the output the isql editor generates.
is that suffficient or u r looking for some more information.
with regards
sridevi
-
March 29, 2007 at 9:33 am #23654adarshMember
Yes we are looking for more information.
Can you export that 2 tables as SQL dump and zip it and attach here don't paste so that we can import easily. This would be the best way to ensure that we don't do any mistakes.
Without this information we cannot do anything.
-
March 29, 2007 at 10:07 am #23655adarshMember
We installed the driver which you mentioned in your last post “DATA DIRECT 'S SYBASE WIRE PROTOCOL DRIVERS” and we tried to import we got same problem what you are facing. This is a driver issue.
Please try to install the driver from “Adaptive Server” (to install this you need to select custom install) and try it will work properly.
-
March 30, 2007 at 9:34 am #23656srideviMember
Dear Adarsh,
Do you mean Adaptive Server Enterprise ODBC Driver by Sybase?
Where can I down load the Adaptive Server driver. We had been using Data Direct's driver.
thanks.
regards
sridevi
-
March 30, 2007 at 9:49 am #23657adarshMember
While Installing the Adaptive Server select custom install and choose ODBC driver from the components and install.
This will install a driver called “Adaptive Server Enterprise” use this driver this will work.
-
March 30, 2007 at 10:09 am #23658srideviMember
actually my requirement is that I have an application with sybase 12.0 as the database on solaris 2.7 running on sun sparc workstation.
my new application is on windows with mysql and needs to replicate the sybase databse. so I need sybase drivers on the pc with wondows 2000 as the os. so i think i cannot install ase on pc nor the drivers. hence could you suggest me some other drivers. Are ase odbc drivers for windows available.
regards
sridevi
-
March 30, 2007 at 10:17 am #23659adarshMember
From here you can download Sybase (Adaptive Server 15.0) for windows, while installing select custom install and choose ODBC driver from components.
This will install a driver called “Adaptive Server Enterprise” with this ODBC driver you should able connect and it will wiok perfectly please try this.
-
March 30, 2007 at 10:28 am #23660srideviMember
thank you.
I will try
sridevi
adarsh wrote on Mar 30 2007, 03:47 PM:From here you can download Sybase (Adaptive Server 15.0) for windows, while installing select custom install and choose ODBC driver from components.This will install a driver called “Adaptive Server Enterprise” with this ODBC driver you should able connect and it will wiok perfectly please try this.
-
April 5, 2007 at 8:55 am #23661srideviMember
Dear Adarsh,
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 tat comes with it is Sybase Ase odbc driver . with this driver the tables from the source sybase databse are listed. but I am able to import the tables without any primary key or indexes. 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?
-
April 5, 2007 at 9:35 am #23662adarshMember
Hi,
After installing that ODBC driver from ASE 15.0 installer did you find any driver like “Adaptive Enterprise Server” in control panel–>Administrator tool–>DataSource(ODBC)–>Add. because i just tried to import a table with PK and Unique key using this driver it is working properly.
Please use this driver.
While creating the DSN you have to specify the correct Server Name(host server) and correct Logon ID.
If you create a table using Logon id “sa” default user, then you have to specify that Login ID in create DSN window.
Make sure that all the details (Server Name, Logon ID, Server Port, Database Name) you are entering while creating the DSN are correct.
Adarsh
-
April 5, 2007 at 10:02 am #23663srideviMember
hi adarsh,
Yes, AFter installing ASE 15 , i found a driver like Adaotive enterprise server and I entered the syabse work station 1p, port no, log in id and the database name . i did not mention the server name. i think it doesnot ask for server name. also i gort a mesage that log in succesful.
is this problem because of the difference in versions.?
-
April 5, 2007 at 10:22 am #23664adarshMember
Hi,
Tell me are you installed more than one version of ASE?
When we install a ASE it will create a four types of servers 1) Sybaes BCKServer 2) SybaseMONServer 3)Sybase SQLServer 4) Sybase XP Server
by default it will take your system name as server name and it will create the server names like this. you need to use the SQL server name in Server Name field in create DSN window.
1) ADARSH_BS for Backup Server
2) ADARSH_MS for MOnitoring Server
3) ADARSH for SQL Server
4) ADARSH_XP for XP Server
If you install the ASE server of another version then it will create name like this ADARSH_BS1,ADARSH_MS1,ADARSH1,ADARSH_XP1 so if you installed more than one verion of server please specify server name.
I tried to create a DSN without specifying the server name i am getting error.
Please verify the server name in which you created that database and also please verify the login id you used to create that database and table and also please specify the port of that correct server in which you have created the database and table.
You need to use that Server Name and Logon Id and correct port.
-
April 5, 2007 at 11:34 am #23665srideviMember
Hi adarsh,
I installed only one version of ASE. and as u said the four servers were installed
But installation of ase was required on my part on the pc was esential for getting the ASE ODBC driver as the datadirect driver was giving the problem mentioned in initial post.
The source database on sybase with ASE 12 is on a sun workstation with ip address 191.0.0.121. so using this ASE driver from ase 15 on the pc I was assessing the sybase databse.
datasource name = system121
server = 191.0.0.121
port =5555
login = dbh
database = ncdb
The login successful message also popped up.
But in the migration tool when i chose copy tables from datasource(sybase datasource) no tables from sybase 12( on sun workstation) were selected.
then I tried the other option i.e copy data by using a query. In this case, the query on the sybase table emp2 was successful but for the result table in the Map tab did not hav any primary key by default. but I made the columb 'ssn' as the primary key and the data was transferred successfuly BUT WITHOUT ANY PRIMARY KEYS.
so as u see it is quite puzzling .
please help me out.
adarsh wrote on Apr 5 2007, 03:52 PM:Hi,Tell me are you installed more than one version of ASE?
When we install a ASE it will create a four types of servers 1) Sybaes BCKServer 2) SybaseMONServer 3)Sybase SQLServer 4) Sybase XP Server
by default it will take your system name as server name and it will create the server names like this. you need to use the SQL server name in Server Name field in create DSN window.
1) ADARSH_BS for Backup Server
2) ADARSH_MS for MOnitoring Server
3) ADARSH for SQL Server
4) ADARSH_XP for XP Server
If you install the ASE server of another version then it will create name like this ADARSH_BS1,ADARSH_MS1,ADARSH1,ADARSH_XP1 so if you installed more than one verion of server please specify server name.
I tried to create a DSN without specifying the server name i am getting error.
Please verify the server name in which you created that database and also please verify the login id you used to create that database and table and also please specify the port of that correct server in which you have created the database and table.
You need to use that Server Name and Logon Id and correct port.
-
April 5, 2007 at 12:01 pm #23666adarshMember
Are you using ASE 12.0 server with ASE 15.0 driver?
If you don't want to uninstall that ASE 12.0 server then please install ASE 15.0 server with driver in another machine and then try. In windows i tried it is working fine.
-
April 6, 2007 at 3:30 pm #23667srideviMember
HI Adarsh,
Let me explain the requirement to u.
I have an old application running in a SUN SPARC workstation with Sybase ASE 12 as the DATABASE and Solaris 2. 7 as the OS. The new project is supposed to develop an application running on windows 2k with MYSQL 5 as the database. The application has to replicate data from sybase to mysql. For That i was running sqlyog on the windows pc with mysql and using datadirect drivers to access the sybase database on the workststion with ip 191.0.0.121. with this tables without any primary keys or constraints i was able to access. I had problems in accessing the tables with primary keys. please refer to the attachment in my fprevious message. then i installed ase 15 on the pc so that i can use the ase driver that comes along with it to access the ase12 sybase database on the workstation through the ethernet lan as suggested by u. u said that the problem is due to datadirect drivers. so u see there is no requirement to install ase 15 on another system.
did u try with this kind of setup ?
please suggest a solution as early as possible.
I would like to tell u that I tried using deatadirect drivers with the CRecordset classes and i wall able access all the tables with primary keys and without primary keys.
-
-
AuthorPosts
- You must be logged in to reply to this topic.