CDbException on password update or new user registration

[only1chunts]

Describe the bug
CDbException when trying to update user password or register an account

To Reproduce
Steps to reproduce the behavior:

1. Go to the change password page https://staging.gigadb.org/user/changePassword
2. fill in form
3. Click on save
4. See CDbException

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots

n1 - CDbException when trying to update user password or register an account

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(64). The SQL statement executed was: UPDATE "gigadb_user" SET "id"=:yp0, "email"=:yp1, "password"=:yp2, "first_name"=:yp3, "last_name"=:yp4, "affiliation"=:yp5, "role"=:yp6, "is_activated"=:yp7, "newsletter"=:yp8, "previous_newsletter_state"=:yp9, "facebook_id"=:yp10, "twitter_id"=:yp11, "linkedin_id"=:yp12, "google_id"=:yp13, "username"=:yp14, "orcid_id"=:yp15, "preferred_link"=:yp16 WHERE "gigadb_user"."id"=35. Bound with :yp0=35, :yp1='[email protected]', :yp2='$argon2id$v=19$m=65536,t=2,p=1$DrmhmKjMKz8k41rg9Wc9LA$E0rSktXtxL6msxt/VQeG6tqRSdXRxTTnDzZbLNCSnsk', :yp3='Chris', :yp4='Hunter', :yp5='GigaDB', :yp6='user', :yp7=true, :yp8=true, :yp9=false, :yp10=NULL, :yp11=NULL, :yp12=NULL, :yp13=NULL, :yp14='[email protected]', :yp15=NULL, :yp16='EBI'

/var/www/vendor/yiisoft/yii/framework/db/CDbCommand.php(358)

