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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
) d/ q# |4 v4 |' Hphp連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
, b+ i2 V( ]8 I/ m# ]/ ?
  S9 T. X6 x1 cmysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers& n: _/ J" J9 h3 N* o- o- N  w; e6 j1 x
mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers1 \/ U( W; l1 C
原因:
- f  {: @4 W* M$ I* t; ~5 T1 J在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:" Q! e3 F. B0 |' L+ P0 Z
vi /etc/my.cnf  加入下列:
& @- P0 H8 L4 z) c[mysqld]  ~: V( e& ?4 G

; |) T9 y* G& Qcharacter-set-server=utf8
4 b' s5 p* P6 [3 p7 R0 {5 D+ Ddefault_authentication_plugin=mysql_native_password
: e0 I8 A0 x6 V( ], wvalidate_password.policy=LOW( f8 ^$ N3 L, F- q4 S2 x
[mysql]( N* U- ]- i7 h3 n4 R
default-character-set=utf8# p3 g7 E& C5 c- B8 J* ?# u* r

0 i1 ?8 @0 W3 |0 ~7 i% _4 W7 Y* q[client]# G6 V9 C2 U& Y* g& z. ]! @
default-character-set=utf8$ I2 q! q: e5 O  b8 K4 p2 E3 X

7 H" J: g0 I6 t- T然後重啟mysqld5 T# N- ]: W3 c6 s+ ?, a/ P% [
service mysqld restart
% ?2 m  D; D7 ^3 @- \+ \) |, j) ~0 [/ }- c
重啟後可能須做下列動作:) Y/ o" p9 ^! z
mysql -u roor -p  - J) K1 J* O/ j) J6 G9 ]3 t
SHOW VARIABLES LIKE 'validate_password%';9 Y% `+ R" @4 A) z2 s4 D0 l
SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!
; E8 j6 M! B1 q+ x
; r( @+ b& T8 j! ~' r註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~
) P, c4 d' I1 n1 A. ^2 _( h8 H
  1. <?
    4 X3 g8 I: e! k; K& l4 r0 J! [
  2. $hostname = "x.x.x.x";
    $ w" r. f0 k' k4 e2 ~; t" l4 Z
  3. $database = "db_name";2 B. B7 n* a0 ]2 }5 ^/ C$ x
  4. $username = "user_name";1 ?% S6 s0 s( U5 f! I: N2 x  L
  5. $password = "pwd";- t# G' X9 M( N9 f+ r) z- p
  6. 7 J6 L, I+ v; J: P2 N
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);; `: M( x, k0 O: C
  8. mysqli_query($conn ,"SET NAMES utf8");7 b9 g7 ]! T5 B" W

  9. * ~# e$ u! k& ~! S4 X" V' o9 ~0 i
  10. $sql = "select count(*) as total from " .  "table_name";
    % k1 a$ n$ X/ k8 s5 t' \
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    $ o, @. o- d2 [4 n" g( S
  12. echo $rec['total'];
    7 W9 B/ y+ e. {* m
  13. mysqli_close($conn);
    7 N' g0 U0 M+ z. O% B
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]
4 n& U2 }* A' x7 K) u% T
' r0 X" \8 c. |8 z6 [3 }  x" G7 n  b. W# e' m+ U9 c0 l- r
當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 D" T; @, s, M/ e8 S. ]3 i
  2. Stack trace:8 {0 j) z" I* F. G3 o
  3. #0 {main}
    : B4 C& Z7 K" F# t
  4.   thrown in /test/connectmysql8.php on line 9
    # q% F- q- N+ g; F% {: g4 ?; T2 J& n
  5. # q4 D* H& f3 X  X5 Y
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9( V" p0 d( ~9 m) q/ C, {; v6 D
  7. Stack trace:2 a0 O! q) b/ k0 r
  8. #0 {main}
    5 @# H( v0 w0 @8 \* n5 z: ]
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
" l; {7 f4 L4 x$ q% Y- i& |% a+ z# w5 w
) T% n1 O; l% E  U3 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.
7 H' E/ S; E: r% `% h6 O" a0 ~說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動
/ h7 j* `" ~0 g1 L& j* r
; t: d" _9 F* t& @1 W/ s: Q(2)! Q: K% H7 G3 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. ~$ \( Y% I4 o
SELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0
  q2 P, T! N: x$ w- H
( b7 |1 S& z1 b3 d. i/ v(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 11 U1 D  `: Y- w# ]. u' `, C" t
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
$ {2 I' H; k5 X
% X9 |; g) v; F- c6 a因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。5 G, b" e' v  R

8 |5 ?# H+ j, L第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，! w  y' b/ n0 d' x% A2 d) \
將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。
+ s& g  N4 J, c8 F0 P" c& |. }
) j6 x+ {! k4 K/ f  a' W( |第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，
) T$ P' A9 z/ \5 ~) x- {將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。
9 Q9 l3 U' [* A1 L
7 t1 \- e% T2 A0 u3 h如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。7 N% T! {1 K5 ?

) m" L( z4 G5 y  S" J  c為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。0 E" S. f+ B9 Q1 r/ [5 @* a! F! w& k
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤
6 l" T8 A) X: [& K7 ^$ T
5 k' C' w4 z. Q3 T
& i* `( G2 O$ b1 V; Z( f; K7 l4 }) ]& m  z/ I

& q( ^  g7 |5 v8 n



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