Question on MySQL database structure: Why 3 MyISAM tables?

[jamesbond]

Hi everyone,

first of all: thanks very much for this great application! I've been using it for 3 or 4 days now and I am totally excited about it. The installation on my server was completely hassle-free and thanks to the fever plugin I have found an app for the iPhone (Reeder) and an app for the iPad (Mr. Reader) that I enjoy using when away from a computer keyboard.

Now to the topic of my post. I have a question on the MySQL database structure. The start page of the wiki says under "Requirements" --> "Server-side":

PostgreSQL or MySQL (InnoDB is required, MyISAM will not work).

As I am using MySQL, I was expecting that all tables created by ttrss would be of type InnoDB. To my surprise, that is not the case, as you can see in the following screenshot: http://s1.directupload.net/images/130811/atk8l76t.png

That means three tables are of type MyISAM (and two of them have a different collation from all the other tables).

My question is: Has this been done on purpose or maybe somehow slipped in accidentally?

Kind regards,
jamesbond.

[AngryChris]

It looks like you found a bug in the ttrss_schema_mysql.sql file. Here's the content where each table is created and the associated ENGINE=InnoDB statements:

Code: Select all

[email protected]:/var/www/tt-rss/schema$ grep -e "create table" -e "ENGINE=InnoDB" ttrss_schema_mysql.sql
create table ttrss_users (id integer primary key not null auto_increment,
   index (theme_id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_feed_categories(id integer not null primary key auto_increment,
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_archived_feeds (id integer not null primary key,
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_counters_cache (
create table ttrss_cat_counters_cache (
create table ttrss_feeds (id integer not null auto_increment primary key,
   foreign key (parent_feed) references ttrss_feeds(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_entries (id integer not null primary key auto_increment,
   author varchar(250) not null default '') ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_user_entries (
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_entry_comments (id integer not null primary key,
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_filter_types (id integer primary key,
   description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_filter_actions (id integer not null primary key,
   description varchar(250) not null unique) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_filters2(id integer primary key auto_increment,
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_filters2_rules(id integer primary key auto_increment,
   foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_filters2_actions(id integer primary key auto_increment,
   foreign key (action_id) references ttrss_filter_actions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_tags (id integer primary key auto_increment,
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_version (schema_version int not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_enclosures (id integer primary key auto_increment,
   foreign key (post_id) references ttrss_entries(id) ON DELETE cascade) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_settings_profiles(id integer primary key auto_increment,
   foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_prefs_types (id integer not null primary key,
   type_name varchar(100) not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_prefs_sections (id integer not null primary key,
   order_id integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_prefs (pref_name varchar(250) not null primary key,
   foreign key (section_id) references ttrss_prefs_sections(id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_user_prefs (
   foreign key (pref_name) references ttrss_prefs(pref_name) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_sessions (id varchar(250) unique not null primary key,
   index (expire)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_feedbrowser_cache (
create table ttrss_labels2 (id integer not null primary key auto_increment,
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_user_labels2 (label_id integer not null,
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_access_keys (id integer not null primary key auto_increment,
     foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_linked_instances (id integer not null primary key auto_increment,
   access_url text not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_linked_feeds (
    foreign key (instance_id) references ttrss_linked_instances(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_plugin_storage (
     foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
create table ttrss_error_log(
   foreign key (owner_uid) references ttrss_users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
[email protected]:/var/www/tt-rss/schema$


Note that the following create statements do not have a corresponding directive to use the InnoDB engine:

Code: Select all

create table ttrss_counters_cache (
create table ttrss_cat_counters_cache (
create table ttrss_feedbrowser_cache (


Likewise, the UTF-8 CHARSET is not specified for the following tables:

Code: Select all

create table ttrss_counters_cache (
create table ttrss_cat_counters_cache (


The fix is to either set MySQL's default storage engine and CHARSET to InnoDB and UTF-8 respectively, or to patch the ttrss_schema_mysql.sql file. In either case, the database needs to be recreated.

Note, I am using TT-RSS 1.8. These issues may have been fixed in 1.9 or trunk.

[erikbennett]

I'm using 1.9 (release, not trunk) and actually just recreated my database for other, unrelated reasons. I can confirm that they are still present in my database.

[feader]

That means three tables are of type MyISAM (and two of them have a different collation from all the other tables).

My question is: Has this been done on purpose or maybe somehow slipped in accidentally?

Since it came up in another thread: This is no accident, and your question was asked before. A quick search would surely saved every poster in this thread some time and breath

[jamesbond]

The fact that both types of tables (MyISAM and InnoDB) are used concerns me because it makes creating backups of the database quite complicated. With MySQL, there are different approaches to backup both types of tables (mysqldump needs different parameters) as discussed here and here.

It would be much easier if only one type of tables was be involved.

[fox]

You don't need to backup counters and tbqh I never had this problem, mysqldump always seemed to backup everything correctly for me.

[sleeper_service]

The fact that both types of tables (MyISAM and InnoDB) are used concerns me because it makes creating backups of the database quite complicated. With MySQL, there are different approaches to backup both types of tables (mysqldump needs different parameters) as discussed here and here.

It would be much easier if only one type of tables was be involved.

just shut down your updater while you're backing up.

[fox]

I'd like to post an obligatory lol @ dealing with mysql while caring about any sort of data integrity whatsoever.

[sleeper_service]

I'd like to post an obligatory lol @ dealing with mysql while caring about any sort of data integrity whatsoever.

I was waiting for that.

but, hey, it works.... mostly.

[jamesbond]

Hi fox,

I know that you are the boss here so I don't know if I am allowed to say this, but: I think that you don't fully understand the problem. The pure existence of the MyISAM tables can lead "mysqldump --single-transaction" to produce inconsistencies of the dumped InnoDB tables. (I don't know if skipping the MyISAM tables with "--ignore-table" would help here or not.) Of course I have to admit that this is a really strange behaviour of MySQL.

But as far as I can see this situation could easily be avoided if all tables where of type InnoDB. Then "mysqldump --single-transaction" would work nicely, produce a consistent dump and tables would not have to be locked during backup.

In contrast to InnoDB tables, MyISAM tables are dumped with "mysqldump --lock-tables". The man page of mysqldump does not refer on how to dump databases with mixed table types. It just says:

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

Of course you can argue that it would be better to use a different database system like PostgreSQL. I guess you are completely right about that! I should definitely take the time to learn how to install and use it.

But in the near term I have to stick to MySQL because I know how to handle basic tasks with it and besides I use other software on my server that does only support MySQL as the backend.

I don't want to produce an endless discussion here that seems to be of no interest for most of the forum members. There is just a simple question that I would like to ask (and hopefully get an answer ): Is there a specific reason why the counter tables that are currently of type MyISAM cannot be of type InnoDB? If yes: what is it?

Thanks in advance & kind regards,
jamesbond.

[sleeper_service]

Of course you can argue that it would be better to use a different database system like PostgreSQL. I guess you are completely right about that! I should definitely take the time to learn how to install and use it.

I started out using mysql for ttrss because I already had mysql running (for mythtv) and after seeing people talking about postgres working better, I decided to spin up a copy.

it's different, reading the docs for the control program (psql) is recommended since they use \ commands instead of words for many things. like \c ttrss to connect, or \d to see what databases you have. no biggie, the worst thing I found was that postgres is much more involved when it comes to making users and granting them rights. you have to explicitly grant to each table if you don't create the database as the target user.

but, overall, it wasn't a big deal, and it works nicely, as long as you're not running it on some broken shared hosting that doesn't let the autovacuum run (their name for cleaning up deleted data in the db.

[AngryChris]

Universal diff for ttrss_schema_mysql.sql for TT-RSS version 1.8. You can figure out how to patch your own if you have something different (let this diff be your guide). This sets the 3 "orphaned" tables to InnoDB. You'll need to recreate your database to use this.

Code: Select all

--- ttrss_schema_mysql.sql.20130909     2013-09-09 17:11:57.188383918 -0500
+++ ttrss_schema_mysql.sql      2013-09-09 17:23:11.163370122 -0500
@@ -84,7 +84,7 @@
        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;
 
 create index ttrss_counters_cache_feed_id_idx on ttrss_counters_cache(feed_id);
 create index ttrss_counters_cache_owner_uid_idx on ttrss_counters_cache(owner_uid);
@@ -96,7 +96,7 @@
        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;
 
 create index ttrss_cat_counters_cache_owner_uid_idx on ttrss_cat_counters_cache(owner_uid);
 
@@ -434,7 +434,7 @@
        feed_url text not null,
        site_url text not null,
        title text not null,
-       subscribers integer not null) DEFAULT CHARSET=UTF8;
+       subscribers integer not null) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
 
 create table ttrss_labels2 (id integer not null primary key auto_increment,
        owner_uid integer not null,


Good luck!

[sleeper_service]

isn't there a way to convert a myisam table to innodb?

[AngryChris]

isn't there a way to convert a myisam table to innodb?

Hah! I guess you can! I don't touch MySQL and didn't look it up.

Code: Select all

ALTER TABLE table_name ENGINE=InnoDB;


From here: 5.4.3. Converting Tables from MyISAM to InnoDB

[fox]

I'll update the schema files for those tables to use innodb. Keeping those tables as isam is not required or anything, I just figured it would be a bit faster.

e: Since myisam is not specified explicitly in the dable definition, I'm not sure if making a migration for this is a good idea - what if it's innodb already or something? We don't want database upgrade scripts to break, this is confusing for people.