CMS Made Simple Forums
https://forum.cmsmadesimple.org/

Unable to upgrade FrontEndUsers due to foreign Key constrain
https://forum.cmsmadesimple.org/viewtopic.php?f=7&t=81852
Page 1 of 1

Author:  peram [ Fri Jan 03, 2020 5:07 pm ]
Post subject:  Unable to upgrade FrontEndUsers due to foreign Key constrain

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
----------------------------------------------

Author:  scooper [ Fri Jan 03, 2020 6:45 pm ]
Post subject:  Re: Unable to upgrade FrontEndUsers due to foreign Key const

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.

Author:  peram [ Sat Jan 04, 2020 3:36 pm ]
Post subject:  Re: Unable to upgrade FrontEndUsers due to foreign Key const

scooper \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.


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

Author:  peram [ Sat Jan 04, 2020 5:06 pm ]
Post subject:  Re: Unable to upgrade FrontEndUsers due to foreign Key const

peram \peram:

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

Author:  velden [ Sun Jan 19, 2020 3:19 pm ]
Post subject:  Re: Unable to upgrade FrontEndUsers due to foreign Key const

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 :-)

Author:  peram [ Thu Jan 23, 2020 7:11 am ]
Post subject:  Re: Unable to upgrade FrontEndUsers due to foreign Key const

velden \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 :-)

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

Author:  velden [ Thu Jan 23, 2020 9:15 am ]
Post subject:  Re: Unable to upgrade FrontEndUsers due to foreign Key const

Yes.

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

Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/