Post view

MySQL資料庫錯誤訊息:This could be because you hit a bug.

在幾天前我寫了這篇「私人」公告,分別貼在會議室與部落格:

向室友ayaka致歉!因為Mac伺服器主機升級,導致網站RAW轉檔錯誤、MySQL資料庫頻頻當機

但MySQL的問題並沒有就這樣結束!在mysqld.local.err錯誤記錄檔案裡面,看到了以前沒有遇到過的錯誤訊息!


2016-12-19 21:00:08 UTC - mysqld got signal 10 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

 

key_buffer_size=8388608

read_buffer_size=131072

max_used_connections=164

max_threads=5000

thread_count=98

connection_count=98

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1991942 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

 

Thread pointer: 0x7fbeb3d69c00

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 700004374e60 thread_stack 0x40000

0   mysqld 0x00000001061f29fd my_print_stacktrace + 61

1   mysqld 0x0000000105fe0c78 handle_fatal_signal + 696

2   libsystem_platform.dylib 0x00007fff8f600bba _sigtramp + 26

3   ??? 0x0000000000000000 0x0 + 0

4   mysqld 0x0000000105fa75d0 _ZL15net_read_packetP6st_netPm + 128

5   mysqld 0x0000000105fa72d3 my_net_read + 99

6   mysqld 0x000000010600ea03 _ZL24server_mpvio_read_packetP13st_plugin_vioPPh + 179

7   mysqld 0x000000010600fd19 _ZL28native_password_authenticateP13st_plugin_vioP25st_mysql_server_auth_info + 89

8   mysqld 0x000000010600cf88 _ZL12do_auth_onceP3THDP19st_mysql_lex_stringP9MPVIO_EXT + 184

9   mysqld 0x000000010600ba1f _Z16acl_authenticateP3THDj + 575

10  mysqld 0x000000010603d1e0 _Z16login_connectionP3THD + 560

11  mysqld 0x000000010603dc36 _Z24do_handle_one_connectionP3THD + 294

12  mysqld 0x000000010603dafb handle_one_connection + 59

13  mysqld 0x00000001063dc877 pfs_spawn_thread + 311

14  libsystem_pthread.dylib 0x00007fff8f60aaab _pthread_body + 180

15  libsystem_pthread.dylib 0x00007fff8f60a9f7 _pthread_body + 0

16  libsystem_pthread.dylib 0x00007fff8f60a1fd thread_start + 13

 

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0): is an invalid pointer

Connection ID (thread ID): 2133

Status: NOT_KILLED

 

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

information that should help you find out what is causing the crash.

在另外一個時間,又再次產生類似的錯誤訊息!


2016-12-19 01:00:06 UTC - mysqld got signal 10 ;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.

 

key_buffer_size=8388608

read_buffer_size=131072

max_used_connections=163

max_threads=5000

thread_count=54

connection_count=54

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1991942 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

 

Thread pointer: 0x7f9bebe65000

Attempting backtrace. You can use the following information to find out

where mysqld died. If you see no messages after this, something went

terribly wrong...

stack_bottom = 700006bdde60 thread_stack 0x40000

0   mysqld 0x00000001039e09fd my_print_stacktrace + 61

1   mysqld 0x00000001037cec78 handle_fatal_signal + 696

2   libsystem_platform.dylib 0x00007fff8f600bba _sigtramp + 26

3   ??? 0x0000000000000000 0x0 + 0

4   mysqld 0x00000001037955d0 _ZL15net_read_packetP6st_netPm + 128

5   mysqld 0x00000001037952d3 my_net_read + 99

6   mysqld 0x00000001037fca03 _ZL24server_mpvio_read_packetP13st_plugin_vioPPh + 179

7   mysqld 0x00000001037fdd19 _ZL28native_password_authenticateP13st_plugin_vioP25st_mysql_server_auth_info + 89

8   mysqld 0x00000001037faf88 _ZL12do_auth_onceP3THDP19st_mysql_lex_stringP9MPVIO_EXT + 184

9   mysqld 0x00000001037f9a1f _Z16acl_authenticateP3THDj + 575

10  mysqld 0x000000010382b1e0 _Z16login_connectionP3THD + 560

11  mysqld 0x000000010382bc36 _Z24do_handle_one_connectionP3THD + 294

12  mysqld 0x000000010382bafb handle_one_connection + 59

13  mysqld 0x0000000103bca877 pfs_spawn_thread + 311

14  libsystem_pthread.dylib 0x00007fff8f60aaab _pthread_body + 180

15  libsystem_pthread.dylib 0x00007fff8f60a9f7 _pthread_body + 0

16  libsystem_pthread.dylib 0x00007fff8f60a1fd thread_start + 13

 

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0): is an invalid pointer

Connection ID (thread ID): 5463

Status: NOT_KILLED

 

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

information that should help you find out what is causing the crash.

在一堆沒任何意義(大概只有MySQL的專家、程式設計者自己看得懂)的訊息裡面,唯一有意義的只有這句話:

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1991942 K  bytes of memory

簡單的說,MySQL伺服器需要「1991942 K  bytes of memory」的記憶體。在現代許多的電腦上都有安裝至少8GB,許多都有16GB,某些高性能電腦甚至還有32GB或64GB的現在,這個看起來很大的數字,到底是多少?

