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

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

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,重新啟動服務後即可。


References:

Leave a comment

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

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

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


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

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

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


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

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


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

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


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

Leave a comment

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

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

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

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

Leave a comment

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

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

# 將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')

# 於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)

 

Leave a comment

[MySQL] root密碼重設

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

 

停止服務

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

 

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

# sudo mysqld_safe --skip-grant-tables

 

這時候登入不需要密碼

# mysql -u root

 

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

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

 

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

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

 

Leave a comment