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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]
作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
3 v" b7 V  ]# u7 V4 G0 \, k$ ]php連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)+ r- i& o' c! a+ t' e0 ]
# q3 j7 ^3 Z. J" |9 H0 W) n6 A9 }' E
mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
, H) y" s1 A, R9 r$ t6 E, b" X# h7 wmysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers6 r$ u/ U2 s$ c" l
原因:% {% E% V- Y  i$ d8 u  s9 H; ^
在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
. n5 v% C4 Q2 D/ ?- N( E, Tvi /etc/my.cnf  加入下列:
7 V9 L* _" a7 a' ?) ?- b, d- O9 |[mysqld]" d* w& b0 _3 b& B! S0 a
5 y3 v# |6 t: C" e( W( a( ~2 i
character-set-server=utf8/ O: c: x8 o3 |
default_authentication_plugin=mysql_native_password, \6 b" |( }# W9 U3 b
validate_password.policy=LOW
, V1 `1 w  k& t+ N* f+ t3 c[mysql]8 m) _# {0 T" X
default-character-set=utf8
% T- A$ W8 j0 o  q4 C1 C1 g+ k
! \6 I& n; `- Q, M[client]
. D3 V9 D$ a. Z/ r1 r4 b+ p/ ~default-character-set=utf8/ L* d. [( e, {/ l0 m6 x- a

7 J6 M7 V3 Q8 ?# c3 T! a然後重啟mysqld, i; n9 A3 [% k2 K: L
service mysqld restart
4 F! ~+ k9 L) s/ {7 M% i0 i" \
% n2 M% d% H. i# O& l3 u重啟後可能須做下列動作:
4 n# @6 z; f) W" {' f, |# \4 _! @mysql -u roor -p  
" I; F* {( i0 l) o, h8 L% [! jSHOW VARIABLES LIKE 'validate_password%';
- l& S, T7 y# r. @SET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!
* k, q; j, N5 y6 `% m* K; G: W" y
9 }: Y! M& S. l* o4 c2 S- b5 l! i0 G註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~8 \8 v7 w2 [, Q1 Z* z
  1. <?6 A8 t5 v' L4 X' ~: B& J
  2. $hostname = "x.x.x.x";
    : y% j7 O+ s5 n6 E  S/ \* D1 {
  3. $database = "db_name";
    ) \2 J( F$ k1 o; ~2 n' \3 l
  4. $username = "user_name";
    4 c3 s& [# m! f+ @
  5. $password = "pwd";! j1 Y/ g! N9 D' S- G
  6. : a6 u& M' B" K8 C; A
  7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);
    8 e" R& A. j9 w) e5 S
  8. mysqli_query($conn ,"SET NAMES utf8");/ L$ _2 R1 B/ W. y1 t; u4 v4 o

  9. " T+ T, V$ \& g0 l  \
  10. $sql = "select count(*) as total from " .  "table_name";
    . D: \- C8 S) q" V3 g
  11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    " B8 ^1 t$ u% n
  12. echo $rec['total'];( _( ]" w; Q! E. L2 y; d- R3 U- q
  13. mysqli_close($conn);2 ~9 N, U; {2 x9 j- G2 ^( p
  14. ?>
複製代碼
[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]
" \9 S# c7 m& M$ U! n( q+ `4 Z( W! S% F/ L

, J% ~0 z& h/ 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:94 E" k) L) l1 C" c" A* O2 G
  2. Stack trace:0 p/ u/ [" o0 Y7 ]1 ^- h. o9 [6 p
  3. #0 {main}* L9 \4 \. d3 ?7 k! a
  4.   thrown in /test/connectmysql8.php on line 9  e% d+ q/ d( Z4 w
  5. ( T: x* w; E5 V
  6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:90 D0 G, j. Y5 `
  7. Stack trace:
    ' o+ `  w! h4 h5 g1 }5 ]! S
  8. #0 {main}. f0 a: ~# z: ]( N( v" T8 ?
  9.   thrown in /test/connectmysql8.php on line 9
複製代碼
這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可
0 i2 C8 p: p8 D: V( O2 @4 ^1 e2 |8 Q

* U$ n" C/ H  |/ d: N3 s[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.
9 E. B+ E9 G0 z' ?& U; |說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動
( B& l' V' @, i9 [: P3 y% ]* u4 Z/ J4 j: o7 O. H. s
(2)5 a$ ^. f! a% F4 s7 f" [  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 'system>0' at line 1
8 H2 x; y/ D5 N. ^. w5 q% ~SELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0' [. n; e* f: e: R1 u

: L/ N+ D' d3 P5 g& {$ 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. Z1 T0 ^* Y: N$ `; J3 H' Y
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
1 P4 z2 C0 h0 C- u- u( b$ T9 r# \6 j
3 h) S: m& L) E3 Y1 t8 h9 T因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
# Y. d# @3 l) g
: j1 o8 a" S' m* _* M4 Z" N- U4 D第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
6 G; v3 L9 ~. i% U" ]7 r將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。
$ }' f) z' Y7 d0 q: D5 V3 v$ l
% I4 N, E* u$ l2 I( ~# l& N第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，( ~( }& |8 _3 g3 ?6 O# J  k8 ]8 Q
將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。% B# ]' ?1 q7 h' i6 C9 {& O7 \" k, d
( U; `0 Q7 C8 N" M3 |: n" l1 r
如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。& u- `5 p6 @- i: O. G9 a; j

- M. Z3 `, T, D' c3 h為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。) \  P4 ~: F) D
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤6 x6 J0 L% T$ G$ I. e/ r
* |  l. I* h$ O/ m" h1 {
+ P5 M5 |( J) h* ^2 V: f8 N
# L: h4 E) C5 c- Z: O
: m  ^) ]6 w; ~" q




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