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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)$ ^. ^! f" D7 X4 \+ T, m; T6 R6 s! ^
php連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
8 H+ t  k, A2 i, I, y; l8 W/ F! [+ }; f# {. r
mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers% P- F$ \' {% h8 r
mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
' e$ e! k  R+ I7 f9 n4 R0 J原因:; Y! Z* x- _7 p! ^- m- [
在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:, ~( L7 d5 v$ s# K8 }" ^
vi /etc/my.cnf  加入下列:2 Q( i! ]* Q6 C8 O) A
[mysqld]
8 r" i1 D/ n, v3 z* y3 x/ t) N
character-set-server=utf84 X, U# f# I+ k
default_authentication_plugin=mysql_native_password
; X8 J. h$ f; b( U) C% pvalidate_password.policy=LOW) u' h+ b: T/ H& G. {3 }
[mysql]
* r0 _3 F$ b' _) w/ Mdefault-character-set=utf8# ?+ O8 g, r* D

. Z8 ^! e( {; O# P3 t5 R1 z+ d[client]" v# j: v9 Y3 z. a- O# E
default-character-set=utf8
( `. h5 S- Y8 t: f9 n, _( e4 L7 G
$ q0 H5 ~2 O" }, c然後重啟mysqld9 P1 `; w. @9 O0 ~
service mysqld restart
6 U6 O0 Y$ c" P' P
% A8 \4 O/ C8 X2 M/ T: \5 A8 I重啟後可能須做下列動作:
! v0 u/ r8 D$ t) t; hmysql -u roor -p  
( ~- D2 {5 N( @SHOW VARIABLES LIKE 'validate_password%';" B' v& n- `( E+ e
SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!
- e& Y: {* D6 z7 }- e. c/ B0 p
( C$ g! l1 y3 C* {" X5 i1 C註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~
9 t2 |- Y' `: G4 {- r0 U  D3 V
  1. <?5 f' I! [+ N, J3 n4 F. m
  2. $hostname = "x.x.x.x";6 ]8 J$ z7 y1 S) w, W  r
  3. $database = "db_name";
    . j$ i9 o/ Q& r) `; n
  4. $username = "user_name";$ y4 |( z3 s0 m" G
  5. $password = "pwd";
    ; k6 [: Z' ~, i7 @0 r0 _
  6.   W) o, X$ |+ V" s$ G6 \
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);) `2 y) g$ E# U& g$ y% @
  8. mysqli_query($conn ,"SET NAMES utf8");
    9 c0 k; Q7 [9 j: O! J" Z% ^
  9. 5 N, `/ {' q/ p. u0 W
  10. $sql = "select count(*) as total from " .  "table_name";: W9 Z) ~: x' S3 e5 b
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));- [# t. Y/ Z5 K$ x
  12. echo $rec['total'];
    - `: x) \/ z6 ^
  13. mysqli_close($conn);6 a5 p5 f/ `+ P5 B+ I( Y+ d7 ~+ b
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]
. D/ {; m; G0 ~2 H- e3 X' v/ L+ c: [% {- m$ M

$ r# [8 y" A2 X8 ~$ X當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* _; W3 Y7 Z! Y6 F  o2 O
  2. Stack trace:
    2 r& t# P/ d: |2 E
  3. #0 {main}, n$ }9 F  Z* V( C9 X
  4.   thrown in /test/connectmysql8.php on line 9
    1 {6 @+ @" S) _+ V! B! p# F, Z  w9 N) [6 H

  5. # D* o5 w& }: s% z7 L3 G
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:98 ]. _6 T4 [& }8 f) f# R1 \  x, ?0 i" F  N; D
  7. Stack trace:
    ) W  s, z0 ^* b( ?0 k. d
  8. #0 {main}
      \0 F) P, [1 M! r2 ?
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
! b0 }! B) `9 F* H+ \; K
/ c: W* [4 X/ m
, k+ _& k9 D: J# t; b5 Z[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.
' \% d, S' e+ E1 }' S說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動& S( \/ j9 ^( U) L

& @+ f$ F& Q9 [+ o(2), R- P; v4 V; {! b" ~
(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- R1 m# h( u+ p# h" A
SELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0! x, w: T( _& T# k' U
) W+ b7 J8 X4 t0 V* h
(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/ a2 B% y" z/ t5 S0 F
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% z& n# h. ]9 r& a* N. T- D
4 e. j7 m" [7 l& l
因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
. R/ P9 b, @5 Q' Y, f6 p% t4 H1 @) ]+ H1 `' Q  z* W, M
第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
! k$ \* t; a& m5 L% H將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。+ C5 P4 X* ?/ A% ~

; d8 U4 _: L) G! ^第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，
& A; G4 ~9 q: n# @) X2 C0 k( n將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。
/ U* w4 N! R/ A+ W5 ]. X
6 \0 j  z: T( A" Q如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。% s4 n$ k3 e7 {- X

, U. ]! f/ Z; B, l' E, z, k/ L為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。
; k7 ]( z1 `, q9 w5 L7 T* z以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤/ [, L  L2 ~, a0 v$ y

( V' I9 f& n2 m( A' r
6 Y! o3 F3 o8 L( _$ S9 d4 h' K# C2 n* K* w/ i

# Q  h/ @( J6 {



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