mysqld 升級到8以上遇到的問題

coav4u

(1)
php連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)

mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
原因:
" p* \8 d7 P7 N在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
vi /etc/my.cnf  加入下列:
4 g+ S* H4 W: x% g; a[mysqld]

8 S+ l% s7 x: V4 Y0 |2 z$ Hcharacter-set-server=utf8
' y) @, s* G' M' S) y! A9 zdefault_authentication_plugin=mysql_native_password
validate_password.policy=LOW
# N" V8 |' j  s+ Z[mysql]
, j9 z7 U( A: Bdefault-character-set=utf8
! o% `% j+ ?( a, \8 [% O
[client]
default-character-set=utf8
, Z& q5 I: N* e( y: P, _1 z
5 o! R: j3 |' e0 V, v, S然後重啟mysqld
service mysqld restart
* Z, {+ \% I+ l+ i& X
重啟後可能須做下列動作:
3 |6 n7 r/ c" ~/ ~+ v5 Amysql -u roor -p  
SHOW VARIABLES LIKE 'validate_password%';
SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!

) L; b, o4 q0 k! }' J註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~

1. <?
   
2. $hostname = "x.x.x.x";
   0 `* C# i; b; `, k4 v, X
3. $database = "db_name";
   1 t& d' l3 ^' y
4. $username = "user_name";
   
5. $password = "pwd";
   ; ^7 G3 D7 d4 u
6. 
   $ E# w: M. s2 _  E+ m) ~3 {, |
7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);
   . `9 {* |+ w% F, l& N$ a
8. mysqli_query($conn ,"SET NAMES utf8");
   % f2 T7 Q! w* b" x' ~; `" H: X
9. 
   
10. $sql = "select count(*) as total from " .  "table_name";
    ) D( D8 ?6 d0 d- \) ^
11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    
12. echo $rec['total'];
    
13. mysqli_close($conn);
    8 C8 r/ S8 ^/ q
14. ?>

複製代碼

遊客，本帖隱藏的內容需要積分高於 999999999999 才可瀏覽，您當前積分為 0

* B# A$ N* d. y2 j* W
) n: W( u; ~# A
1 Q; p/ k6 J7 g! G- C- y當php 7.2.24  connect to mysql server,出現 :

1. PHP Fatal error:  Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
   1 [3 F+ |7 @) Z0 }
2. Stack trace:
   . a" z" P7 L) u2 S3 |1 s& q0 |
3. #0 {main}
   
4.   thrown in /test/connectmysql8.php on line 9
   0 v. ~& r! J" `; r& h, X
5. 
   
6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
   
7. Stack trace:
   
8. #0 {main}
   6 T3 c$ Z% z( T  q. R
9.   thrown in /test/connectmysql8.php on line 9

複製代碼

這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
9 E% r: ?1 `7 t- X; f
/ m3 }" t) _5 ?
$ e0 L0 V0 s3 C3 H: k0 s3 W[Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動
5 j6 R3 j, T: J" w* p
(2)
, x' v5 e5 {2 K" G' A(1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system>0' at line 1
SELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0
8 U: y8 A* s6 A9 K6 ~+ i
(1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups = '' AND starttime<=1532077291 AND (endtime>=1532077291 OR endtime=0) ORD' at line 1
SELECT * FROM forum_announcement WHERE type!=2 AND groups = '' AND starttime<=1532077291 AND (endtime>=1532077291 OR endtime=0) ORDER BY displayorder, starttime DESC, id DESC LIMIT 1
4 y" D4 q3 ^5 R+ ^
因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
' d6 p. U1 i  W* v" Y
第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
, ?, @3 u, g/ Z5 k( c將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。

8 f, S4 [9 W1 p; w5 V7 j/ W第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，
: C6 l/ f% P1 I! n2 M. k8 ^將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。

+ D: n- ^* @. Y7 h0 s- k! J% K如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。

為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤
$ L, W6 y: u: d7 d, T' h# ^
8 J: ^/ t& l" P: O# q: v+ P- Y
0 H/ y( }! c$ d! }6 E

0 f7 k( W& [$ Y8 {( J7 }/ z