Installer fails adding admin user with error "foreign key constraint fails"

[dalers]

I noticed quite a few new commits when I pulled today, but the installer still doesn't complete for me. I was able to dig a little deeper and hopefully this will help someone understand the cause.

The installer fails in Page 6 after showing "Populating the database with demo data", I think when trying to add the admin user to the www_users table.

I tried running the "add admin user" query from page_6.php (starting on line 210) directly in Adminer (AdminerEvo), with anything comming from the SESSION replaced with fixed text. This resulted in the following error:

Error in query (1452): Cannot add or update a child row: a foreign key constraint fails (weberp.www_users, CONSTRAINT www_users_ibfk_1 FOREIGN KEY (defaultlocation) REFERENCES locations (loccode))

Fwiw, I'm using MariaDb 10.6 on FreeBSD, which may be stricter than typical Linux.

Does this tell anyone anything?

Cheers,
Dale

Edited query:

INSERT INTO www_users  (userid,
	password,
	realname,
	customerid,
	supplierid,
	salesman,
	phone,
	email,
	defaultlocation,
	fullaccess,
	cancreatetender,
	lastvisitdate,
	branchcode,
	pagesize,
	timeout,
	modulesallowed,
	showdashboard,
	showpagehelp,
	showfieldhelp,
	blocked,
	displayrecordsmax,
	theme,
	language,
	pdflanguage,
	fontsize,
	department
) VALUES (
	'admin',
	'password',
	'Admin User',
	'',
	'',
	'',
	'',
	'',
	'',
	8,
	1,
	'2024-10-24 18:38:24',
	'',
	'A4',
	10,
	'1,1,1,1,1,1,1,1,1,1,1,1,',
	0,
	1,
	1,
	0,
	50,
	'default',
	'english',
	0,
	0,
	0
)

[dalers]

Also fwiw 178 tables are created according to Adminer (not 175 as reported seperately by @efeoli #204 (comment))

[dalers]

Noticed error in httpd-error.log. Is it expected that the referer is "...Page=5" even though the script running is page_6.php?

[Wed Nov 06 13:06:27.524209 2024] [php:error] [pid 2646] [client 162.223.103.50:61177] PHP Fatal error: Uncaught mysqli_sql_exception: Duplicate entry '1-manuf-Reports-List Materials Not Used anywhere' for key 'PRIMARY' in /usr/local/www/weberp/install/pages/page_6.php:449\nStack trace:\n#0 /usr/local/www/weberp/install/pages/page_6.php(449): mysqli_query()\n#1 /usr/local/www/weberp/install/pages/page_6.php(387): PopulateSQLDataBySQL()\n#2 /usr/local/www/weberp/install/index.php(77): include('...')\n#3 {main}\n thrown in /usr/local/www/weberp/install/pages/page_6.php on line 449, referer: https://weberp.dalescott.net/install/index.php?Page=5

[timschofield]

Well this is weird. I have run the installer with sql-mode set as

sql-mode="STRICT_ALL_TABLES"

using php 8.3.13 and it ran fine. This is supposed to be the strictest. There were a couple of SQL errors which I have corrected, but not with the user.
I have changed page_6.php to turn foreign keys off just before the www_users sql, even though they had already been turned off (maybe mariadb does this differently).

Try getting these from github and see if it works now.

Thanks
Tim

[dalers]

Thanks Tim. I'm getting closer, the database is now populated but the admin user password entered during the install procedure isn't accepted for login.

Started with git pull...

dale@whizzer:/usr/local/www/weberp $ git branch
* master
  v5.0
dale@whizzer:/usr/local/www/weberp $ sudo -u www git pull
remote: Enumerating objects: 21, done.
remote: Counting objects: 100% (21/21), done.
remote: Compressing objects: 100% (5/5), done.
remote: Total 14 (delta 11), reused 12 (delta 9), pack-reused 0 (from 0)
Unpacking objects: 100% (14/14), 1.42 KiB | 91.00 KiB/s, done.
From https://github.com/timschofield/webERP
   752aeeeb9..3d5820abc  master     -> origin/master
Updating 752aeeeb9..3d5820abc
Fast-forward
 install/demo.sql          | 2 +-
 install/pages/page_6.php  | 1 +
 install/sql/menuitems.sql | 2 +-
 3 files changed, 3 insertions(+), 2 deletions(-)
dale@whizzer:/usr/local/www/weberp $

Deleted the old config.php, then dropped and re-created the "weberp" database (utf8mb4_unicode_ci collation), and browsed to the installer. Defaults were used except username and password, and admin user email and password. So far so good, looks like database was populated....

"Restarted" webERP and attempted to login

but not successful.

Examined www_users table in database and "seems" ok. Can you advise how to hack code to still login if password match fails? I was thinking if I could login as admin, I could create a new user with same password then see if I can login as new user, and also check the encrypted passwords in the database to see if they match looking for an issue with password encryption...

[timschofield]

Ah, the demo data will overwrite your choices, so the user/password will be admin/weberp

Is this what you entered?

Tim

[timschofield]

I have fixed this in the master branch.

tim

[dalers]

Thanks, I'm in! I was expecting the admin user password to be what I entered (iirc only the chart of accounts tooltip says it will be ignored if you specify loading demo data). Should I see the "invalid" ID message though? This is the first successful login after trying with the "wrong" password a couple times.

I poked around some and didn't notice any errors, so I'll restore my old demo database and see what happens (btw, if you like the demo database I would happily contribute it to the project - one small cohesive product with about 25 parts in three BOM levels and including a few few purchase, manufacturing and sales orders).

[timschofield]

You are right Dale, it should take the user/password you entered. I will correct that.

Thanks for your help
Tim

[timschofield]

That would be a great addition Dale, thanks!

One item on my to-do list (which I'm hoping to publish) is a better demo database.