Upgrade: SQL Syntax error

[dutchsnowden]

Can I trick it modifying that file AFTER it checks somehow?

 

Downgrading from current ubuntu LTS 20.04 version would be a whole new project for me. Never done such a downgrade. But I am a software engineer with 30 years of programming experience.

 

[Stuart Silvester]

This should work.

Edit applications/core/setup/upg_32000/upgrade.php line 1116 and change it to

\IPS\Db::i()->query( "UPDATE `" . \IPS\Db::i()->prefix . 'groups` SET `g_view_board`=1 WHERE `g_id`=' . (int) $INFO['guest_group'] );

You would need to disable the upgrade file check with:

\define( 'UPGRADE_MD5_CHECK', FALSE );

In your constants.php file.

Make sure you remove this after the upgrade.

[dutchsnowden]

3 minutes ago, dutchsnowden said:

Can I trick it modifying that file AFTER it checks somehow?

[...]

 

Overwrite the modified file after initial check? Probably all scrips are re-checking? And maybe there are other places where new syntax is used?

Just now, Stuart Silvester said:

This should work.

Edit applications/core/setup/upg_32000/upgrade.php line 1116 and change it to

\IPS\Db::i()->query( "UPDATE `" . \IPS\Db::i()->prefix . 'groups` SET `g_view_board`=1 WHERE `g_id`=' . (int) $INFO['guest_group'] );

You would need to disable the upgrade file check with:

\define( 'UPGRADE_MD5_CHECK', FALSE );

In your constants.php file.

Make sure you remove this after the upgrade.

Lemme try this.

1 minute ago, Stuart Silvester said:

This should work.

Edit applications/core/setup/upg_32000/upgrade.php line 1116 and change it to

\IPS\Db::i()->query( "UPDATE `" . \IPS\Db::i()->prefix . 'groups` SET `g_view_board`=1 WHERE `g_id`=' . (int) $INFO['guest_group'] );

You would need to disable the upgrade file check with:

\define( 'UPGRADE_MD5_CHECK', FALSE );

In your constants.php file.

Make sure you remove this after the upgrade.

Cannot locate constants.php, can you lend me a hand with it's location?

 

romulusm@Selenium:~$ grep -r 'UPGRADE_MD5_CHECK' .
./ips_702f7/applications/core/modules/setup/upgrade/systemcheck.php:    if ( \IPS\UPGRADE_MD5_CHECK )
./ips_702f7/init.php:                           'UPGRADE_MD5_CHECK'     => TRUE, // Can be used to prevent the upgrader checking that the files that are present are correct
romulusm@Selenium:~$

 

[dutchsnowden]

I found that constant in these files. init.php and systemcheck.php

Could not locate any constants.php

[Nathan Explosion]

Create one 

[dutchsnowden]

PHP is not my specialty. Creating constants.php should not be too big of a deal but where should I place this file?

 

Could this help me?

 

[Nathan Explosion]

In the same place as the conf_global.php

[dutchsnowden]

Seems it escaped wrongly the "bb_groups" too? (line 1117 is because I commented out original line and replaced with new on next line)

groups is escaped correctly but now bb_groups table name before it seems wrong.

 

Edited January 4, 2022 by dutchsnowden

[Stuart Silvester]

2 minutes ago, dutchsnowden said:

Seems it escaped wrongly the "bb_groups" too? (line 1117 is because I commented out original line and replaced with new on next line)

groups is escaped correctly but now bb_groups before it seems wrong.

 

Revert your changes to upgrade.php and then change line 1116 to the query I have posted here: https://invisioncommunity.com/forums/topic/464771-upgrade-sql-syntax-error/?do=findComment&comment=2876454

 

[dutchsnowden]

Trying now but it will take 16 mins for each try as I need to restore database at the point after UTF8 conversion.

UTF8 conversion takes 1h 30m. Restoring it takes 16m. So after each error, I restore database to make sure I try on fresh.

 

[dutchsnowden]

24 minutes ago, Stuart Silvester said:

Revert your changes to upgrade.php and then change line 1116 to the query I have posted here: https://invisioncommunity.com/forums/topic/464771-upgrade-sql-syntax-error/?do=findComment&comment=2876454

 

It passed that place, let's hope no more events...

 

[dutchsnowden]

I guess same exact error at line in 32001 line 40 now. "groups" not escaped.

 

