1.6 to 1.8.1 DB Errors Galore

akITguy

Hey guys, its been awhile since I've posted here. Well I found myself feeling a bit froggy and deciding to upgrade my 1.6 install to 1.8.1 for some of the new features and lots of bug fixes. Well, long of the short, I've got quite a few DB errors I'm being spammed with and unable to complete the upgrade from the /support/scp page.Followed the instructions here.http://osticket.com/wiki/Upgrade_and_MigrationOnly line that I was unable to confirm was(For versions 1.6 RC1-RC2 OnlyOnce you've overwritten the files, rename to (config.php is found in root osTicket directory).)I'm not fully sure if I was on RC1-RC2 or if I was beyond that (not sure if there was an RC3 or what). The config.php does not exist in my root osTicket folder. In its place was a file named ost_config.php. However the above documentation says it should be named "ostconfig.php"?Anyhow, I receive these following errors17 x DB Error #10542 x DB Error #1146DB Error #1054

Unknown column 'key' in 'field list'

---- Backtrace ----

#0 (root)/include/mysqli.php(138): osTicket->logDBError('DB Error #1054', '[SELECT id, `ke...')

#1 (root)/include/class.config.php(46): db_query('SELECT id, `key...')

#2 (root)/include/class.staff.php(70): Config->Config('staff.3')

#3 (root)/include/class.staff.php(39): Staff->load('Klaus')

#4 (root)/include/class.usersession.php(165): Staff->Staff('Klaus')

#5 (root)/include/class.auth.php(396): StaffSession->__construct('Klaus')

#6 (root)/include/class.auth.php(369): StaffAuthenticationBackend->validate('Klaus')

#7 (root)/scp/staff.inc.php(60): StaffAuthenticationBackend:()

#8 (root)/scp/admin.inc.php(16): require('/home4/haemon/p...')

#9 (root)/scp/system.php(18): require('/home4/haemon/p...')

#10 {main}

DB Error #1146

Table 'haemon_ostt1.ost_group_dept_access' doesn't exist

---- Backtrace ----

#0 (root)/include/mysqli.php(138): osTicket->logDBError('DB Error #1146', '[SELECT DISTINC...')

#1 (root)/include/class.staff.php(232): db_query('SELECT DISTINCT...')

#2 (root)/include/class.staff.php(246): Staff->getDepartments()

#3 (root)/include/class.ticket.php(2025): Staff->getDepts()

#4 (root)/include/class.staff.php(425): Ticket->getStaffStats(Object(StaffSession))

#5 (root)/include/class.staff.php(431): Staff->getTicketsStats()

#6 (root)/include/class.nav.php(117): Staff->getNumAssignedTickets()

#7 (root)/include/class.nav.php(31): StaffNav->getSubMenus()

#8 (root)/scp/staff.inc.php(126): StaffNav->StaffNav(Object(StaffSession))

#9 (root)/scp/admin.inc.php(16): require('/home4/haemon/p...')

#10 (root)/scp/logs.php(16): require('/home4/haemon/p...')

#11 {main}--------------------------------------------------------------------------------------------------------------------------------------Not sure if this is a config file issue, but looks like one of the config files and the MYSQL DB aren't jiving with each other. I did run a script that I found via some searching for this particular issue for OSTicket upgrades that was suppose to correct another issue I had before this apparently.<?php// your connectionmysql_connect("localhost","Username","Password");mysql_select_db("DatabaseName");// convert code$res = mysql_query("SHOW TABLES");while ($row = mysql_fetch_array($res)){ foreach ($row as $key => $table) { mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci"); echo $key . " => " . $table . " CONVERTED "; }}?>I ran it is a cron job with full admin privs and received an email verifying that it ran. Content-type: text/html

0 => ost_api_key CONVERTED Tables_in_haemon_ostt1

=> ost_api_key CONVERTED 0 =>

ost_canned_attachment CONVERTED Tables_in_haemon_ostt1

=> ost_canned_attachment CONVERTED 0 =>

ost_canned_response CONVERTED Tables_in_haemon_ostt1

=> ost_canned_response CONVERTED 0 =>

ost_config CONVERTED Tables_in_haemon_ostt1 =>

ost_config CONVERTED 0 => ost_department

CONVERTED Tables_in_haemon_ostt1 => ost_department

CONVERTED 0 => ost_email CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_email CONVERTED 0

=> ost_email_filter CONVERTED Tables_in_haemon_ostt1

=> ost_email_filter CONVERTED 0 =>

