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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)* I6 F0 l9 a0 u3 ?  T
php連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試). v  I5 l3 Z) W4 J/ A9 X

: B2 P& v5 w/ v  cmysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers( F  c( c; b" r/ R
mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
/ u: J2 z* h3 S/ v原因:9 m$ H6 B( Q# W! z, \. P
在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
, q+ y' K' E) a, L3 ^  L# [vi /etc/my.cnf  加入下列:0 |- R+ Q6 r7 ~) U
[mysqld]
+ Q) l! d  s& a
+ w6 C. J3 ^' Z' G. t2 scharacter-set-server=utf8& q$ ?( X9 M% d8 F# S* K
default_authentication_plugin=mysql_native_password
+ ^( Z) \) ?+ T% f6 X4 nvalidate_password.policy=LOW1 P8 ]$ I6 V- X' o: D0 K3 L  W' a
[mysql]% Z# E, O" {, ]. I  d3 U& M
default-character-set=utf8
8 U& h6 _; s2 r3 O: i* d0 }4 y" R  E& Q- g; j! L; |
[client]
* Q0 ?. G- g3 h+ W1 fdefault-character-set=utf83 E9 m0 R  j# J' r- {  u& }

. n+ x$ ?0 U( ~# k  W0 S3 e# U1 x然後重啟mysqld' O" n  f9 x3 o- [5 l8 M
service mysqld restart6 E& I. J" |4 Y% ^( T) h. Z- A
3 ]  E2 o8 i8 f* q; e5 z
重啟後可能須做下列動作:' X/ d+ R  a1 [! x# \: }, E- f
mysql -u roor -p  9 Z+ \, p. j- ]: [
SHOW VARIABLES LIKE 'validate_password%';* P" G* ]0 |& p2 G2 `
SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!
6 F# M& K% Y3 ^2 B& h8 F$ ?6 ]9 G% a, `6 Q. s  R
註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~
" Y0 m- j- t- j
  1. <?
      N) t+ {8 C. P0 m  b& X
  2. $hostname = "x.x.x.x";
    . f# f+ t* T  W# W+ V# A
  3. $database = "db_name";( R. u$ e- w; p% Y) @
  4. $username = "user_name";$ ~5 u8 s2 V* b& E2 H. u
  5. $password = "pwd";
    ' y9 `! M4 N3 f/ @3 F

  6. . M" T  Z1 F4 b5 ~" N1 \
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);
    9 u) ~) M" ]- C3 S
  8. mysqli_query($conn ,"SET NAMES utf8");; G- D7 E, h* ]9 \% G0 |

  9. $ H+ g+ W3 D, D( ^
  10. $sql = "select count(*) as total from " .  "table_name";
    9 k0 n% U$ a$ Q! W6 }2 `3 _
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    4 a, a  @7 h% A, z; x: K' ]
  12. echo $rec['total'];
    / X% `: V  \4 M( y) A' N& B
  13. mysqli_close($conn);
    3 q; k( B0 E7 \; G% a/ \
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]
/ O- z, I6 L0 P: z6 B1 t* e7 w4 O3 k' c+ P9 O# `
9 r; Q: Y% Q  m& ^! I7 G
當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
    0 q2 w) @" z& P0 x1 x
  2. Stack trace:
    ; y- o5 `- c) O& a2 O- O5 r6 f
  3. #0 {main}! e) B% @" a0 h% ~# p1 S
  4.   thrown in /test/connectmysql8.php on line 99 Y  x) f* I, K) F  S* U! |' K
  5. ( ?  N/ `- H# x' i: O
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
    1 n1 I8 R% G0 K( q
  7. Stack trace:, N7 f! L; \  K0 r
  8. #0 {main}
    1 z& p4 e9 x, L* R' F
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可( R+ ?3 P! x2 k/ d0 T

! h5 b3 M+ n/ A# l, P2 d& S6 S
$ Z) m( m# q. o, b" H9 u- v[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.
! t$ B: }: t, L- T4 K) Q" J說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動/ ~$ R; ~' ~/ n( m* E) z0 N

6 B8 b0 c: K5 J3 V/ i(2)1 M! ]# }5 E& `0 f3 Q! m
(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
" `6 c# Q/ G3 }0 m. x; JSELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0: L: S) }( ?$ y, R) ?/ |
% @5 W5 l/ \& m* w6 G& p& Y) 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 'groups = '' AND starttime<=1532077291 AND (endtime>=1532077291 OR endtime=0) ORD' at line 1- S  m8 y& Z% b" l
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
6 z; @  Q: @& F: V8 W1 d+ w, C' `+ G: `* @8 a6 k0 i5 e$ l
因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。% d% j) ]3 {, S4 }) f' B
/ y" @% o) b; E/ S8 R' [. P
第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，& s+ f* C  e3 i3 q/ S
將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。9 |3 |/ n; k/ T1 \6 u7 _) g7 n0 T3 m
) r% U* u9 [. ?- k2 U
第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，; e4 `+ T  b; X* Z* ~
將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。9 @' \; o2 E& v* e* d
7 |& m6 P( I. Z2 Y; Z7 b5 T: c
如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。9 P# M7 C  z8 p6 z/ D. R0 G9 X
  d; A& H# X. Q4 }
為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。3 U% @% A1 R4 t9 C; ?: }3 o) g& n
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤
% _: k, h; R  H6 Z6 ^0 a* R9 |, \- ^) W
$ m/ f# e( T5 O4 L* u

) C. Z9 F! ]% F+ {0 u! D5 I! J; H" r3 y. b




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