1 Topic by davestroy (edited by davestroy 2004-01-14 03:00)

  • Registered: 2004-01-14
  • Posts: 3

Topic: Mysql INNER JOIN Error

Hi,

My ISP uses MySQL 3.22.32 and according to the documentation this version does not support INNER JOIN. This seem pretty crucial for this forum to work. Please give me a fix...

Iget these kind of error statements:

punbb/viewtopic.php
Line: 99

PunBB reported: Unable to fetch topic info 
Database reported: You have an error in your SQL syntax near 
'INNER JOIN mlt_forum_forums AS f ON t.forum_id=f.id 
LEFT JOIN mlt_forum_subscrip' at line 1 (Errno: 1064)
  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Mysql INNER JOIN Error

I'm very sorry, but that will include rewriting a large number of queries. 3.22.32 is very old. You really should tell your ISP to get a grip and upgrade to a newer version. 3.22.32 was released in February 2000 and that was looong time ago in Internet years :)

Might I ask what ISP it is?

Edit: I just noticed the install document says: "Version 3.23 or later is recommended. PunBB will probably work with older versions though.". That is wrong and I will update it for the next version of PunBB. The absolute minimum for PunBB to work is 3.23.17.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • Registered: 2004-01-14
  • Posts: 3

Re: Mysql INNER JOIN Error

It is possible to rewrite the sql statements into 2 or three separate statements. However, this invloves much time to understand the database structure.

Lucky for me, my ISP just installed a new MySQl database (4.x) so problem solved for me. It is one of them greedy german providers. It is very slow and they are called Strato (www.strato.de). To every one reading this: STAY AWAY FROM STRATO! THEY SUCK BIG TIME. ALSO STAY AWAY FROM FSDATA (www.fsdata.se) THEY ARE REAL GOOD BUT REAL EXPENSIVE TOO (150 kr /månad)

Over n Out

Thanks for a clean forum smile Hate massive phpbb

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Mysql INNER JOIN Error

Nice to hear it worked out :)

davestroy wrote:

It is possible to rewrite the sql statements into 2 or three separate statements. However, this invloves much time to understand the database structure.

Actually, you don't have to. You just have to rewrite the joins to not use SQL92 JOIN syntax. I.e.

SELECT posts.* FROM posts INNER JOIN topics ON topics.id=posts.topic_id WHERE topics.topic_id=666;

would be rewritten into

SELECT posts.* FROM posts, topics WHERE topics.id=posts.topic_id AND topics.topic_id=666;

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • Frank H
  • Former PunBB Developer
  • Offline
  • Registered: 2003-03-16
  • Posts: 1,423

Re: Mysql INNER JOIN Error

Is there an advantage to use inner join instead of SQL92?

(as I'm used to use the latter method) smile

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Mysql INNER JOIN Error

In the case above, there are no advantages. The advantages show first when you want more control over which table is joined with which. E.g. a LEFT JOIN means that all rows in the table to the left will be included and all rows that match the join condition from the right table. A RIGHT JOIN is the same, but vice versa.

Also, the join syntax PunBB uses is the current standard. However unlikely, support for the old style syntax could be dropped in future versions of the databases PunBB supports.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • Registered: 2004-01-14
  • Posts: 3

Re: Mysql INNER JOIN Error

I havd had the experience that using WHERE statements returns zero rows when one of the conditions is false. A JOIN statement returns the cells in a row that exists... Somehow like that. Meaning that a WHERE statement stops lokking for rows if it encounters one that does not match the search critera, and JOIN's continue until the end of the table.

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Mysql INNER JOIN Error

Well, that's not completetly right. It all depends on how you construct your query. Anything that can be done with the new style can also be done with the old style. It's just a lot uglier.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website