dutchsnowden Posted December 28, 2021 Posted December 28, 2021 (edited) Starting a testinstall upgrade (dry run simulation on a real database backup), I got this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET `g_view_board`=? WHERE g_id=?' at line 1 /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1116 UPDATE `bb_groups` groups SET `g_view_board`=? WHERE g_id=? I clicked retry and got this: Duplicate column name 'status_author_id' /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1075 Now, anything I click, Retry or Continue, this is the only message that is shown. Edited December 28, 2021 by dutchsnowden
Marc Posted December 28, 2021 Posted December 28, 2021 Please update all access details on file. We can then take a look at the issue for you.
dutchsnowden Posted December 28, 2021 Author Posted December 28, 2021 (edited) Should I update the details with a test server testinstall? I cannot afford to do such tests on the production server and keep it offline who knows how much? I need to know I can solve any upgrade problem on a test server first. Edited December 28, 2021 by dutchsnowden
Marc Posted December 28, 2021 Posted December 28, 2021 If this is a test instance, I would advise that you revert and try this again in the first instance. If you are then having issues still, yes you can update with your test instance details
Miss_B Posted December 28, 2021 Posted December 28, 2021 1 hour ago, dutchsnowden said: Starting a testinstall upgrade (dry run simulation on a real database backup), I got this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET `g_view_board`=? WHERE g_id=?' at line 1 /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1116 UPDATE `bb_groups` groups SET `g_view_board`=? WHERE g_id=? I clicked retry and got this: Duplicate column name 'status_author_id' /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1075 Now, anything I click, Retry or Continue, this is the only message that is shown. What version of Ipb are you upgrading from?
dutchsnowden Posted December 28, 2021 Author Posted December 28, 2021 Just now, Miss_B said: What version of Ipb are you upgrading from? 3.1.4 -> 4.6.9 latest
dutchsnowden Posted December 28, 2021 Author Posted December 28, 2021 22 minutes ago, Marc Stridgen said: If this is a test instance, I would advise that you revert and try this again in the first instance. If you are then having issues still, yes you can update with your test instance details I will try this once more on a brand new installed ubuntu test server and if I hit the same snag, I will get back to you here. Is this ok?
Miss_B Posted December 28, 2021 Posted December 28, 2021 4 minutes ago, dutchsnowden said: 3.1.4 -> 4.6.9 latest That column that it is mentioned in your error, status_author _id, is not part of default 4.6.9 database a.f.a.i.k. So it must be either from a third plugin/app or from the 3.1.x versions. Can you check the database and see if it is added by a custom third party or it is part of Ipb 3.1.x. If the former, disable all your third party plugins/apps and give the upgrader another go. If the latter, I would recommend to do the upgrade in steps. Menaing upgrade first to the very latest version of 3.x version and then upgrade to 4.6.9.
dutchsnowden Posted December 28, 2021 Author Posted December 28, 2021 (edited) 3 minutes ago, Miss_B said: That column that it is mentioned in your error, status_author _id, is not part of default 4.6.9 database a.f.a.i.k. So it must be either from a third plugin/app or from the 3.1.x versions. Can you check the database and see if it is added by a custom third party or it is part of Ipb 3.1.x. If the former, disable all your third party plugins/apps and give the upgrader another go. If the latter, I would recommend to do the upgrade in steps. Meaning upgrade first to the very latest version of 3.x version and then upgrade to 4.6.9. Where do I get any other installation package other than the latest? Is there an old install files archive somewhere that I missed? Also how do I check the database for custom third party? How do I know which would be the columns that are not standard? I have only one download available in "client area" that is the latest. Are the previous releases available somewhere? Edited December 28, 2021 by dutchsnowden
Marc Posted December 28, 2021 Posted December 28, 2021 2 minutes ago, dutchsnowden said: Where do I get any other installation package other than the latest? Is there an old install files archive somewhere that I missed? Also how do I check the database for custom third party? How do I know which would be the columns that are not standard? Im not sure why you would want an older version? Unfortunately, being from such an old release, you may well have issues. We havent supported the release you are upgrading from for a considerable amount of time. I may be able to get hold of a 3.4 release if you want to upgrade to that first of all if you want to try that? We would not provide support for 3.x versions though unfortunately.
dutchsnowden Posted December 28, 2021 Author Posted December 28, 2021 (edited) 6 minutes ago, Marc Stridgen said: Im not sure why you would want an older version? Unfortunately, being from such an old release, you may well have issues. We havent supported the release you are upgrading from for a considerable amount of time. I may be able to get hold of a 3.4 release if you want to upgrade to that first of all if you want to try that? We would not provide support for 3.x versions though unfortunately. I do not in fact. Another client as me suggested I should try. Not sure how it would help. But could not see any other versions of IPB anywhere so I asked. Edited December 28, 2021 by dutchsnowden
Miss_B Posted December 28, 2021 Posted December 28, 2021 2 minutes ago, dutchsnowden said: Where do I get any other installation package other than the latest? Is there an old install files archive somewhere that I missed? Also how do I check the database for custom third party? How do I know which would be the columns that are not standard? You will have to check the install code of the plugins/apps and see what stuff they add in the database. The reason that I suggested the upgrade in steps btw, is that if that field is a standard ipb one, it might have been changed or even renamed at a later version for example. So by upgrading first to the latest 3.4x version, the changes will be done/added properly and you can upgrade to 4.6.9 without issues. Something to think about.
dutchsnowden Posted December 28, 2021 Author Posted December 28, 2021 34 minutes ago, Miss_B said: That column that it is mentioned in your error, status_author _id, is not part of default 4.6.9 database a.f.a.i.k. So it must be either from a third plugin/app or from the 3.1.x versions. [...] In fact the error is the first image. SQL syntax error and status_author_id is not even mentioned there. This is the error that upgrade stops with: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET `g_view_board`=? WHERE g_id=?' at line 1 /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1116 UPDATE `bb_groups` groups SET `g_view_board`=? WHERE g_id=?
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 So neverending story. Solve all problems, now after UTF-8 conversion it starts upgrade. And hit this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET `g_view_board`=? WHERE g_id=?' at line 1 /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1116 UPDATE `bb_groups` groups SET `g_view_board`=? WHERE g_id=? Not sure how and why there's a SQL syntax error in upg_3200...
Jim M Posted January 4, 2022 Posted January 4, 2022 32 minutes ago, dutchsnowden said: So neverending story. Solve all problems, now after UTF-8 conversion it starts upgrade. And hit this: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET `g_view_board`=? WHERE g_id=?' at line 1 /var/www/html/ips_3547c/applications/core/setup/upg_32000/upgrade.php::1116 UPDATE `bb_groups` groups SET `g_view_board`=? WHERE g_id=? Not sure how and why there's a SQL syntax error in upg_3200... Could you please update the access information on file and we can certainly take a look!
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 Server is configured SSH only with Public/Private key. Not sure how to enter those.
Jim M Posted January 4, 2022 Posted January 4, 2022 OK, no worries. Just saw too this is a 3.x upgrade. I was going to confirm your server requirements which you can do using the following: https://invisioncommunity.com/files/file/7046-invision-community-requirements-checker/ Could you confirm the MySQL version which you are using here? As my colleague said, you are upgrading from a version which is no longer supported. Have you tried upgrading your 3.1 instance to 3.4 before trying the version 4 upgrade?
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 (edited) Yes. I got some errors with those too, upgrading from 3.1.4 to 3.4... I have those too. Something with calendar and zero dates. Could not progress after that error. What is REALLY odd is that I did succeeded upgrading on a test server at one point with same procedure, but different download from Invision. Requirements ARE met, as I can confirm with ips4.php. And I get over the UTF8 conversion. My SQL version is 8.0: mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) The only changes in its config: Edited January 4, 2022 by dutchsnowden
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 Soon after, this happens. Then regardless of what I click, RETRY or CONTINUE, all I get is next author duplicate error:
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 Can't we track down how come there is a SQL Syntax error in upgrade scripts? There are numbers in error messages. I can sunt SQL queries if needed manually. I went to the PHP at first error but could not say what was the query that was run and gave SYNTAX ERROR. I would expect not seeing such an error as syntax.
Jim M Posted January 4, 2022 Posted January 4, 2022 Is there any issues otherwise with prepared queries on your MySQL instance? Are you able to try on 5.6 or 5.7? Just to confirm, when attempting the upgrade from 3.1.x to 3.4.9, this was done on PHP 5.6 and MySQL 5.5, right?
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 No. Everything I tried was on MySQL 8. mySQL query error: SELECT e.* FROM bb_cal_events e WHERE e.event_approved=1 AND ( ( e.event_start_date <= '2022-01-04' AND e.event_end_date >= '2021-12-01' ) OR ( ( e.event_end_date IS NULL OR e.event_end_date='0000-00-00 00:00:00' ) AND e.event_start_date >= '2021-12-01' AND e.event_start_date <= '2022-01-04' ) OR ( e.event_recurring=3 AND DATE_FORMAT(event_start_date, '%c')=12 AND e.event_end_date <= '2022-01-04' ) ) SQL error: Incorrect DATETIME value: '0000-00-00 00:00:00' SQL error code: 1525 Date: Wednesday 29th December 2021 01:50:01 AM This was the error I got from 3.4 test run. 3 minutes ago, Jim M said: Is there any issues otherwise with prepared queries on your MySQL instance? [...] I am not sure if I understand what you are asking here.
Solution Stuart Silvester Posted January 4, 2022 Solution Posted January 4, 2022 `groups` is a reserved keyword in MySQL 8.0.2 and newer. 3.1 predates this by many many years. You may need to do the upgrade on an earlier version of MySQL, but I'll see if we can come up with a workaround. Jim M 1
dutchsnowden Posted January 4, 2022 Author Posted January 4, 2022 (edited) So only thing changed is escaping "groups" right? So now I got this error... It complains that file is modified. Edited January 4, 2022 by dutchsnowden
Jim M Posted January 4, 2022 Posted January 4, 2022 4 minutes ago, dutchsnowden said: So only thing changed is escaping "groups" right? So now I got this error... It complains that file is modified. This is due to you have modified that file. As mentioned, it would be better to downgrade MySQL to 5.6/5.7 where this reserved word is not present so you are not modifying the software.
Recommended Posts