Host Upgraded MySQL to 4.0.13 ... now code fails

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

phpbb runs fine, and they dont really offer support for making external scripts really... Its basically just using information in the same database as phpbb...

whats annoying me is it did work now that after my host upgraded MySQL it doesnt... :(

Coz it was work in progress as well.. had it working... came back the next day and... no longer did my code function. I still gotta get it to use BBCode for some parts and also use line returns...

any way first thing first....
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Have a look on the mySQL site and see if there is anything in the change log relating to JOINS/ON conditions, I reckon the support for a specific format of SQL has been changed slightly.

I've tested this on 3.23 and works fine, which may have been your previous version, I'm in the process of upgrading/evaluating version 4.0.13 so I may be able to help once I get all my DB's (>100GB of data) over to mySQL 4.

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Greater than 100gb holy....

Thats gonna take a couple of years... I have loked around the mysql site come up with nothing.. im going to go look again

I found stuff on this page...

http://www.mysql.com/doc/en/JOIN.html

not sure what it all means really.. coz Im very new to mysql as this is my first script...
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Where not exactly getting this sorted for you in double time, are we :)

Bear with me.

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

maybe this will help?

Post by yamokosk »

I went digging at the mySQL site and found this in the change log for your new version of mySQL:

"Fixed bug with NATURAL LEFT JOIN, NATURAL RIGHT JOIN and RIGHT JOIN when using many joined tables. The problem was that the JOIN method was not always associated with the tables surrounding the JOIN method. If you have a query that uses many RIGHT JOIN or NATURAL ... JOINS you should check that they work as you expected after upgrading MySQL to this version. (Bug #291) "

Hope this helps.
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

one more thing

Post by yamokosk »

One other thing I read from the mysql website:

"RIGHT JOIN works analogously as LEFT JOIN. To keep code portable across databases, it's recommended to use LEFT JOIN instead of RIGHT JOIN."

I am assuming they meant across different versions of mySQL?

But couldn't you just change your SQL syntax to incorporate LEFT JOIN? Per their change log, their was nothing changed with LEFT JOIN, only RIGHT JOIN.
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Well now i know its an offical error thing...

I doesnt really tell me how to fix it... thanks tho :)
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Your gunna have to tune the query (re-write it). Use/try the dot notation, explained in the manual page you posted.

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Ahh k.. just my to RIGHT JOIN statments are no longer vaild or soemthing... , to be honest i got zippo idea how to rewrite this, if any one with great knowledge and a wee bit of spare time good sho me what needs to be done .. or least send me in the right direction that would be nice.. thanks to all that have help so far. :)

Cheers
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

If my woman doesn't give me grief over the weekend for spending too much time on my puter I'll see what I can do, not to say other forum members should'nt have a go ;)

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Cool thanks man... your a champ good to see your willing to help :D thanks again.. even if you dont get the time.
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Just bumping really... don't hit me :(

:roll:
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Still no ideas any body... I have been trying to tweak this code to work myself but its just not happening.... please anybody!
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

I've had fun with this one, here's were I got upto:

Once I got mySQL up and running I retried your query and it didn't work, so we are on the same sheet. Decided that I needed more tables etc so installed phpBB (which is rather good, if I may say) and populated the database.

I've been trying to re-write your query to be 2 SELECTS, the joins being the issue, but it's hard to determine what the actual output should be.

I would suggest that you give 2 select statements a try on your application. AFAIK, you can't do what you need to do in a single query :(

Sorry, I tried :)

Regards,
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

HEhe... stupid 4.0.13... how is it better if it can do less haha :(

Yeah well the output my code use to create was something like

"Topic title" posted by "Username" @ "Date/Time"

"Post text body"

Read more (link to topic)

... hmm 2 select statements... i didnt know you could really do that, but wouldnt that just muddle thing up? like JOINs make sure the information matchs up in the tables... where using 2 select statements may not do this.. i dunno... I do know very little after all haha.

Thanks buddy :)
Post Reply