Unable to upgrade FrontEndUsers due to foreign Key constrain

Have a question or a suggestion about a 3rd party addon module or plugin?
Let us know here.
Post Reply
peram
Forum Members
Forum Members
Posts: 29
Joined: Mon May 09, 2005 11:39 am

Unable to upgrade FrontEndUsers due to foreign Key constrain

Post by peram »

Hi.

I'm in the process of updateting our CMSMS installation and when trying to upgrade to FrontEndUsers 1.23.4 and beyond I get this error :

ALTER TABLE cms_module_feusers_belongs ADD FOREIGN KEY (userid) REFERENCES cms_module_feusers_users (id) Cannot add or update a child row: a foreign key constraint fails (`db188403`.`#sql-12d_e5a77a`, CONSTRAINT `#sql-12d_e5a77a_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `cms_module_feusers_users` (`id`))

details on our install is as follows :
----------------------------------------------
Cms Version: 2.2.13
Installed Modules:
AdminSearch: 1.0.4
Album: 1.10-beta2
Backup: 0.5
CGCalendar: 2.6.2
CGContentUtils: 2.5.4
CGExtensions: 1.65.1
CGFeedMaker: 1.0.17
CGSimpleSmarty: 2.2.1
CGUserDirectory: 1.4.3
CGUserDirectory: 1.4.3
CMSContentManager: 1.1.8
CMSMailer: 6.2.14
CmsJobManager: 0.1.3
CustomContent: 1.9
DesignManager: 1.1.7
DownloadManager: 1.5.2
FileManager: 1.6.10
FilePicker: 1.0.4.1
Forum: 0.9.4
FrontEndUsers: 1.22.1
Gallery: 2.4.2
JQueryTools: 1.4.2
MenuManager: 1.50.3
MicroTiny: 2.2.4
ModuleManager: 2.1.7
Navigator: 1.0.9
News: 2.51.8
Printing: 1.1.1
Search: 1.51.7
SelfRegistration: 1.7.4
ThemeManager: 1.1.8
TinyMCE: 3.3.1
TreeManager: 0.6.0
Uploads: 1.26.2

Config Information:
php_memory_limit:
max_upload_size: 200000000
url_rewriting: none
page_extension:
query_var: page
auto_alias_content: true
locale:
set_names: true
timezone: Europe/Oslo
permissive_smarty: false

Php Information:
phpversion: 7.2.15
md5_function: On (True)
json_function: On (True)
gd_version: 2
tempnam_function: On (True)
magic_quotes_runtime: Off (False)
E_ALL: 22527
E_STRICT: 0
E_DEPRECATED: 0
test_file_timedifference: No time difference found
test_db_timedifference: No time difference found
create_dir_and_file: 1
memory_limit: 128M
max_execution_time: 30
register_globals: Off (False)
output_buffering: 4096
disable_functions: pcntl_alarm, pcntl_fork, pcntl_waitpid, pcntl_wait, pcntl_wifexited, pcntl_wifstopped, pcntl_wifsignaled, pcntl_wexitstatus, pcntl_wtermsig, pcntl_wstopsig, pcntl_signal, pcntl_signal_dispatch, pcntl_get_last_error, pcntl_strerror, pcntl_sigprocmask, pcntl_sigwaitinfo, pcntl_sigtimedwait, pcntl_exec, pcntl_getpriority, pcntl_setpriority
open_basedir:
test_remote_url: Success
file_uploads: On (True)
post_max_size: 200M
upload_max_filesize: 200M
session_save_path: /tmp (1777)
session_use_cookies: On (True)
xml_function: On (True)
xmlreader_class: On (True)
check_ini_set: On (True)
curl: On

Performance Information:
allow_browser_cache: Off (False)
browser_cache_expiry: 60
php_opcache: On (True)
smarty_cache: Off (False)
smarty_compilecheck: Off (False)
auto_clear_cache_age: Off (False)
Server Information:
Server Software: Apache
Server Api: cgi-fcgi
Server Os: Linux 4.4.0-141-generic On x86_64
Server Db Type: MySQL (mysqli)
Server Db Version: 5.7.21
Server Db Grants: Could not find a suitable "GRANT ALL" permission, this does not necessarily lead to problems... But if you have problems installing/removing modules or adding and deleting items/pages this could be the cause!

Permission Information:
tmp: /home/ostensjojanitsjar/www/tmp (0755)
tmp_cache: /home/ostensjojanitsjar/www/tmp/cache (0755)
templates_c: /home/ostensjojanitsjar/www/tmp/templates_c (0755)
modules: /home/ostensjojanitsjar/www/modules (0755)
uploads: /home/ostensjojanitsjar/www/uploads (0755)
File Creation Mask (umask): /home/ostensjojanitsjar/www/tmp/cache (0755)
config_file: 0444
----------------------------------------------
scooper
Forum Members
Forum Members
Posts: 242
Joined: Fri Dec 09, 2005 12:36 pm
Location: Marlow, UK

Re: Unable to upgrade FrontEndUsers due to foreign Key const

Post by scooper »

That's a blast from the past.

I seem to remember that there was an issue with key restraints in older versions of FEU.

Looking at this post from a few years back I seem to suggest it was fixed in versions of FEU after 1.31.2 so I would try upgrading FEU directly to 1.31.3 rather than anything earlier.

There's also a link in that post to the bug tracker where there's more info which might be useful.
peram
Forum Members
Forum Members
Posts: 29
Joined: Mon May 09, 2005 11:39 am

Re: Unable to upgrade FrontEndUsers due to foreign Key const

Post by peram »

scooper wrote:That's a blast from the past.

I seem to remember that there was an issue with key restraints in older versions of FEU.

Looking at this post from a few years back I seem to suggest it was fixed in versions of FEU after 1.31.2 so I would try upgrading FEU directly to 1.31.3 rather than anything earlier.

There's also a link in that post to the bug tracker where there's more info which might be useful.
Thanks for the tip, but even with 1.31.3 I get the same error :

ALTER TABLE cms_module_feusers_belongs ADD FOREIGN KEY (userid) REFERENCES cms_module_feusers_users (id) Cannot add or update a child row: a foreign key constraint fails (`db188403`.`#sql-12d_e97880`, CONSTRAINT `#sql-12d_e97880_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `cms_module_feusers_users` (`id`))

This is a site I have just upgraded from 1.11.something to 1.12.2 and then 2.2.13. I do have the database and web-backups still so maybe I should do a roll-back to the 1.11.something but should I upgrade FEU to 1.31.3 before I do the lift to 1.12.2 ?

Any pointers are much welcome :)

