Resolved No such record 'expireInterval' into settings table

[tkalfaoglu]

Having just upgraded from 12.0 to Onyx, I have one issue -- during graylisting migration, it gave some warnings, and now I have this issue:

Jul 5 21:31:13 pluto greylisting filter[8623]: Starting greylisting filter...
Jul 5 21:31:13 pluto greylisting filter[8623]: No such record 'user_prefs' into settings table
Jul 5 21:31:13 pluto greylisting filter[8623]: No such record 'greyInterval' into settings table
Jul 5 21:31:13 pluto greylisting filter[8623]: Unable to get expire interval from GL database
Jul 5 21:31:13 pluto greylisting filter[8623]: Unable to create record into data GL table

It seems the table is missing some fields.. what shall I do?
Many thanks, -tk

 

[UFHH01]

Hi tkalfaoglu,

what is the output of the command:

/usr/local/psa/bin/grey_listing -i

I have one issue -- during graylisting migration, it gave some warnings, and now I have this issue:

Pls. provide the warnings from your logs, for further investigations.

 

[IgorG]

Looks like GL sqlite database is corrupted for some reason.
To fully rebuild greylisting database you need to remove all files from /var/lib/plesk/mail/greylist and then run /usr/lib64/plesk-9.0/mail_grey_restore utility.

 

[tkalfaoglu]

Hi tkalfaoglu,

what is the output of the command:

/usr/local/psa/bin/grey_listing -i

Pls. provide the warnings from your logs, for further investigations.

Many thanks.. Here is the first one:
# /usr/local/psa/bin/grey_listing -i
Grey listing configuration.

Grey listing checking disabled
Grey interval 2 minutes
Expire interval 260000 minutes
Penalty interval 2 minutes
Penalty disabled
Personal grey listing
configuration prohibited

Server-wide black list:

Server-wide white list:
*@*.arcelik.com.tr
*@*.aysberg.com
*@*.batisan.com.tr
*@*.beko.com.tr
*@*.bilset.com.tr
*@parallels.com
*@pavotek.com.tr
*@pemsan.com
*@penguenapp.com
*@post.at
*@ptr.com.tr
*@rcsargo.net

White domains patterns list:
*.office365.com
*.outlook.com
*.outlook.office.com
*google.com
*mail.ru
*parallels.com
*rambler.ru
*yahoo.com
*yandex.ru

Black domains patterns list:
*[0-9][0-9]-[0-9][0-9]-[0-9][0-9]*
*[0-9][0-9].[0-9][0-9].[0-9][0-9]*
*[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]*
*[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9[0-9]][0-9]*
dsl|broadband|hsd
dynamic|static|ppp|dyn-ip|dial-up

SUCCESS: Gathering of server wide information complete.

 

[tkalfaoglu]

here is an excerpt from the migration log..

===> Cumulative Plesk upgrade (final stage) has been started.
===> Preparing Plesk upgrade (final stage).
Trying to reload service postfix... done
done
done
Trying to register service postgresql... done
Trying to configure SSL ciphers and protocols for: ... done
Trying to move out custom web server templates from /usr/local/psa/admin/conf/templates/custom/ to /usr/local/psa/admin/conf/templates/custom_templates
_before_17.0_JfWpIFg2/... done
Custom web server templates that may break web server reconfiguration were moved from /usr/local/psa/admin/conf/templates/custom/ to /usr/local/psa/admi
n/conf/templates/custom_templates_before_17.0_JfWpIFg2/
Unable to dump mailname black/white lists into GreyListing database
Trying to upgrade gitman extension to git... no gitman extension installed
Trying to add missing PHP-FPM services into registered services database... done
After end of upgrade for 'core' (stage 'post') following actions are registered as failed: 20160425172000-upgrade_gl_rules_17_0_12.
===> Plesk was not upgraded completely. See installation log for details.
Trying to install site isolation settings... config found. Nothing to be done Trying to add header to file /usr/local/psa/etc/webalizer.conf... complet
ed successfully
taskId=2014-11-20-12-30-24 step=upgrade result=0 message=
taskId=2014-12-12-11-46-20 step=upgrade result=0 message=
taskId=2014-12-19-09-19-34 step=upgrade result=0 message=
taskId=2015-02-17-03-42-16 step=upgrade result=0 message=
taskId=2015-02-25-14-22-44 step=upgrade result=0 message=
taskId=2015-03-24-07-31-27 step=upgrade result=0 message=
taskId=2015-03-25-17-50-40 step=upgrade result=0 message=
taskId=2015-04-02-13-50-32 step=upgrade result=0 message=
taskId=2015-04-17-04-59-48 step=upgrade result=0 message=
taskId=2015-04-17-05-14-55 step=upgrade result=0 message=
taskId=2015-04-17-05-17-21 step=upgrade result=0 message=
(.......)
taskId=2016-06-27-09-35-49 step=upgrade result=0 message=
taskId=2016-06-28-04-45-31 step=upgrade result=0 message=
PHP Warning: Error while sending QUERY packet. PID=17729; File: /usr/local/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php, Line: 307

