Home | ArchiveList | Category | About
Home > PC > Nucleus > MySQL 5.x系の対応
いつの間にか、アーカイブリスト(タイトル一覧)で任意のカテゴリを選ぶとSQLエラーが出る状態になってました。

※カッコが二つ続くとNucleusで変換をかけてしまうので、一部全角のカッコが混じってます。
mySQL error with query SELECT DISTINCT(i.inumber) as itemid, i.ititle as title, i.ibody as body, m.mname as author, m.mrealname as authorname, UNIX_TIMESTAMP(i.itime) as timestamp, i.itime, i.imore as more, m.mnumber as authorid, i.icat as catid, i.iclosed as closed, c.cname as category FROM nucleus_item as i, nucleus_member as m, nucleus_category as c, nucleus_blog as b LEFT JOIN nucleus_plug_multiple_categories as p ON i.inumber=p.item_id WHERE i.iauthor=m.mnumber and i.icat=c.catid and i.idraft=0 and i.iblog=1 and ((i.inumber=p.item_id and (p.categories REGEXP "(^|,)44(,|$)" or i.icat=44)) or (p.item_id IS NULL and i.icat=44)) and i.itime<="2010-04-06 00:47:17" and b.bnumber = c.cblog ORDER BY i.itime DESC LIMIT 0,50: Unknown column 'i.inumber' in 'on clause'

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /var/www/home/takehana/public_html/cgi/nucleus/nucleus/libs/BLOG.php on line 174
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/home/takehana/public_html/cgi/nucleus/nucleus/libs/BLOG.php on line 213
Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/home/takehana/public_html/cgi/nucleus/nucleus/libs/BLOG.php on line 222


「Unknown column 'i.inumber' in 'on clause'」で検索してみたら、原因はレンタルサーバーのMySQLのバージョンが4.x系から5.x系へアップグレードされてた事でした。

どうもMySQLのバグがあるようで、joinの優先順位が変わっているらしいです(参考1参考2参考3)。参考URLの修正をしようかと NP_MultiCategory を開いてみたら、なんとすでに修正はされてました。

なんでだろうか、と思ってエラーのSQLでGREPしてみたら、原因は実は NP_TitleList でした。気がついてみたら当たり前でした、先入観はいけませんね。そういうわけで、下記部分を修正して、対応完了しました。

NP_TitleList の717行あたり
//2010/04/06_takehana_upd_s
// $query .= ' FROM '.sql_table('item').' as i, '.sql_table('member').' as m, '.sql_table('category').' as c, '.sql_table('blog').' as b'.$mtable;
$query .= ' FROM '.sql_table('member').' as m, '.sql_table('category').' as c, '.sql_table('blog').' as b, '.sql_table('item').' as i'.$mtable;
//2010/04/06_takehana_upd_e


ついでに、MySQLの4.x系時代にて文字コードが latin1 になってたせいで検索結果が変になることがあったので、5.x系になった機会にこちらの対応もしました。

まずは既存の latin1 のDBの中身をエクスポートします。そして別途 utf8 のDBを用意してインポートします。これだとDB全体としては utf8 なんですが、テーブル一つ一つは latin1 のままになっているんで、下記SQL文を一個一個実行していって全てのテーブルを utf8 へ変更しました。

ALTER TABLE <テーブル名> CHARSET=utf8;
(例)ALTER TABLE nucleus_item CHARSET=utf8;


ここまでやった後で、Nucleus の config.php を新しいDBへ接続するように書き換えれば完了です。


