養鴨宗師: mysql

顯示具有 mysql 標籤的文章。顯示所有文章

2022-08-18

phpmyadmin 5.2.0 + mariadb 10.88.0.1，檢視使用者權限時遇到"Not enough privilege to view users"(權限不足以檢視使用者)

場景：
phpmyadmin 5.2.0
mariadb 10.88.0.1

問題：
phpmyadmin點選使用者帳號(User accounts)頁籤時，無法顯示使用者，出現"Not enough privilege to view users"(權限不足以檢視使用者)錯誤訊息

解決方式：
執行以下語句，並重新啟動mariadb
use mysql;
INSERT INTO `tables_priv` (`Host`, `Db`, `User`, `Table_name`, `Grantor`, `Timestamp`, `Table_priv`, `Column_priv`) VALUES ('localhost','mysql','mariadb.sys','global_priv','root@localhost','0000-00-00 00:00:00','Select,Delete','');

ref:
https://stackoverflow.com/questions/60387072/not-enough-privilege-to-view-users-in-phpmyadmin

2021-08-24

升級JDK 1.8至最新版本後，MySQL Connector/J (TOMCAT JDBC)無法正常連線MySQL

升級JDK後，發現MYSQL JDBC無法連到資料庫，查了一下log，發現以下錯誤訊息：

javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

看來又是TLS無法自動降級使用的問題，先到mysql下指令看一下目前使用的tls版本：

mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';

+---------------+-----------------------+

| Variable_name | Value |

+---------------+-----------------------+

| tls_version | TLSv1,TLSv1.1 |

+---------------+-----------------------+

1.首先嘗試修改JDBC連線字串，加入

jdbc:mysql://<host>:<port>/<dbname>?enabledTLSProtocols=TLSv1,TLSv1.1(...後略)

然後在tomcat.service的Java啟動選項加入：

‑Djdk.tls.client.protocols="TLSv1,TLSv1.1,TLSv1.2" (但不清楚是不是會影響到tomcat網站的tls 1.3連線)

結果無效。

2.直接去jre修改/jre/lib/security/java.security，把以下統統mark起來

#jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \

# DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \

# include jdk.disabled.namedCurves

好了，JDBC連線字串跟tomcat.service復原回原來也沒問題。

由於試了太多次，還被mysql server block了，用以下指令可以解除：

mysqladmin -h 192.168.1.1 -u root -p flush-hosts

ref:

https://stackoverflow.com/questions/67332909/why-can-java-not-connect-to-mysql-5-7-after-the-latest-jdk-update-and-how-should

https://aws.amazon.com/tw/blogs/opensource/tls-1-0-1-1-changes-in-openjdk-and-amazon-corretto/

https://java.com/en/configure_crypto.html

https://dev.mysql.com/doc/refman/5.7/en/encrypted-connection-protocols-ciphers.html#encrypted-connection-supported-protocols

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html

2021-05-19

apache + php 連線過多卡住

apache + php 連線過多卡住，若是資料庫問題，可考慮使用proxysql，畢竟php沒有sql thread pool，就算是用 pconnect() 設定上也是很麻煩，也且一個連線也只能給同一個process的thread共用，只能說是半套，那不如直接用connect()連完就drop即可。

若不是資料庫而是apache撐不住，可以調整 prefork模組的設定(可用apachectl -V或httpd -V查看是不是prefork模式)，

譬如 apache 2.4的/etc/apache2/mods-available/mpm_prefork.conf：

StartServers 4

MinSpareServers 15

MaxSpareServers 75

ServerLimit 3000

MaxRequestWorkers 3000

MaxConnectionsPerChild 9000

</IfModule>

或apacpe 2.0 的 /etc/httpd/conf/httpd.conf

StartServers 8

MinSpareServers 15

MaxSpareServers 75

ServerLimit 3000

MaxClients 3000

MaxRequestsPerChild 9000

</IfModule>

2021-03-17

phpmyadmin在innodb表格資料超過50000筆時，無法正確抓到筆數並切換頁

從phpmyadmin 4.8.0後，因效能考量，預設50000筆內是用select count取得資料列數，超過50000筆時才用show table status，但常會造成取的筆數不對，因此可以把phpmyadmin的$cfg['MaxExactCount']參數加大:

