Fatal Error Upgrading

[ironmangary]

I am attempting to upgrade LotGD from v1.1.1 +nb to 1.3.0 under PHP 8.1 and MySQL 5.7.23. When I click "Save Settings" at step 9 (modules), I get the following fatal error:

Fatal error: Uncaught mysqli_sql_exception: Duplicate column name 'function' in /home4/MUNGED_USERNAME/earthgonewrong.com/lib/dbwrapper_mysqli_proc.php:23 Stack trace: #0 /home4/MUNGED_USERNAME/earthgonewrong.com/lib/dbwrapper_mysqli_proc.php(23): mysqli_query(Object(mysqli), 'ALTER TABLE mod...') #1 /home4/MUNGED_USERNAME/earthgonewrong.com/lib/tabledescriptor.php(107): db_query('ALTER TABLE mod...') #2 /home4/MUNGED_USERNAME/earthgonewrong.com/lib/installer/installer_stage_9.php(14): synctable('module_hooks', Array, true) #3 /home4/MUNGED_USERNAME/earthgonewrong.com/installer.php(152): require_once('/home4/MUNGED_USERNAME...') #4 {main} thrown in /home4/MUNGED_USERNAME/earthgonewrong.com/lib/dbwrapper_mysqli_proc.php on line 23

I'm not sure of the proper solution and would appreciate any guidance. Thanks!

[ironmangary]

To add to this, the table it's complaining about is module_hooks. I'm not sure where the duplicate 'functions' column error stems from, though. From the mysql cli:

| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| modulename | varchar(50) | NO | PRI | NULL | |
| location | varchar(50) | NO | PRI | NULL | |
| function | varchar(50) | NO | PRI | NULL | |
| whenactive | text | NO | | NULL | |
| priority | int(11) | NO | | 50 | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

A fresh install gave me a different MySQL error at stage 10.

Fatal error: Uncaught mysqli_sql_exception: Table 'MUNGED_USERNAME_tmp.settings' doesn't exist in /home4/MUNGED_USERNAME/public_html/logdtest/lib/dbwrapper_mysqli_proc.php:23 Stack trace: #0 /home4/MUNGED_USERNAME/public_html/logdtest/lib/dbwrapper_mysqli_proc.php(23): mysqli_query(Object(mysqli), 'SELECT * FROM s...') #1 /home4/MUNGED_USERNAME/public_html/logdtest/lib/settings.class.php(44): db_query('SELECT * FROM s...') #2 /home4/MUNGED_USERNAME/public_html/logdtest/lib/settings.class.php(16): settings->loadSettings() #3 /home4/MUNGED_USERNAME/public_html/logdtest/lib/settings.php(10): settings->__construct('settings') #4 /home4/MUNGED_USERNAME/public_html/logdtest/lib/installer/installer_stage_default.php(11): savesetting('installer_versi...', '1.3.0 +nb Editi...') #5 /home4/MUNGED_USERNAME/public_html/logdtest/installer.php(155): require_once('/home4/MUNGED_USERNAME...') #6 {main} thrown in /home4/MUNGED_USERNAME/public_html/logdtest/lib/dbwrapper_mysqli_proc.php on line 23

Again, the database user has full privs. This was a fresh install, so of course, some tables wouldn't have existed. So, both methods failed. Any assistance appreciated.

[NB-Core]

There are two issues here, I'll try to address them separately.

Number 1: Upgrading
It sadly cuts off the relevant statement, so I can't see what it tries to do. I think it tries to add the function column again.
As function is a reserved name, it could be that the check for existing columns doesn't work.
Workaround: please rename the existing column "function" to i.e. "afunction" and try to upgrade.
See if that works. (then we have the culprit), and let me know.

Number 2:
It seems the settings table hasn't been created.
Can you please check the database if there are the other tables existing?
It should have thrown an error when "settings" could not be created.

[ironmangary]

For #1, I can't seem to rename the 'function' field, and I'm using the syntax:

alter table module_hooks change function function_bak;

I've tried with backticks, single quotes, and no quotes around the field names. I get a generic syntax error. Very frustrating and I'm pretty sure this is the proper way to do it.

For #2, I didn't get the error about the 'settings' table this time, but I got an error similar to #1. Before running step 10 (I skipped step 9), there were no tables. The error:

Fatal error: Uncaught mysqli_sql_exception: Key column 'mfunction' doesn't exist in table in /home4/MUNGED_USERNAME/public_html/logdtest/lib/dbwrapper_mysqli_proc.php:23 Stack trace: #0 /home4/MUNGED_USERNAME/public_html/logdtest/lib/dbwrapper_mysqli_proc.php(23): mysqli_query(Object(mysqli), 'CREATE TABLE mo...') #1 /home4/MUNGED_USERNAME/public_html/logdtest/lib/tabledescriptor.php(24): db_query('CREATE TABLE mo...') #2 /home4/MUNGED_USERNAME/public_html/logdtest/lib/installer/installer_stage_9.php(14): synctable('module_hooks', Array, true) #3 /home4/MUNGED_USERNAME/public_html/logdtest/installer.php(152): require_once('/home4/MUNGED_USERNAME...') #4 {main} thrown in /home4/MUNGED_USERNAME/public_html/logdtest/lib/dbwrapper_mysqli_proc.php on line 23

Also, if it makes any difference, I'm running PHP 8.2 and MySQL 5.7. I don't know what's going on here.

[NB-Core]

For #1 yeah, it's a reserved function, defined in all_tables.php Line 1089
'function'=>array(
'name'=>'function', 'type'=>'varchar(50)'
),

It does it with ticks, not backticks. Maybe that helps. I think above should work.

This worked (testing server) for me, I did add 5 chars to it:
ALTER TABLE module_hooks CHANGE function function VARCHAR(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

For #2,

My bad, the key is set to "mfunction". I think I have renamed it in the key column.
Try renaming it to "function" in all_tables lines 1098ff:
'key-PRIMARY'=>array(
'name'=>'PRIMARY',
'type'=>'primary key',
'unique'=>'1',
'columns'=>'modulename,location,mfunction'
),

They key is defined wrong, that needs to change though.

[ironmangary]

Fixed and fixed! Thanks!