Project

General

Profile

Feature #6500

Support InnoDB

Added by Yannick Warnier about 6 years ago. Updated over 4 years ago.

Status:
Feature implemented
Priority:
Urgent
Assignee:
Category:
Global / Others / Misc
Target version:
Start date:
31/07/2013
Due date:
% Done:

100%

Estimated time:
Spent time:
Complexity:
Normal
SCRUM pts - complexity:
?

Description

Since the migration to one single database, the c_* tables (previously course-specific tables) have received the addition of a "c_id" field which represents the course.

However (and this was my fault for not being clear enough), the point of moving to one single database is really to have unique identifiers in the form of one column (not a combination of two columns) in the course-related tables.

This unique ID, as discussed with Julio, should be called "iid" so as to give us time to accomodate the code to the new style and avoid migration frustration (and avoid chocking with the current "id"). We would thus add an "iid" column as the primary key (and remove the "primary key" aspect of the current combination of "c_id + id").

This implies, for each table of the c_* series, to make the corresponding changes wherever there is an insert (updates, deletes and reads are still ok with the old method for now) to use the AUTO_INCREMENT for the "iid" field instead of the "id" field.

This was not really urgent, but it becomes more of an issue as we hit a larger community, in the sense that we need more optimizations for larger installations, and InnoDB is one of the first (and most efficient) elements we can use for that. Between others, Galera (a special truly-redundant layer on top of MySQL/MariaDB) allows for nice load spreading but requires tables to be InnoDB.

Associated revisions

Revision 621c8fec (diff)
Added by Yannick Warnier about 6 years ago

Added iid field to c_announcement and c_announcement_attachment - missing chash structure change - refs #6500

Revision 7897cc4b (diff)
Added by Yannick Warnier about 6 years ago

Added iid field to c_attendance* tables for InnoDB - missing chash structure change - refs #6500

History

#1

Updated by Yannick Warnier about 6 years ago

The current changes to add the iid field are not 100% "safe" in a transaction point of view, because they allow the possibility to try and insert twice the same ID (which will fail thanks to the unique key on (c_id + id)), so this task shouldn't be closed before every access to these items is made through iid, and the "id" field can be safely removed.

#2

Updated by Yannick Warnier about 6 years ago

It took me 1h45 to do 7 tables. There's only 85 to go (although quiz* tables should already have it).

#3

Updated by Francis Gonzales over 5 years ago

Tables:
*c_quiz
*c_quiz_question
*c_quiz_rel_question

ALTER TABLE `c_quiz`
ADD `iid` int(11) unsigned NOT NULL AUTO_INCREMENT UNIQUE FIRST,
CHANGE `id` `id` int(10) unsigned NOT NULL AFTER `c_id`,
COMMENT='' ENGINE='InnoDB';

ALTER TABLE `c_quiz_question`
ADD `iid` int(11) unsigned NOT NULL AUTO_INCREMENT UNIQUE FIRST,
CHANGE `id` `id` int(10) unsigned NOT NULL AFTER `c_id`,
COMMENT='' ENGINE='InnoDB';

ALTER TABLE `c_quiz_rel_question`
ADD `id` int(11) NULL AUTO_INCREMENT UNIQUE FIRST,
COMMENT='' ENGINE='InnoDB';

#4

Updated by Yannick Warnier over 5 years ago

Francis, can you remind me of why you cannot use PRIMARY key instead of UNIQUE?

#6

Updated by Francis Gonzales over 5 years ago

That's right @Marko,

InnoDB documentation (http://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html) says, "When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
".

Particularly, I think that's great because we don't have to lose our primary keys we just have to add a unique key (if necessary).

#7

Updated by Yannick Warnier over 5 years ago

Actually the article ends up recommending to use PRIMARY KEYs because they are more determinalistic (you set them and they're the one the system will use).
We also have to think about other database management systems, and I don't think this logic is available everywhere, so we should set a PRIMARY KEY where we intend it to be (in this case iid). "Loosing" the primary key is just a matter of dropping it, then adding it anew (this can be done overnight or during the upgrade procedure from 1.9 to v10).

#8

Updated by Yannick Warnier about 5 years ago

  • Priority changed from High to Urgent
#9

Updated by Yannick Warnier over 4 years ago

  • Target version changed from 2.0 to 1.10.0

Moving back to 1.10.0 because we can definitely do it now and it should even make the switch to 2.0 easier afterwards

#10

Updated by Yannick Warnier over 4 years ago

  • % Done changed from 0 to 20

This is still urgent and will be implemented progressively over the following 2 weeks, adding a single-field primary-key to all tables.

#11

Updated by Yannick Warnier over 4 years ago

  • Status changed from Assigned to Feature implemented
  • % Done changed from 20 to 100

This has been completely completed (yes, I know, pleonasm) by Julio through the merge of the doctrine branch.
This is great. I can already feel the speed difference :-) (I'm sure it's got nothing to do, but still...)

Also available in: Atom PDF