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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
6 k% y6 T8 {/ M% u8 ^% U* k1 W9 Nphp連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
+ S4 ]  z% R0 J. i& }. n; O4 G/ f( {% I8 X# a9 i$ ], i
mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
1 A0 I8 g! R( \2 g: h# qmysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
" n  g6 u/ f! _! Q6 R$ x6 h/ I7 e! Z原因:
' |- ^! w. q# u( i$ B在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:6 a6 w& U2 r7 m: P
vi /etc/my.cnf  加入下列:0 A0 j/ y  S9 G6 u
[mysqld]" R: |$ q. ^# G1 a1 G

" Q$ O5 X- e/ c$ r% Icharacter-set-server=utf8
% z$ v( U5 q% C! y4 R0 d/ udefault_authentication_plugin=mysql_native_password( P$ l/ Z! u& T! Y' @
validate_password.policy=LOW
  h4 v/ v( I! Q9 u) S# _5 E+ `3 x! s[mysql]0 d5 J. Z" `- |1 W! R9 W8 Q
default-character-set=utf8- Q) g; |: Z6 t

3 J/ C' L& W5 y% k[client]5 j5 o/ {2 f" ~
default-character-set=utf8
0 ?$ e! q; @0 D7 L
  ^, ?  ~0 u$ Y1 }然後重啟mysqld
6 n" C$ A! C; \/ t: x2 T& xservice mysqld restart
5 q% k! V4 ]. f9 k5 p: d' X' D% Y& K! R( i- J
重啟後可能須做下列動作:6 `6 g' R4 N( a3 ?7 C) {5 ]4 O4 P% q
mysql -u roor -p  % X" Q* p  U! [$ c9 q4 G
SHOW VARIABLES LIKE 'validate_password%';2 i! V5 e5 I8 E$ d
SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!! M$ Q8 T" B. O; e: \
1 b( I: l! [% m( F
註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~
9 q2 e3 u5 f9 d3 G0 E0 x9 C
  1. <?2 V4 E# u2 m' J
  2. $hostname = "x.x.x.x";4 f$ ?3 M, x; m* b) n9 T
  3. $database = "db_name";6 `' a- K2 I6 y2 S1 `7 q' n4 |0 m
  4. $username = "user_name";
    $ f- V& x" _7 `% |9 p
  5. $password = "pwd";3 s& X' p& d0 A. x
  6. - \: M3 w; B5 i6 G2 \  Q3 ^
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);) |3 e3 ~' u' G, k
  8. mysqli_query($conn ,"SET NAMES utf8");
    * W- J. B) G* P& N

  9. + j( ]" V& Z5 E/ y6 a" P
  10. $sql = "select count(*) as total from " .  "table_name";
    ) i4 m% ~: z5 R) |" t: [7 d1 V7 ^; M: |
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    ; n' t9 T: W% H0 H+ B; p% ~; M* j9 I
  12. echo $rec['total'];
    7 W: ]: T; P$ s+ ?, G1 V
  13. mysqli_close($conn);+ R+ L; p. {2 R2 o
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]4 b- d0 G- X. N* A8 c5 G

# N7 Q) @) B( W9 U/ E+ [3 v( J  O! |% Q# t# U2 o' \
當php 7.2.24  connect to mysql server,出現 :
  1. PHP Fatal error:  Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:97 h( S' M8 J  V, u, H: O) Q
  2. Stack trace:
    9 S$ q; W6 U: A# u
  3. #0 {main}
    9 R0 l  X  ?. O( B2 V
  4.   thrown in /test/connectmysql8.php on line 9. W8 T3 T0 B% U) v1 V& M! e, ~
  5. & D2 t% W. `' a0 L4 u' K) ~
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
    $ a) v3 p% K& u4 o
  7. Stack trace:  _# v6 x4 V( B# V) G: |( A
  8. #0 {main}
    % ~& B& E* J) i, ]- l$ Z, \/ j
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
- d0 G/ h! L+ r+ W  {
2 K( n5 P3 O' u7 v2 V
4 g" {* g5 v9 [: J7 M[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.) ^  I, L0 _+ k
說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動; L% G4 q$ x  a

7 n, s% C# V, \6 m: D(2)! ], \( w3 p" b  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
7 A; X) ?, K  n. P  u) A; Z2 y9 Q! bSELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0! ~; Y% v9 k, Z, J% m

  M$ B$ U( {% L& 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 'groups = '' AND starttime<=1532077291 AND (endtime>=1532077291 OR endtime=0) ORD' at line 1, j. F3 }6 T* t1 \! m* Q
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
# R$ {3 f6 m9 O: f( V: q! R; N: v. Q% t: X: G5 `3 V
因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
. t* t7 D' n, a9 f
6 H: b: X$ x7 N, l" \- D第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
6 Z3 a* b1 V: s$ ^將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。
7 q3 ^7 y9 G8 p- C7 c
5 f6 U, x$ ?' N) Z第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，
' }6 }9 t8 r3 \' q; w& r/ T. t將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。
3 Q" b% Z; y- a8 _( s4 x- l5 s: B( m5 ~( a
如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。
/ j$ H/ `, X4 R2 s% c5 l  V" Y5 t- v* S# K$ N9 n9 d9 O; c
為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。
+ I# H) b9 m3 y' b2 k8 y以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤6 i3 J4 p. X" [6 K% A$ }6 T

  ?) o# |; a; n$ R) X* x1 G/ J' m+ q' ]& L+ F4 |

9 r% K' X2 A& \  z. U4 A' B# e; s& l) _1 ]1 z( p/ H




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