No PRIMARY KEY defined

[RedGerry]

🙁 Hi guys..

I managed to get the Linux version working thanks to the email from your support crew.

I operate several large PHP-Nuke sites on an IBM server. I have a second IBM server that I'm trying to sync to allow downtime on the main one. Your tool is perfect for the job.

The problem I've run in to is that several, well about twenty, of about 80 nuke tables contain no primary key. This throws the error back:

No PRIMARY KEY defined in the table '`nuke_bbauth_access`'

and so on and these tables are not copied at all. I took an SQL dump of the DB and created it's mirror manually on the second server. After a couple of hours and several changes I ran the sync tool again with the same error.

Is this an impasse? should I create keys in these tables to allow your software to work? That all said, brilliant wee package you've got here and it looks like replacing my trusty MySQL FrontEnd as my primary tool. Cron jobbing the sync will definitely let me sleep at night… 😮

[Shadow]

The problem is that sja uses primary keys to determine which rows must be updated, so currently a table must have a primary key in order to be synced by SQLyog. If you need to mirror a db, then you might consider using MySql's built-in replication feature.

[Patrick]

What about weak entity sets which, by definition, have no primary key??

SQLyog seems to be a fantastic product, surely it's not crippled by something as fundamental as this!

[Shadow]

Even SQLyog's GUI cannot really handle tables without PKs… You should request it as a new feature!

[kams]

Same here… Great product ! but this PK rule made it useless for me…

Feature request :

Isn't it possible to ask user what they want to do with the pk less tables ?

Either copy it one way or the other… then later have a merge/sync function…

Tx again for your hard work !

[emagin]

Is there any kind of a workaround to this PK requirement?

This is killer. I mean, what DB that you want to synch DOESN'T have tables w/out PK's.

Any kind of hack would be of interest.

[emagin]

Ok, we can probably force all tables to have PKs.

Will it support multi field PKs?

[Shadow]

Sure, it doesn't matter how many fields a PK has.

[halcyon]

Hi I'm using SQLYog 3.71 and also having the same problem with sync'ing tables without a PK.. I have tables where the key column lists them as MUL, which I'm assuming is multiple… and yet it does not sync. Here is the message I get:

No PRIMARY KEY defined in the table '`yazdFilter`'

Here is the table info:

Code:

Column Information For – salesweb.yazdFilter
——————————————–

Field         Type     Collation  Null    Key     Default  Extra   Privileges                       Comment
————  ——-  ———  ——  ——  ——-  ——  ——————————-  ——-
filterObject  blob     NULL       YES             (NULL)           select,insert,update,references        
forumID       int(11)  NULL               MUL     0                select,insert,update,references        
filterIndex   int(11)  NULL               MUL     0                select,insert,update,references        

Index Information For – salesweb.yazdFilter
——————————————-

Table       Non_unique  Key_name     Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment
———-  ———-  ———–  ————  ———–  ———  ———–  ——–  ——  ——  ———-  ——-
yazdFilter           1  forumID                 1  forumID      A               (NULL)    (NULL)  (NULL)          BTREE              
yazdFilter           1  filterIndex             1  filterIndex  A               (NULL)    (NULL)  (NULL)          BTREE              

DDL Information For – salesweb.yazdFilter
—————————————–

Table       Create Table                                                                                                                                                                                                                                    
———-  ————————————————————————————————————————————————————————————————————————————————
yazdFilter  CREATE TABLE `yazdFilter` (                                                                                                                                                                                                                    
             `filterObject` blob,                                                                                                                                                                                                                          
             `forumID` int(11) NOT NULL default '0',                                                                                                                                                                                                      
             `filterIndex` int(11) NOT NULL default '0',                                                                                                                                                                                                  
             KEY `forumID` (`forumID`),                                                                                                                                                                                                                    
             KEY `filterIndex` (`filterIndex`)                                                                                                                                                                                                            
           ) TYPE=MyISAM DEFAULT CHARSET=latin1                                                                                                                                                                                                            

Any ideas??? We REALLy need to backup/sync these tables.

Thanks

[Shadow]

As I said before, sja is currently unable to sync tables without a PK. I know no hack or workaround. It is up to Ritesh to incorporate a new feature. He argues that you cannot tell which record gets updated in a table without PK as there may be multiple rows holding the same values.

You should consider using MySql's built-in replication feature, that won't mind about PKs.

[Author]

Posts