Primary keys on all tables to help replication

Request new functionality here
Halfwalker
Bear Rating Trainee
Bear Rating Trainee
Posts: 18
Joined: 05 Jul 2014, 22:10

Primary keys on all tables to help replication

Postby Halfwalker » 05 Jul 2014, 22:42

I have ttrss running nicely under Percona XtraDB Clustering 5.5. This is on a 3-node cluster (with other stuff in the DB too). One of the requirements from Percona is for all InnoDB tables to have a primary key

http://www.mysqlperformanceblog.com/2013/05/01/follow-these-basics-when-migrating-to-percona-xtradb-cluster-for-mysql/

Running a quick check on ttrss, these tables come up with no PK. In bold is the column name I've manually set as a primary key.

ttrss.ttrss_cat_counters_cache feed_id
ttrss.ttrss_counters_cache feed_id
ttrss.ttrss_feedbrowser_cache add pk_id column
ttrss.ttrss_linked_feeds instance_id
ttrss.ttrss_user_labels2 label_id
ttrss.ttrss_version schema_version
ttrss.ttrss_user_prefs - not sure what to do here

Code: Select all

alter table ttrss_counters_cache add primary key (feed_id);
  :
alter table ttrss_feedbrowser_cache add pk_key int(10) auto_increment primary key;

Not sure what to do with the ttrss_user_prefs table though.

Would it be possible on the next update for the schema to include the primary key definitions ?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Primary keys on all tables to help replication

Postby fox » 05 Jul 2014, 22:59

This sounds a lot like "pls help me make another feedly" so probably no.

e: to elaborate, the keys are set the way they are supposed to be set, I think. changing things arbitrarily because of some whatsitsface mysql thing doesn't sound like a good idea to me.

Halfwalker
Bear Rating Trainee
Bear Rating Trainee
Posts: 18
Joined: 05 Jul 2014, 22:10

Re: Primary keys on all tables to help replication

Postby Halfwalker » 05 Jul 2014, 23:25

No, not looking for another feedly. Just using tt-rss, but also wanting to take advantage of existing DB replication properly. It's a fairly minor change in ttrss_schema_mysql.sql, from (using ttrss_counters_cache table for example here)

Code: Select all

