Post view

修正升級到MySQL Community Server 5.7.17造成「照片檔名」排序錯誤的問題

在去年的「04/21/16」我寫了一篇關於升級MySQL資料庫到當時的最新版本MySQL Community Server 5.7.12之後,很快就發現「照片檔名」排序錯誤的問題!


insoler網站嘗試升級資料庫到MySQL Community Server 5.7.12

雖然MySQL Community Server 5.7.12號稱Packages for El Capitan (10.11) are compatible with Yosemite (10.10) and Mavericks (10.9).同時支援Mac OS X 10.9〜10.11,但其實我在OS X 10.10.5上面安裝好MySQL Community Server 5.7.12,匯入備份的insoler.sql資料庫檔案,就在insoler發現了重大的問題!

這個重大問題就像這樣:

msql5712001.jpg

 對一般人來說,可能完全看不出上面這個畫面有什麼樣的重大問題?所以... 提醒你一下,請特別留意一下照片的檔案名稱順序

正確的「照片順序」應該是底下這個樣子才對:

msql5712002.jpg

 上面的情況是我重新安裝目前最新的MySQL Community Server 5.6.30版本的結果。事實上這個才是正確結果!你只要注意一下照片的檔案名稱的順序,應該會發現正確的順序是按照上傳時間「最近」(不是按照照片的拍攝時間,但通常都會是照片的拍照順序)也就是「最新」上傳的順序往後排列,所以正確的順序應該是9796、9795、9794、9793、9792、9791這樣的順序才對。最後拍攝的一張照片,最後上傳的一張照片,會放在insoler的左上角,成為最新的一張照片。

但是,一旦升級到最新的MySQL Community Server 5.7.12版本的話,照片的順序就變得亂七八糟!這還只是可以看到的重大問題!其他問題還可以從mysqld.local.err的Logs記錄檔案裡面看到。


在當時我並不知道這個問題是出在哪裡?由於insoler網站的PHP程式碼在當時並沒有任何改變,我只有變更MySQL資料庫,從MySQL 5.6.30升級到MySQL 5.7.12而已,直覺上認為這個問題應該是「MySQL 5.7.12」與「MySQL 5.6.30」不相容!但這個問題就算是升級到現在最新的「MySQL 5.7.17」也不會有任何改善!

insoler不可能永遠都不升級MySQL資料庫版本!就算MySQL社群到現在還繼續修正、維護舊的5.6系列,到目前最新的「MySQL 5.6.35」版本,但終究會有一天停止維護5.6系列版本!因此,我花了許多時間來追查Dolphin的PHP程式碼,終於發現Dolphin在網站首頁「顯示照片」的時候,竟然是使用「bx_photos_main」資料表的「Date」日期欄位做為排序依據!

從phpMyAdmin來看「bx_photos_main」資料表的內容,會像是這個樣子。其中的「Date」日期欄位是以PHP的「time()」函式的時間格式來儲存。

在PHP官方網站對於「time()」函式的說明是:

返回自從 Unix 紀元(格林威治時間 1970 年 1 月 1 日 00:00:00)到當前時間的秒數。

簡單的說,「time()」函式的時間精密度只能記錄到「秒」這個單位而已。無法達到「1/100秒」或是「1/10000秒」更精確的單位!雖然在一般的情況下,不見得都需要「1/100秒」以下更精確的單位,只需要記錄現在的時間就行了。比如文章的發表時間、討論區的話題回覆時間、照片的上傳時間等。

但是從一個會員數有幾千萬、幾億的大型社群網站、超大型社群網站來看,時間的精確度只能達到「秒」的話,有時候就會很難區分先後順序!因為在「1秒內」就可能有幾萬個會員發表討論、上傳許多照片,因此時間的精密度只能記錄到「秒」這個單位的話,就可能會出錯!

雖然PHP的「time()」函式可以傳回目前的「日期、時間」,但因為不同國家會使用不同的「日期、時間」格式,所以不是像你想像的那樣,直接儲存「2017/01/15 19:24:35」這樣看起來似乎一目瞭然的時間格式,而是把時間(從格林威治時間到現在時間)換算成「秒數」,使用純數字「1484104299」這樣的格式來儲存。之後要顯示成「2017/01/15 19:24:35」或是「01/15/17 19:24:35」都可以按照實際的需求來轉換到正確的日期格式。

PHP的「time()」函式使用「1484104299」這樣的格式來記錄時間,雖然對「人」來說會難以閱讀,但基本上並沒有太大的問題。全世界使用PHP來架設的網站,都是這樣使用。真正的問題是PHP程式碼要如何運用?

