SQL error 1064 after upgrade

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
User avatar
alex75
Registered User
Posts: 358
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

SQL error 1064 after upgrade

Post by alex75 » Mon May 13, 2019 11:59 am

Hi guys.
after updating a board from phpbb 3.0 to phpbb 3.1 and later to phpbb 3.2
I get this error by clicking on any forum (if instead I click on a topic, it works correctly):

Code: Select all

[phpBB Debug] PHP Notice: in file [ROOT]/viewforum.php on line 627: Undefined index: b
[phpBB Debug] PHP Notice: in file [ROOT]/viewforum.php on line 633: Undefined index: b

SQL ERROR [ mysqli ]

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 'ASC LIMIT 20' at line 4 [1064]

SQL

SELECT t.topic_id FROM (phpbb_topics t) WHERE t.forum_id = 206 AND t.topic_type IN (0, 1) AND (t.topic_visibility = 1) ORDER BY t.topic_type DESC, ASC LIMIT 20

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 997
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/mysql_base.php
LINE: 45
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 270
CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/viewforum.php
LINE: 695
CALL: phpbb\db\driver\factory->sql_query_limit()
screenshot.jpg

User avatar
david63
Registered User
Posts: 16093
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: SQL error 1064 after upgrade

Post by david63 » Mon May 13, 2019 12:04 pm

Looks to me as if you either have an old version of viewforum.php, or it is a modified version as there is no code on either of those two lines - in fact I cannot see anything resembling that anywhere near that code in that file.
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
alex75
Registered User
Posts: 358
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 » Mon May 13, 2019 12:14 pm

Thanks for the quick response. I updated, replacing the files from the original package. viewforum.php is a new unmodified file.
Currently:
phpbb 3.2.5
php 5.6.40
MySQL(i) 5.7.26

User avatar
david63
Registered User
Posts: 16093
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: SQL error 1064 after upgrade

Post by david63 » Mon May 13, 2019 1:59 pm

Sorry I was looking at 3.2.7. Having said that I cannot see where the index: b would be coming from unless it is a carry over from a MOD in your 3.0 board as that is not a "normal" search index key.
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51701
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL error 1064 after upgrade

Post by Brf » Mon May 13, 2019 2:55 pm

david63 wrote:
Mon May 13, 2019 1:59 pm
I cannot see where the index: b would be coming from
I am betting it is this:

Code: Select all

t.topic_type DESC, ASC 
See there is no field to sort by named before the "ASC"

User avatar
alex75
Registered User
Posts: 358
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 » Mon May 13, 2019 3:43 pm

I deleted the tables of the old mod. Probably, as David said, it is a residual instruction that has changed the type of search in some table. how can i restore it?

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51701
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL error 1064 after upgrade

Post by Brf » Mon May 13, 2019 5:14 pm

The error message is implying that you do not have a default "Display topics ordering by:" in your UCP's Board Prefs

User avatar
alex75
Registered User
Posts: 358
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 » Mon May 13, 2019 5:56 pm

exact! Great Brf ! now works.
display preferences were already set correctly; I only clicked on send to reconfirm them, so everything started working again.
however, the problem occurs with all 3600 users. I should tell everyone to do this. how could i do it for everyone?
Edit:
even the guest has the same problem. how do i fix it?

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51701
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL error 1064 after upgrade

Post by Brf » Mon May 13, 2019 6:00 pm

The default for phpbb_users column user_topic_sortby_type is "t" so run something like this:

Code: Select all

update phpbb_users set user_topic_sortby_type = 't', user_topic_sortby_dir = 'd';

User avatar
alex75
Registered User
Posts: 358
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 » Mon May 13, 2019 6:32 pm

Perfect! Many thanks. Now everything works perfectly

Post Reply

Return to “[3.2.x] Support Forum”