ost_email_filter_rule CONVERTED Tables_in_haemon_ostt1

=> ost_email_filter_rule CONVERTED 0 =>

ost_email_template CONVERTED Tables_in_haemon_ostt1 =>

ost_email_template CONVERTED 0 => ost_faq

CONVERTED Tables_in_haemon_ostt1 => ost_faq

CONVERTED 0 =>

ost_faq_attachment CONVERTED Tables_in_haemon_ostt1

=> ost_faq_attachment CONVERTED 0 =>

ost_faq_category CONVERTED Tables_in_haemon_ostt1 =>

ost_faq_category CONVERTED 0 => ost_faq_topic

CONVERTED Tables_in_haemon_ostt1 => ost_faq_topic

CONVERTED 0 => ost_file CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_file CONVERTED 0

=> ost_groups CONVERTED Tables_in_haemon_ostt1

=> ost_groups CONVERTED 0 => ost_help_topic

CONVERTED Tables_in_haemon_ostt1 => ost_help_topic

CONVERTED 0 => ost_session CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_session CONVERTED 0

=> ost_sla CONVERTED Tables_in_haemon_ostt1 =>

ost_sla CONVERTED 0 => ost_staff CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_staff CONVERTED 0

=> ost_syslog CONVERTED Tables_in_haemon_ostt1

=> ost_syslog CONVERTED 0 => ost_team

CONVERTED Tables_in_haemon_ostt1 => ost_team CONVE

RTED 0 => ost_team_member CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_team_member CONVERTED<br

/>0 => ost_ticket CONVERTED Tables_in_haemon_ostt1

=> ost_ticket CONVERTED 0 =>

ost_ticket_attachment CONVERTED Tables_in_haemon_ostt1

=> ost_ticket_attachment CONVERTED 0 =>

ost_ticket_email_info CONVERTED Tables_in_haemon_ostt1

=> ost_ticket_email_info CONVERTED 0 =>

ost_ticket_event CONVERTED Tables_in_haemon_ostt1 =>

ost_ticket_event CONVERTED 0 => ost_ticket_lock

CONVERTED Tables_in_haemon_ostt1 => ost_ticket_lock

CONVERTED 0 => ost_ticket_message CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_ticket_message CONVERTED<br

/>0 => ost_ticket_note CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_ticket_note CONVERTED<br

/>0 => ost_ticket_priority CONVERTED<br

/>Tables_in_haemon_ostt1 => ost_ticket_priority

CONVERTED 0 => ost_ticket_response CONVERTED<br

/>Tables_in_haemo

n_ostt1 => ost_ticket_response CONVERTED 0 =>

ost_ticket_thread CONVERTED Tables_in_haemon_ostt1 =>

ost_ticket_thread CONVERTED 0 => ost_timezone

CONVERTED Tables_in_haemon_ostt1 => ost_timezone

CONVERTED

akITguy

Unsure of what else I can or should be doing. Any help would be very much appreciated. I was really looking forward to the 1.8.2 update for the client login ability to track multiple tickets at the same time. So getting 1.8.1 running for the sake of making the next upgrade that much easier is key for me.Thanks in advance :)

peter

We have a bug in v1.8.1 having to do with attachment migration during upgrade.

ntozier

To expand a little on what @[deleted] said, if you restore to 1.6 and grab the 1.7.x install archive and upgrade to that first then you should be able to upgrade from 1.7.x to 1.8.1 and get around the attachment issue.

akITguy

Thanks or the heads up, I shouldn't have any attachments. I did read someone saying something about having to add attachments (fake) folder or something to make the install work.

Any chance this information could be added to the internal upgrade documentation for 1.8.x?

I'll go the 1.6 restore to 1.7.0 route and then 1.7.0 to 1.8.1 in that order correct? Any serious issues from 1.6 to 1.7?

ntozier

It a bug in the upgrader. It should be fixed in the next release.I had no problems going from 1.6ST to 1.7. (and then to 1.7.2) Then I went to 1.8.0.1 to 1.8.1.

akITguy

Is that the necessary upgrade path?1.6ST > 1.7 > 1.7.2 > 1.8.0.1 > 1.8.1Or is it possible to go...1.6ST > 1.7.2 > 1.8.1Thanks again for the help and advice. I'd like to make sure this is done right.

ntozier

You should be able to go from 1.6ST to any 1.7.x to 1.8.1ST. I simply told you the versions that I personally upgraded from to as an example.

akITguy