Dolphin真正的問題是:

沒有考慮到「大型社群網站」可能會在同一個時間內,在一秒之內就可能有幾萬個會員,同時上傳了好幾張照片!

如果只用「秒」這個時間單位來排序照片,就絕對會出錯!因為這樣系統會無法分辨在「一秒內」上傳的先後順序!但神奇的事,Dolphin使用精確度只有達到「秒」的「時間」單位來排序,搭配「MySQL 5.6.35」的時候,正好「負負得正」剛好可以排序出正確的順序!但是搭配新的「MySQL 5.7.17」的時候,就會因為新的「MySQL 5.7.17」會認為同樣的「時間」並沒有先後順序的差別!反而會造成「上傳照片順序錯誤」的問題!

我只抓取「Date」欄位的畫面,這樣就可以很清楚的看到,在同樣的「1484104299」時間內,同一秒之內就上傳了3張照片!有3筆資料的時間都是「1484104299」。這樣當然會導致「MySQL 5.7.17」無法區別照片的上傳順序!

雖然從「bx_photos_main」資料表的「Date」日期欄位有好幾筆資料的時間相同,猜想問題大概就是出在這裡,但是要找到出問題的程式碼是在哪裡?這就沒有那麼容易了!因此又花了幾個月的時間,終於找到在「inc/classes/BxDolSearch.php」的getSorting副程式出問題!

程式使用switch指令來判斷排序方式,沒有特別指定的話,default預設是使用「"ORDER BY `date` DESC";」日期欄位來排序,而且排序的方向順序是由大到小。

    /*

     * Get sorting part of query according current sorting mode

     * @param string $sSortType sorting type

     * return array with sql elements order and ownFields

     */

    function getSorting ($sSortType = 'last')

    {

        $aOverride = $this->getAlterOrder();

        if (is_array($aOverride) && !empty($aOverride))

            return $aOverride;

 

        $aSql = array();

        switch ($sSortType) {

            case 'rand':

                $aSql['order'] = "ORDER BY RAND()";

                break;

            case 'top':

                $sHow = "DESC";

                $aSql['order'] = "ORDER BY `Rate` $sHow, `RateCount` $sHow, `date` $sHow";

                break;

            case 'score':

                if (is_array($this->aCurrent['restriction']['keyword'])) {

                    $aSql['order'] = "ORDER BY `score` DESC";

                    $aSql['ownFields'] .= $this->getSearchFieldsCond($this->aCurrent['searchFields'], $this->aCurrent['restriction']['keyword']['value'], 'score'). ', ';

                }

                break;

            case 'voteTime':

                $aSql['order'] = "ORDER BY `voteTime` DESC";

                break;

            case 'none':

                $aSql['order'] = "";

                break;

            default:

                $aSql['order'] = "ORDER BY `date` DESC";

        }

        return $aSql;

    }

其實只要略為看得懂PHP程式碼的人,應該會留意到getSorting副程式的預設參數是「$sSortType = 'last'」,也就是說呼叫getSorting副程式可以不指定參數,這樣「$sSortType」參數內容就會是預設的「last」。然而在switch裡面,竟然卻完全沒有處理「last」這個排序方式!排序方式只有rand、top、score、voteTime、none。

要解決升級「MySQL 5.7.17」就會因為無法分辨照片上傳的「Date」時間,造成照片顯示的順序錯誤的問題,其實只要加上一個「case 'last':」的判斷式,指定使用「"ORDER BY `ID` DESC";」上傳資料的「ID」順序編號,這樣就絕對不會出錯!無論是一秒內同時上傳3張照片,或是上傳30000張照片,都絕對不會出錯!因為「ID」是每一筆資料唯一的編號,絕對不會重複,而且也不用理會上傳時間,最後上傳的照片永遠都會自動使用「自動累加1」的「ID」編號。

        switch ($sSortType) {

            case 'last':

                $aSql['order'] = "ORDER BY `ID` DESC"; //BNW 修正MySQL 5.7照片順序錯誤的問題

                break;

            case 'rand':

                $aSql['order'] = "ORDER BY RAND()";

                break;

所以只要把程式碼改成這樣,就可以解決資料庫是使用「MySQL 5.6.35」或是「MySQL 5.7.17」的問題了。

蘇言霖 01/11/2017 0 513
Comments
Order by: 
Per page:
 
  • There are no comments yet
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
01/11/2017 (674 days ago)
Actions