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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
' y/ L% J9 w2 dphp連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
0 {$ @% Z3 q/ b3 h* A+ U# n% b$ f, `; ^: @9 F' U* ~
mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
9 J7 C4 Y) m$ @- r7 m3 S% |mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
3 P& z* N5 Y- d6 g" q; }5 f原因:
, M& X4 ]% N/ i+ ^4 M1 I5 I* P3 t- ?在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
2 x1 w% s5 L5 G. ?7 ^3 ?vi /etc/my.cnf  加入下列:; |7 @, k. H, r* f
[mysqld]
) F' r2 u% Z, ?, R; h2 \0 ~% `
1 S+ T4 E" }- A- t% L4 _) jcharacter-set-server=utf8
- y; ]+ O" m# E6 _/ q/ Rdefault_authentication_plugin=mysql_native_password& }5 C$ A! ]! I9 J) Y% X
validate_password.policy=LOW
& Z3 ~1 F: {7 z5 W[mysql]4 W! T% P  ~: ]0 Q' X. [3 }
default-character-set=utf8
2 Z" ?! |6 x2 k4 Y9 g
# }5 d3 X3 Y2 H2 {[client]
: U0 ~  R- g$ L2 m0 r+ Vdefault-character-set=utf8
0 d+ G* X8 o+ ?4 E8 s" i# q. p) d: ]! _, Z# L. ~
然後重啟mysqld& m/ |0 H, B+ h  Y- e" n  S  Z
service mysqld restart2 S6 ^: o% j- ]- Q; a$ I

& e, f$ e3 e. ~5 E5 S6 K重啟後可能須做下列動作:5 E, H4 S$ V, \/ R5 {% ^, T
mysql -u roor -p  
8 m. u0 a1 {4 ]6 A: j8 j0 cSHOW VARIABLES LIKE 'validate_password%';
! ~, X# w  W* ~7 ]/ w( G& ?SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!
! D) w+ h( q' b+ g: e$ y9 N8 c9 Z. h  k+ e
註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~
5 C: r+ M+ I* `
  1. <?# b; P% t* k) W
  2. $hostname = "x.x.x.x";' K0 T2 Y; }8 I; W' y1 v# T1 i% B2 U
  3. $database = "db_name";4 K1 c( P" i5 y* b/ [1 o$ c, E* e
  4. $username = "user_name";  N; p, [8 b$ n' a, t
  5. $password = "pwd";
    ' `" s" E8 B1 X( {

  6. . c' [! {$ b# }) c- p2 m4 N* w
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);
    , s( T6 T& @& m* [: B2 G4 Y
  8. mysqli_query($conn ,"SET NAMES utf8");
    1 w: r* P7 n" \6 S' L
  9. 1 r$ H1 |6 k$ T% p+ q4 s* M
  10. $sql = "select count(*) as total from " .  "table_name";
    3 m3 O. d9 ?. y$ B% T
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));7 e, m. |% Z4 b9 e5 l( |! ?  [
  12. echo $rec['total'];, E! r! V: b7 j
  13. mysqli_close($conn);4 g% M: I  w/ F6 j' E( ?
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]" C/ F6 W, v' X/ D* C) @, j
! j# O. |5 Y8 d8 E8 [! f

; l' x) [: p/ c. ]( C, I* M當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
    6 O* U4 Y2 t9 o
  2. Stack trace:
    ! y$ M# K7 V) H* A! f# C) y
  3. #0 {main}, ?8 r  P7 _6 F% b0 E. n) `
  4.   thrown in /test/connectmysql8.php on line 91 \/ ~# K. R8 Z5 }1 M
  5. 3 ^( ^4 e7 f4 ~. M
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:99 R2 K0 \  O1 P/ i, h: L* R; X
  7. Stack trace:* V2 U& y0 ~# f7 R2 U# Y
  8. #0 {main}9 P/ N0 V$ J' h+ w" y
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
4 O) Y, @! G7 u: |$ I2 L$ G0 n/ Y
8 b3 ?( d" D* L( `* _) k' G
) `: A: k6 g3 z: f[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.! M% r3 U9 a2 m4 D! A* [4 I' {
說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動
$ ^% D6 h8 s0 @; x& M8 z7 y
$ K7 X: ], i/ T. N(2). `( h$ Y( b) t
(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
+ W& ^- c9 j8 T+ VSELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0# y& }5 w, I, i( g9 e

+ L! }  A& W% S2 k9 F9 N(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
- \9 F" ?2 B$ }" @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 13 Y3 r7 {- Y- j, B8 n9 d/ u

- Z, m6 R9 @. R0 I4 [; s) {. e, _因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。5 m5 \1 Z( @  _0 n/ A% m: r
& S& q: A( H6 u" w9 I; \" R
第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
, e, _5 ]9 u" g  Z' V將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。
4 S' c5 }5 v  |& U- Z) `2 u
( q: p5 Q& X8 F% D; @第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，6 O) R% p( P- o' B2 S
將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。  K5 I0 h# S/ y% b7 R% _

* M6 l, G+ a2 m( h+ V( T2 n如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。
1 f5 T) t6 ?( W& Y7 E: H
3 |: f# E! p4 C. n& B為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。* J& a5 d& p' c2 K* m7 @0 H
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤. {1 c7 ?" [% u. r
4 d' y- Q9 u4 G9 @" U; s; D* k

" v" J3 q3 O2 i. i4 \) U* c+ j* \+ r) y2 e% {4 s! s

7 U& X4 [9 m& V3 C% \' e; _



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