Troubleshooting Row Size Too Large Errors with InnoDB

エラーの原因

このエラーの主な原因は、InnoDBのページサイズ（通常は16KB）と行フォーマットに関連しています。InnoDBは、1つのデータページに複数の行を格納しようとしますが、1行のサイズがページサイズの半分（デフォルトで約8KB）を超えると、このエラーが発生する可能性があります。

特に、以下のような場合に発生しやすくなります。

• innodb_page_sizeが小さい場合
  デフォルトのinnodb_page_sizeは16KBですが、これを小さく設定している場合、1行あたりの最大サイズも小さくなります。
• 古い行フォーマットを使用している場合
  MariaDBやMySQLの古いバージョンで作成されたテーブルは、デフォルトでCOMPACTやREDUNDANTといった古い行フォーマットを使用している場合があります。これらのフォーマットは、DYNAMICやCOMPRESSEDに比べてオーバーフローページへのデータ格納効率が悪く、行サイズ制限に早く達する傾向があります。
• TEXT/BLOB以外のカラムが多い場合
  TEXTやBLOBカラムは、データの一部がオーバーフローページに格納されるため、行サイズ制限の計算にはプレフィックス部分のみが含まれます。しかし、それ以外の一般的なデータ型（VARCHAR、INTなど）は、行内に直接格納されるため、これらの合計サイズが制限を超えるとエラーになります。
• 多数のVARCHARカラムを使用している場合
  VARCHARカラムは可変長ですが、行の合計サイズが大きくなるとこの制限に抵触しやすくなります。特にUTF-8などのマルチバイト文字セットを使用している場合、1文字あたりのバイト数が増えるため、より少ない文字数で制限に達することがあります（例: utf8mb4は最大4バイト/文字）。

解決策

「Row size too large」エラーを解決するには、いくつかの方法があります。

1. • MariaDB 10.2.2以降、およびMySQL 5.7以降では、デフォルトの行フォーマットはDYNAMICになっています。古いテーブルや、意図的に別のフォーマットを使用している場合は、これを変更することで改善されることがあります。
   • DYNAMICおよびCOMPRESSEDフォーマットは、可変長カラム（VARCHAR、TEXT、BLOBなど）の大部分をオーバーフローページに格納するため、主データページ内の行サイズを小さく保つことができます。
   • テーブルの行フォーマットを変更するには、以下のSQLを実行します。

     ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;
     -- または
     ALTER TABLE your_table_name ROW_FORMAT=COMPRESSED;
     

   • この変更を行う前に、my.cnf（またはmy.ini）に以下の設定が有効になっていることを確認してください。

     [mysqld]
     innodb_file_format = Barracuda  # MariaDB 10.2.2以降では不要な場合が多い
     innodb_file_per_table = 1
     innodb_large_prefix = 1        # utf8mb4で長いインデックスが必要な場合に有効
     

     設定変更後はMariaDBの再起動が必要です。

2. 一部のカラムをTEXTまたはBLOB型に変更する

   • もし、非常に長い文字列を格納するVARCHARカラムがある場合、それをTEXT型（またはMEDIUMTEXT, LONGTEXT）に変更することを検討してください。TEXT/BLOB型は、データ自体がメインの行データとは別の場所に格納されるため、行サイズ制限にカウントされるのはそのポインタ（プレフィックス）のサイズのみとなります。
   • ただし、TEXT/BLOB型にするとインデックスの扱いなどに制限が出る場合があるため、注意が必要です。

3. innodb_page_sizeを大きくする（注意が必要）

   • innodb_page_sizeをデフォルトの16KBから32KBや64KBに増やすことで、1行あたりの最大サイズも大きくなります。
   • ただし、この設定はMariaDBインスタンスの初期化時にしか変更できません。 既に稼働中のデータベースで変更するには、既存のデータをエクスポートし、innodb_page_sizeを変更した上で新しいデータベースインスタンスを作成し、データをインポートし直す必要があります。これは非常に手間がかかる作業であり、I/O性能にも影響を与える可能性があるため、慎重に検討する必要があります。

4. innodb_strict_modeを無効にする（推奨されないが一時的な回避策）

   • innodb_strict_mode = OFFに設定すると、行サイズが大きすぎる場合でもエラーではなく警告として扱われることがあります。しかし、これは根本的な解決にはならず、データの切り捨てや予期しない挙動を引き起こす可能性があるため、本番環境での使用は推奨されません。 デバッグや一時的な対応としてのみ使用すべきです。

