Problem/Motivation

Discovered in #2575725: Run PHPunit tests one at a time via UI to avoid exception when selecting too many. The simpletest schema looks like this:

CREATE TABLE `simpletest` (
  `message_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: Unique simpletest message ID.',
  `test_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Test ID, messages belonging to the same ID are reported together',
  `test_class` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The name of the class that created this message.',
  `status` varchar(9) NOT NULL DEFAULT '' COMMENT 'Message status. Core understands pass, fail, exception.',
  `message` text NOT NULL COMMENT 'The message itself.',
  `message_group` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The message group this message belongs to. For example: warning, browser, user.',
  `function` varchar(255) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'Name of the assertion function or method that created this message.',
  `line` int(11) NOT NULL DEFAULT '0' COMMENT 'Line number on which the function is called.',
  `file` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name of the file where the function is called.',
  PRIMARY KEY (`message_id`),
  KEY `reporter` (`test_class`,`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=739 DEFAULT CHARSET=utf8mb4 COMMENT='Stores simpletest messages'

The reporter key looks completely useless as there are no look ups on the test_class field in core - perhaps the old qa.drupal.org used it - but DrupalCI certainly does not. Also even if it was required adding the message_id to it is pointless as this is the primary key on the table.

Proposed resolution

Remove the reporter index and create an index on test_id since there are lots of lookups by that:

  • This query in SimpletestResultsForm would benefit from an index on test_id... this index does not help...
  • The query in simpletest_last_test_get() does not use the index either... again a test_id index would help...
  • The query in simpletest_script_load_messages_by_test_id() also does not use it ... and yet again a test_id index would help.
  • The query in simpletest_script_open_browser() also does not use it... and yet again a test_id index would help.
  • The query in simpletest_clean_results_table() also does not use it... and yet again a test_id index would help.
  • The query in TestBase::deleteAssert() does not use it.

Remaining tasks

User interface changes

None

API changes

None

Data model changes

Changed indexes only - no data changes.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

alexpott created an issue. See original summary.

alexpott’s picture

Version: 8.1.x-dev » 8.2.x-dev
Status: Needs work » Needs review
FileSize
2.63 KB

Status: Needs review » Needs work

The last submitted patch, 2: 2748315-2.patch, failed testing.

jonathan1055’s picture

adding the message_id to it is pointless as this is the primary key on the table.

Maybe message_id was added to make the composite index (with test_class) unique. I am guessing that this table has been modified a bit for 8.x to (attempt to) cater for running PHPunit tests, which were not in the original design of the module in Drupal6 or 7.

Good analysis on the usage (or rather non-usage) of this index, however I would suggest waiting until some of the other simpletest/PHPunit issues are resolved before altering the table. See the related issues I have just added.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.0-beta1 was released on August 3, 2016, which means new developments and disruptive changes should now be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.0-alpha1 will be released the week of January 30, 2017, which means new developments and disruptive changes should now be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.0-alpha1 will be released the week of January 17, 2018, which means new developments and disruptive changes should now be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

quietone’s picture

Project: Drupal core » SimpleTest
Version: 9.1.x-dev » 8.x-3.x-dev
Component: simpletest.module » Code

Triaging issues in simpletest.module as part of the Bug Smash Initiative to determine if they should be in the Simpletest Project or core.

This looks like it belongs in the Simpletest project.

alexpott’s picture

Project: SimpleTest » Drupal core
Version: 8.x-3.x-dev » 8.9.x-dev
Component: Code » ajax system
Issue tags: +Needs reroll

Funnily enough this schema is still part of core - see \Drupal\Core\Test\TestDatabase::testingSchema() so this is a core fix and needs a reroll.

alexpott’s picture

Component: ajax system » phpunit

Putting under phpunit because that's the generic testing bucket.

anushrikumari’s picture

Status: Needs work » Needs review
FileSize
2.82 KB

Rerolled patch for 8.9.x

anushrikumari’s picture

DamienMcKenna’s picture

Title: Fix indexes on simpletest table. » Fix indexes on simpletest table
Version: 8.9.x-dev » 9.4.x-dev
Status: Needs review » Needs work
Issue tags: -Needs reroll

It seems like this should split into two - the core/lib/Drupal/Core/Test/TestDatabase.php changes int his issue and the rest moved to the Simpletest contrib module.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.0-alpha1 was released on May 6, 2022, which means new developments and disruptive changes should now be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 10.1.x-dev

Drupal 9.5.0-beta2 and Drupal 10.0.0-beta2 were released on September 29, 2022, which means new developments and disruptive changes should now be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 10.1.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch, which currently accepts only minor-version allowed changes. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.