Oracle 12 bug - missing provided user emails and names

[Will Humphries]

Hi there,

A bug in Oracle 12c can cause Sakai to fail to look up provided users'
email, first name, and last name.

I haven't made a JIRA for this because it's not a Sakai bug. If there's
a better place to make note of this let me know - maybe a note in
https://confluence.sakaiproject.org/display/DOC/Sakai+10+database+support or
https://confluence.sakaiproject.org/display/DOC/Sakai+Admin+Guide+-+Database+Configuration+and+Tuning
?

If you answer yes to all of the 4 questions below, you may be impacted
by this Oracle bug:

Are you running Oracle 12c, version 12.1.0.1 or higher?

Do you have a mix of provided and local users?
- provided users have a sakai_user_id_map record with no corresponding
sakai_user record. Their e-mail, first name, last name are pulled from
something like LDAP on demand.

Do you have optimizer_adaptive_features set to true in your Oracle db?
- It's true by default in 12c.

Have you *not* applied this patch?
https://support.oracle.com/rs?type=bug&id=18430870
- You need an Oracle support account to access this link. Tufts' DBA's
provided me with the contents, let me know if you need them.

===================
Steps to duplicate
===================
Do this on a non-production instance.
1. Identify a site that has both provided and local users enrolled.
2. Add the Messages tool to that site if it's not already present.
3. Restart Tomcat, or clear caches (Admin Workspace->Memory->Reset All
Caches)
4. Visit the site, visit the Messages tool, and click 'Compose Message'.
This must be done before any other tool in the site is visited.

When you look at the drop-down list of users in the 'To' box, all of the
provided users should only have their eid (username) displayed. If you
attempt to e-mail those users, they won't receive an e-mail.

The users may continue to show up with only their usernames throughout
Sakai for a period of time, while their data lives in a cache. I don't
recall which cache it was, but the duration at Tufts was 10 minutes.


===================
How to fix:
===================
Tufts chose to disable adaptive query optimization altogether. Other
Oracle-backed applications we run recommended that we disable it for
those applications, so we felt comfortable with that approach. It's also
the workaround recommended in the support link above. Here's the SQL our
DBA's used to do this
alter system set optimizer_adaptive_features=false scope=both

You could also try applying the patch from Oracle, again from the link
above. If you do that with success, please report back!

There's no released version of Oracle 12c that includes the patch. It'll
be included in 12.2, but that's not coming until 2016.


===================
Details on the bug:
===================
Oracle 12c has a new 'adaptive query optimization' feature. A bug in
that feature results in some 'left join' queries having incorrect
results. Specifically, the joining field in the table being joined to
will have values returned even if there is no row with that value. One
of the queries Sakai uses to look up user info is impacted.

This is the query, from UserSqlServiceDefault.getUsersWhereIdsInSql()
"select SAKAI_USER_ID_MAP.USER_ID, SAKAI_USER_ID_MAP.EID,
SAKAI_USER.USER_ID AS USER_USER_ID, SAKAI_USER.EMAIL,
SAKAI_USER.FIRST_NAME, SAKAI_USER.LAST_NAME, SAKAI_USER.TYPE,
SAKAI_USER.PW, SAKAI_USER.CREATEDBY, SAKAI_USER.MODIFIEDBY,
SAKAI_USER.CREATEDON, SAKAI_USER.MODIFIEDON from SAKAI_USER_ID_MAP left
join SAKAI_USER on SAKAI_USER_ID_MAP.USER_ID=SAKAI_USER.USER_ID where
SAKAI_USER_ID_MAP.USER_ID in ('?','?')"

If at least one of the user_id's included in the query has a sakai_user
record, and at least one user_id doesn't have a sakai_user record, all
of the rows returned will have that row's user_id present for
sakai_user.user_id, even those user_id's which have no sakai_user
record. Sakai sees that value, and assumes the provided users are local
users with no email, first name, or last name.

Only some tools use this query to look up users. Site Info doesn't, for
example. So if an instructor somebody visits Site Info before they visit
Messages, they won't be impacted.


===================
Duplicating from db client
===================
Get user_id's for a provided and local user.

Run this query with the two user_id's you found:

select SAKAI_USER_ID_MAP.USER_ID, SAKAI_USER.USER_ID AS USER_USER_ID,
SAKAI_USER.MODIFIEDON from SAKAI_USER_ID_MAP left join SAKAI_USER on
SAKAI_USER_ID_MAP.USER_ID=SAKAI_USER.USER_ID where
SAKAI_USER_ID_MAP.USER_ID in ('', '');

If the bug is present, the provided user will have two columns returned.
One of them is a sakai_user.user_id value that doesn't exist in that table.

Then run the same query with an order by:

select SAKAI_USER_ID_MAP.USER_ID, SAKAI_USER.USER_ID AS USER_USER_ID,
SAKAI_USER.MODIFIEDON from SAKAI_USER_ID_MAP left join SAKAI_USER on
SAKAI_USER_ID_MAP.USER_ID=SAKAI_USER.USER_ID where
SAKAI_USER_ID_MAP.USER_ID in ('', '') order by user_id;

Note that the provided user_id is only shown in one column, as it should be.

[Neal Caidin]

That would be great if you wouldn't mind updating the Confluence docs. 

-- Neal

--
You received this message because you are subscribed to the Google Groups "Sakai Production" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-producti...@apereo.org.
To post to this group, send email to sakai-pr...@apereo.org.
Visit this group at http://groups.google.com/a/apereo.org/group/sakai-production/.