Upgrade failed - SQL error?

[LaurieMatthew]

Hey all, 

I have 4 forums with Invision and 3 have upgraded no problem but 1 is not working. 

When I try to upgrade via the ACP it tells me to manually download and upload the upgrade. I've done so but when I try to continue the upgrade I get the following 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 '/cms_custom_database_1 SET record_publish_date = record_saved, record_future_dat' at line 1 
 

I tried uploading the whole package instead, but it then tells me to run the install using a new database prefix which results in a new blank forum. 

So I'm a bit stuck. All my forums are with the same server/host company so not sure why there is this problem on one. Any help much appreciated! 

Edited August 19, 2022 by LaurieMatthew

[Stuart Silvester]

The problem is that you're using a forward slash as a table prefix and as such it's part of the table name. 

I would recommend renaming the tables to remove the / and then edit conf_global.php and change 

'sql_tbl_prefix' => '/',

to

'sql_tbl_prefix' => '',

Then go back to <url>/admin/upgrade to finish the upgrade.

[LaurieMatthew]

6 hours ago, Stuart Silvester said:

The problem is that you're using a forward slash as a table prefix and as such it's part of the table name. 

I would recommend renaming the tables to remove the / and then edit conf_global.php and change 

'sql_tbl_prefix' => '/',

to

'sql_tbl_prefix' => '',

Then go back to <url>/admin/upgrade to finish the upgrade.

Hi thanks for that 🙂

I've gone into myPHP but when I attempt to change the prefix from / to (blank) I get an error saying that the table already exists. 

Is there another way that I can achieve this? 

Or is it possible to upload the files to a new database with the correct blank prefix and then connect my content to that? 

Thank you 

[Miss_B]

2 minutes ago, LaurieMatthew said:

I've gone into myPHP but when I attempt to change the prefix from / to (blank) I get an error saying that the table already exists. 

It looks like you are editing the prefix in the phpmyadmin or some other databse management tool. The edit should be applied to the conf_global.php file. You can find that in the root of your forum folder. Hope it helps.

[Marc Stridgen]

Actually both. The table itself needs the prefix changing as well.

If you are having that issue, you would need to contact your hosting company for assistance

[LaurieMatthew]

Ok thank you again, I did try the host and they suggested I contact the software devs. 
 

I’ll contact them again and see if I can get it changed 

[Marc Stridgen]

No problem. Good luck with getting the issue resolved

[teraßyte]

Most likely the table's file on disk doesn't have the slash and that's why you get the error about the table already existing.

Instead of an empty prefix try using a different prefix like ipb_ or anything else really. That should solve your renaming issue. After the rename to ipb_tableName you should also be able to just change it back to tableName alone, too.

Edited August 19, 2022 by teraßyte

[LaurieMatthew]

So I got this sorted, it was indeed the / prefix causing the issue. The reason I couldn't rename the tables was that for some reason I had 2 complete sets, one with the / prefix and one without. 

I deleted the tables without the /, I was then able to rename the / tables to no prefix. Edited the conf_global and hey presto - a working upgrade! 

Thank you everyone for the help 🙂

Edited August 20, 2022 by LaurieMatthew
autocorrect

[Marc Stridgen]

Glad to see you finally got this working 🙂