5. テーブル設計の見直し

   • 上記の方法で解決しない場合、テーブルの設計自体を見直す必要があるかもしれません。
     • カラム数を減らす
       不要なカラムがないか確認し、削除できないか検討します。
     • 関連テーブルに分割する
       関連性の低いデータを別のテーブルに分割することで、元のテーブルの行サイズを減らすことができます。
     • JSONデータ型を使用する
       複雑な構造のデータを1つのカラムに格納したい場合は、MariaDBのJSONデータ型（実体はLONGTEXT）を利用することで、個々のカラムを減らすことができます。

エラーが発生しているテーブルの行フォーマットを確認するには、以下のクエリを使用します。

SHOW TABLE STATUS LIKE 'your_table_name';

出力のRow_formatカラムを確認してください。

---

MariaDBでInnoDBストレージエンジンを使用している際に遭遇する「Row size too large」エラーは、データベース設計やテーブル定義に関連する一般的な問題です。このエラーは、テーブルの単一の行がInnoDBの内部的な最大サイズ制限を超えた場合に発生します。

エラーメッセージの例

このエラーが発生した際、通常は以下のようなメッセージが表示されます。

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOBs are stored inline.

または

ERROR 1005 (HY000): Can't create table 'your_database.your_table' (errno: 139 "Row size too large")

• In current row format, BLOBs are stored inline.: これは、現在使用している行フォーマット（例: COMPACTやREDUNDANT）では、TEXT/BLOBデータも行内に格納されようとするため、問題が悪化する可能性があることを示唆しています。
• Changing some columns to TEXT or BLOB may help.: これは、問題を解決するための直接的なヒントです。
• Row size too large (> 8126): これは、1行のデータサイズが約8KB（InnoDBのページサイズの半分強）を超えたことを示しています。正確な値はInnoDBの内部的なオーバーヘッドによって異なります。

エラーが発生する主な状況

このエラーは主に以下の状況で発生します。

• INSERTまたはUPDATE時
  非常に長い文字列データをVARCHARカラムに挿入または更新しようとした際に、その行の合計サイズが制限を超えてしまう場合。ただし、これはVARCHARの場合、その定義された最大長ではなく、実際に挿入されるデータの長さによって計算されますが、定義自体が大きすぎるとCREATE TABLE時に問題になることがあります。
• ALTER TABLE時
  既存のテーブルにカラムを追加したり、既存のカラムのデータ型を変更したりした結果、行サイズが最大値を超えてしまう場合。
• CREATE TABLE時
  新しいテーブルを作成しようとした際に、そのテーブル定義で1行が最大サイズを超える可能性がある場合。

エラーの一般的な原因とトラブルシューティング

前回の説明と重複する部分もありますが、よりトラブルシューティングの観点から掘り下げます。

原因A: 不適切な行フォーマット (COMPACTまたはREDUNDANT)

• トラブルシューティング
  

  1. テーブルの行フォーマットを確認する
     

     SHOW CREATE TABLE your_table_name;
     

     出力の末尾にROW_FORMAT=COMPACTやROW_FORMAT=REDUNDANTがあるか確認します。 または

     SHOW TABLE STATUS LIKE 'your_table_name';
     

     Row_formatカラムの値を確認します。
  2. 行フォーマットをDYNAMICに変更する
     これが最も効果的で一般的な解決策です。DYNAMIC行フォーマットでは、VARCHARやTEXT/BLOBのような可変長データ型は、行サイズが大きくなりそうになると自動的にオーバーフローページ（他のページ）に格納されます。これにより、主データページの行のフットプリントが大幅に削減され、行サイズ制限を超えにくくなります。

     ALTER TABLE your_table_name ROW_FORMAT=DYNAMIC;
     

  3. 注意点
     
     • この変更を行う前に、my.cnf（またはmy.ini）に以下の設定が有効になっていることを確認してください（MariaDB 10.2.2以降ではinnodb_file_formatは不要な場合が多いですが、念のため）。

       [mysqld]
       innodb_file_per_table = 1   # 各テーブルが専用の.ibdファイルを持つようにする
       innodb_large_prefix = 1     # DYNAMIC/COMPRESSEDで767バイト以上のインデックスプレフィックスを許可
       

       これらの設定の変更はMariaDBの再起動が必要です。
     • DYNAMICへの変更は、既存のデータが再構築されるため、テーブルサイズが大きい場合は時間がかかります。

• 問題
  MariaDBやMySQLの古いバージョンで作成されたテーブル、あるいは意図的に指定されたテーブルは、デフォルトでCOMPACTまたはREDUNDANT行フォーマットを使用している場合があります。これらのフォーマットは、VARCHARやTEXT/BLOBのデータを主データページ内に可能な限り格納しようとするため、行サイズが肥大化しやすいです。特に、TEXTやBLOB型でも、その先頭部分（プレフィックス）が行内に格納されますが、COMPACTではこのプレフィックスが大きくなる傾向があり、行サイズを圧迫します。

