Null values in user settings, safe to remove?

I’m not sure if those are artifacts from previous upgrades, but I have a lot of stuff like that:


In most cases I have the same information with and without NULL in the assoc_type and assoc_id. I noticed that users that had this sort of “information” appear more than once in the list of reviewers, and are counted more than once in the list of users. Also, even editing the user profile I cannot get rid of those NULL values. I assume those values should never be NULL by the current schema, right? Are these safe to be removed?

Also, is there a way for me to check the same issue on other tables? Which tables can have NULL values?

1 Like

(Cross-posted: [OJS] Some user_settings values cannot be edited out due to errors in the database · Issue #7146 · pkp/pkp-lib · GitHub)

Bumping this up, can I change the NULL values to 0 here? Is there ever a reason for some of those to be NULL?

Hi @luizborges,

In schema, the default values for those columns in stable 3.3.0 branch is 0: pkp-lib/common.xml at stable-3_3_0 · pkp/pkp-lib · GitHub

@Vitaliy would it be safe to change all the NULL values to 0 then? I would probably have to remove those entries will that would be duplicated before doing this change.

In your specific case, if replacing NULL to 0, e.g., in preferredPublicName, you will have duplicates. MySQL will throw an error.

Can you look at the PHP log for the error associated with the problem, described on the GitHub issue page?

I’d say that it’s better to explore the problem in more details first, before changing the database.

I found no error in the logs, and I think they aren’t being created, they are probably old stuff that remained from previous migrations.

The program logic is probably ignoring that those values exist and just saving using 0’s, but when retrieving it isn’t considering that it might be NULL (or maybe PHP has some type coercion that converts NULL to 0, don’t know) and since the NULL entries precede the 0 entries, those are the ones showed on the system.

Can you reproduce the error with not being able to update the user profile?

I agree that it looks like those NULL values come from an old release.
Also, this is the first report that those NULL values cause problems, thus, it’s possible that the error may originate from a different cause. For example, I’m not able to reproduce the error on my local OJS instance by creating entries like on the screenshot.

  • If there are 2 identical entries but one with NULL values in assoc... fields and another has 0 values, say, like preferredPublicName row for en_US locale in the example, changing referredPublicName from user profile form is successful. Values of the latter column are updated.

  • If exists only row with NULL values for assoc... fields, then the form during initialization reads setting value for preferredPublicName from it; when saving, it duplicates the row but with 0 values for assoc_id and assoc_type and saves setting value there.

Is it the same in your case?

In general, from a brief look, I can say that in this scenario, User object is updated/created without unexpected results that may cause errors. Those NULL values shouldn’t cause a problem. I’m trying to understand the priority of this.

It does exactly like you describe, it updates/creates the 0 values and leaves the NULL untouched.

What I found was that when reading values, the NULL values were displayed, probably because they appear before than the 0 entries in the database. So if someone updated their display name, the information would be saved, but it would not be reflected on the system, since the old NULL data would be displayed instead of the new entries.

Also, in the assign reviewer and user grid, NULL entries in the display name affected the user count, to the point that in the assign reviewer I found users being displayed four times (they had 4 settings for preferred name in different locales). Actually, this user is the one from the screenshot above. After I removed the NULL values it stopped being counted repeatedly…

Hi @luizborges,

Thanks for the explanation. I’ve opened an issue: Null values in assoc columns in user_settings prevent correct data reading after update · Issue #7167 · pkp/pkp-lib · GitHub.