Error occured while sending feedback. HTTP code returned: 502
[2017-07-05 20:34:45] ERR [panel] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away:
0: /usr/local/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php:307
PDO->beginTransaction()
1: /usr/local/psa/admin/externals/Zend/Db/Adapter/Pdo/Abstract.php:307
Zend_Db_Adapter_Pdo_Abstract->_beginTransaction()
2: /usr/local/psa/admin/externals/Zend/Db/Adapter/Abstract.php:496
Zend_Db_Adapter_Abstract->beginTransaction()
3: /usr/local/psa/admin/plib/Upgrade/Task/17.0.15/2016-07-20-04-59-45_MigrateWordPress.php:30
Plesk\Upgrade\Task\MigrateWordPress->upgrade(object of type Plesk\Upgrade\Deferred)
4: /usr/local/psa/admin/plib/Upgrade/Executor/Sync.php:15
Plesk\Upgrade\Executor_Sync->upgrade(string '2016-07-20-04-59-45', object of type Plesk\Upgrade\TaskLoader, object of type Plesk\Upgrade\Deferred)
5: /usr/local/psa/admin/plib/Upgrade/Command/Task.php:46
Plesk\Upgrade\Command_Task->run(string '2016-07-20-04-59-45', string 'upgrade')
6: /usr/local/psa/admin/plib/Upgrade/upgrade.php:19
Error occured while sending feedback. HTTP code returned: 502

PHP Warning: Error while sending QUERY packet. PID=11030; File: /usr/local/psa/admin/externals/Zend/Db/Statement/Pdo.php, Line: 228

Error occured while sending feedback. HTTP code returned: 502
taskId=2016-07-20-04-59-45 step=upgrade result=1 message=ERROR: PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away (Abstract.php:307)
Some steps of upgrade failed. Run upgrade with option --repair to rerun failed steps.

WARNING!
Some problems are found during execute post install/upgrade actions(see log file: /var/log/plesk/install/plesk_17.5.3_installation.log)

Continue...

Trying to reconfigure Tomcat services... done
Starting forced Plesk Key upgrade task

 

[UFHH01]

Hi tkalfaoglu,

even that the provided commands from @Igor should already solve your issue, did you probably forget to follow the suggestions from your log?

Run upgrade with option --repair to rerun failed steps.

 

[tkalfaoglu]

Looks like GL sqlite database is corrupted for some reason.
To fully rebuild greylisting database you need to remove all files from /var/lib/plesk/mail/greylist and then run /usr/lib64/plesk-9.0/mail_grey_restore utility.

Many thanks -- I'm running it now..

 

[tkalfaoglu]

Hi tkalfaoglu,

even that the provided commands from @Igor should already solve your issue, did you probably forget to follow the suggestions from your log?

Many thanks, no I saw that, but I was not sure which command to run; I had launched it from the GUI.

 

[UFHH01]

Hi tkalfaoglu,

pls. note, that the GUI and it's command are limited, while you often have additional command options with commands over the command line.

=> Command Line Utilities Overview​

 

[tkalfaoglu]

Actually, Igor's suggestion did not fix the problem - after the mail_grey_restore, I re-enabled greylisting and I'm seeing the same errors..
Jul 6 11:07:35 pluto greylisting filter[37496]: Starting greylisting filter...
Jul 6 11:07:35 pluto greylisting filter[37496]: No such record 'user_prefs' into settings table
Jul 6 11:07:35 pluto greylisting filter[37496]: No such record 'greyInterval' into settings table
Jul 6 11:07:35 pluto greylisting filter[37496]: Unable to get expire interval from GL database
Jul 6 11:07:35 pluto greylisting filter[37496]: Unable to create record into data GL table

...sounds like I'll have to go with the --repair option - or if you can provide the sql commands to add those fields, that would be great.. they are probably varchar 255, but I don't dare guess it Many thanks, -t

 

[IgorG]

What do you see in settings database?

Check it with

# sqlite3 /var/lib/plesk/mail/greylist/settings.db
sqlite> .tables

 

[tkalfaoglu]

GOT IT..
I had to issue two commands:
# plesk bin grey_listing --update-server -status on -personal-conf false -grey-interval 2 -expire-interval 129600 -penalty-interval 5 -penalty-status false
# /usr/local/psa/admin/bin/glmng --disable-user-prefs

these two restored the missing values in the table.. Many thanks everyone!!
-turgut

 

[IgorG]

Ohh, sorry, now GL settings are located in main psa database:

# plesk db
MariaDB [psa]> select * from GL_settings;
+---------------+-----------------+-------+
| serviceNodeId | param | value |
+---------------+-----------------+-------+
| 1 | enabled | false |
| 1 | expireInterval | 51840 |
| 1 | greyInterval | 5 |
| 1 | penaltyEnabled | false |
| 1 | penaltyInterval | 2 |
+---------------+-----------------+-------+
5 rows in set (0.00 sec)