52AV手機A片王|52AV.ONE

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
! [8 L+ v) D8 _- X/ C7 y6 D, lphp連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
2 h  A7 p# d' T: j6 [. t
5 T  t: B& V+ f) @5 U, Zmysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
% ^- P+ s; W1 B. amysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
, B# v! p7 ?. ?+ Z% g& t' y! l3 Y. Y' p原因:
9 v, x- o) K( G9 j5 d. F$ q在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:& A) C3 J8 A/ B8 B+ W! ~0 D2 M
vi /etc/my.cnf  加入下列:
% X* i. a+ @+ q4 Y: f[mysqld]
/ V( X2 v  t# A7 Y+ L' o7 G/ u, I2 f* Q# f1 q: J5 A' a" i7 ]. Q
character-set-server=utf8& Y; G" V1 p& M- x) O
default_authentication_plugin=mysql_native_password9 R0 h$ d, K0 F% I( y8 c
validate_password.policy=LOW# t7 I# R9 }, q- i8 Y% e$ f
[mysql]: P  A' G6 K7 i
default-character-set=utf8
2 s- m6 P; @" f5 y- d. q
6 \& v1 ^, `6 b[client]" X  L( p; e  O  S  ]& Z$ \; g
default-character-set=utf8
; \9 o/ A8 |( h
+ ?/ {$ L8 D0 U/ S7 b* H; [然後重啟mysqld+ v" ^0 ]: [3 E/ a! R1 B( R
service mysqld restart/ L* z6 `: a0 v# j, @

) u/ H; B( P; P* R. }" @# k重啟後可能須做下列動作:7 V9 |1 U* A6 B- \: y6 L1 V
mysql -u roor -p  
  p: ]9 a! j8 {) J$ g% ]0 f1 G9 eSHOW VARIABLES LIKE 'validate_password%';
. I% f$ {5 M+ p+ }SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!6 V) l0 O7 w2 z

2 H5 l7 c$ n& w% W. s6 u8 i& V註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~9 G# I% `, ]! t* m* n
  1. <?) g9 W8 N5 D/ A2 H! @
  2. $hostname = "x.x.x.x";0 H8 E! O, W& E! m
  3. $database = "db_name";
    * s" ]8 p7 o/ K, U" d. g
  4. $username = "user_name";
    - Y' |  Z/ B9 P3 u; C
  5. $password = "pwd";5 m5 M& M! Z( \% C) x8 H8 z! E0 {5 [' k

  6. 6 G( Z; I" V# H, {
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);" b1 v' o, L! x4 W
  8. mysqli_query($conn ,"SET NAMES utf8");' P2 t- }1 P4 F: R; c

  9. 2 X6 O7 a; f5 b7 e2 v% x+ e
  10. $sql = "select count(*) as total from " .  "table_name";
    9 k. V# x) w. D$ S) }
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    ; j! q3 C2 u% c; H' Q8 z
  12. echo $rec['total'];  C' x  X- `( x4 m
  13. mysqli_close($conn);9 C0 n% X& ~8 S8 k
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]0 d& z- R$ H0 X  @& W
( `) N6 b; F8 H  q/ B$ i

6 N5 K$ h3 Y6 q7 p2 \' G6 U當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) e3 J( {& z% {7 O) r
  2. Stack trace:
    , X* Q  S- U. t) T
  3. #0 {main}9 }2 |1 X3 {# P6 w1 n" B) g
  4.   thrown in /test/connectmysql8.php on line 9
    " K. x5 H7 s" V) P$ b

  5. 3 N. h2 A5 U# A. E
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
    ' C* ]0 R0 \# k- \; I! f# x
  7. Stack trace:
    / b0 ]2 J" T# K1 s8 k! h8 ]4 R
  8. #0 {main}
    . D" p1 b8 R6 l8 i7 n
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
! Y4 X" Z" |( N8 n5 v9 Y+ C7 N2 \% Z+ k

9 A" v, s/ C" A# ]. o+ I[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.* V$ f% P; ]) V
說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動
7 i( c0 W3 u1 G( O" I! ]  r! ]% H) t( ~& A
(2)
0 v" M- v+ W; R(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
4 F6 A, F8 V" b7 L+ j3 a3 P1 }+ sSELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0
! a* ]5 v6 O' `
! B8 C" F8 l4 o; X# J: O; b5 y(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 13 c/ }- t) t* s% e
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
7 }  u, C- v8 D
' c7 a' I6 J) x1 Y' x因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。! H5 B: M9 m) @. |

# U- @2 i) T) I3 C第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
/ A" A# n  ^8 Q2 B" A將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。! w% n8 m+ j0 s' ?+ y

9 ^4 }9 D/ a- p+ e2 a第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，0 j! j* ]% y2 E3 g1 v
將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。* G; q' F9 d; I# i- u( a
6 P* @7 L; w$ J+ s0 W! s+ m
如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。
; Z/ P5 S! e2 d! k/ |; Y. U0 @
4 d0 ^/ v+ J0 [2 e9 `. V為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。
) F( z. e5 @8 `; o! v& _: T以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤5 O' q' W$ w2 _8 P

+ E" H; {* z3 D5 ~9 \. H7 X' `. s' z' ?: A* Y5 w

: X0 \1 S( C) y- K% S( E( l" [# V9 }' x' s% M8 y




歡迎光臨 52AV手機A片王|52AV.ONE (https://www.52av.one/) Powered by Discuz! X3.2