Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

user_audits_log table has bad index

Description

SAK-23634 created user_audits_log table with index on (id, site_id) but all queries are done against site_id. MySQL does a full table scan.

ALTER TABLE `user_audits_log`
MODIFY COLUMN `site_id` varchar(99) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `id`,
MODIFY COLUMN `role_name` varchar(99) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `user_id`,
DROP INDEX `user_audits_log_index`,
ADD INDEX `user_audits_log_index`(`site_id`);

Here are the queries I see in the logs:

sql = "select user_id, role_name, action_taken, audit_stamp, source, action_user_id from user_audits_log where site_id = '" + siteId + "' order by audit_stamp desc"; String sql = "delete from user_audits_log where site_id = ?";

Activity

Show:

Curtis Van Osch March 28, 2019 at 10:19 AM

Same problem here dropping the index on Oracle.  I'm not sure why the primary key constraint is referencing that index when the primary key is only supposed to be ID (according to the script for creating the table).

I guess the correct approach is to drop the offending constraint in order to drop the index and then recreate both?

randall l smtih February 13, 2019 at 11:47 AM

I just used the sakai_12_oracle_conversion.sql script to upgrade from Sakai 11.4 to Sakai 12.   I noticed a database error when executing:

DROP INDEX `user_audits_log_index`,

Error stated that index could not be dropped because of existing primary key constraint.   Assume the index added here is for query by site_id and is not intended to replace existing primary key index.

Matthew Jones October 16, 2017 at 10:10 AM

Merged this to 12, not sure what we'd do with 11 since the conversion scripts moved. Would probably have to go into an 11.5 script and people notified if this was important enough to be merged back so leaving it for now.

Sam Ottenhoff October 9, 2017 at 11:31 AM

I'm running this in PROD ... confirmed index fix is good

Fixed

Details

Priority

Affects versions

Fix versions

Components

Assignee

Reporter

Conversion Script Required

Yes

Created October 6, 2017 at 9:46 AM
Updated March 28, 2019 at 10:19 AM
Resolved October 9, 2017 at 10:01 AM

Flag notifications