Database row ID assumption - Githubissues

Chillibear created Redmine issue ID 7345

I think there may be an assumption made about the row IDs of certain elements in the ModX Revolution codebase. Having installed ModX Rev 2.2.0-pl2 onto one of our hosting environments that makes use of MySQL in a master-master configuration we found elements in the admin interface that were incorrect.

Specifically the value of 'Content Type' and 'Uses Template' for new (and the existing sample) pages, both these properties were set to a value of '1' rather than their correct values of 'Base template' and 'HTML' (or rather the indexes of these keys behind the scenes).

Upon a little investigation the default values (from global settings) were indeed '1' for both of these properties, so the system was working correctly in assigning a '1', but due to the master-master database setup neither 'Base_template' or 'HTML' were assigned a DB row ID of 1 when the tables were populated and hence the problem. It would appear that the 'default value' for these should be the DB row ID of 'Base template' and 'HTML' respectively and somewhere when those default values are generated there is an assumption that these DB row IDs will be '1'. For many installs that will be correct, but it is still an assumption that can be wrong.

I should note that at this point I am second guessing the problem - I'm not familiar with the ModX codebase and have yet to locate the code that actually populates the settings values. However the 2.2.0-pl2 install works flawlessly on an environment with just a conventional MySQL deployment. In a Master-Master deployment there are two key settings that affect the automatic row ID:

auto-increment_increment auto-increment-offset

Those are used typically to avoid ID clashes when rows are created on different nodes, so in our case rows are not neatly numbered 1,2,3,4 etc. but instead increment say as 2,4,6,8,10 etc. So that leads to a situation where those 'default' values for 'Content Type' and 'Uses Template' are not located in DB row ID 1, but rather in 2 or 3 or some other value. The code that populates these default values should of course be looking these IDs up by searching for their keys, I am guessing it isn't.

It should be possible to mimic this fault with a single MySQL instance by just setting the above parameters to adjust the ID increment, that's not something I have yet tested, but it should make replicating the bug easy.

It is possible to change the 'default settings' post install to reflect the correct values (although this seems only to work when changed via the 'inline' interface rather than the 'update settings panel' for each property) and we then find things to work as expected.

Essentially because 'out of the box' pages lack a content type or correct template nothing renders when viewing the default pages on the site.

markh submitted:

I've got no experience with master-master setups, however I do know that the content types are created and set specifically during the installation. So after any install, the HTML content type should always have ID 1.

Again, no experience with master-master setups, but I would assume that either way the HTML content type would still be 1.

Chillibear submitted:

I'm sure the HTML type doesn't always have an ID of 1, if I look at our master-master MySQL database the fields in the content_type table are populated thus:

mysql> select * from modx_content_type;
+----+------------+--------------------+---------------------+-----------------+---------+--------+
| id | name       | description        | mime_type           | file_extensions | headers | binary |
+----+------------+--------------------+---------------------+-----------------+---------+--------+
|  2 | HTML       | HTML content       | text/html           | .html           | NULL    |      0 |
|  5 | XML        | XML content        | text/xml            | .xml            | NULL    |      0 |
|  8 | text       | plain text content | text/plain          | .txt            | NULL    |      0 |
| 11 | CSS        | CSS content        | text/css            | .css            | NULL    |      0 |
| 14 | javascript | javascript content | text/javascript     | .js             | NULL    |      0 |
| 17 | RSS        | For RSS feeds      | application/rss+xml | .rss            | NULL    |      0 |
+----+------------+--------------------+---------------------+-----------------+---------+--------+
6 rows in set (0.00 sec)

Which are in keeping with our values for _auto-incrementincrement and auto-increment-offset (http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment) in the MySQL configuration, so these rows are not having hardcoded IDs set, rather the SQL insert statements are populating the rest of the columns and leaving an auto_increment to sort out the ID, it would be possible to force them (but I'd avoid that approach). In this case the ModX install was talking to the second database server and so ended up with 2,5,8 etc. (had it been talking to the first database server we'd have got 1,4,7, the third database server would have created 3,6,9 for the IDs)

My problem is simply that when the contents of the system_settings table are created/populated it appears an assumption about those IDs being 1,2,3... has been made when filling in the value field for some keys.

mysql> describe modx_system_settings;
+-----------+--------------+------+-----+---------------------+-----------------------------+
| Field     | Type         | Null | Key | Default             | Extra                       |
+-----------+--------------+------+-----+---------------------+-----------------------------+
| key       | varchar(50)  | NO   | PRI |                     |                             |
| value     | text         | NO   |     | NULL                |                             |
| xtype     | varchar(75)  | NO   |     | textfield           |                             |
| namespace | varchar(40)  | NO   |     | core                |                             |
| area      | varchar(255) | NO   |     |                     |                             |
| editedon  | timestamp    | NO   |     | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+-----------+--------------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)

If you're more familiar with the code can you point me to where in the setup code the system_settings table is populated. I've not been through it in detail yet so some pointers would help.

markh submitted:

It's one of the files in core\packages\core\modSystemSetting, wouldn't know which one though! ;) Luckily we can just look at the source that created that if you checked out from git, or just look at www.github.com/modxcms/revolution.

The source file is _build/data/transport.core.system_settings.php, the default_content_type setting is set at line 441+ to 1 (which is appropriate at least 99,9% of the cases as far as I know!).

I'm just assigning to this Jason as he probably knows more about this subject.

Chillibear submitted:

I see, the build code generates those vehicle files (which I failed to note since they are all bundled up in the install download) which in turn provide the data for the install.

For reference: default_content_type: @bf0a3689abf27d9e67093d81b2ff5664.vehicle@ default_template: @0c1a0fea6a766a2d98934b85d6f14553.vehicle@

I guess the difficulty would be in supplying dynamic content for those values. The compromise work around if it proves difficult to provide dynamic values at install would of course be to at least identify the places where such DB id assumptions are made, then we can easily tweak things post install. I suppose that's my main concern about our deployments - we've spotted two places which caused an obvious issue, but there could be other less obvious settings that could cause more subtle issues.

modxbot / migrate

Database row ID assumption #7345