原因B: 多数のVARCHARカラムまたは非常に長いVARCHARカラムの使用

• トラブルシューティング
  

  1. VARCHARカラムのデータ型を見直す
     

     • 本当にそのVARCHARの長さが必要か再検討し、不必要に長い定義（例: VARCHAR(255)だが実際には数文字しか入らない）は短くする。
     • 非常に長いテキストデータを格納する必要がある場合は、TEXT、MEDIUMTEXT、またはLONGTEXT型への変更を検討する。TEXT/BLOB型は、行サイズ制限の計算において、メインの行内にはポインタ（通常は20バイト程度）しか格納されず、実際のデータはオーバーフローページに格納されるため、行サイズを大幅に削減できます。

  2. テーブルの正規化
     

     • もし、複数のVARCHARカラムが論理的にグループ化できる、または繰り返し現れるデータである場合、それらを別の関連テーブルに分割することを検討します。これにより、元のテーブルの行サイズが減少し、データベースの正規化も進みます。

• 問題
  VARCHARは可変長ですが、最大長が定義されており、InnoDBは内部的にこの最大長に基づいて行の潜在的な最大サイズを計算します。特に、多数のVARCHAR(255)やVARCHAR(500)のようなカラムが存在する場合、たとえ実際のデータが短くても、これらのカラムの定義長の合計が行サイズ制限を超えてしまうことがあります。また、utf8mb4のようなマルチバイト文字セットを使用している場合、1文字あたりのバイト数が増えるため、より少ない文字数で制限に達しやすくなります（例: VARCHAR(255)はutf8mb4で最大255 * 4 = 1020バイトを消費する可能性があります）。

原因C: innodb_page_sizeが小さい（特殊なケース）

• トラブルシューティング
  

  1. innodb_page_sizeを確認する
     

     SHOW VARIABLES LIKE 'innodb_page_size';
     

  2. innodb_page_sizeの変更（非常に限定的なケース）
     
     • この設定は、MariaDBインスタンスの初期化時にのみ設定可能です。 既にデータがある稼働中のデータベースでは、この設定を変更することはできません。変更するには、データベースをバックアップし、innodb_page_sizeを変更した状態で新しいデータベースインスタンスを初期化し、バックアップを復元する必要があります。
     • 通常、このエラーでinnodb_page_sizeを変更する必要があるケースは稀です。他の解決策を優先的に検討すべきです。

• 問題
  デフォルトのinnodb_page_sizeは16KBです。InnoDBの行サイズ制限は約ページサイズの半分（innodb_page_size / 2）です。もし、何らかの理由でinnodb_page_sizeを8KBや4KBなどの小さな値に設定している場合、行サイズ制限も比例して小さくなります。

原因D: カラム数の多さ

• トラブルシューティング
  

  1. テーブル設計の見直し
     
     • 本当にすべてのカラムが必要か、重複する情報がないかを確認する。
     • 論理的に関連するカラムのグループを別のテーブルに分割（正規化）することを検討する。
     • JSONデータ型などを利用して、非構造化データを1つのカラムに格納することも選択肢になりえます。

• 問題
  個々のカラムのサイズが小さくても、テーブルに非常に多くのカラムが存在する場合、それらの合計サイズが行サイズ制限を超えてしまうことがあります。InnoDBは、各カラムのデータに加えて、NULLバイトマップやオフセット情報などのオーバーヘッドを行内に持つため、カラム数が増えるほどこのオーバーヘッドも増加します。

• MariaDBのバージョン
  MariaDBのバージョンによって、InnoDBの機能やデフォルト設定が異なります。新しいバージョンほどDYNAMIC行フォーマットがデフォルトであったり、より効率的なストレージ管理が行われています。使用しているMariaDBのバージョンを確認し、最新の安定版を使用しているか確認することも重要です。
• 文字セットの影響
  utf8mb4などのマルチバイト文字セットは、latin1などのシングルバイト文字セットよりも多くのバイトを消費します。例えば、VARCHAR(255) CHARACTER SET utf8mb4は最大1020バイトを消費する可能性がありますが、VARCHAR(255) CHARACTER SET latin1は最大255バイトです。行サイズを計算する際には、文字セットによるバイト消費を考慮する必要があります。

---

このエラーは主にSQLレベルでのテーブル定義や変更に起因するため、ここではSQLコマンドを中心に、その影響と解決策を示すプログラミング例を提供します。

エラーを再現する例 (Row Size Too Large)