regs,
peram
peram
Forum Members
Forum Members
Posts: 29
Joined: Mon May 09, 2005 11:39 am

Re: Unable to upgrade FrontEndUsers due to foreign Key const

Post by peram »

peram wrote:
This is a site I have just upgraded from 1.11.something to 1.12.2 and then 2.2.13. I do have the database and web-backups still so maybe I should do a roll-back to the 1.11.something but should I upgrade FEU to 1.31.3 before I do the lift to 1.12.2 ?

Any pointers are much welcome :)

regs,
peram
Sorry for answering my own post, but tried the rollback now and get the same error with FEU 1.31.1 on 1.12.2, so now I'm stumped and in desperate need for some help...
regs,
peram
User avatar
velden
Dev Team Member
Dev Team Member
Posts: 3483
Joined: Mon Nov 28, 2011 9:29 am
Location: The Netherlands

Re: Unable to upgrade FrontEndUsers due to foreign Key const

Post by velden »

I'm not sure but seems that the foreign key constraint that should be added is failing because the constraint would be invalid immediately.

So find the entries in table 'cms_module_feusers_belongs' where the userid does not exist in table 'cms_module_feusers_users' (column 'id').

Depending on the number of users you may want to write a sql query for that.

Then consider removing those entries from table 'cms_module_feusers_belongs'

To be sure: don't remove your backup yet :-)
peram
Forum Members
Forum Members
Posts: 29
Joined: Mon May 09, 2005 11:39 am

Re: Unable to upgrade FrontEndUsers due to foreign Key const

Post by peram »

velden wrote:I'm not sure but seems that the foreign key constraint that should be added is failing because the constraint would be invalid immediately.

So find the entries in table 'cms_module_feusers_belongs' where the userid does not exist in table 'cms_module_feusers_users' (column 'id').

Depending on the number of users you may want to write a sql query for that.

Then consider removing those entries from table 'cms_module_feusers_belongs'

To be sure: don't remove your backup yet :-)
Thanks for responding, and just to make sure I understand you correctly. You mean that I need to remove all entries that does not exist in both tables ?

regs,

peram
User avatar
velden
Dev Team Member
Dev Team Member
Posts: 3483
Joined: Mon Nov 28, 2011 9:29 am
Location: The Netherlands

Re: Unable to upgrade FrontEndUsers due to foreign Key const

Post by velden »

Yes.

Though, again, use some common sense before deleting too much.
Post Reply

Return to “Modules/Add-Ons”