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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
  S$ X6 W, A! p3 R2 sphp連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
* W- }9 h1 b5 S3 c8 I8 ~) _$ ], U+ i
mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
) |3 q" {$ ~! @0 \; l/ I3 U6 mmysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers- k/ e9 p) ]1 e7 T$ m' q* M8 r5 A
原因:
6 W+ X3 Y; v9 q- v4 j在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
$ p; b( Z" f# yvi /etc/my.cnf  加入下列:
& E% U- u4 M; e" M8 L& }1 e* L[mysqld]
5 p6 `. ^$ m8 L1 ^. {  d- h0 R  N* V, I& d* y
character-set-server=utf8
& ]% d% s, z& B+ r* ~% Wdefault_authentication_plugin=mysql_native_password' R  W. t6 l0 ~4 U3 f
validate_password.policy=LOW: X* G& l( ~7 N
[mysql]( {; X+ v0 m4 U/ A
default-character-set=utf8
: @7 H- z( |' f! N: Y) M
7 m" r4 R- `" B! b9 P* l[client]
5 w3 |7 E3 M6 }% ?2 o& q$ _default-character-set=utf8' C( ?5 R2 Z7 r

5 y2 `+ y8 _, B7 X) q( g. @然後重啟mysqld6 j, Z5 H" z2 j' X$ v' A8 n) X+ A
service mysqld restart
: q* q* C1 q  G3 X3 r$ o' l$ w9 {7 h0 z4 D$ g
重啟後可能須做下列動作:0 }( K! D! D% e, A( g
mysql -u roor -p  9 F: D, i2 g, G9 {
SHOW VARIABLES LIKE 'validate_password%';
% U! v4 j) Y# s9 ?3 J+ YSET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!: k8 Z# K9 W' m( c$ @1 l7 ]

) r1 v2 q" v( z8 h% B註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~- J0 }9 Z" o* }4 p  K7 y
  1. <?" c& Q# z% g" T! U2 d* V6 ?9 T
  2. $hostname = "x.x.x.x";/ r# j6 ?6 L; b' S: `+ ?
  3. $database = "db_name";3 q/ |' w2 Y2 Q6 T
  4. $username = "user_name";
    * D" i9 W; K6 e  D. B' E
  5. $password = "pwd";* d0 x; a3 C# i1 Q4 Q) W* Z

  6. $ P; N8 [) z& G! g5 X9 p9 k
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);. ^( ~% C. |9 C5 E) ~
  8. mysqli_query($conn ,"SET NAMES utf8");5 m- p; ~6 Y0 W0 F

  9. 8 f' R5 B0 o1 e+ [
  10. $sql = "select count(*) as total from " .  "table_name";" ~  z7 a. ^- m
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    ) P% w( F; _4 _+ \
  12. echo $rec['total'];5 f+ m+ [+ u: A& }: h5 L+ M
  13. mysqli_close($conn);
    $ s1 l; ]1 G) [9 E9 p7 V
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]& ]" \/ {6 h# D$ G7 b& Z! A9 T

1 Q; {; F' J: q2 f- R
1 N- `$ p$ v2 ]* t9 U- z- Q6 w  S7 ]/ T當php 7.2.24  connect to mysql server,出現 :
  1. PHP Fatal error:  Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:99 g5 M: M" U9 X! w6 ]" Q+ d
  2. Stack trace:4 p/ c" `' c) {3 ?+ k
  3. #0 {main}
    $ y0 }0 r. u. J8 q+ O
  4.   thrown in /test/connectmysql8.php on line 9- e, x2 G& ?3 w4 Y: D0 ]" D
  5. 2 T# D$ O6 E# V) v; e
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
    ; F' H8 U9 c+ X( n) _# Y
  7. Stack trace:2 `- m' ]4 K: {/ Y( {! r- s
  8. #0 {main}& y. ]6 ?$ R. b0 [: w% ^. E, u5 O
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可, B, Z7 X0 F5 K2 {! I& L% r4 O4 a
7 G+ E: n# q8 m6 A, l" P* r

$ k( U1 m# a, |7 e[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.
9 Z. R4 t1 Q3 t# S說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動1 v0 |( H( w" t* {0 f

# a7 }/ t3 a2 [+ p1 F9 {(2)
3 x( C4 i! v$ ^& x$ P, c$ ?' o(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
, _0 A2 w1 o' v0 C+ hSELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0
4 s" Q" v* o* W  N( }! B0 {$ c4 }8 ~1 e+ J! w, I0 S
(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
, Z6 F2 L9 v/ ISELECT * 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
# `" H0 J3 ]( k* R- P/ v
/ }- X- q9 m7 n) h因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
' q# K  d9 l4 E% P9 T- ?: |$ t7 M. F$ J8 \! B, F
第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，8 q$ a4 A( h$ l6 e( @* ]9 Z' D
將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。& }" K0 s/ H6 X1 ]& b' Y
* `; w% p- H3 t7 K
第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，
! M5 q( P9 J9 f( c: @! C6 X) t/ Z& \將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。
6 s; `2 ]) L; i* g9 }2 K  W0 T6 w" z6 S. ]% Z4 W) z
如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。$ |' Y0 S/ ?5 M  i

. T- q  `- w+ R$ X為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。7 p+ e# n$ K, a
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤1 }5 W$ O: @4 t# V3 Z8 F3 Z7 ]

, q' S7 F! |; G1 k6 Y7 `, W! {
) v7 h4 ^* j* B9 g. H4 ?
# F* g1 y! k. h1 ]( u& k  p5 \
/ z# E, v- R8 i/ U7 I* S. x



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