Can't execute SQL - unknown column custom21

[swvjeff]

Script URL: I can provide in private if needed
Mods for HESK version: 2018.1.0
HESK version: 2.8.1
Hosting company: Self-hosted
URL of phpinfo.php: I can provide in private if needed
URL of session_test.php: I can provide in private if needed
What terms did you try when SEARCHING for a solution: "can't execute sql" "custom21"

Write your message below:

I upgraded from HESK 2.6.6 to 2.8.1 and Mods for HESK from 2.5.4 to 2018.1.0. The HESK upgrade seemed to go without a hitch. During the Mods for HESK upgrade, I had to comment out a migration that was trying to set the users.autoreload column based on a value of the users.autorefresh column (if I recall correctly). For some reason, it appeared either the autoreload or autorefresh column didn't exist (I forget which one). This seemed like an innocent change because our team doesn't use the auto refresh option, and as long as the migrations completed without that query, I didn't mind. After commenting the migration out, the installation completed without any errors. However, when I try to submit a new ticket I'm seeing the following (seemingly unrelated) error:

Can't execute SQL: INSERT INTO `tickets` ( `trackid`, `name`, `email`, `category`, `priority`, `subject`, `message`, `dt`, `lastchange`, `articles`, `ip`, `language`, `openedby`, `owner`, `attachments`, `merged`, `status`, `latitude`, `longitude`, `html`, `user_agent`, `screen_resolution_height`, `screen_resolution_width`, `due_date`, `history` , `custom1`, `custom2`, `custom3`, `custom4`, `custom5`, `custom6`, `custom7`, `custom8`, `custom9`, `custom10`, `custom11`, `custom12`, `custom13`, `custom14`, `custom15`, `custom16`, `custom17`, `custom18`, `custom19`, `custom20`, `custom21`, `custom22`, `custom23`, `custom24`, `custom25`, `custom26`, `custom27`, `custom28`, `custom29`, `custom30`, `custom31`, `custom32`, `custom33`, `custom34`, `custom35`, `custom36`, `custom37`, `custom38`, `custom39`, `custom40`, `custom41`, `custom42`, `custom43`, `custom44`, `custom45`, `custom46`, `custom47`, `custom48`, `custom49`, `custom50` ) VALUES ( 'T35-PAN-JX76', 'Test', 'test@example.com', '1', '3', 'Test ticket', 'Test', NOW(), NOW(), NULL, '172.68.46.179', 'English', '0', '0', '', '', '0', 'E-0', 'E-0', '0', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:61.0) Gecko/20100101 Firefox/61.0', 900, 1440, NULL, '' , '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' )

MySQL said:
Unknown column 'custom21' in 'field list'

Note: I do see custom21-50 in the *stage_tickets* table, but I do


I'm going through the HESK 2.8.1 installation/upgrade files and I *do* see a SQL query to create the tickets table (which includes custom21-50 fields), but I *do not* see any SQL queries in the upgrade file to alter table / add the custom21-50 fields.

I also searched for "custom21" in the Mods for HESK 2018.1.0 installation and see a migration that adds the custom21-50 columns to the tickets table (./install/migrations/v302/AddMissingCustomFields.php), however v302 immediately follows the v300 directory, which is the one with the autorefresh columns. I know I could manually run this SQL query, but I'm guessing if the v300 and v302 migrations failed to execute, there might have been more. The weird thing is, the Mods for HESK database validator is giving me "Your database is valid" all the way through 3.3.0.

It seems like this is actually an issue with HESK upgrade, but seeing as how there's a migration in Mods for HESK relating to this, I figured I'd ask here. Do you have any advice as to why this might have happened and the best way to fix this?

Thanks!


Edit -- I just noticed the AddMissingCustomFields migration actually alters *stage_tickets*, which I'm guessing is a MFH-specific table? This is actually probably a HESK issue, but while I'm here I'm curious to hear your thoughts on the issue.

[Klemen]

HESK update file (install/update.php) indeed creates 50 fields in the custom_fields table and make sure there are 50 custom columns in the tickets table.

Do you have custom_fields and custom_statuses tables in the database?

[mkoch227]

I just noticed the AddMissingCustomFields migration actually alters *stage_tickets*, which I'm guessing is a MFH-specific table?

stage_tickets is a MfH-specific table, yes. That table is used to hold tickets that are waiting for email verification. Once the email is verified, the ticket is then moved to the tickets table.

[swvjeff]

HESK update file (install/update.php) indeed creates 50 fields in the custom_fields table and make sure there are 50 custom columns in the tickets table.

Do you have custom_fields and custom_statuses tables in the database?

I do have the custom_fields and custom_status tables in the database. I manually ran the SQL query to add those extra columns to the tickets table. It seems extremely hacky to do that and I have no clue why the column migrations failed silently, but everything seems okay now.

Edit: I see the spot in update.php that adds the custom21-50 fields (I was searching for "custom21" exactly but the query is written dynamically). It looks like a few other queries might have failed too (i.e. modifying the status and autoreload columns), but nothing I can't manually run myself. Thanks for the help!