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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)$ p- \2 G. K: u, g
php連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)
; P8 K% E7 [: {# e5 Q7 \
" X2 |, _0 l  H) @mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers, E, ]: |4 }7 G1 {: {7 I
mysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers( U6 N8 n* i- i
原因:6 u# B9 F  |$ B, H
在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
. c4 c' w1 g3 Q6 ?+ cvi /etc/my.cnf  加入下列:
5 n$ o! S* }) v[mysqld]
! c7 y- J! y( h, T  u$ P3 u: ?9 `2 W: k; W  D5 b
character-set-server=utf8
) p+ a7 Z+ n7 O: Wdefault_authentication_plugin=mysql_native_password
: ]3 R$ U! {! t. c' Jvalidate_password.policy=LOW4 Q! a7 y5 f; r9 g! E
[mysql]9 b; s4 d7 k5 t/ C) t$ A; ]3 R+ Q7 z
default-character-set=utf8
( T% N6 e' }5 p, z8 g' T" _" Y* S3 C
0 V4 I4 ^' @  k5 o( S9 g[client]8 g7 o6 ^  p% ?. {/ |
default-character-set=utf8) Q$ j5 I8 C4 N8 k& E
; g( n, Y* P% {1 ?; A! [5 n
然後重啟mysqld
9 F( n3 L7 {. z; Z9 N# ~service mysqld restart7 Z" c8 l3 c2 f" E" U% [3 }. l

, \+ k5 U; U- s  V$ A重啟後可能須做下列動作:
+ M9 X- l2 o- Xmysql -u roor -p  
& C7 ?  O* l; R. N" rSHOW VARIABLES LIKE 'validate_password%';
0 {( g6 m# I, S; C1 @" O5 hSET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!( w$ T0 n2 s# x+ |

1 U* i( e0 O3 h6 F1 m註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~  H* ^( ^" G% ~# N
  1. <?$ l5 e! u4 U' w7 w* A
  2. $hostname = "x.x.x.x";
    % g" A1 k& \- T( X- g( ~2 Q9 M: K
  3. $database = "db_name";1 E. q/ A& }: X' @4 V0 [" R% x
  4. $username = "user_name";
    " r5 I# _& u& @! r/ E  m9 `
  5. $password = "pwd";
    ( ~/ e6 {# C1 F# U

  6. " k- I" Y6 `" d# x4 b! U
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);% ^& d: y; e( }, r# B
  8. mysqli_query($conn ,"SET NAMES utf8");
    7 F$ {: T8 T. ]& t6 F
  9. ( y2 r# m7 z; [: t  r3 \
  10. $sql = "select count(*) as total from " .  "table_name";
    ! D& p/ I; \% U- o
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));& `# b+ I9 v- L
  12. echo $rec['total'];
    & q& ?+ c" e1 ]3 Y. b: {2 y7 s- l
  13. mysqli_close($conn);4 h6 `  m- w/ r/ G  H
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]
% ]" m1 s, {% [8 s. [! l5 Q2 F# u$ l7 T

: U, n- L( x6 V6 M: k: i) |% i, 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:9- L6 v' U# l' Y  d
  2. Stack trace:- ^, O* ]: j' f- Q
  3. #0 {main}
    . V4 \7 l4 O* \
  4.   thrown in /test/connectmysql8.php on line 9
    $ [1 X. j( c( f* \' A

  5. : v# B, C! I9 y* W
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9+ V* t3 A5 u  n' N7 G$ r
  7. Stack trace:
    6 S8 e0 U1 U& g3 ~7 j' |4 s8 e
  8. #0 {main}
    8 W4 c0 T9 o3 T" u3 \8 h' v
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可8 [7 P/ F# |! t' [, k+ T1 b
0 O1 u8 a0 `5 s# H
/ A( J- I! s. `/ a
[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.
5 S$ R- C% W7 S  \( a1 U" M7 @( f說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動" c$ ?8 g5 d6 S0 d* ^% x
% |& x8 @, \3 S; `6 n; b
(2)
! y$ E- [' J7 A/ F# t% ~& @1 M; r4 v5 D(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, h1 K+ @7 f0 D% T2 OSELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0+ b* N2 [' E" Q. R

8 Q8 @$ G" E* k! P3 {  G(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 h) n+ ]8 M+ x  ^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, o6 Q& @# v0 D! w( }+ ~

) H) Z, H/ R$ A因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
* X; H9 P( z5 H. w1 |- v4 K2 [
4 I2 f( n2 }# Y$ E第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
) [& ^+ \3 l6 z& b8 L2 y; ~& A( {將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。7 Q6 r  Y" ]1 ~- q0 M! b2 C6 @
2 h. U; d+ J9 Y
第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，5 _/ ?3 r# T6 D/ q
將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。
2 Q2 y0 f5 l8 U$ w$ [3 p
. N- r3 t$ V. d4 v5 r: c( G: |  X如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。0 z2 `, n# p3 z  W$ k9 I

/ R- z/ Q0 [& y為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。; k# _8 ?$ b8 b4 g$ o8 u
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤
0 b: _" M5 Y1 I, c+ c/ Y+ K9 I, O6 c4 j! a1 A! F3 f, z0 _
3 Y5 k' c" [# |% x

- Z" B. _, N& m. D% a5 W% Q/ s1 |. L" ^




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