まず、意図的に「Row size too large」エラーを発生させるテーブルを作成する例を示します。 ここでは、InnoDBのデフォルト行フォーマットがCOMPACTまたはREDUNDANTである環境、あるいは明示的にそれを指定する環境を想定しています。（MariaDBの新しいバージョンではDYNAMICがデフォルトのため、意図的にROW_FORMAT=COMPACTを指定しています。）

-- 事前準備: 既存のテーブルがあれば削除
DROP TABLE IF EXISTS large_row_test_compact;

-- 意図的に行サイズが大きくなるテーブルを作成
-- 大量のVARCHARカラム（それぞれが最大長を使用する可能性）を定義
-- ROW_FORMAT=COMPACT を明示的に指定して、エラーを発生しやすくする
-- VARCHAR(255) CHARACTER SET utf8mb4 は1カラムあたり最大1020バイト消費する可能性がある
-- 8192バイト（約8KB）制限を超えさせるため、多くのカラムを定義
CREATE TABLE large_row_test_compact (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col_1 VARCHAR(255) CHARACTER SET utf8mb4,
    col_2 VARCHAR(255) CHARACTER SET utf8mb4,
    col_3 VARCHAR(255) CHARACTER SET utf8mb4,
    col_4 VARCHAR(255) CHARACTER SET utf8mb4,
    col_5 VARCHAR(255) CHARACTER SET utf8mb4,
    col_6 VARCHAR(255) CHARACTER SET utf8mb4,
    col_7 VARCHAR(255) CHARACTER SET utf8mb4,
    col_8 VARCHAR(255) CHARACTER SET utf8mb4,
    -- さらに多くのカラムを追加して確実にエラーを発生させる
    col_9 VARCHAR(255) CHARACTER SET utf8mb4,
    col_10 VARCHAR(255) CHARACTER SET utf8mb4,
    col_11 VARCHAR(255) CHARACTER SET utf8mb4,
    col_12 VARCHAR(255) CHARACTER SET utf8mb4,
    col_13 VARCHAR(255) CHARACTER SET utf8mb4,
    col_14 VARCHAR(255) CHARACTER SET utf8mb4,
    col_15 VARCHAR(255) CHARACTER SET utf8mb4,
    col_16 VARCHAR(255) CHARACTER SET utf8mb4,
    col_17 VARCHAR(255) CHARACTER SET utf8mb4,
    col_18 VARCHAR(255) CHARACTER SET utf8mb4,
    col_19 VARCHAR(255) CHARACTER SET utf8mb4,
    col_20 VARCHAR(255) CHARACTER SET utf8mb4,
    col_21 VARCHAR(255) CHARACTER SET utf8mb4,
    col_22 VARCHAR(255) CHARACTER SET utf8mb4,
    col_23 VARCHAR(255) CHARACTER SET utf8mb4,
    col_24 VARCHAR(255) CHARACTER SET utf8mb4,
    col_25 VARCHAR(255) CHARACTER SET utf8mb4,
    -- ... more columns ... (必要に応じてさらに追加)
    -- VARCHAR(255) utf8mb4が25カラムで約25 * 1020 = 25500バイトとなり、8KBを大きく超える
    -- InnoDBのオーバーヘッドも考慮すると、実際にはこれより少ないカラム数でエラーになる可能性もある
    some_text_column TEXT CHARACTER SET utf8mb4 -- TEXTもCOMPACTでは行サイズに影響を与えやすい
) ROW_FORMAT=COMPACT ENGINE=InnoDB;

-- このCREATE TABLE文は、通常以下のようなエラーを生成します。
-- ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOBs are stored inline.

解決策1: 行フォーマットをDYNAMICに変更する

最も推奨される解決策です。これにより、可変長データ（VARCHAR、TEXT、BLOB）がメインの行データから分離され、オーバーフローページに格納されるようになります。

-- 事前準備: large_row_test_compact が上記エラーで作成できなかった場合、
-- まずはエラーを回避するために、一部カラムを減らしてテーブルを作成し、
-- その後にALTER TABLEでROW_FORMATを変更する、という手順も考えられます。
-- ここでは、上記テーブルが何らかの理由で（例えば innodb_strict_mode=OFF で警告になっていた場合など）
-- 作成できたと仮定して、ALTER TABLEの例を示します。

-- まずテーブルの状態を確認
SHOW TABLE STATUS LIKE 'large_row_test_compact';
-- Row_format が 'Compact' であることを確認

-- 行フォーマットを DYNAMIC に変更
ALTER TABLE large_row_test_compact ROW_FORMAT=DYNAMIC;

-- 変更後のテーブルの状態を確認
SHOW TABLE STATUS LIKE 'large_row_test_compact';
-- Row_format が 'Dynamic' に変更されていることを確認

