Wednesday, December 28, 2005

What could be causing this?

I recently migrated a Nucleus CMS from MySQL 4.x to MySQL 5.0 but upon migration, I get the following message

ERROR 1054 (42S22): Unknown column 'i.inumber' in 'on clause'


when running the query:

SELECT i.inumber as itemid, i.iblog as blog, i.ititle as title, i.ibody as body, m.mname as author, m.mrealname as authorname, i.itime, i.imore as more, m.mnumber as authorid, m.memail as authormail, m.murl as authorurl, c.cname as category, i.icat as catid, i.iclosed as closed, t.tags as tags, t.item_id as item_id FROM nucleus_item as i, nucleus_member as m, nucleus_category as c LEFT join nucleus_plug_tags as t ON t.item_id=i.itemid WHERE i.iauthor=m.mnumber and i.iblog=1 and i.icat=c.catid and i.idraft=0 and (( match (ititle,ibody,imore) against ('aids') > 0 ) or ( t.tags LIKE '%/aids/%') ) and i.itime<="2005-12-28 19:15:54" ORDER BY i.itime DESC;



The interesting thing is that the column exists in the table and no change has been made. Any ideas what would be causing this?

Thanks

Update

Just noticed that the following works

SELECT i.inumber as itemid, i.iblog as blog, i.ititle as title, i.ibody as body, i.itime, i.imore as more, i.icat as catid, i.iclosed as closed, t.tags as tags, t.item_id as item_id FROM nucleus_item as i LEFT join nucleus_plug_tags as t ON t.item_id=i.inumber WHERE i.iblog=1 and i.idraft=0 and (( match (ititle,ibody,imore) against ('aids') > 0 ) or ( t.tags LIKE '%/aids/%') ) and i.itime<="2005-12-28 19:15:54" ORDER BY i.itime DESC;



What could be causing this? When I shorten the query, it works but not when other tables are involved?

Any ideas??

3 comments:

Frank said...

Thanks Gary,
you saved the day. I was thinking that it had something to do with the upgrade. Will post the updated query soon.

Thanks
Frank

Frank said...

Thank you Scott for the link.

Frank said...

Thanks Peter for the additional information.

I appreciate everyone taking the time to help me out. Please let me know if I can be of any assistance.

Frank