Database update error "1062 Duplicate entry '0' for key 'PRIMARY'"

More
3 years 8 months ago #125709 by christianl
Hello community,

when ugrading Limesurvey from version 2.06+ build 150729 to version 206+ build150911 I get this error message:

CDbCommand konnte das SQL-Statement nicht ausführen: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

How do I turn on advanced logging so that I can find the causing table? PHP or mySQL? mysqlcheck is reporting no errors in the database itself, Limesurvey version 2.06+ Build 150729 is running fine.
How to upgrade my database to the newest version?

Thanks

Please Log in or Create an account to join the conversation.

LimeSurvey Partners
More
3 years 8 months ago - 3 years 8 months ago #125713 by LouisGac
you can turn on debug mode in application/configuration/config.php
Last edit: 3 years 8 months ago by LouisGac.
The following user(s) said Thank You: christianl

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago - 3 years 8 months ago #125723 by christianl
OK, that helped.
There was indeed a survey with duplicate id (both 0) in the answer table.
Don't know how this can happen.
I changed one id to 1 and the upgrade process finishes smoothly.
But after this when I login, this error shows up.

Trying to get property of non-object
/var/wwws/limesurvey/application/helpers/common_helper.php(203)

A lot of error messages and no limesurvey.
Any suggestions?

Thanks
PHP notice

Trying to get property of non-object

/var/wwws/limesurvey/application/helpers/common_helper.php(203)

191 $timeadjust = getGlobalSetting('timeadjust');
192 App()->setLanguage((isset(Yii::app()->session) ? Yii::app()->session : 'en'));
193
194 if(is_null($cached)) {
195 $surveyidresult = Survey::model()
196 ->permission(Yii::app()->user->getId())
197 ->with('defaultlanguage')
198 ->findAll(array('order'=>'surveyls_title'));
199
200 $surveynames = array();
201 foreach ($surveyidresult as $result)
202 {
203 $surveynames[] = array_merge($result->attributes, $result->defaultlanguage->attributes);
204 }
205
206 $cached = $surveynames;
207 } else {
208 $surveynames = $cached;
209 }
210 $surveyselecter = "";
211 if ($returnarray===true) return $surveynames;
212 $activesurveys='';
213 $inactivesurveys='';
214 $expiredsurveys='';
215 if ($surveynames)

Stack Trace
#0

/var/wwws/limesurvey/application/views/admin/super/adminmenu.php(99): getSurveyList(false, null)

094 <?php }?>
095 </div>
096 <div class='menubar-right'>
097 <label for='surveylist'><?php eT("Surveys:");?></label>
098 <select id='surveylist' name='surveylist' onchange="if (this.options[this.selectedIndex].value!='') {window.open('<?php echo $this->createUrl("/admin/survey/sa/view/surveyid/"); ?>/'+this.options[this.selectedIndex].value,'_top')} else {window.open('<?php echo $this->createUrl("/admin/survey/sa/index/");?>','_top')}">
099 <?php echo getSurveyList(false, $surveyid); ?>
100 </select>
101 <a href="<?php echo $this->createUrl("admin/survey/sa/index"); ?>">
102 <img src='<?php echo $sImageURL;?>surveylist.png' alt='<?php eT("Detailed list of surveys");?>' />
103 </a>
104

#1
+
/var/wwws/limesurvey/framework/web/CBaseController.php(126): require("/var/wwws/limesurvey/application/views/admin/super/adminmenu.php")
#2
+
/var/wwws/limesurvey/framework/web/CBaseController.php(95): CBaseController->renderInternal("/var/wwws/limesurvey/application/views/admin/super/adminmenu.php", array("showupdate" => false, "surveyid" => null, "iconsize" => "40", "sImageURL" => "/limesurvey/styles/blobblueish/images/"), true)
#3
+
/var/wwws/limesurvey/framework/web/CController.php(869): CBaseController->renderFile("/var/wwws/limesurvey/application/views/admin/super/adminmenu.php", array("showupdate" => false, "surveyid" => null, "iconsize" => "40", "sImageURL" => "/limesurvey/styles/blobblueish/images/"), true)
#4

/var/wwws/limesurvey/application/controllers/AdminController.php(391): CController->renderPartial("/admin/super/adminmenu", array("showupdate" => false, "surveyid" => null, "iconsize" => "40", "sImageURL" => "/limesurvey/styles/blobblueish/images/"))