-- DYNAMIC フォーマットに変更後、同じカラム構成でテーブルを再作成する試み
-- (もし最初のCREATE TABLEがエラーになった場合、この設定を有効にして再試行すると成功する可能性が高い)
DROP TABLE IF EXISTS large_row_test_dynamic;

CREATE TABLE large_row_test_dynamic (
    id INT AUTO_INCREMENT PRIMARY KEY,
    col_1 VARCHAR(255) CHARACTER SET utf8mb4,
    col_2 VARCHAR(255) CHARACTER SET utf8mb4,
    col_3 VARCHAR(255) CHARACTER SET utf8mb4,
    col_4 VARCHAR(255) CHARACTER SET utf8mb4,
    col_5 VARCHAR(255) CHARACTER SET utf8mb4,
    col_6 VARCHAR(255) CHARACTER SET utf8mb4,
    col_7 VARCHAR(255) CHARACTER SET utf8mb4,
    col_8 VARCHAR(255) CHARACTER SET utf8mb4,
    col_9 VARCHAR(255) CHARACTER SET utf8mb4,
    col_10 VARCHAR(255) CHARACTER SET utf8mb4,
    col_11 VARCHAR(255) CHARACTER SET utf8mb4,
    col_12 VARCHAR(255) CHARACTER SET utf8mb4,
    col_13 VARCHAR(255) CHARACTER SET utf8mb4,
    col_14 VARCHAR(255) CHARACTER SET utf8mb4,
    col_15 VARCHAR(255) CHARACTER SET utf8mb4,
    col_16 VARCHAR(255) CHARACTER SET utf8mb4,
    col_17 VARCHAR(255) CHARACTER SET utf8mb4,
    col_18 VARCHAR(255) CHARACTER SET utf8mb4,
    col_19 VARCHAR(255) CHARACTER SET utf8mb4,
    col_20 VARCHAR(255) CHARACTER SET utf8mb4,
    col_21 VARCHAR(255) CHARACTER SET utf8mb4,
    col_22 VARCHAR(255) CHARACTER SET utf8mb4,
    col_23 VARCHAR(255) CHARACTER SET utf8mb4,
    col_24 VARCHAR(255) CHARACTER SET utf8mb4,
    col_25 VARCHAR(255) CHARACTER SET utf8mb4,
    some_text_column TEXT CHARACTER SET utf8mb4
) ROW_FORMAT=DYNAMIC ENGINE=InnoDB;

-- このCREATE TABLE文は、ROW_FORMAT=DYNAMICのおかげで、
-- エラーなく成功する可能性が高いです。

重要な注意点
my.cnf（またはmy.ini）に以下の設定が有効になっていることを確認してください。MariaDB 10.2.2以降では、多くの場合innodb_file_format = Barracudaは不要ですが、innodb_file_per_tableとinnodb_large_prefixは重要です。

[mysqld]
innodb_file_per_table = 1
innodb_large_prefix = 1

設定変更後はMariaDBサーバーの再起動が必要です。

解決策2: 一部のカラムをTEXTまたはBLOB型に変更する

VARCHARカラムが非常に長く、かつ全文検索の対象などでなければ、TEXT型に変更することで行サイズを大幅に削減できます。

-- 事前準備: エラーが発生した large_row_test_compact のようなテーブルを想定
-- （もし作成できなかった場合は、CREATE TABLE時に変更して試す）

-- 現在のテーブル定義を確認 (簡略版)
-- SHOW CREATE TABLE large_row_test_compact;
-- Example: col_A VARCHAR(500), col_B VARCHAR(1000) など

-- ALTER TABLE を使用して、長いVARCHARカラムをTEXT型に変更
ALTER TABLE large_row_test_compact
MODIFY COLUMN col_1 VARCHAR(255) CHARACTER SET utf8mb4 TEXT CHARACTER SET utf8mb4, -- VARCHARからTEXTへ変更
MODIFY COLUMN col_2 VARCHAR(255) CHARACTER SET utf8mb4 TEXT CHARACTER SET utf8mb4; -- 同様に変更

-- もし、元のテーブル作成時にエラーが出た場合、最初からTEXT型で定義し直す
DROP TABLE IF EXISTS large_row_text_columns;

CREATE TABLE large_row_text_columns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    -- 長い文字列を格納するカラムをTEXT型で定義
    long_description TEXT CHARACTER SET utf8mb4,
    json_data LONGTEXT CHARACTER SET utf8mb4, -- JSONデータもLONGTEXTとして格納可能
    short_name VARCHAR(100) CHARACTER SET utf8mb4,
    -- 他の短いカラム...
    col_other_1 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_2 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_3 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_4 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_5 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_6 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_7 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_8 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_9 VARCHAR(255) CHARACTER SET utf8mb4,
    col_other_10 VARCHAR(255) CHARACTER SET utf8mb4
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -- TEXT型を使う場合はDYNAMICが特に有効

