Allow settings.php to specify the table engine

I can't think of when I'd want to do this. But I can't think of a compelling argument to now allow it, so...

My only thought would be that it should be documented somewhere. Probably in the settings.php docblock where we have examples of setting the collation already. Let's add that, and then we can commit this.

Needs documentation as Crell said.

Anyone using this for MyISAM probably needs to look properly at what INNODB offers over MyISAM + attempt a recovery of corrupted my MyISAM to re-enforce the distain for the antiquated engine.

There's potentially some cases for MEMORY, Archive and some MariaDB plugins like OQGraph, SphinxSE and Cassandra though all the MariaDB ones require arguments to be also accessible as an array of key/value pairs.

SQLite supports WITHOUT ROWID. Postgresql has storage parameters. So perhaps an array of table options would be a better interface for all.

options = array('Engine' => 'Sphinx', 'connection' => ...);

options = array('without rowid' => NULL....);

options = array('fillfactor' => 30.3);

These should be arguments to createTable / db_create_table rather than a setttings.php setting.

On a semi-related note using the memory engine for the semaphore table is a good fit
#1898204: Do not use InnoDB for the semaphore table, use Memory

moved my previous comments to #2232461 as it was going off the setting.php topic of this issue.

I'm thinking this can be closed?

According to #697220: Run tests on all supported database servers, this is blocking testbot support for MyISAM. If that's the case and it's a hard blocker, this should probably be elevated to critical.

I don't believe this is a hard-block for that issue. If we wanted a MyISAM testbot (why?), that would be a database-level setting not table-level. This issue *would* be a blocker for #1898204: Do not use InnoDB for the semaphore table, use Memory, but there's no reason that's a release-blocking issue.

The use case here is, as #4 noted, using more new/esoteric engines in specialized cases (cache tables, semaphore table, log table, etc.), not converting the whole DB to an engine that dates from the Clinton presidency.

If we wanted a MyISAM testbot (why?)

Because we support MyISAM, and we break it once every month or so, which also means breaking http://simplytest.me/.

I believe @jthorson is the one who suggested it's blocking the MyISAM testbot. (?)

If I recall correctly (but this is stretching the limits of what I remember), when we attempted to build a dedicated MyISAM bot at BADCamp, we discovered that the installation process was hardcoded somewhere to use InnoDB ... and thus, when the installation process ran, we ended up with InnoDB tables even if the DB default was MyISAM. Thus, we need a way to override the default in the core installation process, if we want a dedicated MyISAM testbot.