386 $aData = $updateNotification->result;
387
388 $aData = $surveyid;
389 $aData = Yii::app()->getConfig('adminthemeiconsize');
390 $aData = Yii::app()->getConfig('adminimageurl');
391 $this->renderPartial("/admin/super/adminmenu", $aData);
392
393 }
394
395 public function _loadEndScripts()
396 {

#5

/var/wwws/limesurvey/application/core/Survey_Common_Action.php(252): AdminController->_showadminmenu(null)

247 if (!isset($aData) || ($aData !== false && (!is_array($aData) || !in_array('browse', array_keys($aData)))))
248 {
249 if(!isset($aData))
250 {
251 $this->_updatenotification();
252 Yii::app()->getController()->_showadminmenu(!empty($aData) ? $aData : null);
253 }
254
255 if (!empty($aData))
256 {
257

#6
+
/var/wwws/limesurvey/application/controllers/admin/globalsettings.php(300): Survey_Common_Action->_renderWrappedTemplate("", "globalSettings_view", array("title" => "hi", "message" => "message", "usercount" => "18", "surveycount" => "101", ...))
#7
+
/var/wwws/limesurvey/application/controllers/admin/globalsettings.php(105): GlobalSettings->_renderWrappedTemplate("", "globalSettings_view", array("title" => "hi", "message" => "message", "usercount" => "18", "surveycount" => "101", ...))
#8
+
/var/wwws/limesurvey/application/controllers/admin/globalsettings.php(45): GlobalSettings->_displaySettings()
#9
unknown(0): GlobalSettings->index()
#10
+
/var/wwws/limesurvey/framework/web/actions/CAction.php(108): ReflectionMethod->invokeArgs(GlobalSettings, array())
#11
+
/var/wwws/limesurvey/application/core/Survey_Common_Action.php(99): CAction->runWithParamsInternal(GlobalSettings, ReflectionMethod, array())
#12
+
/var/wwws/limesurvey/framework/web/CController.php(308): Survey_Common_Action->runWithParams(array())
#13
+
/var/wwws/limesurvey/framework/web/CController.php(286): CController->runAction(GlobalSettings)
#14
+
/var/wwws/limesurvey/framework/web/CController.php(265): CController->runActionWithFilters(GlobalSettings, array())
#15
+
/var/wwws/limesurvey/application/controllers/AdminController.php(164): CController->run("globalsettings")
#16
+
/var/wwws/limesurvey/framework/web/CWebApplication.php(282): AdminController->run("globalsettings")
#17
+
/var/wwws/limesurvey/framework/web/CWebApplication.php(141): CWebApplication->runController("admin/globalsettings")
#18
+
/var/wwws/limesurvey/framework/base/CApplication.php(184): CWebApplication->processRequest()
#19
+
/var/wwws/limesurvey/index.php(211): CApplication->run()
Last edit: 3 years 8 months ago by christianl. Reason: errormessage added

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago #125725 by LouisGac
What happen is that the code try to generate the list of surveys for the select box in the menu bar and can't get them.

Here the call to the model (to the survey table) :
  $surveyidresult = Survey::model()
            ->permission(Yii::app()->user->getId())
            ->with('defaultlanguage')
            ->findAll(array('order'=>'surveyls_title'));

Basically, it says : "find all the surveys the user has the permission to access, joined with its default languages "

In your case, this request fails.

I'd say it's because you changed the id of the survey, but you didn't changed its "foreign keys" in other tables.
In this case, in the SurveyLanguageSetting table, you must changed the surveyls_survey_id to the new one...
The following user(s) said Thank You: christianl

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago #125731 by christianl
Actual I'm hosting 101 surveys.
With phpmyadmin I took a look in the lime_surveys_languagesettings table.
I found a survey with no corresponding entry in the lime_surveys table which also had the language set to NULL.
After deleting the entry everything works fine.
So actually a broken limesurvey table, but I still can't explain how this happened.
Thanks again

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago #125768 by DenisChenu
bugs.limesurvey.org/view.php?id=9890

I don't want to fix it actually .... i want to know how it happens .... and if we can find the problem in db fix.

Do you have a languagesettings for this survey id ?

Denis

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand .
An error happen ? Before make a new topic : remind the Debug mode .

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago - 3 years 8 months ago #125773 by christianl
Hi,

what do you mean by languagesetting?
In my case there was a survey in the table lime_surveys (here number 745884) which had no corresponding entry in lime_surveys_languagesettings. Furthermore the language in lime_surveys was set to NULL.
The survey was created 2014-08-12 but not many other information has been saved. No owner, owner_id 0, no mail address and so on.
So somehow this was either a remaining of an unsuccessful deletion or creation.
I don't have any further information about this. I don't think that we deleted an old language as you suggested in your bug report. This could possibly have happened during an update process.
Maybe it's just possible to intercept this error message of the yii framework by an error trap, which checks for NULL-values when actually data is expected.

Cheers
Last edit: 3 years 8 months ago by christianl. Reason: typo

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago #125774 by DenisChenu

christianl wrote: the table lime_surveys (here number 745884) which had no corresponding entry in lime_surveys_languagesettings.

I have my answer :)

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand .
An error happen ? Before make a new topic : remind the Debug mode .

Please Log in or Create an account to join the conversation.

More
3 years 8 months ago #125776 by christianl

Please Log in or Create an account to join the conversation.

Start now!

Just create your account and start using Limesurvey today.

Register now
Join our Newsletter!