讓我來「除以1024」換算一下。

1991942 KB / 1024 = 1945.255859375 MB

大約是需要「1945MB」的記憶體。再除以1024的話:

1945M / 1024 = 1.899663925170898 GB

換句話說MySQL自己認為需要將近1.9GB的記憶體!那麼,insoler使用的Mac mini沒有這麼多的記憶體?絕對有!我使用的2台Mac mini Server 2.3GHz 四核心 Intel Core i7 處理器,都有安裝最高的「16GB」記憶體!

而且負責MySQL伺服器的Mac mini Server上面除了MySQL以外,並沒有安裝額外的「常駐伺服器程式」!換句話說扣除macOS 10.12.2系統本身需要的3GB〜6GB記憶體,絕對還有剩下「10GB」以上的真實記憶體(虛擬記憶體還不算)可以使用!

明明就還有「10GB」以上的真實記憶體,怎麼會竟然為了「2GB」就造成「MySQL當機」?hit

當然,在「MySQL當機」的那幾個小時裡面,自動定時備份也沒有任何結果,因為無法連接MySQL資料庫,當然也就無法備份。

既然MySQL一開始就說明「This could be because you hit a bug. 」這可能是「你」觸發了一個Bug... 事實上我什麼也沒有做!深夜的那段時間,大家都在「睡眠」當中。我當然也無法解決這個問題,只能等MySQL的新版本來解決。但是按照慣例... MySQL的新版本幾乎不會解決什麼問題!cant

另外,MySQL Workbench 6.3.8最新版本與最新的macOS 10.12版本不合,不斷當機!導致很多「macOS Server伺服器管理人員」的抱怨!例如:

MySQL Workbench: Frequent Crashes in Mac OS X?

MySQL Compatibility with MacOS Sierra

Here's what I'm trying to use:

MySQL Community Server 5.7.15

MySQL Workbench 6.3.7

macOS Sierra 10.12 (16A323)

MySQL Workbench hangs on macOS 10.12.1 (Sierra)

雖然有人說「It solved for me too on MySQL Workbench 6.2.4 – many thanks!」我剛好也有備份這個版本,但情況與MySQL Workbench 6.3.8完全一樣慘!只要一登入MySQL,點選「Status」狀態,就會導致MySQL Workbench死當,沒有回應,只能強制結束!hit

簡單的說,在macOS 10.12.2最新版本上,沒有一個版本的MySQL Workbench可以正常使用!cant

我知道有許多「伺服器管理人員」或是「站長」會改用phpMyAdmin來取代MySQL Workbench。我當然也不例外,我也有使用phpMyAdmin。不過phpMyAdmin無法完全取代MySQL Workbench!有某些功能就是只能透過MySQL Workbench才行。至少MySQL Server的「Server Status」執行狀態,就無法完整顯示在phpMyAdmin裡面,只能看到一部分。

更重要的是資料庫的「Backup備份」與「Restore回復」這兩個動作也無法在phpMyAdmin完成。特別是當資料庫很大的時候,備份、回復時間超過30秒(或是60秒,要依照php.ini來決定)以上,就會導致PHP終止程式,最後只會看到網頁Timeout的錯誤訊息!當然,回復的資料庫不完全,也根本無法使用!

這是我早就知道的事情,所以我不會在phpMyAdmin做負荷太重的工作,只會用來執行簡單的SQL指令。

既然最新的MySQL Workbench 6.3.8不能用在新的macOS 10.12.2,也不能依靠phpMyAdmin。我剩下的唯一選擇就是在2005年發表的工具,非常老舊的「MySQL Tools 1.2.12」來匯出、匯入備份的資料庫。很可笑的,這個老舊的MySQL Utilities竟然比新的MySQL Workbench還要更好用!可以在macOS 10.12.2版本上正常執行!

雖然2005年的「MySQL Tools 1.2.12」早就已經無法下載,超過10年以上的程式,也早就沒有新的版本,也沒有人維護。後來全部都改成MySQL Workbench。但想不到竟然在最新的macOS 10.12.2上面,新的MySQL Workbench 6.3.8竟然無法使用!只要一點選「Server Status」就死當!反而是11年前的「MySQL Tools 1.2.12」居然還可以在最新的macOS 10.12.2正常使用!

MySQL GUI Tools for 5.0 Release 12 available for download

MySQL Administrator 1.2.12 GA 

MySQL Query Browser 1.2.12 GA 

MySQL Migration Toolkit 1.1.12 GA 

這種「老舊工具」還比「最新工具」還要好用、實在的這種情況,我認為MySQL有這麼多非常荒謬、可笑的問題,都是「Oracle」根本不重視Mac OS X的使用者!他們只在意Oracle自己與Linux的使用者而已。

蘇言霖 2016/12/20 2 1663
Comments
Order by: 
Per page:
 
  •  ayaka: 
     

    其實可以嘗試一下Mariadb,不過我確實認為Linux以外的支援只是順便進行而已,因為用戶太小眾了。

     
     2016/12/251 replies1 replies 
    0 points
     
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
2016/12/20 (2655 days ago)
Actions