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: 509
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

SQL error 1064 after upgrade

Post by alex75 »

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: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: SQL error 1064 after upgrade

Post by david63 »

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!

I now no longer support any of my extensions but they will start to become available here
User avatar
alex75
Registered User
Posts: 509
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 »

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: 20646
Joined: Thu Dec 19, 2002 8:08 am

Re: SQL error 1064 after upgrade

Post by david63 »

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!

I now no longer support any of my extensions but they will start to become available here
User avatar
Brf
Support Team Member
Support Team Member
Posts: 53398
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL error 1064 after upgrade

Post by Brf »

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: 509
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 »

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: 53398
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL error 1064 after upgrade

Post by Brf »

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: 509
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 »

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: 53398
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: SQL error 1064 after upgrade

Post by Brf »

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: 509
Joined: Sun Jun 10, 2012 9:09 am
Location: Italy
Name: Alessandro
Contact:

Re: SQL error 1064 after upgrade

Post by alex75 »

Perfect! Many thanks. Now everything works perfectly
Post Reply

Return to “[3.2.x] Support Forum”