解決策3: テーブル設計の見直し（例: 分割）

行サイズが大きすぎる場合、そもそも1つのテーブルに含める情報が多すぎる可能性があります。論理的に関連する情報を別のテーブルに分割することで、各テーブルの行サイズを減らすことができます。

元の設計 (エラーになりやすい例)
users テーブルに、ユーザーの基本情報と非常に長い設定データやプロフィール詳細がすべて含まれている。

CREATE TABLE users_bad_design (
    user_id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255),
    password_hash VARCHAR(255),
    registration_date DATETIME,
    -- ここから非常に長い設定データ
    user_preferences_json TEXT, -- JSON文字列
    profile_bio LONGTEXT,
    address_street VARCHAR(255),
    address_city VARCHAR(100),
    address_state VARCHAR(100),
    address_zip VARCHAR(20),
    -- その他、多数のオプション設定カラム...
    setting_1 VARCHAR(500),
    setting_2 VARCHAR(500),
    -- ...
    setting_N VARCHAR(500)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

改善された設計 (テーブル分割)
ユーザーの基本情報と、アクセス頻度の低い、または非常に長い情報を別のテーブルに分割します。

-- ユーザー基本情報テーブル
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    registration_date DATETIME NOT NULL,
    -- 他の必須で短いカラム
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

-- ユーザープロフィールと設定の詳細テーブル (usersテーブルに1対1で関連)
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY, -- usersテーブルのuser_idへの外部キー
    profile_bio TEXT CHARACTER SET utf8mb4,
    user_preferences_json TEXT CHARACTER SET utf8mb4, -- JSONデータにはTEXTまたはLONGTEXT
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