create table ttrss_counters_cache (
        feed_id integer not null,
        owner_uid integer not null,
        value integer not null default 0,
        updated datetime not null,
        foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

to adding the primary key attribute

Code: Select all

create table ttrss_counters_cache (
        feed_id integer not null primary key,  <<<<<<<<<<
        owner_uid integer not null,
        value integer not null default 0,
        updated datetime not null,
        foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

The ttrss_feedbrowser_cache table would just have another field defined pk_key int(10) auto_increment primary key
Updating the schema would just be the handful of alter table lines.
I don't believe it would have any bad effects anywhere ...

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Primary keys on all tables to help replication

Postby fox » 05 Jul 2014, 23:36

I do understand what you need to change to add a primary key to the table, you don't really have to explain the keywords here.

e: I see some of those are only missed for mysql because fuck mysql I guess so nobody ever cared to notice. If you make a proper pull request I'll consider merging it.

Halfwalker
Bear Rating Trainee
Bear Rating Trainee
Posts: 18
Joined: 05 Jul 2014, 22:10

Re: Primary keys on all tables to help replication

Postby Halfwalker » 05 Jul 2014, 23:38

fox wrote:e: to elaborate, the keys are set the way they are supposed to be set, I think. changing things arbitrarily because of some whatsitsface mysql thing doesn't sound like a good idea to me.

I agree, things shouldn't be arbitrary, which is why I'm asking here. If it doesn't have any effect though, then why not ? It would enable DB replication to work smoothly. In general primary keys are never a bad thing, so again, why not define them on tables that are missing them ?

And heh :) I know you know mysql - better that I. I'm just showing what *I* did.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Primary keys on all tables to help replication

Postby fox » 05 Jul 2014, 23:44

I can suggest the following, which should obviously be done in sync for both schema files:

- feedbrowser_cache has feed_url as a primary in pgsql so mysql should use that
- counters_caches, user_prefs - you'll need to add a column i.e. id integer primary key
- linked_instances has a primary key already?
- user_labels2 you can't just set label_id as a primary here, probably requires a separate id column
- version - whatever

e: Probably remove some indexes from newly keyed fields too.

Halfwalker
Bear Rating Trainee
Bear Rating Trainee
Posts: 18
Joined: 05 Jul 2014, 22:10

Re: Primary keys on all tables to help replication

Postby Halfwalker » 08 Jul 2014, 04:20

FYI, this is what I'm doing in my installer, and so far so good.

Code: Select all

    alter table ttrss_cat_counters_cache add pk_key int(10) auto_increment primary key;
    alter table ttrss_counters_cache     add pk_key int(10) auto_increment primary key;
    alter table ttrss_feedbrowser_cache  add pk_key int(10) auto_increment primary key;
    alter table ttrss_linked_feeds       add pk_key int(10) auto_increment primary key;
    alter table ttrss_user_labels2       add pk_key int(10) auto_increment primary key;
    alter table ttrss_user_prefs         add pk_key int(10) auto_increment primary key;
    alter table ttrss_version            add primary key (schema_version); "

Percona DB replication seems to be working fine, and the app seems to be OK on either node. The PK (doesn't really matter what it is, just that there IS one) just provides for a deterministic replication scheme from what I read at Percona.

Thanks for your help.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Primary keys on all tables to help replication

Postby fox » 09 Jul 2014, 14:55

If you want any of this changed in trunk, post a properly made pull request.

Halfwalker
Bear Rating Trainee
Bear Rating Trainee
Posts: 18
Joined: 05 Jul 2014, 22:10

Re: Primary keys on all tables to help replication

Postby Halfwalker » 12 Jul 2014, 00:51

Gotta learn git first ...

hrk
Bear Rating Disaster
Bear Rating Disaster
Posts: 75
Joined: 24 Apr 2013, 12:39

Re: Primary keys on all tables to help replication

Postby hrk » 13 Jul 2014, 13:17

If you want to learn GIT, then you should go here: http://git-scm.com/book

This (free) book brought me from "How the hell does this work?" to "OMFG it's so easy and powerful and I'll migrate every project to git!".

Having said that, an observation: you clearly didn't follow fox's guidelines to defining the keys. Any particular reason for that?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Primary keys on all tables to help replication

Postby fox » 17 Jul 2014, 14:20

One can only hope people using his "installer" (whatever that is) don't come here for support after experiencing the results of his bold ideas applied to tt-rss schema.

Also, this is one of the many reasons I only support whatever was downloaded from tt-rss.org.

e: also,

Code: Select all

ERROR 1170 (42000) at line 435: BLOB/TEXT column 'feed_url' used in key specification without a key length


oh

Halfwalker
Bear Rating Trainee
Bear Rating Trainee
Posts: 18
Joined: 05 Jul 2014, 22:10

Re: Primary keys on all tables to help replication

Postby Halfwalker » 01 Aug 2014, 00:18

hrk - thanks for the pointer, will check it out. As for the keys, I did follow his guidelines, except for feedbrowser_cache. I just wanted to be as unobtrusive as possible, so a simple auto-increment int as a PK seems to do the trick, and avoids the issue with specifying a length for feed_url ... It's just a way to have a unique identifier so the Percona/mysql cluster replication will work cleanly.

fox - I'm sorry, I didn't realize primary keys were a bold idea.

The "installer" is just my own scripted setup. Used by myself and a few others. Got tired of referring to notes and snippets every time I set up a new box or vps, so wound up scripting things. That way I know that it's built the way *I* want, and consistently every time.

For example I use it for building a 3-node redundant email system, and toss in tt-rss as an additional service on top of exim4/dovecot/roundcube/clamav/spamassassin/etc/yada/yada. It's in place and working really swimmingly now - one can hit the servers in a dns-round-robin fashion and everything is kept in sync.

Thanks for all the help.


Return to “Feature requests”

Who is online

Users browsing this forum: No registered users and 4 guests