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 [url=http://tt-rss.org/forum/viewtopic.php?f=22&t=1981]fever plugin[/url] 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":

[quote="Wiki"]PostgreSQL or MySQL (InnoDB is required, MyISAM will not work).

[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]
[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$
[/code]
Note that the following create statements do not have a corresponding directive to use the InnoDB engine:

[code]
create table ttrss_counters_cache (
create table ttrss_cat_counters_cache (
create table ttrss_feedbrowser_cache (
[/code]
Likewise, the UTF-8 CHARSET is not specified for the following tables:

[code]
create table ttrss_counters_cache (
create table ttrss_cat_counters_cache (
[/code]
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]

[quote="jamesbond"]
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?

[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 [url=http://serverfault.com/questions/410552/do-innodb-tables-got-locked-during-mysqldump-when-mixed-with-myisam]here[/url] and [url=http://dba.stackexchange.com/questions/19532/safest-way-to-perform-mysqldump-on-a-live-system-with-active-reads-and-writes]here[/url].

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]

[quote="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 [url=http://serverfault.com/questions/410552/do-innodb-tables-got-locked-during-mysqldump-when-mixed-with-myisam]here[/url] and [url=http://dba.stackexchange.com/questions/19532/safest-way-to-perform-mysqldump-on-a-live-system-with-active-reads-and-writes]here[/url].

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

[fox]

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

[sleeper_service]

[quote="fox"]I'd like to post an obligatory lol @ dealing with mysql while caring about any sort of data integrity whatsoever.

[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:

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

[sleeper_service]

[quote="jamesbond"]
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.

[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]
--- 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,
[/code]
Good luck!

[sleeper_service]

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

[AngryChris]

[quote="sleeper_service"]isn't there a way to convert a myisam table 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.