1064You 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_photo_max_vars=REPLACE(g_photo_max_vars, ':150:150', ':200:300') W' at line 1
/var/www/html/forums/applications/core/setup/upg_32001/upgrade.php::40
UPDATE `bb_groups` groups  SET g_photo_max_vars=REPLACE(g_photo_max_vars, ':150:150', ':200:300') WHERE g_photo_max_vars LIKE '%:150:150' 

 

\IPS\Db::i()->update( 'groups', "g_photo_max_vars=REPLACE(g_photo_max_vars, ':150:150', ':200:300')", "g_photo_max_vars LIKE '%:150:150'" );

 

Edited January 4, 2022 by dutchsnowden

[dutchsnowden]

So, I've come to a conclusion and succeeded first dry-run. Friendly URLs not working, redirects not working, and missing uploads files, but database is sane and working.

Not sure how to solve this one:

What I did was upgrade pure database only to 3.4 version (everything except calendar).

Then from 3.4 version to 4.6.9 all worked smoothly (including calendar and pages).

So for now I tricked the upgrade and managed to convert everything to 4.6.9. Any ideas how to solve the template error above?

 

[Randy Calvert]

Just now, dutchsnowden said:

So, I've come to a conclusion and succeeded first dry-run. Friendly URLs not working, redirects not working, and missing uploads files, but database is sane and working.

Not sure how to solve this one:

What I did was upgrade pure database only to 3.4 version (everything except calendar).

Then from 3.4 version to 4.6.9 all worked smoothly (including calendar and pages).

So for now I tricked the upgrade and managed to convert everything to 4.6.9. Any ideas how to solve the template error above?

 

Have you tried logging into the Admin CP, clicking the support link in the upper right hand corner and then clearing the system cache?  (Middle right of the page)

[dutchsnowden]

Admin Dashboard works correctly it seems.

 

Just now, Randy Calvert said:

Have you tried logging into the Admin CP, clicking the support link in the upper right hand corner and then clearing the system cache?  (Middle right of the page)

Clearing system cash? Guide me. I am new to this 4.6.9.

 

This one?

 

It does not help clearing system caches.

 

[dutchsnowden]

OK. Solved that issue throwing exceptions. CAPTCHA had no value, as soon as I set a value, it went away and I could login.

 

[Marc]

Glad to see you managed to get the upgrade through. I would suggest getting your live one done and up to date now, so that you are in a better position for us to support you

[dutchsnowden]

Yes. As soon as possible. Moved DNS already pointing to the same old server but TTL very small. 00:00 CET most probably.

 

 

[Stuart Silvester]

I have submitted a potential fix for the upgrade SQL issues when upgrading from < 4.0.0 using MySQL 8.0.2 or newer. Following review and approval this change should be included in an upcoming release.

[dutchsnowden]

Glad to hear Stuart! I hope this will help a future upgrader!

[dutchsnowden]

9 minutes ago, Stuart Silvester said:

I have submitted a potential fix for the upgrade SQL issues when upgrading from < 4.0.0 using MySQL 8.0.2 or newer. Following review and approval this change should be included in an upcoming release.

To be honest, your solution was awesome, and thank you for it, but who knows how many lines should have been changed because of that escaping or however it is called. We corrected one, I stumbled upon the second, but there may be tens afterwards.

 

[Stuart Silvester]

4 minutes ago, dutchsnowden said:

To be honest, your solution was awesome, and thank you for it, but who knows how many lines should have been changed because of that escaping or however it is called. We corrected one, I stumbled upon the second, but there may be tens afterwards.

 

Yes, it wasn't a good enough workaround. I went a different route for the fix I have committed.

[dutchsnowden]

I am close to finish upgrade to 3.4 on production server then start with 4.6.9

Sorry but I am so stressed out and so much sleep missing that I needed to share this here.

 

[Jim M]

46 minutes ago, dutchsnowden said:

I am close to finish upgrade to 3.4 on production server then start with 4.6.9

Sorry but I am so stressed out and so much sleep missing that I needed to share this here.

 

Nice! Glad to see this progressing for you.

[Randy Calvert]

50 minutes ago, dutchsnowden said:

I am close to finish upgrade to 3.4 on production server then start with 4.6.9

Sorry but I am so stressed out and so much sleep missing that I needed to share this here.

 

A trip down memory lane!