$cfg['MaxExactCountViews'] = 0;//disable trying to count the number of rows in any view
$cfg['MaxExactCount'] = 0;//disable correcting the InnoDB estimates

或者去點擊table總表的row count的數字去更新也行 (沒試過)

ref:
https://docs.phpmyadmin.net/en/latest/config.html#cfg_MaxExactCount
https://docs.phpmyadmin.net/en/latest/faq.html#faq3-11
https://stackoverflow.com/questions/10811405/phpmyadmin-and-big-tables
https://www.kavoir.com/2012/07/make-phpmyadmin-show-exact-number-of-records-for-innodb-tables.html

2018-04-29

JAVA與MySQL的UTFB8MB4問題

1.升級MySQL 至少 > 5.5.3，並修改/etc/my.cnf，加入以下設定後，重開機
(JDBC預設會抓資料庫的character-set-server來做character判斷)
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

2.修改資料table裡，將需要支援擴充字集的「欄位」改成utfbmb4_unicode_ci

3.升級 tomcat/lib裡的mysql-connector-java，至少 > 5.1.13，建議5.1.47(含)以上

4.tomcat修改context.xml，連線字串中加入
(characterEncoding=UTF8已預設相容UTF8MB4，此設定拿掉會直接讀資料庫的character-set-server，其實也沒影響)
(autoReconnect=true是為了能自動重新讀取資料庫設定，以避免讀到舊暫存設定)
useUnicode=true&autoReconnect=true&characterEncoding=UTF8

5.java正則表示式中，使用Pattern.compile("regex")似乎無法正常判斷unicode範圍20000 ~ 2A6D6 (42711字)及2F800 ~ 2FA1D (542字)的4 bytes字元，因此改用String.matches("regex")：
//是否全為漢字
str.matches("^[\\u4E00-\\u9FBB\\u3400-\\u4DB5\\uF900-\\uFAD9\\x{20000}-\\x{2A6D6}\\x{2F800}-\\x{2FA1D}\$\$]+$")

6.javascript的全漢字正則表示式：
!/^([\u4E00-\u9FBB|\u3400-\u4DB5|\uF900-\uFAD9]|[\uD800-\uDBFF][\uDC00-\uDFFF])+$/.test(STRING)

ref:
https://segmentfault.com/a/1190000000616820
http://seanlook.com/2016/10/23/mysql-utf8mb4/

https://blog.csdn.net/leshami/article/details/42024217
https://www.mdeditor.tw/pl/2FbL/zh-tw

https://mathiasbynens.be/notes/mysql-utf8mb4

ref(unicode編碼轉換):
https://www.jianshu.com/p/35f5f7d07732
https://codepoints.net/U+20000

2017-03-28

找出由大到小第N個項目

找出由大到小第N個項目，蠻有創意的SQL寫法

SELECT * FROM Employee Emp1 
WHERE (N-1) = 
(
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary
)

2017-02-21

MySQL(MariaDB) 設定檢查、調整工具

可根據你提供的訊息，產生MySQL(MariaDB)設定檔：

https://tools.percona.com

(裡面還有query語句檢查工具)

MySQL的perl檢查工具，可找出目前有那些設定或安全性有問題：

https://github.com/major/MySQLTuner-perl

2015-06-04

TABLE PARTITIONS 分區

為了讓LOG TABLE能分區PARTITION，就用了這個指令....
當然也有能自動化新增PARTITION的指令啦，不過我為了保險起見還是用這種手動的：

