Jump to content

Upgrade: SQL Syntax error


Go to solution Solved by Stuart Silvester,

Recommended Posts

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

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
Posted (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 by dutchsnowden
Posted

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

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

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

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

Posted (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 by dutchsnowden
Posted
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.

Posted (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 by dutchsnowden
Posted
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. 

 

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

 

Posted

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

 

 

Posted
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!

Posted

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? 

 

Posted (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:

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
Posted

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.

 

Posted

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?

Posted

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.

 

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

  • Recently Browsing   0 members

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