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

標題: mysqld 升級到8以上遇到的問題 [打印本頁]

---

作者: coav4u    時間: 2021-3-14 13:06
標題: mysqld 升級到8以上遇到的問題
(1)
php連到mysql出現2行errors : (discuz網頁出現的是連接到mysql錯誤表格,需自行寫一隻連接mysql的php程式來測試)

mysqli_real_connect(): Server sent charset (255) unknown to the client. Please, report to the developers
9 F2 M8 }" ?1 j! R& pmysqli_real_connect(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers
原因:
) R- n- k. @" n在MySQL 8.0.21中，caching_sha2_password是默認的身份驗證插件，而不是以往的mysql_native_password。所以和php不相容。可以降級php，也可以修改MySQL的配置。 吾人決定修改MySQL的配置:
vi /etc/my.cnf  加入下列:
[mysqld]
4 ^$ t; T8 U. y
character-set-server=utf8
. W8 s* n; @9 J6 ^default_authentication_plugin=mysql_native_password
% A! K4 G. f9 }* ^) Svalidate_password.policy=LOW
/ }0 r1 q0 _; f) T7 m[mysql]
default-character-set=utf8
* V& |' e8 |7 n' R; c( }4 l( t* r
- H7 e) w9 q- p1 [3 {1 r( y[client]
default-character-set=utf8
3 v3 ]2 ~! k. e9 |% z- x  C) ]+ z: @
$ W% W/ N- {4 {6 I5 \  M然後重啟mysqld
service mysqld restart
! t( `- D/ y/ x+ ~
重啟後可能須做下列動作:
. e! s  H3 d$ s  {: H9 Nmysql -u roor -p  
- F7 ]; g% R1 Y; h9 x7 @4 wSHOW VARIABLES LIKE 'validate_password%';
/ H3 [7 t. v8 s( ?3 K# WSET GLOBAL validate_password_policy=LOW;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密碼';搞定!!

  e5 O0 U, h2 A; f- |: m註: mysqld啟動時,在/var/log/mysql/mysqld.log紀錄著:註: 可以先寫支php程式以測試 ~~~
9 `  O6 w  y1 y

1. <?
   ; \; f% z- n$ f% m
2. $hostname = "x.x.x.x";
   3 p% P$ e& ~& d. M3 Z, a  j
3. $database = "db_name";
   
4. $username = "user_name";
   / A! j* k6 v8 e$ A$ g
5. $password = "pwd";
   / ~9 l* g; C7 \# A# e1 ~2 X5 e
6. 
   ! {% O' P# g) }: }
7. $conn = mysqli_connect($hostname, $username, $password,$database) or trigger_error(mysql_error(),E_USER_ERROR);
   
8. mysqli_query($conn ,"SET NAMES utf8");
   + y4 O1 _, I  t, m( S# D  x
9. 
   7 b* t' X! W  ]8 g' [! w
10. $sql = "select count(*) as total from " .  "table_name";
    : V- g8 _+ E" O# L2 j) W1 f
11. $rec = mysqli_fetch_assoc(mysqli_query( $conn,$sql));
    
12. echo $rec['total'];
    3 j% V5 e0 S: q
13. mysqli_close($conn);
    6 L! W3 }$ q' z$ I% Q% Y2 Q
14. ?>

複製代碼

[hide=d100000000000000000000,999999999999]於video1主機下 /test/connectmysql8.php[/hide]


5 P" G6 L2 t: q當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
   
2. Stack trace:
   
3. #0 {main}
   2 g" F- Y! @' R" d
4.   thrown in /test/connectmysql8.php on line 9
   ! J3 B6 |5 _' `% I8 C+ H
5. 
   
6. Fatal error: Uncaught Error: Call to undefined function mysqli_connect() in /test/connectmysql8.php:9
   * D6 q6 C) u" K$ J' {# G. u/ I
7. Stack trace:
   ; \$ N, ^; i# W9 h" U
8. #0 {main}
   $ q. h3 F/ C) @  w
9.   thrown in /test/connectmysql8.php on line 9

複製代碼

這是版本相依的問題,只要 yum install php-mysqlnd  或 yum install php-mysqli 即可

) _$ e% b* m( {8 ]; R8 S1 Y
  m9 W6 H; u7 W/ N[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.
說明: 千萬不能照它建議用"UTF8MB4",否則無法成功啟動

(2)
(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
SELECT COUNT(*) FROM common_usergroup WHERE type='special' and system>0

& z0 L, A0 z( J; s(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
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

因為MySQL 8.0+將system和groups作為了關鍵字，所以不能直接在SQL中將system和groups這兩個詞作為數據庫、數據表、數據表字段中的任意一種使用，如果數據庫名或數據表名或數據表字段名使用了system或groups作為名字，必須使用一對反引號引用起來。寫作`system`或`groups`。
9 M' Z8 P" u3 b' ]1 l) ?& O# _
第一個錯誤需要找到Discuz!目錄中的source\class\table\table_common_usergroup.php，
將文件中所有的system全部改成`system`，保存後重新上傳到服務器的相同目錄即可。
% x- }2 ]/ n3 m0 g; `7 @4 L
: h" `: `* J8 R7 }" o; Q* r7 t第二個錯誤需要找到Discuz!目錄下中的source\class\table\table_forum_announcement.php，
將文件中所有的groups全部改為`groups`，保存後重新上傳到服務器的相同目錄即可。

, P6 o) j$ r0 c如果在MySQL 8+環境下安裝Discuz!，在安裝前需要給install\data\install.sql文件中沒有加引號的所有system逐一添加一對反引號保存重新上傳到服務器的相同目錄，否則也會出現SQL語法錯誤。

$ A. j: S' y& `' y% |為了避免語法錯誤，在寫SQL語句時，一定要為所有的數據庫名、數據表名、數據表字段名全部添加一對反引號，像Discuz!這樣的寫法是很不妥的。
以上參考:   升級到 MySQL 8.0+ 後Discuz! 更新緩存時出現SQL語法錯誤

7 R* ^1 S& e! J$ Y8 t

' g# r/ C  p+ l* R, U5 s

---

歡迎光臨 52AV手機A片王|52AV.ONE (https://www.52av.one/)

Powered by Discuz! X3.2