mariaDB issue timezone settings during the upgarde

bartekct

Hi,

I have quite old osTicket which is working internally since 2012.
Version 1.6 ST (there is quite old mariaDB 5.5.68, php 5.4.16, apache 2.4.6)
Last time I was trying to upgrade this system to newest.
To not touch live production system I created new VM (firstly with Debian 13 nextly with Debian 12)
Installed there apache2 (2.4.65), mariadb (10.11.14) , php (8.2.29), all extensions etc
I copied all files to new server from old one, import DB from old one)
Before run the page I upload new version (I tried with 1.18.2 and with 1.17.6)
When all was ready I run osticket - page started and I can login, so looks like all is ready.
Checks show that all required version of system are ok ( i have green checks)
I start upgrade and then in results I have core error:

[UPDATE ost_config SET default_timezone_id = (SELECT id FROM ost_timezone WHERE offset = ost_config.timezone_offset)] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset = ost_config.timezone_offset)' at line 2

After that page crashed , when i clear cookies after login Upgrade is not possible anymore, and it is showing:

I was trying few times (I was changing maridb server version, php etc ) - results are similar always.
Trying to find answer what I am doing wrong...

KevinTheJedi

bartekct

I don’t think I’ve seen this one before. In any case try updating this line:

https://github.com/osTicket/osTicket/blob/53339df0f5c5b0fcfc109600d62646a19c1db7e7/include/upgrader/streams/core/c00511c7-7be60a84.patch.sql#L73

And add backticks around the word “offset” like this:

`offset`

Save those changes and retest.

Cheers.

bartekct

Thanks, I will retest shortly.

In the mean time what I found, but not sure if it is connected

https://stackoverflow.com/questions/26349714/mariadb-set-timezone-in-config

Maybe this is this syntax issue "-" instead of "_"

KevinTheJedi

bartekct

No, not related. Your issue is dealing with osTicket’s old timezone table. The link you posted is dealing with MySQL’s/MariaDB’s timezone config.

Cheers.

bartekct

so after adding backtics as you mentioned, now have another similar issue but in other place:

[core]: Upgrader Error
[UPDATE ost_staff SET timezone_id = (SELECT id FROM ost_timezone WHERE offset = ost_staff.timezone_offset)] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset = ost_staff.timezone_offset)' at line 2

bartekct

I comment in the same way also line 139 in this file, retest and results now have as below:

[UPDATE ost_staff SET timezone_id = (SELECT id FROM ost_timezone WHERE 'offset' = ost_staff.timezone_offset)] Subquery returns more than 1 row

KevinTheJedi

bartekct

Just remove those two lines (138-139) as they are not needed anyways.

Cheers.

bartekct

Ok. I fixed (to make step forward) hardcoding timezone_id. It allow me make next step.
Nextly I have to edit the ALTER TABLE (as somehow this one from script not want to aplly)

Now I am stuck on 1.7 level
[INSERT INTO ost_email_template_group SELECT tpl_id, isactive, name, notes, created, updated FROM ost_email_template] Duplicate entry '1' for key 'PRIMARY'

And of course I understand that there are duplicates in tpl_id (but not sure why it is used as Primary Key as there are possible duplicates)
But also I am not sure how properly edit it woithout broking something more.

KevinTheJedi

bartekct

Go to the email template table, find duplicate record where tpl_id is the same and delete until only one record per tpl_id.

Cheers.

bartekct

I restore DB once again and start proccess once again - somehow now it goes smoothly but not without issues 🙂
I succesfully upgraded to newest version - but still there are some bugs - for example "customers" not upgrade properly and on most tickets there are actually not customer.

but lets say there is partial success 🙂

KevinTheJedi

bartekct

Hmmm, don't think I've heard of that one before. Care to elaborate?

Cheers.

bartekct

looks like Users Directory is empty, also in tickets there are no linked users/emails
Organizations also empty.

I think probably becasue what I see in System logs now:

DB Error 1267
 DB Error #1267
