Jump to content

Upgrade: SQL Syntax error


Go to solution Solved by Stuart Silvester,

Recommended Posts

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=? 

image.png.7ba306417ea8564508221a8f8324c41b.png

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

image.png.c441a4de7c8b2fb06e53c128ffbc5629.png

Now, anything I click, Retry or Continue, this is the only message that is shown.

Edited by dutchsnowden
Link to comment
Share on other sites

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=? 

image.png.7ba306417ea8564508221a8f8324c41b.png

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

image.png.c441a4de7c8b2fb06e53c128ffbc5629.png

Now, anything I click, Retry or Continue, this is the only message that is shown.

What version of Ipb are you upgrading from?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 by dutchsnowden
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by dutchsnowden
Link to comment
Share on other sites

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. 

 

Link to comment
Share on other sites

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=? 

 

Link to comment
Share on other sites

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...

 

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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? 

 

Link to comment
Share on other sites

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:

image.png.2f55b0f5045afef107a2db8f1c56e316.png

image.png.2669f7101f0459bcf4362575e1dc60ee.png

image.png.bdb7be0946a1ba1fdc5ca09d23be3a97.png

image.png.9e1d876372bbe39a074855a7cf7c0994.png

image.png.2ba8d8bd3110c5bd4b584ff1a2d650f7.png

image.png.3090d26cca33245728cb2eebea087056.png

Edited by dutchsnowden
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

4 minutes ago, dutchsnowden said:

 

So only thing changed is escaping "groups" right?

image.thumb.png.2395289048f0e50885f33f96fa842d92.png

So now I got this error...

It complains that file is modified.

image.png.228f28fa78082383bfc84fcc0100b683.png

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.

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...