346         {
347             if($this->_connection->enableProfiling)
348                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().$par.')','system.db.CDbCommand.execute');
349 
350             $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;
351             $message=$e->getMessage();
352             Yii::log(Yii::t('yii','CDbCommand::execute() failed: {error}. The SQL statement executed was: {sql}.',
353                 array('{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
354 
355             if(YII_DEBUG)
356                 $message.='. The SQL statement executed was: '.$this->getText().$par;
357 
358             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
359                 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
360         }
361     }
362 
363     /**
364      * Executes the SQL statement and returns query result.
365      * This method is for executing an SQL query that returns result set.
366      * @param array $params input parameters (name=>value) for the SQL execution. This is an alternative
367      * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing
368      * them in this way can improve the performance. Note that if you pass parameters in this way,
369      * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.
370      * Please also note that all values are treated as strings in this case, if you need them to be handled as

Stack Trace

#0	+  /var/www/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(1756): CDbCommand->execute()
#1	+  /var/www/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(1130): CActiveRecord->updateByPk(35, array("id" => 35, "email" => "[email protected]", "password" => "$argon2id$v=19$m=65536,t=2,p=1$DrmhmKjMKz8k41rg9Wc9LA$E0rSktXtxL...", "first_name" => "Chris", ...))
#2	+  /var/www/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(811): CActiveRecord->update(null)
#3	–  /var/www/protected/models/ChangePasswordForm.php(46): CActiveRecord->save(false)41 $user->password = $this->password; 42 $user->password_repeat = $this->confirmPassword; 43 $user->newsletter = $this->newsletter; 44 $user->encryptPassword(); 45 46 if($user->save(false)) { 47 return true; 48 } 49 } 50 return false; 51 }
#4	–  /var/www/protected/controllers/UserController.php(406): ChangePasswordForm->changePass()401 402 if(isset($_POST['ChangePasswordForm'])) 403 { 404 $model->attributes=$_POST['ChangePasswordForm']; 405 $model->newsletter=$_POST['ChangePasswordForm']['newsletter']; 406 if($model->validate() && $model->changePass()) 407 $this->redirect('/user/view_profile'); 408 } 409 $model->password = $model->confirmPassword = ''; 410 $this->render('changePassword',array('model'=>$model)); 411 }
#5	+  /var/www/vendor/yiisoft/yii/framework/web/actions/CInlineAction.php(49): UserController->actionChangePassword()
#6	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(308): CInlineAction->runWithParams(array())
#7	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(134): CController->runAction(CInlineAction)
#8	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CFilter.php(40): CFilterChain->run()
#9	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(1148): CFilter->filter(CFilterChain)
#10	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CInlineFilter.php(58): CController->filterAccessControl(CFilterChain)
#11	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(131): CInlineFilter->filter(CFilterChain)
#12	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(291): CFilterChain->run()
#13	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(265): CController->runActionWithFilters(CInlineAction, array("accessControl"))
#14	+  /var/www/vendor/yiisoft/yii/framework/web/CWebApplication.php(282): CController->run("changePassword")
#15	+  /var/www/vendor/yiisoft/yii/framework/web/CWebApplication.php(141): CWebApplication->runController("user/changePassword")
#16	+  /var/www/vendor/yiisoft/yii/framework/base/CApplication.php(185): CWebApplication->processRequest()
#17	–  /var/www/index.php(23): CApplication->run()18 require_once($composer); 19 20 # Run the Yii1.1 application 21 Yii::$enableIncludePath = false; 22 $config=dirname(FILE).'/protected/config/main.php'; 23 Yii::createWebApplication($config)->run();

2021-11-30 10:59:00 nginx/1.21.3 Yii Framework/1.1.20-yii2-2.0.15.1-migration

Originally posted by @only1chunts in #747 (comment)

Additional information

• Likely to be a database problem

[only1chunts]

see also #864 - same error message when trying to create a new account

[only1chunts]

still getting CDb exception in Jan20 version:

CDbException

CDbCommand failed to execute the SQL statement: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(64). The SQL statement executed was: UPDATE "gigadb_user" SET "id"=:yp0, "email"=:yp1, "password"=:yp2, "first_name"=:yp3, "last_name"=:yp4, "affiliation"=:yp5, "role"=:yp6, "is_activated"=:yp7, "newsletter"=:yp8, "previous_newsletter_state"=:yp9, "facebook_id"=:yp10, "twitter_id"=:yp11, "linkedin_id"=:yp12, "google_id"=:yp13, "username"=:yp14, "orcid_id"=:yp15, "preferred_link"=:yp16 WHERE "gigadb_user"."id"=35. Bound with :yp0=35, :yp1='[email protected]', :yp2='$argon2id$v=19$m=65536,t=2,p=1$BNyHBP32MiD+81R351wrxA$HkODM8OfS9yRjX4BpYbAmROgKwut0A82mIRH/mRD3yU', :yp3='Chris', :yp4='Hunter', :yp5='GigaScience database', :yp6='user', :yp7=true, :yp8=NULL, :yp9=true, :yp10=NULL, :yp11=NULL, :yp12=NULL, :yp13=NULL, :yp14='[email protected]', :yp15=NULL, :yp16='EBI'

/var/www/vendor/yiisoft/yii/framework/db/CDbCommand.php(358)

346         {
347             if($this->_connection->enableProfiling)
348                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().$par.')','system.db.CDbCommand.execute');
349 
350             $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;
351             $message=$e->getMessage();
352             Yii::log(Yii::t('yii','CDbCommand::execute() failed: {error}. The SQL statement executed was: {sql}.',
353                 array('{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
354 
355             if(YII_DEBUG)
356                 $message.='. The SQL statement executed was: '.$this->getText().$par;
357 
358             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
359                 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
360         }
361     }
362 
363     /**
364      * Executes the SQL statement and returns query result.
365      * This method is for executing an SQL query that returns result set.
366      * @param array $params input parameters (name=>value) for the SQL execution. This is an alternative
367      * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing
368      * them in this way can improve the performance. Note that if you pass parameters in this way,
369      * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.
370      * Please also note that all values are treated as strings in this case, if you need them to be handled as

Stack Trace

#0	+  /var/www/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(1756): CDbCommand->execute()
#1	+  /var/www/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(1130): CActiveRecord->updateByPk(35, array("id" => 35, "email" => "[email protected]", "password" => "$argon2id$v=19$m=65536,t=2,p=1$BNyHBP32MiD+81R351wrxA$HkODM8OfS9...", "first_name" => "Chris", ...))
#2	+  /var/www/vendor/yiisoft/yii/framework/db/ar/CActiveRecord.php(811): CActiveRecord->update(null)
#3	–  /var/www/protected/controllers/UserController.php(286): CActiveRecord->save(false)281 $user->is_activated=true; 282 $user->terms= $attrs['terms']; 283 $user->newsletter= $attrs['newsletter']; 284 $user->encryptPassword(); 285 286 if ($user->save(false)) { 287 Yii::log("Update password and prepare to send email"); 288 $this->sendPasswordEmail($user); 289 } 290 else { 291 Yii::log(FUNCTION."> Error: could not save new user password", 'error');
#4	+  /var/www/vendor/yiisoft/yii/framework/web/actions/CInlineAction.php(49): UserController->actionReset()
#5	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(308): CInlineAction->runWithParams(array("username" => "", "style" => "float:right"))
#6	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(134): CController->runAction(CInlineAction)
#7	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CFilter.php(40): CFilterChain->run()
#8	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(1148): CFilter->filter(CFilterChain)
#9	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CInlineFilter.php(58): CController->filterAccessControl(CFilterChain)
#10	+  /var/www/vendor/yiisoft/yii/framework/web/filters/CFilterChain.php(131): CInlineFilter->filter(CFilterChain)
#11	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(291): CFilterChain->run()
#12	+  /var/www/vendor/yiisoft/yii/framework/web/CController.php(265): CController->runActionWithFilters(CInlineAction, array("accessControl"))
#13	+  /var/www/vendor/yiisoft/yii/framework/web/CWebApplication.php(282): CController->run("reset")
#14	+  /var/www/vendor/yiisoft/yii/framework/web/CWebApplication.php(141): CWebApplication->runController("user/reset/username//style/float:right")
#15	+  /var/www/vendor/yiisoft/yii/framework/base/CApplication.php(185): CWebApplication->processRequest()
#16	–  /var/www/index.php(23): CApplication->run()18 require_once($composer); 19 20 # Run the Yii1.1 application 21 Yii::$enableIncludePath = false; 22 $config=dirname(FILE).'/protected/config/main.php'; 23 Yii::createWebApplication($config)->run();

2022-01-20 18:33:48 nginx/1.21.3 Yii Framework/1.1.20-yii2-2.0.15.1-migration

[rija]

When using: https://staging.gigadb.org/user/reset/username/
It works with some email (e.g: Mary Ann). Doesn't work with others (like Chris H' work email)

The server error is the same as described in description

[pli888]

still getting CDb exception in Jan20 version:

@rija @only1chunts I've instantiated a new dockerhost EC2 server but have not been able to replicate the CDb exception when changing a user's password on the https://staging.gigadb.org/user/changePassword page. Debug messages do not show any problems with running database migrations which is also confirmed by the m500000_000100_update_password_column_gigadb_user_tab in the tbl_migration table. In addition, the gigadb_user table has a password column that is varchar(128), and not varchar(64).

@only1chunts If you can confirm that the change password functionality is working on the staging server then I suggest we move this ticket to the Tasks Done column. But I will do the same manually checks on the change password functionality when I next have to re-deploy the staging server to make sure the problem does not re-occur.

[only1chunts]

I still get the CDb Exception, try to reset the password for "[email protected]"
1 - goto: https://staging.gigadb.org/user/reset/username/
2 - insert username "[email protected]"
3 - click "Reset"

4 - see error:

[only1chunts]

NB whilst testing this I attempted to delete my [email protected] user account, but that functionality isn't working in staging, I dont think its working in live either! so I have create a low impact bug ticket for that too #952
When I used the admin page to delete my account it didn't delete it, but it did flag it as "not activated" instead?!

[pli888]

CDbException when trying to update user password or register an account

@only1chunts I'm able to update my password for my user account on beta.gigadb.org using the /user/changePassword page without getting an CDbException error message.

I can also created a new account on the beta website too.

[only1chunts]

beta appears to be down for me so I cant check at the moment

[only1chunts]

it up now, and I can confirm that the reset password page is indeed working for me, as is the change password link and the create new account pages.