ALTER TABLE SMS_LOG
PARTITION BY RANGE(yearmonth DIV 100)
SUBPARTITION BY HASH(yearmonth MOD 100)
(
  PARTITION p2014 VALUES LESS THAN (2015) (SUBPARTITION s0a,SUBPARTITION s0b,SUBPARTITION s0c,SUBPARTITION s0d,SUBPARTITION s0e,SUBPARTITION s0f,SUBPARTITION s0g,SUBPARTITION s0h,SUBPARTITION s0i,SUBPARTITION s0j,SUBPARTITION s0k,SUBPARTITION s0l),
  PARTITION p2015 VALUES LESS THAN (2016) (SUBPARTITION s1a,SUBPARTITION s1b,SUBPARTITION s1c,SUBPARTITION s1d,SUBPARTITION s1e,SUBPARTITION s1f,SUBPARTITION s1g,SUBPARTITION s1h,SUBPARTITION s1i,SUBPARTITION s1j,SUBPARTITION s1k,SUBPARTITION s1l),
  PARTITION p2016 VALUES LESS THAN (2017) (SUBPARTITION s2a,SUBPARTITION s2b,SUBPARTITION s2c,SUBPARTITION s2d,SUBPARTITION s2e,SUBPARTITION s2f,SUBPARTITION s2g,SUBPARTITION s2h,SUBPARTITION s2i,SUBPARTITION s2j,SUBPARTITION s2k,SUBPARTITION s2l),
  PARTITION p2017 VALUES LESS THAN (2018) (SUBPARTITION s3a,SUBPARTITION s3b,SUBPARTITION s3c,SUBPARTITION s3d,SUBPARTITION s3e,SUBPARTITION s3f,SUBPARTITION s3g,SUBPARTITION s3h,SUBPARTITION s3i,SUBPARTITION s3j,SUBPARTITION s3k,SUBPARTITION s3l),
  PARTITION p2018 VALUES LESS THAN (2019) (SUBPARTITION s4a,SUBPARTITION s4b,SUBPARTITION s4c,SUBPARTITION s4d,SUBPARTITION s4e,SUBPARTITION s4f,SUBPARTITION s4g,SUBPARTITION s4h,SUBPARTITION s4i,SUBPARTITION s4j,SUBPARTITION s4k,SUBPARTITION s4l),
  PARTITION p2019 VALUES LESS THAN (2020) (SUBPARTITION s5a,SUBPARTITION s5b,SUBPARTITION s5c,SUBPARTITION s5d,SUBPARTITION s5e,SUBPARTITION s5f,SUBPARTITION s5g,SUBPARTITION s5h,SUBPARTITION s5i,SUBPARTITION s5j,SUBPARTITION s5k,SUBPARTITION s5l),
  PARTITION p2020 VALUES LESS THAN (2021) (SUBPARTITION s6a,SUBPARTITION s6b,SUBPARTITION s6c,SUBPARTITION s6d,SUBPARTITION s6e,SUBPARTITION s6f,SUBPARTITION s6g,SUBPARTITION s6h,SUBPARTITION s6i,SUBPARTITION s6j,SUBPARTITION s6k,SUBPARTITION s6l),
  PARTITION p2021 VALUES LESS THAN (2022) (SUBPARTITION s7a,SUBPARTITION s7b,SUBPARTITION s7c,SUBPARTITION s7d,SUBPARTITION s7e,SUBPARTITION s7f,SUBPARTITION s7g,SUBPARTITION s7h,SUBPARTITION s7i,SUBPARTITION s7j,SUBPARTITION s7k,SUBPARTITION s7l),
  PARTITION p2022 VALUES LESS THAN (2023) (SUBPARTITION s8a,SUBPARTITION s8b,SUBPARTITION s8c,SUBPARTITION s8d,SUBPARTITION s8e,SUBPARTITION s8f,SUBPARTITION s8g,SUBPARTITION s8h,SUBPARTITION s8i,SUBPARTITION s8j,SUBPARTITION s8k,SUBPARTITION s8l),
  PARTITION p2023 VALUES LESS THAN (2024) (SUBPARTITION s9a,SUBPARTITION s9b,SUBPARTITION s9c,SUBPARTITION s9d,SUBPARTITION s9e,SUBPARTITION s9f,SUBPARTITION s9g,SUBPARTITION s9h,SUBPARTITION s9i,SUBPARTITION s9j,SUBPARTITION s9k,SUBPARTITION s9l),
  PARTITION p2024 VALUES LESS THAN (2025) (SUBPARTITION s10a,SUBPARTITION s10b,SUBPARTITION s10c,SUBPARTITION s10d,SUBPARTITION s10e,SUBPARTITION s10f,SUBPARTITION s10g,SUBPARTITION s10h,SUBPARTITION s10i,SUBPARTITION s10j,SUBPARTITION s10k,SUBPARTITION s10l),
  PARTITION p2025 VALUES LESS THAN (2026) (SUBPARTITION s11a,SUBPARTITION s11b,SUBPARTITION s11c,SUBPARTITION s11d,SUBPARTITION s11e,SUBPARTITION s11f,SUBPARTITION s11g,SUBPARTITION s11h,SUBPARTITION s11i,SUBPARTITION s11j,SUBPARTITION s11k,SUBPARTITION s11l),
  PARTITION p2026 VALUES LESS THAN (2027) (SUBPARTITION s12a,SUBPARTITION s12b,SUBPARTITION s12c,SUBPARTITION s12d,SUBPARTITION s12e,SUBPARTITION s12f,SUBPARTITION s12g,SUBPARTITION s12h,SUBPARTITION s12i,SUBPARTITION s12j,SUBPARTITION s12k,SUBPARTITION s12l),
  PARTITION p2027 VALUES LESS THAN (2028) (SUBPARTITION s13a,SUBPARTITION s13b,SUBPARTITION s13c,SUBPARTITION s13d,SUBPARTITION s13e,SUBPARTITION s13f,SUBPARTITION s13g,SUBPARTITION s13h,SUBPARTITION s13i,SUBPARTITION s13j,SUBPARTITION s13k,SUBPARTITION s13l),
  PARTITION p2028 VALUES LESS THAN (2029) (SUBPARTITION s14a,SUBPARTITION s14b,SUBPARTITION s14c,SUBPARTITION s14d,SUBPARTITION s14e,SUBPARTITION s14f,SUBPARTITION s14g,SUBPARTITION s14h,SUBPARTITION s14i,SUBPARTITION s14j,SUBPARTITION s14k,SUBPARTITION s14l),
  PARTITION p2029 VALUES LESS THAN (2030) (SUBPARTITION s15a,SUBPARTITION s15b,SUBPARTITION s15c,SUBPARTITION s15d,SUBPARTITION s15e,SUBPARTITION s15f,SUBPARTITION s15g,SUBPARTITION s15h,SUBPARTITION s15i,SUBPARTITION s15j,SUBPARTITION s15k,SUBPARTITION s15l),
  PARTITION p2030 VALUES LESS THAN (2031) (SUBPARTITION s16a,SUBPARTITION s16b,SUBPARTITION s16c,SUBPARTITION s16d,SUBPARTITION s16e,SUBPARTITION s16f,SUBPARTITION s16g,SUBPARTITION s16h,SUBPARTITION s16i,SUBPARTITION s16j,SUBPARTITION s16k,SUBPARTITION s16l),
  PARTITION p_MAXVALUE VALUES LESS THAN (MAXVALUE) (SUBPARTITION s17a,SUBPARTITION s17b,SUBPARTITION s17c,SUBPARTITION s17d,SUBPARTITION s17e,SUBPARTITION s17f,SUBPARTITION s17g,SUBPARTITION s17h,SUBPARTITION s17i,SUBPARTITION s17j,SUBPARTITION s17k,SUBPARTITION s17l)

);

