(SOLVED) Database structure validation

[shadedk]

Version of script: 3.1.2
What terms did you try when SEARCHING for a solution: Database structure

Write your message below:

Is there a function/script to validate my database structure? One of my HESK databases seems to be missing fields.

I run 2 HESK installations, both licensed, both on version 3.1.2, the newest started on version 3.1.1 (here in 2020), and the old one started on version 2.5.3 (in 2014) and have therefore been updated many times.

The "team users" of the oldest HESK installation, noticed today that the "Create new ticket" button under the admin section did not work anymore, it failed with an SQL error, on the following statement:

SELECT `id`, `name`, `priority` FROM `hesk_categories` ORDER BY `cat_order` ASC

It worked fine on the young installation, so I looked in the database, and discovered that the "priority" field in that table where missing on the old installation, I created the field as in the other, and now the "Create new ticket" works fine again.

I looked quick in the database and can see that in the table hesk_attachments the field "type" is also missing on the old installation, so are there any more fields that are missing from the old installaion? They do not use heskdesk, so it is possible that missing fields are not used.

Is there a way I can validate the structure in the database for missing fields?

Thanks in advance.

[Klemen]

This usually happens when someone uploads the new files, but doesn't run the install/update script which takes care of database updates. Or if someone restores an old (incompatible) database backup.

What I would do is:

1. Backup files and database
2. Remove the changes you manually made to the database
3. Upload "install" folder from Hesk 3.1.2
4. Open hesk/install and click the "Update" option

If that doesn't work, you will have to manually check the structure against the SQL code found in file install/install.php (located in function hesk_iTables, starting around line 211)

[shadedk]

I am quite sure that the database allways have been updated together with new files, but i the early days of Hesk we did not update every often so mabee a big jump in version did this. But nevermind that.

I tried the update and it did not fix it, so i manually compare our old Hesk installation with the new one and fixed the structure. So it is fine now

[Klemen]

What version did you upgrade from?

[shadedk]

This time from 3.1.1 to 3.1.2, but before 2.8 updates has not been apllied very often.