Let me start by saying this is not a common problem. It stems from an old phpBB installation where the user database table has been modified in someway, though there are likely other ways for it to be triggered.
Any failed insert into the USERS_TABLE will not be logged and will not throw an error.
Lets say for any reason the insert has failed. The $user_id that is returned will be (int) 0. The check in the ucp_register.php file looks for === false as an error, not == false (which would include 0). This means that a $user_id of 0, which is a failure, will not trigger a failure.
The user will be displayed a successful registration screen AND receive an activation email (if enabled) where the $user_id is 0.
In functions_user.php (function user_add):
In ucp_register.php (function main):
Change the code in ucp_register.php to:
Add error checking to the SQL statement in the functions_user.php file.