この記事へのコメント
Please JavaScript ON for comment. %3c%61%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%22%3e%3c%2f%61%3e %3c%66%6f%72%6d%20%6d%65%74%68%6f%64%3d%22%70%6f%73%74%22%20%61%63%74%69%6f%6e%3d%22%23%6e%75%63%6c%65%75%73%5f%63%66%22%3e %09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%61%63%74%69%6f%6e%22%20%76%61%6c%75%65%3d%22%61%64%64%63%6f%6d%6d%65%6e%74%22%20%2f%3e %09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%75%72%6c%22%20%76%61%6c%75%65%3d%22%69%6e%64%65%78%2e%70%68%70%3f%69%74%65%6d%69%64%3d%32%34%34%33%26%61%6d%70%3b%63%61%74%69%64%3d%34%32%22%20%2f%3e %09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%69%74%65%6d%69%64%22%20%76%61%6c%75%65%3d%22%32%34%34%33%22%20%2f%3e %09 %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6e%61%6d%65%22%3e%4e%61%6d%65%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%69%6e%70%75%74%20%6e%61%6d%65%3d%22%75%73%65%72%22%20%73%69%7a%65%3d%22%34%30%22%20%6d%61%78%6c%65%6e%67%74%68%3d%22%34%30%22%20%76%61%6c%75%65%3d%22%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%66%69%65%6c%64%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6e%61%6d%65%22%20%2f%3e %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%63%6c%61%73%73%3d%22%6c%61%62%65%6c%6e%61%6d%65%22%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6d%61%69%6c%22%3e%55%52%4c%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%69%6e%70%75%74%20%6e%61%6d%65%3d%22%75%73%65%72%69%64%22%20%73%69%7a%65%3d%22%34%30%22%20%6d%61%78%6c%65%6e%67%74%68%3d%22%36%30%22%20%76%61%6c%75%65%3d%22%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%66%69%65%6c%64%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%6d%61%69%6c%22%20%2f%3e %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%63%6c%61%73%73%3d%22%6c%61%62%65%6c%6e%61%6d%65%22%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%65%6d%61%69%6c%22%3e%4d%61%69%6c%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%69%6e%70%75%74%20%6e%61%6d%65%3d%22%65%6d%61%69%6c%22%20%73%69%7a%65%3d%22%34%30%22%20%6d%61%78%6c%65%6e%67%74%68%3d%22%31%30%30%22%20%76%61%6c%75%65%3d%22%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%66%69%65%6c%64%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%65%6d%61%69%6c%22%20%2f%3e %09%09%3c%64%69%76%20%63%6c%61%73%73%3d%74%72%61%63%6b%62%61%63%6b%69%6e%66%6f%3e %09%09%09%3c%6c%61%62%65%6c%20%63%6c%61%73%73%3d%22%6c%61%62%65%6c%6e%61%6d%65%22%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%62%6f%64%79%22%3e%43%6f%6d%6d%65%6e%74%3a%3c%2f%6c%61%62%65%6c%3e %09%09%3c%2f%64%69%76%3e %09%09%3c%74%65%78%74%61%72%65%61%20%6e%61%6d%65%3d%22%62%6f%64%79%22%20%20%63%6c%61%73%73%3d%22%63%6f%6d%6e%61%6d%65%22%20%63%6f%6c%73%3d%22%35%35%22%20%72%6f%77%73%3d%22%31%30%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%62%6f%64%79%22%3e%3c%2f%74%65%78%74%61%72%65%61%3e %09%09%3c%62%72%2f%3e%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%63%68%65%63%6b%62%6f%78%22%20%6e%61%6d%65%3d%22%6e%6f%74%73%70%61%6d%22%20%76%61%6c%75%65%3d%22%32%34%34%33%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%73%70%61%6d%22%20%63%68%65%63%6b%65%64%3d%22%63%68%65%63%6b%65%64%22%20%2f%3e%3c%6c%61%62%65%6c%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%73%70%61%6d%22%3eチェックを外してください%3c%2f%6c%61%62%65%6c%3e %3c%69%6e%70%75%74%20%74%79%70%65%3d%22%68%69%64%64%65%6e%22%20%6e%61%6d%65%3d%22%74%69%6d%65%72%22%20%76%61%6c%75%65%3d%22%31%35%37%33%39%35%36%31%38%32%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%74%69%6d%65%72%22%20%2f%3e %09%09%3c%62%72%20%2f%3e%3c%62%72%20%2f%3e %09%09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%63%68%65%63%6b%62%6f%78%22%20%76%61%6c%75%65%3d%22%31%22%20%6e%61%6d%65%3d%22%72%65%6d%65%6d%62%65%72%22%20%69%64%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%72%65%6d%65%6d%62%65%72%22%20%20%2f%3e %09%09%3c%6c%61%62%65%6c%20%66%6f%72%3d%22%6e%75%63%6c%65%75%73%5f%63%66%5f%72%65%6d%65%6d%62%65%72%22%3e%53%61%76%65%20%59%6f%75%72%20%49%6e%66%6f%3c%2f%6c%61%62%65%6c%3e %09%09%3c%62%72%20%2f%3e %09%09%3c%69%6e%70%75%74%20%74%79%70%65%3d%22%73%75%62%6d%69%74%22%20%61%6c%74%3d%22%41%64%64%20%43%6f%6d%6d%65%6e%74%22%20%76%61%6c%75%65%3d%22%41%64%64%20%43%6f%6d%6d%65%6e%74%22%20%63%6c%61%73%73%3d%22%66%6f%72%6d%62%75%74%74%6f%6e%22%20%2f%3e %09%09%3c%62%72%20%2f%3e %3c%2f%66%6f%72%6d%3e
Home | PageTop | RSS2.0 | ATOM