If this is accurate (and again, it's a foggy recollection), this would still be an issue in the new architecture as well.

Rummaging in the back of my brain covered in cobwebs was this recollection: I think we made the decision years ago that InnoDB was always better (because of transactions and row-level locking). However, if you specify InnoDB but InnoDB is not available MySQL will, helpfully(?), silently give you MyISAM instead. So if we always ask for InnoDB, people who don't know what a table engine is will get the better one and people on POS hosts that only offer MyISAM (do those even still exist?) will get MyISAM. I believe that memory is filed under "things we do to protect ignorant users from themselves and not ask them to think".

The current patch would only allow for varying the default engine; #5/#8 mistakenly confuse it with varying the engine per-table, which is an advanced and tricky feature to get right. (My bad for #8, sorry.) Let's save that for another issue.

Regardless, this still needs docs and a way to verify that it works (since testbot can't in this case) before it can be committed, regardless of whether it blocks anything.

I haven't tested this but wouldn't setting

$databases['default']['default']['mysql_engine'] = 'MyISAM';

in settings.php override the hardcoded InnoDB default? In that case testbot wouldn't need this patch in order to test on MyISAM would it?

Nevermind that, I misread. It would still need a patch.

Since testbot can't test this:

Using the stock settings.php:

mysql> SHOW TABLE STATUS like '%';
+-----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------------------------------------------+
| Name                  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment                                                      |
+-----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------------------------------------------+
| actions               | InnoDB |      10 | Compact    |    9 |           1820 |       16384 |               0 |            0 |  27262976 |           NULL | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores action information.                                   |
| authmap               | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |              1 | 2014-05-28 01:33:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores distributed authentication mapping.                   |
| batch                 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores details about batches (processes that run in...       |
| block                 | InnoDB |      10 | Compact    |   12 |           1365 |       16384 |               0 |        32768 |  27262976 |             13 | 2014-05-28 01:34:04 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores block settings, such as region and visibility...      |
| block_custom          | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |              1 | 2014-05-28 01:34:04 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores contents of custom-made blocks.                       |
| block_node_type       | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:02 | NULL        | NULL       | utf8_general_ci |     NULL |                | Sets up display criteria for blocks based on content types   |
| block_role            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:04 | NULL        | NULL       | utf8_general_ci |     NULL |                | Sets up access permissions for blocks based on user roles    |
| blocked_ips           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |              1 | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores blocked IP addresses.                                 |
| cache                 | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Generic cache table for caching things not separated out...  |
| cache_block           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:04 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table for the Block module to store already built...   |
| cache_bootstrap       | InnoDB |      10 | Compact    |    3 |          10922 |       32768 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table for data required to bootstrap Drupal, may be... |
| cache_field           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:03 | NULL        | NULL       | utf8_general_ci |     NULL |                | Generic cache table for caching things not separated out...  |
| cache_filter          | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:01 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table for the Filter module to store already...        |
| cache_form            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table for the form system to store recently built...   |
| cache_menu            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table for the menu system to store router...           |
| cache_page            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table used to store compressed pages for anonymous...  |
| cache_path            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Cache table for path alias lookup.                           |
| date_format_locale    | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |  27262976 |           NULL | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores configured date formats for each locale.              |
| date_format_type      | InnoDB |      10 | Compact    |    3 |           5461 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores configured date format types.                         |
| date_formats          | InnoDB |      10 | Compact    |   35 |            468 |       16384 |               0 |        16384 |  27262976 |             36 | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores configured date formats.                              |
| field_config          | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |       131072 |  27262976 |              1 | 2014-05-28 01:34:03 | NULL        | NULL       | utf8_general_ci |     NULL |                |                                                              |
| field_config_instance | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |  27262976 |              1 | 2014-05-28 01:34:03 | NULL        | NULL       | utf8_general_ci |     NULL |                |                                                              |
| file_managed          | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        65536 |  27262976 |              1 | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores information for uploaded files.                       |
| file_usage            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        49152 |  27262976 |           NULL | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Track where a file is used.                                  |
| filter                | InnoDB |      10 | Compact    |    5 |           3276 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:00 | NULL        | NULL       | utf8_general_ci |     NULL |                | Table that maps filters (HTML corrector) to text formats ... |
| filter_format         | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        32768 |  27262976 |           NULL | 2014-05-28 01:34:01 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores text formats: custom groupings of filters, such as... |
| flood                 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |  27262976 |              1 | 2014-05-28 01:33:54 | NULL        | NULL       | utf8_general_ci |     NULL |                | Flood controls the threshold of events, such as the...       |
| history               | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:34:02 | NULL        | NULL       | utf8_general_ci |     NULL |                | A record of which users have read which nodes.               |
| menu_links            | InnoDB |      10 | Compact    |  139 |            471 |       65536 |               0 |        65536 |  27262976 |            115 | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Contains the individual links within a menu.                 |
| menu_router           | InnoDB |      10 | Compact    |  141 |            697 |       98304 |               0 |        49152 |  27262976 |           NULL | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Maps paths to various callbacks (access, page and title)     |
| node                  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |       180224 |  27262976 |              1 | 2014-05-28 01:34:02 | NULL        | NULL       | utf8_general_ci |     NULL |                | The base table for nodes.                                    |
| node_access           | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |            0 |  27262976 |           NULL | 2014-05-28 01:34:02 | NULL        | NULL       | utf8_general_ci |     NULL |                | Identifies which realm/grant pairs a user must possess in... |
| node_revision         | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |  27262976 |              1 | 2014-05-28 01:34:02 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores information about each saved version of a node.       |
| node_type             | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |  27262976 |           NULL | 2014-05-28 01:34:02 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores information about all defined node types.             |
| queue                 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |  27262976 |             10 | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores items in queues.                                      |
| registry              | InnoDB |      10 | Compact    |  239 |            205 |       49152 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Each record is a function, class, or interface name and...   |
| registry_file         | InnoDB |      10 | Compact    |   88 |            186 |       16384 |               0 |            0 |  27262976 |           NULL | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Files parsed to build the registry.                          |
| role                  | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        32768 |  27262976 |              3 | 2014-05-28 01:33:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores user roles.                                           |
| role_permission       | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:57 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores the permissions assigned to user roles.               |
| semaphore             | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |  27262976 |           NULL | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Table for holding semaphores, locks, flags, etc. that...     |
| sequences             | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |            0 |  27262976 |              2 | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores IDs.                                                  |
| sessions              | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |               0 |        49152 |  27262976 |           NULL | 2014-05-28 01:33:55 | NULL        | NULL       | utf8_general_ci |     NULL |                | Drupal’s session handlers read and write into the...         |
| system                | InnoDB |      10 | Compact    |  157 |           1252 |      196608 |               0 |        32768 |  27262976 |           NULL | 2014-05-28 01:33:56 | NULL        | NULL       | utf8_general_ci |     NULL |                | A list of all modules, themes, and theme engines that are... |
| url_alias             | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        32768 |  27262976 |              1 | 2014-05-28 01:33:56 | NULL        | NULL       | utf8_general_ci |     NULL |                | A list of URL aliases for Drupal paths; a user may visit...  |
| users                 | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        81920 |  27262976 |           NULL | 2014-05-28 01:33:58 | NULL        | NULL       | utf8_general_ci |     NULL |                | Stores user data.                                            |
| users_roles           | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |  27262976 |           NULL | 2014-05-28 01:33:58 | NULL        | NULL       | utf8_general_ci |     NULL |                | Maps users to roles.                                         |
| variable              | InnoDB |      10 | Compact    |   11 |           1489 |       16384 |               0 |            0 |  27262976 |           NULL | 2014-05-28 01:33:53 | NULL        | NULL       | utf8_general_ci |     NULL |                | Named variable/value pairs created by Drupal core or any...  |
| watchdog              | InnoDB |      10 | Compact    |   13 |           1260 |       16384 |               0 |        49152 |  27262976 |             14 | 2014-05-28 01:34:04 | NULL        | NULL       | utf8_general_ci |     NULL |                | Table that contains logs of all system events.               |
+-----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------------------------------------------+
48 rows in set (0.02 sec)

While defining $databases['default']['default']['mysql_engine'] = 'MyISAM'; in settings.php:

mysql> SHOW TABLE STATUS like '%';
+-----------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------------------------------------------------------+
| Name                  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment                                                      |
+-----------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------------------------------------------------------+
| actions               | MyISAM |      10 | Dynamic    |    9 |             76 |         688 |  281474976710655 |         8192 |         0 |           NULL | 2014-05-28 01:29:46 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores action information.                                   |
| authmap               | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         2048 |         0 |              1 | 2014-05-28 01:29:51 | 2014-05-28 01:29:51 | NULL       | utf8_general_ci |     NULL |                | Stores distributed authentication mapping.                   |
| batch                 | MyISAM |      10 | Dynamic    |    0 |              0 |        1344 |  281474976710655 |         3072 |      1344 |           NULL | 2014-05-28 01:29:46 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores details about batches (processes that run in...       |
| block                 | MyISAM |      10 | Dynamic    |   12 |             40 |         484 |  281474976710655 |        10240 |         0 |             13 | 2014-05-28 01:29:57 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores block settings, such as region and visibility...      |
| block_custom          | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         2048 |         0 |              1 | 2014-05-28 01:29:57 | 2014-05-28 01:29:57 | NULL       | utf8_general_ci |     NULL |                | Stores contents of custom-made blocks.                       |
| block_node_type       | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         2048 |         0 |           NULL | 2014-05-28 01:29:54 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | Sets up display criteria for blocks based on content types   |
| block_role            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         2048 |         0 |           NULL | 2014-05-28 01:29:57 | 2014-05-28 01:29:57 | NULL       | utf8_general_ci |     NULL |                | Sets up access permissions for blocks based on user roles    |
| blocked_ips           | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         1024 |         0 |              1 | 2014-05-28 01:29:46 | 2014-05-28 01:29:46 | NULL       | utf8_general_ci |     NULL |                | Stores blocked IP addresses.                                 |
| cache                 | MyISAM |      10 | Dynamic    |    1 |             84 |          84 |  281474976710655 |         9216 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:33 | NULL       | utf8_general_ci |     NULL |                | Generic cache table for caching things not separated out...  |
| cache_block           | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:31 | NULL       | utf8_general_ci |     NULL |                | Cache table for the Block module to store already built...   |
| cache_bootstrap       | MyISAM |      10 | Dynamic    |    5 |           3331 |       16656 |  281474976710655 |         9216 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:33 | NULL       | utf8_general_ci |     NULL |                | Cache table for data required to bootstrap Drupal, may be... |
| cache_field           | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:31 | NULL       | utf8_general_ci |     NULL |                | Generic cache table for caching things not separated out...  |
| cache_filter          | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:31 | NULL       | utf8_general_ci |     NULL |                | Cache table for the Filter module to store already...        |
| cache_form            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:29:47 | 2014-05-28 01:29:47 | NULL       | utf8_general_ci |     NULL |                | Cache table for the form system to store recently built...   |
| cache_menu            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:29:47 | 2014-05-28 01:29:47 | NULL       | utf8_general_ci |     NULL |                | Cache table for the menu system to store router...           |
| cache_page            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:31 | NULL       | utf8_general_ci |     NULL |                | Cache table used to store compressed pages for anonymous...  |
| cache_path            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:30:31 | 2014-05-28 01:30:31 | NULL       | utf8_general_ci |     NULL |                | Cache table for path alias lookup.                           |
| date_format_locale    | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         1024 |         0 |           NULL | 2014-05-28 01:29:47 | 2014-05-28 01:29:47 | NULL       | utf8_general_ci |     NULL |                | Stores configured date formats for each locale.              |
| date_format_type      | MyISAM |      10 | Dynamic    |    3 |             20 |          60 |  281474976710655 |         9216 |         0 |           NULL | 2014-05-28 01:29:47 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores configured date format types.                         |
| date_formats          | MyISAM |      10 | Dynamic    |   35 |             30 |        1064 |  281474976710655 |         8192 |         0 |             36 | 2014-05-28 01:29:47 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores configured date formats.                              |
| field_config          | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         2048 |         0 |              1 | 2014-05-28 01:29:55 | 2014-05-28 01:29:55 | NULL       | utf8_general_ci |     NULL |                |                                                              |
| field_config_instance | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |              1 | 2014-05-28 01:29:56 | 2014-05-28 01:29:56 | NULL       | utf8_general_ci |     NULL |                |                                                              |
| file_managed          | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |              1 | 2014-05-28 01:29:48 | 2014-05-28 01:29:48 | NULL       | utf8_general_ci |     NULL |                | Stores information for uploaded files.                       |
| file_usage            | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |           NULL | 2014-05-28 01:29:48 | 2014-05-28 01:29:48 | NULL       | utf8_general_ci |     NULL |                | Track where a file is used.                                  |
| filter                | MyISAM |      10 | Dynamic    |    5 |             93 |         468 |  281474976710655 |        10240 |         0 |           NULL | 2014-05-28 01:29:53 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | Table that maps filters (HTML corrector) to text formats ... |
| filter_format         | MyISAM |      10 | Dynamic    |    1 |             32 |          32 |  281474976710655 |        13312 |         0 |           NULL | 2014-05-28 01:29:53 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | Stores text formats: custom groupings of filters, such as... |
| flood                 | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |              1 | 2014-05-28 01:29:48 | 2014-05-28 01:29:48 | NULL       | utf8_general_ci |     NULL |                | Flood controls the threshold of events, such as the...       |
| history               | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 3659174697238527 |         1024 |         0 |           NULL | 2014-05-28 01:29:54 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | A record of which users have read which nodes.               |
| menu_links            | MyISAM |      10 | Dynamic    |  114 |            172 |       19704 |  281474976710655 |        28672 |         0 |            115 | 2014-05-28 01:29:48 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Contains the individual links within a menu.                 |
| menu_router           | MyISAM |      10 | Dynamic    |  137 |            416 |       57080 |  281474976710655 |        35840 |         0 |           NULL | 2014-05-28 01:29:48 | 2014-05-28 01:30:33 | NULL       | utf8_general_ci |     NULL |                | Maps paths to various callbacks (access, page and title)     |
| node                  | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |              1 | 2014-05-28 01:29:54 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | The base table for nodes.                                    |
| node_access           | MyISAM |      10 | Dynamic    |    1 |             20 |          20 |  281474976710655 |         8192 |         0 |           NULL | 2014-05-28 01:29:54 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Identifies which realm/grant pairs a user must possess in... |
| node_revision         | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         1024 |         0 |              1 | 2014-05-28 01:29:54 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | Stores information about each saved version of a node.       |
| node_type             | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         1024 |         0 |           NULL | 2014-05-28 01:29:54 | 2014-05-28 01:29:54 | NULL       | utf8_general_ci |     NULL |                | Stores information about all defined node types.             |
| queue                 | MyISAM |      10 | Dynamic    |    0 |              0 |        1092 |  281474976710655 |        10240 |      1092 |             10 | 2014-05-28 01:29:48 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores items in queues.                                      |
| registry              | MyISAM |      10 | Dynamic    |  237 |             65 |       15412 |  281474976710655 |        24576 |         0 |           NULL | 2014-05-28 01:29:48 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Each record is a function, class, or interface name and...   |
| registry_file         | MyISAM |      10 | Dynamic    |   88 |             96 |        8472 |  281474976710655 |         8192 |         0 |           NULL | 2014-05-28 01:29:48 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Files parsed to build the registry.                          |
| role                  | MyISAM |      10 | Dynamic    |    2 |             28 |          56 |  281474976710655 |         4096 |         0 |              3 | 2014-05-28 01:29:51 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores user roles.                                           |
| role_permission       | MyISAM |      10 | Dynamic    |    2 |             28 |          56 |  281474976710655 |         6144 |         0 |           NULL | 2014-05-28 01:29:51 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores the permissions assigned to user roles.               |
| semaphore             | MyISAM |      10 | Dynamic    |    0 |              0 |          84 |  281474976710655 |        13312 |        84 |           NULL | 2014-05-28 01:29:49 | 2014-05-28 01:30:37 | NULL       | utf8_general_ci |     NULL |                | Table for holding semaphores, locks, flags, etc. that...     |
| sequences             | MyISAM |      10 | Fixed      |    1 |              7 |           7 | 1970324836974591 |         2048 |         0 |              2 | 2014-05-28 01:29:49 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | Stores IDs.                                                  |
| sessions              | MyISAM |      10 | Dynamic    |    1 |             88 |          88 |  281474976710655 |        12288 |         0 |           NULL | 2014-05-28 01:29:49 | 2014-05-28 01:30:37 | NULL       | utf8_general_ci |     NULL |                | Drupal’s session handlers read and write into the...         |
| system                | MyISAM |      10 | Dynamic    |  138 |            524 |       72404 |  281474976710655 |        24576 |         0 |           NULL | 2014-05-28 01:29:49 | 2014-05-28 01:30:06 | NULL       | utf8_general_ci |     NULL |                | A list of all modules, themes, and theme engines that are... |
| url_alias             | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |  281474976710655 |         4096 |         0 |              1 | 2014-05-28 01:29:49 | 2014-05-28 01:29:49 | NULL       | utf8_general_ci |     NULL |                | A list of URL aliases for Drupal paths; a user may visit...  |
| users                 | MyISAM |      10 | Dynamic    |    2 |             86 |         172 |  281474976710655 |        13312 |         0 |           NULL | 2014-05-28 01:29:51 | 2014-05-28 01:30:37 | NULL       | utf8_general_ci |     NULL |                | Stores user data.                                            |
| users_roles           | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 2533274790395903 |         1024 |         0 |           NULL | 2014-05-28 01:29:51 | 2014-05-28 01:29:51 | NULL       | utf8_general_ci |     NULL |                | Maps users to roles.                                         |
| variable              | MyISAM |      10 | Dynamic    |   19 |             56 |        1104 |  281474976710655 |         4096 |        32 |           NULL | 2014-05-28 01:29:46 | 2014-05-28 01:30:37 | NULL       | utf8_general_ci |     NULL |                | Named variable/value pairs created by Drupal core or any...  |
| watchdog              | MyISAM |      10 | Dynamic    |   15 |            242 |        3644 |  281474976710655 |         5120 |         0 |             16 | 2014-05-28 01:29:57 | 2014-05-28 01:30:37 | NULL       | utf8_general_ci |     NULL |                | Table that contains logs of all system events.               |
+-----------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------------------------------------------------------+
48 rows in set (0.08 sec)

And the Drupal 8 version

#17 looks good to me. Leaving for @Crell to RTBC though.

Close enough for government work. Thanks!

I think this is safely backportable, so tagging. (David is free to disagree with me.)

#14 was actually a D7 patch, here it is again with updated wording so it matches the D8 patch in #17.

17: drupal-myisam-D8-2152443.patch queued for re-testing.

Wondering whether this patch shouldn't add a unit test for the expectation, at least for D8?

Actually... sorry, but per #2275535: [policy, no patch] Drop support for non-transactional database engines (including MyISAM) we should probably not use MyISAM as our example alternate engine, since we're very likely going to drop support for that anyway.

NDB then?

NDB then. :-) sun, what kind of test would you want to see here? Do you mean a full install test or... what unit would you test?

The processing of settings.php values into Database connection info is a separate concern, not touched here, and hopefully covered by tests already.

Thus, essentially just that a mysql_engine in Database connection info is taken over into a CREATE TABLE statement. Ideally as a phpunit test, since we just want to assert the effective SQL statement without creating an actual table. Looks like that should be easily possible.

Based on the fact that we removed support for MyISAM in Drupal 8 at #2275535: [policy, no patch] Drop support for non-transactional database engines (including MyISAM), I think this is now relevant for D7 only.

No, there's other useful engines in MySQL that still support transactions. Hence #25.

Hi,

One other transactional storage engine which would be good to support is TokuDB
There is also work on RocksDB at Facebook which will offer even more choices.

On other hand I would consider to support only transactional storage engines, especially over time - having 2 code paths for MyISAM and everything else is quite a pain in the ass and error prone. Plus I would say majority of the users running MyISAM are doing this because it was created for them by default not recognizing the risks they have (like risk of ruined data if server is restarted without proper shutdown)

Peter: We already dropped MyISAM for Drupal 8 over a year ago. :-) Right now, everything is InnoDB. This issue is about allowing site admins to pick another engine if they want to.

Just a reroll.

Summary needs update because myisam is not supported in d8 and must not so the no reason to allow it (IMO core should prevent it) but other engines should be allowed to allow experiment with new abilities

@sokru: If you change the default.settings.php file, you also have to change the same file in core/assets/scaffold/files/default.settings.php

Should solve #45 and failing tests.

isset($info['mysql_engine']) ? $info['mysql_engine'] : 'InnoDB'
Could be written as $info['mysql_engine'] ?? 'InnoDB'

1. +++ b/core/INSTALL.mysql.txt
   @@ -41,5 +41,6 @@ If successful, MySQL will reply with:
    If the InnoDB storage engine is available, it will be used for all database
   -tables. InnoDB provides features over MyISAM such as transaction support,
   -row-level locks, and consistent non-locking reads.
   +tables unless otherwise specified in settings.php. InnoDB provides features
   +over MyISAM such as transaction support, row-level locks, and consistent
   +non-locking reads.
   

   Lets change the text to something like: "The default storage engine is InnoDB, it will be used for all database tables unless otherwise specified in settings.php. InnoDB provides features over MyISAM such as transaction support, row-level locks, and consistent non-locking reads."
   I do not know in which version of MySQL InnoDB was not available, but it was a very long time ago.

2. We also need some testing for this issue. I think the best location would be Drupal\KernelTests\Core\Database\SchemaTest.
   The test should do something like:
   1. Create a table using the default database connection.
   2. Test that the table is created with the InnoDB storage engine.
   3. Get the default connection info.
   4. Add the mysql_engine setting to the connection info.
   5. Use the changed connection info to create a new connnection.
   6. Create for the new connection a simple table.
   7. Test that the new table is created with the other storage engine.

+++ b/core/INSTALL.mysql.txt
@@ -41,5 +41,6 @@ If successful, MySQL will reply with:
+tables unless otherwise specified in settings.php. InnoDB provides features
+over MyISAM such as transaction support, row-level locks, and consistent
+non-locking reads.

I don't think we should be talking about MyISAM here - modern Drupal doesn't support it.

Adding test coverage based on #48

Also addressing pointers from #47 and #51

I have added a CR and updated the IS.
The added tests the added functionality.
All changes look good to me.
For me it is RTBC.

@raman.b: Thank you for writing the test.

To follow up on #51, should we explicitly state which engines Drupal DOES support?

@johnwebdev: In the patch is stated that the default table engine is InnoDB and the same for the CR. I have updated the IS with the same information.

#55 Yeah it does, but I think it would be helpful to list a set of engines that are supported by Drupal, where i.e. MySIAM is not one of them.

@johnwebdev: The only supported table engine for MySQL is InnoDB. For all others it is go and try for yourself and see if it works. Good luck and please report back how it went.

Right. Do we want to say that in the documentation? Something like: Drupal has only been tested and actively supports the use of InnoDB table engine.

Drupal has only been tested and actively supports the use of InnoDB table engine.

Lets add this text or something similar to the setting.php file.
I have already updated the CR with the text.

@johnwebdev: Thank you for your input.

1. +++ b/core/assets/scaffold/files/default.settings.php
   @@ -185,7 +185,8 @@
   + * variable, to reduce the database connection timeout to 5 seconds, and to use
   + * the NDB storage engine for MySQL tables:
   
   +++ b/sites/default/default.settings.php
   @@ -185,7 +185,8 @@
   + * variable, to reduce the database connection timeout to 5 seconds, and to use
   + * the NDB storage engine for MySQL tables:
   

   and to use the NDB storage engine as the default for MySQL tables:

We should add the word default. This can be overridden on the table specification level. There's no test of this function but it will definitely work. We could add a followup to add it to the test added here.

2. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Schema.php
   @@ -96,7 +96,7 @@ protected function createTableSql($name, $table) {
   -      'mysql_engine' => 'InnoDB',
   +      'mysql_engine' => $info['mysql_engine'] ?? 'InnoDB',
   

   I wonder whether setting the default in \Drupal\Core\Database\Driver\mysql\Connection::__construct() is worth it. For example:

     public function __construct(\PDO $connection, array $connection_options) {
       $connection_options += ['mysql_engine' => 'InnoDB'];
       parent::__construct($connection, $connection_options);
     }
   

   That way this info will always be set on the connection options and we don't have to test for its existence. OTOH this at least makes easy to see what is used as a default.

   Not sure.

Here i have re-rolled it against 9.3.x and also updated the 1st point from comment #60 above..