[INSERT INTO `ost_user` (`name`, `default_email_id`, `created`, `updated`) SELECT MAX(`name`), A2.`id`, A1.`created`, A1.`updated` FROM `ost_ticket` A1 INNER JOIN `ost_user_email` A2 ON (A1.`email` = A2.`address`) GROUP BY A2.`id`] Illegal mix of collations (utf8mb3_general_ci, IMPLICIT) and (utf8mb3_uca1400_ai_ci, IMPLICIT) for operation '='<br /> <br /> ---- Backtrace ----<br /> #0 (root)/include/class.setup.php(71): osTicket->logDBError()<br /> #1 (root)/include/class.setup.php(44): SetupWizard->load_sql()<br /> #2 (root)/include/class.upgrader.php(433): SetupWizard->load_sql_file()<br /> #3 (root)/include/class.upgrader.php(355): StreamUpgrader->cleanup()<br /> #4 (root)/include/class.upgrader.php(121): StreamUpgrader->doTask()<br /> #5 (root)/include/ajax.upgrader.php(35): Upgrader->__call()<br /> #6 (root)/include/class.dispatcher.php(153): UpgraderAjaxAPI->upgrade()<br /> #7 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch()<br /> #8 (root)/scp/ajax.php(327): Dispatcher->resolve()<br /> #9 {main}

Nextly again Db Error 1267

DB Error #1267
[UPDATE `ost_ticket` A1 JOIN `ost_user_email` A2 ON A2.`address` = A1.`email` SET A1.`user_id` = A2.`user_id`, A1.`user_email_id` = A2.`id`] Illegal mix of collations (utf8mb3_uca1400_ai_ci, IMPLICIT) and (utf8mb3_general_ci, IMPLICIT) for operation '='<br /> <br /> ---- Backtrace ----<br /> #0 (root)/include/class.setup.php(71): osTicket->logDBError()<br /> #1 (root)/include/class.setup.php(44): SetupWizard->load_sql()<br /> #2 (root)/include/class.upgrader.php(433): SetupWizard->load_sql_file()<br /> #3 (root)/include/class.upgrader.php(355): StreamUpgrader->cleanup()<br /> #4 (root)/include/class.upgrader.php(121): StreamUpgrader->doTask()<br /> #5 (root)/include/ajax.upgrader.php(35): Upgrader->__call()<br /> #6 (root)/include/class.dispatcher.php(153): UpgraderAjaxAPI->upgrade()<br /> #7 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch()<br /> #8 (root)/scp/ajax.php(327): Dispatcher->resolve()<br /> #9 {main}

And finally:

DB Error #1072
[ALTER TABLE `ost_ticket` DROP COLUMN `name`, DROP COLUMN `email`, DROP COLUMN `phone`, DROP COLUMN `phone_ext`, DROP COLUMN `subject`, DROP COLUMN `priority_id`] Key column 'email' doesn't exist in table<br /> <br /> ---- Backtrace ----<br /> #0 (root)/include/class.setup.php(71): osTicket->logDBError()<br /> #1 (root)/include/class.setup.php(44): SetupWizard->load_sql()<br /> #2 (root)/include/class.upgrader.php(433): SetupWizard->load_sql_file()<br /> #3 (root)/include/class.upgrader.php(355): StreamUpgrader->cleanup()<br /> #4 (root)/include/class.upgrader.php(121): StreamUpgrader->doTask()<br /> #5 (root)/include/ajax.upgrader.php(35): Upgrader->__call()<br /> #6 (root)/include/class.dispatcher.php(153): UpgraderAjaxAPI->upgrade()<br /> #7 (root)/include/class.dispatcher.php(40): UrlMatcher->dispatch()<br /> #8 (root)/scp/ajax.php(327): Dispatcher->resolve()<br /> #9 {main}

But maybe I am wrong

KevinTheJedi

bartekct

Ahhhh! This could be the source of all your issues! When you created the new table it looks like you used utf8mb3 instead of utf8. I would suggest that you recreate the database using utf8 character set and utf8_general_ci collation, re-import the data, and reattempt the upgrade.

Cheers.

bartekct

On orginal DB i ahve everywhere setup utf8 and utf8_general_ci. When I dump DB, and I am restoring it on new server somehow it is setup to utf8mb3. Alter DB and alter table not works for me, DB is keeping utf8mb3 doesnt matter what I did. I create new DB with theoritically utf8 and utf8_general_ci, but after importing data from dump everything is back to utf8mb3.

Strange, need to figure it out.