Custom columns in xf_user getting overridden

[caleb89taylor]

Hi all,
We have some custom columns in xf_user that hold some custom auth id's. For new users after we upgraded from 1.1 to 1.3 these values are returning as 0 even though they have some value like 123455 in the database. Debugging all the way down to a query being run to get user by userId shows that when I run the exact query:

SELECT user.* , user_profile.*, user_option.*, user_privacy.*, permission_combination.cache_value AS global_permission_cache FROM xf_user AS user LEFT JOIN xf_user_profile AS user_profile ON (user_profile.user_id = user.user_id) LEFT JOIN xf_user_option AS user_option ON (user_option.user_id = user.user_id) LEFT JOIN xf_user_privacy AS user_privacy ON (user_privacy.user_id = user.user_id)
LEFT JOIN xf_permission_combination AS permission_combination ON (permission_combination.permission_combination_id = user.permission_combination_id) WHERE user.user_id = '12345';

in MySql workbench I get all the correct values as expected, but the result of:

return $this->_getDb()->fetchRow('                        
        SELECT user.*                                     
                ' . $joinOptions['selectFields'] . '      
        FROM xf_user AS user                              
        ' . $joinOptions['joinTables'] . '                
        WHERE user.user_id = ?                            
', $userId);

in Xenforo_Model_User::getUserById() returns a PHP object where our custom column id is 0.

My question is it possible this value is getting casted to 0 based upon some expected data type? I've tried modifying the column type to VARCHAR and INTEGER to test but it still gets converted to 0. It's currently a BIGINT. Any help would be appreciated.

 

[Mike]

What's the name of the column? Is it getting "covered" by another column with that name perhaps?

 

[caleb89taylor]

ign_auth_id. I don't believe it's getting covered by another column. In the PHP object getting returned I do see it a field called "external_auth" as an encoded value. Must be custom code that got changed around.

Edit: Found it. I was getting overridden by a join. Thanks for the help.

 

[Mike]

I really can't say I see any reason for that to not work then. The external_auth thing did change, but it wouldn't change your column.

I suppose it could be bigint related, though we did have a couple bigint columns in the past I believe, though you may not have used them. The only reason I could see bigint being an issue is if your PHP MySQL library version differs from your MySQL version and that there's an incompatibility (this has happened in a few cases, but I haven't heard anything like this with bigint). Regardless, converting to a different type for testing should be sufficient to solve it. You may need to throw some different queries at it manually to see if you can discover any rhyme or reason to when it's invalid.

 

[caleb89taylor]

I really can't say I see any reason for that to not work then. The external_auth thing did change, but it wouldn't change your column.

I suppose it could be bigint related, though we did have a couple bigint columns in the past I believe, though you may not have used them. The only reason I could see bigint being an issue is if your PHP MySQL library version differs from your MySQL version and that there's an incompatibility (this has happened in a few cases, but I haven't heard anything like this with bigint). Regardless, converting to a different type for testing should be sufficient to solve it. You may need to throw some different queries at it manually to see if you can discover any rhyme or reason to when it's invalid.

See my edit. I think something changed in how the user is looked up or the join is done or something as one of the tables didn't get the correct custom column value. It was correct in xf_user but not in xf_user_profile and it was taking the empty value from xf_user_profile.