Good to know, thanks ntozier. Was reading many other posts on the forums and noticed your usually the guy helping people out with these issues. Just wanted to say thanks for taking the time to help me (and others), rock on.

akITguy

Well no dice. Went for the 1.6ST to 1.7ST upgrade and ran into some errors.Upgrader in the logs state that it applied 5 updates and 2 pending tasks.Then goes into a general Upgrader Error

Table 'haemon_ostt1.ost_group_dept_access' doesn't existThe 1146 is the same or similar error to what I had before. Im not sure if its not able to read the DB due to the illegal mix of collations? I'm curious why the table ost_group_dept_access doesn't exist. I'm also curious if it would make sense for these future upgrade packages to check the DB and if a table is legitmately missing, have it add that table..? Just a thought.Any words of wisdom on why I'm running into this issue even with a 1.6 to 1.7 upgrade? When initially attempting to run the 1.6 to 1.8.1 upgrade I ran a script on the DB as I mentioned above to correct the collation issue. My DB and files were restored back to 1.6 obviously so I'm wondering if its worth attempting that PHP script again and if it will even help with the 1146 errors as it didn't the first time.

ntozier

Are sure your running 1.6ST? (and not some random rc#) If you are you will need to upgrade to 1.6ST first. You can always add the ost_group_dept_access table manually by running this code:CREATE TABLE `%TABLE_PREFIX%group_dept_access` ( `group_id` int(10) unsigned NOT NULL default '0', `dept_id` int(10) unsigned NOT NULL default '0', UNIQUE KEY `group_dept` (`group_id`,`dept_id`), KEY `dept_id` (`dept_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

akITguy

I've got the 1.6.0 install that is auto installed from Fantastico, it mentioned nothing about RC and I didn't think Fantastico put RC of software installs on there anyhow.I'll attempt to create that table manually with the code you provided and report back.

ntozier

Let me know. :)

akITguy

I took a breather from it again, then did what I thought to be a restore to the SQL DB. Then I applied first the Charset conversion script knowing that would be an issue again. Then ran the script you provided ntozier to create the group_dept_access. It seems odd because I'm pretty sure that I restored the DB via HostGator's control panel.Yet when I ran the upgrader, confident that I had pre-fixed all previous error messages, and then ran into more... I've just become seriously confused at what the root cause of these problems are.Here are two new issues. Duplicate entry 'Level I Support' for key 'name'I got past this by assuming I could delete the single entry for this, thus allowing it to create it (without error) and continue onto the rest of the install. So I deleted it, then re-ran the upgrader and yet again another error (not this one at least). Duplicate column name 'file_id'So I get the feeling that my issue here is a bad restore of the DB? Duplicates again!? Shouldn't be possible if they it were restored to a previous date, especially considering some of these tables didn't exist in 1.6.

ntozier

It sounds like you restored an already upgraded version of the database.

akITguy

Well I went ahead and deleted the DB, then did a restore and there was a big difference, about .5MB difference in size. I noticed some of the newly generated tables disappeared. So here is what I did after that.1.) Restored DB, and re-attached the same users to the DB with proper permissions (everything for now)2.) Created the table that its always looking for ost_group_dept_access3.) Converted all tables to Unicode from General as I keep getting the same error regarding missed collation.4.) Started the upgrade process again from square one.It gets through 5-6 succesfully upgraded parts and then stops AGAIN at the mixed collation. From the looks of it, all of the tables are unicode until I start the upgrade and the newly added tables are put in general? Whats with that. It seems like a very large oversight to mix the two if it causes issues with tables sharing information.So I started over AGAIN, changed all of the tables to General and got much further. This time I got the following error. Table 'ost_group_dept_access' already existsIs it now not liking that I created the group_dept_access?

akITguy

***SOLVED***So, when converting everything over to general it can successfully build the group_dept_access table by itself as it doesn't run into mixed collation errors prior to it. All one needs to do when getting themselves into this situation is convert your DB main collation to utf8_general_ci. Then I went in under each table using MyPHPAdmin tool from hostgator (or other tools similar) and converted each table over. You could probably use the script provided earlier as well, but I wanted to make sure no stone was left unturned.Thanks ntozier for your help :)

ntozier

Looks like it. Since it says that the table already exists.BTW regarding the mixed table collations I saw something about that the other day on github. Here they are:https://github.com/osTicket/osTicket-1.8/pull/680andhttps://github.com/osTicket/osTicket-1.8/pull/687I hope that helps you. :)