ref:
http://article.denniswave.com/6342
http://www.codedata.com.tw/database/mysql-performance-tuning
https://mariadb.com/kb/en/mariadb/create-table/#partitions
https://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html

dynamic partition:
http://stackoverflow.com/questions/13839177/dynamic-mysql-partitioning-based-on-unixtime
http://code.openark.org/blog/mysql/your-magical-range-partitioning-maintenance-query
http://datacharmer.blogspot.co.il/2008/12/partition-helper-improving-usability.html

2015-02-03

SQL 參數化查詢

MySQL
MySQL 的參數格式是以 "?" 字元加上參數名稱而成。
UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

PostgreSQL/SQLite
PostgreSQL 和 SQLite 的參數格式是以「:」加上參數名而成。當然，也支援類似 Access 的匿名參數。
UPDATE "myTable" SET "c1" = :c1, "c2" = :c2, "c3" = :c3 WHERE "c4" = :c4

參數化查詢是防止SQL Injection的另一種方法，但對我目前的case來說沒必要，
mysql_real_escape_string 就能搞定的東西我認為沒必要再複雜化，
或用Active Record就已經處理好這些問題了，
只是怕以後會用到，先學起來。

reference:
http://zh.wikipedia.org/wiki/%E5%8F%83%E6%95%B8%E5%8C%96%E6%9F%A5%E8%A9%A2

訂閱：文章 (Atom)