Browsed by
標籤：database

[MariaDB/MySQL] ERROR 1118 (42000): Row size too large (> 8126)

[MariaDB/MySQL] ERROR 1118 (42000): Row size too large (> 8126)

Comments 0 Comment

今天在匯入資料庫時出現錯誤，導致匯入中斷

ERROR 1118 (42000) at line 437: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

 

造成的主因是設定檔中啟用了  innodb_strict_mode

在 MariaDB 10.2.2，或是 MySQL 5.7.7 以上的版本預設會開啟

這裡使用的伺服器是 MariaDB 10.2.8

所以在匯入的 SQL 檔單中有單行超過 8126 個字元的話

在 innodb_strict_mode 開啟的情況下會觸發錯誤，並中斷執行

 

一般情況下建議是開啟的

因為相較之下 run-time error 會更頭痛

不過這裡只是單純要匯入資料

所以暫時先關閉後再開啟

編輯 /etc/mysql/my.cnf，並將 innodb_strict_mode 設為0

重新啟動服務後即可

 

Reference:

[MariaDB/MySQL] mysqldump include/exclude (ignore) specific table

[MariaDB/MySQL] mysqldump include/exclude (ignore) specific table

Comments 0 Comment

備份資料庫其中一種方式是使用 mysqldump 指令，例如我要備份一個名為db1的資料庫：

1
2
3
4
mysqldump -u admin -p db1
# 參數說明
# -u: 使用者名稱
# -p: 密碼，指令enter後會要求輸入密碼，也可以直接將密碼連接在後面，如密碼為123456的話，參數改成 '-p123456' 後指令會直接執行，不會再次要求輸入密碼

 

若只要備份特定的資料表時，有三種方式

  1. 排除/忽略特定資料表 (Exclude / Ignore)
    使用參數 --ignore-table 去排除，需完整名稱 (即 database.table)；多個 table 無法使用逗點 (comma) 串接，需個別指定。適用於排除少數資料表。

    1
    mysqldump -u root -p123456 myDB --ignore-table=myDB.logs --ignore-table=myDB.users > dump.sql

     

  2. 指定資料表名稱 (Include)
    在資料庫名稱後面指定資料表名稱，可指定多個資料表；無法使用 * 去指定特殊的 prefix / suffix；適用於匯出少數資料表。

    1
    mysqldump -u root -p123456 myDB table_1 table_2 table_3 > dump.sql

     

  3. 包含特定資料表前綴/後綴或特定 pattern 的資料表 (Include prefix / suffix / pattern)
    利用 sub command 在內層執行 SQL 先取得指定資料表名稱後，再執行外層的 mysqldump ，適用於資料表數量眾多，需匯出指定資料表也較多的資料庫。

    1
    mysqldump -u root -p123456 myDB  $(mysql -u root -p123456 myDB -Bse "SHOW TABLES LIKE 'my_table_%'") > dump.sql

    一般情況下用 LIKE 語法就夠了，若情況特殊，用 REGEXP 語法去撈也是可行的，需注意 WHERE 的欄位是 Tables_in_資料庫名稱 。

    1
    mysqldump -u root -p123456 myDB  $(mysql -u root -p123456 myDB -Bse "SHOW TABLES WHERE `Tables_in_myDB` REGEXP 'table_[0-9]+_[A-Za-z]+'") > dump.sql

 

Reference: http://stackoverflow.com/a/26514699/7521686

[MySQL] 在同一張資料表進行複製資料的操作

[MySQL] 在同一張資料表進行複製資料的操作

Comments 0 Comment

最近剛好碰到需要在同一個table內copy資料的情況
由於先dump出來再操作實在是覺得有點麻煩又有點蠢
想到應該可以直接下SQL來完成這件事情
於是稍微研究了一下
基本上只要避開primary column即可完成
例如：有一張資料表叫做table_1，表結構有三個欄位，分別是col_1, col_2, col_3，其中col_1為primary column，然後使用下列SQL語法：

1
INSERT INTO `table_1`(`col_2`, `col_3`) SELECT (`col_2`, `col_3`) FROM `table_1` WHERE `col_2` > 100

下了這行SQL後，會在資料表複製並寫入現在col_2值大於100的資料

[MySQL] 字串取代與連接 (REPLACE、CONCAT)

[MySQL] 字串取代與連接 (REPLACE、CONCAT)

Comments 0 Comment

取代字串：REPLACE(欄位名稱, '欲取代的字串', '取代後的字串')

1
2
3
4
5
6
# 將efg開頭的字串取代
UPDATE `table` SET `column` = REPLACE(`column`, 'efg', 'zzz') WHERE `column` LIKE 'efg%';
將efg結尾的字串取代
UPDATE `table` SET `column` = REPLACE(`column`, 'efg', 'zzz') WHERE `column` LIKE '%efg';
將欄位column_2含有efg字串取代成zzz，不過將取代後的結果覆寫於column_1，不更動column_2原有的內容
UPDATE `table` SET `column_1` = REPLACE(`column_2`, 'efg', 'zzz') WHERE `column_2` LIKE '%efg%';

 

連接字串：CONCAT('字串1', '字串2', ... '字串n')

1
2
3
4
5
6
7
8
9
10
11
12
# 於column值為456的資料行首插入字串123
UPDATE `table` SET `column` = CONCAT('123', `column`) WHERE `column`='456';
(Result: 123456)
# 於column值為456的資料行尾插入字串123
UPDATE `table` SET `column` = CONCAT(`column`, '123') WHERE `column`='456';
(Result: 456123)
# 於column值為456的資料行首插入字串123，行尾插入789
UPDATE `table` SET `column` = CONCAT('123', `column`, '789') WHERE `column`='456';
(Result: 123456789)
於column值為456的資料行首行尾各插入原值，使其值為重覆三次的字串
UPDATE `table` SET `column` = CONCAT(`column`, `column`, `column`) WHERE `column`='456';
(Result: 456456456)

 

[MySQL] root密碼重設

[MySQL] root密碼重設

Comments 0 Comment

如果忘記root密碼可以這樣重設

 

停止服務

1
# sudo /usr/local/etc/rc.d/mysql-server stop

 

以安全模式啟動mysql，並跳過檢查

1
# sudo mysqld_safe --skip-grant-tables

 

這時候登入不需要密碼

1
# mysql -u root

 

接著修改DB記錄，把root密碼設成123456，並重新載入權限

1
2
3
4
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD("123456") WHERE User='root';
mysql> flush privileges;
mysql> exit

 

最後重新啟動MySQL，大功告成

1
# sudo /usr/local/etc/rc.d/mysql-server restart

 

第1頁 / 共2頁12