-- ユーザーアドレス情報テーブル (usersテーブルに1対1で関連)
CREATE TABLE user_addresses (
    user_id INT PRIMARY KEY, -- usersテーブルのuser_idへの外部キー
    address_street VARCHAR(255),
    address_city VARCHAR(100),
    address_state VARCHAR(100),
    address_zip VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

-- INSERT例 (Python/PHPなどのアプリケーションから実行されることを想定)
-- usersテーブルへの挿入
INSERT INTO users (username, email, password_hash, registration_date)
VALUES ('johndoe', '[email protected]', 'hashed_password_abc', NOW());

-- user_profilesテーブルへの挿入 (usersテーブルに挿入されたuser_idを取得して利用)
INSERT INTO user_profiles (user_id, profile_bio, user_preferences_json)
VALUES (LAST_INSERT_ID(), 'This is a very long biography text for John Doe...', '{"theme": "dark", "notifications": true}');

-- user_addressesテーブルへの挿入
INSERT INTO user_addresses (user_id, address_street, address_city, address_state, address_zip)
VALUES (LAST_INSERT_ID(), '123 Main St', 'Anytown', 'CA', '90210');

innodb_page_sizeの確認（変更は非推奨）

innodb_page_sizeはMariaDBの初期化時にしか設定できないため、既存のデータベースでは通常変更しません。しかし、現在の設定値を確認することはできます。

SHOW VARIABLES LIKE 'innodb_page_size';
-- 通常は 16384 (16KB) が表示されます。

---

MariaDB InnoDBにおける「Row size too large」エラー：代替のプログラミング関連解決策

「Row size too large」エラーは、主にテーブル設計の制約から発生しますが、既存のアプリケーションやデータ構造を大きく変更せずに対応したい場合、いくつかの代替手段が考えられます。これらは、根本的な解決策（ROW_FORMAT=DYNAMICやTEXT/BLOBの使用）が難しい場合や、特定のユースケースに特化したものです。

データをJSON形式で格納し、必要な部分のみを抽出する

なぜ代替手段になるのか

• 行サイズへの影響
  JSONデータ型は内部的にTEXT型として扱われるため、データ本体はオーバーフローページに格納され、メインの行サイズにはポインタのみが寄与します。
• 柔軟性
  スキーマの変更が頻繁なデータ（例：ユーザー設定、商品属性）に適しており、ALTER TABLE操作の回数を減らせます。
• カラム数の削減
  多数のオプション設定や非構造化データがある場合に、それらを個別のカラムとして持つのではなく、単一のJSONカラムに集約できます。これにより、行のオーバーヘッドが減ります。

プログラミング例

テーブル定義の変更

-- 元のテーブル (例: 多数のVARCHARカラムでエラーになる可能性)
/*
CREATE TABLE products_old (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description VARCHAR(2000),
    attribute_color VARCHAR(50),
    attribute_size VARCHAR(20),
    attribute_material VARCHAR(100),
    -- 他にも多くの属性カラム...
    attribute_weight DECIMAL(10,2),
    attribute_dimensions VARCHAR(100)
    -- ...
) ENGINE=InnoDB ROW_FORMAT=COMPACT;
*/

-- 代替案: 属性をJSONカラムに集約
CREATE TABLE products_new (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT CHARACTER SET utf8mb4, -- 説明が長い場合はTEXTにする
    attributes JSON, -- ここに可変な属性データをJSON形式で格納
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; -- JSONを使用する場合、DYNAMICを推奨

データの挿入（例：PythonのPymysqlライブラリを使用）

import pymysql
import json

# MariaDB接続設定
conn = pymysql.connect(
    host='127.0.0.1',
    user='your_user',
    password='your_password',
    database='your_database',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with conn.cursor() as cursor:
        product_name = "Awesome Gadget"
        product_description = "This is a super cool gadget with many features..."
        product_attributes = {
            "color": "blue",
            "size": "medium",
            "material": "plastic",
            "weight_kg": 0.5,
            "dimensions_cm": "10x5x2",
            "warranty_years": 2,
            "is_waterproof": True
        }

        # JSONオブジェクトを文字列に変換してDBに挿入
        sql = "INSERT INTO products_new (name, description, attributes) VALUES (%s, %s, %s)"
        cursor.execute(sql, (product_name, product_description, json.dumps(product_attributes)))
        conn.commit()
        print("Data inserted successfully.")

        # JSONデータを含む行を検索
        sql = "SELECT id, name, JSON_EXTRACT(attributes, '$.color') AS color, JSON_EXTRACT(attributes, '$.weight_kg') AS weight FROM products_new WHERE id = %s"
        cursor.execute(sql, (cursor.lastrowid,))
        result = cursor.fetchone()
        print("\nRetrieved data:")
        print(result)

        # JSONデータの更新
        new_attributes = product_attributes.copy()
        new_attributes["color"] = "red"
        new_attributes["is_waterproof"] = False
        
        sql = "UPDATE products_new SET attributes = %s WHERE id = %s"
        cursor.execute(sql, (json.dumps(new_attributes), result['id']))
        conn.commit()
        print(f"\nUpdated attributes for product ID {result['id']}.")

        sql = "SELECT id, name, JSON_EXTRACT(attributes, '$.color') AS color FROM products_new WHERE id = %s"
        cursor.execute(sql, (result['id'],))
        updated_result = cursor.fetchone()
        print(updated_result)

finally:
    conn.close()

オブジェクトストレージ（S3など）の利用とURLの格納

非常に大きなバイナリデータ（画像、動画、PDFなど）や、アプリケーションで頻繁に直接アクセスしない大規模なテキストファイルがある場合、それらをデータベース内に直接格納するのではなく、Amazon S3のようなオブジェクトストレージサービスにアップロードし、そのURL（またはパス）のみをデータベースに格納する方法です。

• コスト効率
  大容量のデータをデータベースのディスクに保存するよりも、オブジェクトストレージの方がコスト効率が良い場合があります。
• スケーラビリティ
  オブジェクトストレージは、通常、非常に高いスケーラビリティと可用性を提供します。
• 行サイズからの完全な分離
  実際のデータはデータベースの外に存在するため、行サイズ制限の対象外となります。データベースに格納されるのは、数十〜数百バイトのURL文字列のみです。

テーブル定義の変更

-- 元のテーブル (例: BLOBカラムが巨大なファイルでエラーになる可能性)
/*
CREATE TABLE documents_old (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    file_content LONGBLOB -- 非常に大きなファイル
) ENGINE=InnoDB ROW_FORMAT=COMPACT;
*/

-- 代替案: ファイル内容をURLとして格納
CREATE TABLE documents_new (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    file_url VARCHAR(2048), -- S3などのURLを格納するのに十分な長さ
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

データのアップロードとURLの格納（例：PythonのBoto3ライブラリとPymysqlを使用）

import pymysql
import boto3
import os

# MariaDB接続設定 (上記と同じ)
conn = pymysql.connect(
    host='127.0.0.1',
    user='your_user',
    password='your_password',
    database='your_database',
    charset='utf8mb4'
)

# AWS S3設定
s3 = boto3.client(
    's3',
    aws_access_key_id='YOUR_AWS_ACCESS_KEY_ID',
    aws_secret_access_key='YOUR_AWS_SECRET_ACCESS_KEY',
    region_name='your-aws-region' # 例: 'us-east-1'
)
S3_BUCKET_NAME = 'your-unique-s3-bucket-name'

# ダミーのファイル作成
dummy_file_path = 'large_document.pdf'
with open(dummy_file_path, 'wb') as f:
    f.write(os.urandom(1024 * 1024 * 5)) # 5MBのダミーファイル

try:
    # S3にファイルをアップロード
    s3_key = f"documents/{os.path.basename(dummy_file_path)}"
    s3.upload_file(dummy_file_path, S3_BUCKET_NAME, s3_key)
    file_url = f"https://{S3_BUCKET_NAME}.s3.amazonaws.com/{s3_key}"
    print(f"File uploaded to S3: {file_url}")

    with conn.cursor() as cursor:
        document_title = "Important Annual Report"

        # データベースにURLを挿入
        sql = "INSERT INTO documents_new (title, file_url) VALUES (%s, %s)"
        cursor.execute(sql, (document_title, file_url))
        conn.commit()
        print("Document URL inserted into database successfully.")

        # データベースからURLを取得
        sql = "SELECT id, title, file_url FROM documents_new WHERE title = %s"
        cursor.execute(sql, (document_title,))
        result = cursor.fetchone()
        print("\nRetrieved document info:")
        print(result)

        # 取得したURLを使ってファイルをダウンロード（例）
        # s3.download_file(S3_BUCKET_NAME, s3_key, 'downloaded_document.pdf')
        # print("File downloaded from S3 using URL.")

finally:
    conn.close()
    os.remove(dummy_file_path) # ダミーファイルを削除

非常に柔軟なスキーマや巨大なドキュメント、または頻繁に構造が変わるデータを扱う場合、MongoDBやElasticsearchのようなドキュメント指向データベースをMariaDBと併用することも選択肢になります。

• 行サイズの問題回避
  大量の非構造化データが原因で行サイズエラーが発生する場合、その部分をドキュメントDBに移管することでMariaDB側の問題を解消できます。
• ユースケースの分離
  MariaDBで構造化された主要なデータを管理し、ドキュメントDBで柔軟なスキーマのデータを管理することで、各データベースの長所を活かせます。
• 専用の機能
  ドキュメントDBは、非リレーショナルデータの格納とクエリに特化しており、MariaDBのようなリレーショナルDBの行サイズ制限とは無縁です。

プログラミング例 (概念的な説明)

MariaDB側

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    document_db_id VARCHAR(255) UNIQUE, -- ドキュメントDBのドキュメントIDを格納
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

// hypothetical Node.js application
const mysql = require('mysql2/promise');
const mongoose = require('mongoose');

// MariaDB接続設定
const dbConfig = {
    host: '127.0.0.1',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database',
};

// MongoDB接続設定
mongoose.connect('mongodb://localhost:27017/user_data_db', {
    useNewUrlParser: true,
    useUnifiedTopology: true
});

// MongoDBスキーマ
const userProfileSchema = new mongoose.Schema({
    userId: Number, // MariaDBのuser_idと紐付け
    bio: String,
    preferences: Object, // 柔軟な設定オブジェクト
    activityLog: Array, // ユーザーのアクティビティ履歴
    // ...
});
const UserProfile = mongoose.model('UserProfile', userProfileSchema);

async function createUserAndProfile(username, email, bio, preferences) {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);

        // MariaDBにユーザー基本情報を挿入
        const [userResult] = await connection.execute(
            'INSERT INTO users (username, email) VALUES (?, ?)',
            [username, email]
        );
        const mariaDbUserId = userResult.insertId;

        // MongoDBにユーザープロフィールを挿入
        const profileDoc = new UserProfile({
            userId: mariaDbUserId,
            bio: bio,
            preferences: preferences,
            activityLog: []
        });
        const savedProfile = await profileDoc.save();

        // MariaDBのユーザーレコードにMongoDBのドキュメントIDを更新
        await connection.execute(
            'UPDATE users SET document_db_id = ? WHERE user_id = ?',
            [savedProfile._id.toString(), mariaDbUserId]
        );

        console.log(`User ${username} created with MariaDB ID: ${mariaDbUserId} and MongoDB ID: ${savedProfile._id}`);
    } catch (error) {
        console.error('Error creating user:', error);
    } finally {
        if (connection) connection.end();
    }
}

// 例の呼び出し
createUserAndProfile(
    'janedoe',
    '[email protected]',
    'A passionate developer and open-source enthusiast with a long history...',
    {
        theme: 'light',
        notifications: { email: true, push: false },
        favorite_categories: ['tech', 'travel', 'food']
    }
);