CODEWITHSUNDEEP
mysqlsql
user125583
user125583

Reputation: 11

Error Code: 1118 Row size too large (>65535)

CREATE TABLE IF NOT EXISTS client_details (
id BIGINT AUTO_INCREMENT PRIMARY KEY,

client_description VARCHAR(1024),
reuse_refresh_tokens BOOLEAN NOT NULL DEFAULT 1,
dynamically_registered BOOLEAN NOT NULL DEFAULT 0,
allow_introspection BOOLEAN NOT NULL DEFAULT 0,
id_token_validity_seconds BIGINT NOT NULL DEFAULT 600,

client_id VARCHAR(256),
client_secret VARCHAR(2048),
access_token_validity_seconds BIGINT,
refresh_token_validity_seconds BIGINT,

application_type VARCHAR(256),
client_name VARCHAR(256),
token_endpoint_auth_method VARCHAR(256),
subject_type VARCHAR(256),

logo_uri VARCHAR(2048),
policy_uri VARCHAR(2048),
client_uri VARCHAR(2048),
tos_uri VARCHAR(2048),

jwks_uri VARCHAR(2048),
sector_identifier_uri VARCHAR(2048),

request_object_signing_alg VARCHAR(256),

user_info_signed_response_alg VARCHAR(256),
user_info_encrypted_response_alg VARCHAR(256),
user_info_encrypted_response_enc VARCHAR(256),

id_token_signed_response_alg VARCHAR(256),
id_token_encrypted_response_alg VARCHAR(256),
id_token_encrypted_response_enc VARCHAR(256),

token_endpoint_auth_signing_alg VARCHAR(256),

default_max_age BIGINT,
require_auth_time BOOLEAN,
created_at TIMESTAMP NULL,
initiate_login_uri VARCHAR(2048),
post_logout_redirect_uri VARCHAR(2048),
unique(client_id)
);

===============
I would like to create a table with MySQL Workbench, but there is an error :

"Error Code: 1118. Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs"

I don't understand how the row size is bigger than 65535.

Any help please?

Upvotes: 0

Views: 6604

Answers (3)

Sarvesh Sawant
Sarvesh Sawant

Reputation: 1

CREATE TABLE IF NOT EXISTS client_details (
id BIGINT AUTO_INCREMENT PRIMARY KEY,

client_description VARCHAR(1024),
reuse_refresh_tokens BOOLEAN NOT NULL DEFAULT 1,
dynamically_registered BOOLEAN NOT NULL DEFAULT 0,
allow_introspection BOOLEAN NOT NULL DEFAULT 0,
id_token_validity_seconds BIGINT NOT NULL DEFAULT 600,

client_id VARCHAR(256),
client_secret VARCHAR(2048),
access_token_validity_seconds BIGINT,
refresh_token_validity_seconds BIGINT,

application_type VARCHAR(256),
client_name VARCHAR(256),
token_endpoint_auth_method VARCHAR(256),
subject_type VARCHAR(256),

logo_uri VARCHAR(2048),
policy_uri VARCHAR(2048),
client_uri VARCHAR(2048),
tos_uri VARCHAR(2048),

jwks_uri VARCHAR(2048),
sector_identifier_uri VARCHAR(2048),

request_object_signing_alg VARCHAR(256),

user_info_signed_response_alg VARCHAR(256),
user_info_encrypted_response_alg VARCHAR(256),
user_info_encrypted_response_enc VARCHAR(256),

id_token_signed_response_alg VARCHAR(256),
id_token_encrypted_response_alg VARCHAR(256),
id_token_encrypted_response_enc VARCHAR(256),

token_endpoint_auth_signing_alg VARCHAR(256),

default_max_age BIGINT,
require_auth_time BOOLEAN,
created_at TIMESTAMP NULL,
initiate_login_uri VARCHAR(2048),
post_logout_redirect_uri VARCHAR(2048),
unique(client_id)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

Add end of query-> ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 0

symcbean
symcbean

Reputation: 48387

While there are various ways to fudge a solution, your approach is far too naive and if you find workarounds for the problems you are experiencing just now you are storing up problems for the future.

You should start by profiling your data and sizing your fields appropriately. Not just specifying the largest size the data might possibly be.

Why do you have 2 unique identifiers on the table?

Moving the larger fields into a seperate table will help to improve performance:

CREATE TABLE IF NOT EXISTS client_details (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 client_description VARCHAR(1024),
 reuse_refresh_tokens BOOLEAN NOT NULL DEFAULT 1,
 dynamically_registered BOOLEAN NOT NULL DEFAULT 0,
 allow_introspection BOOLEAN NOT NULL DEFAULT 0,
 id_token_validity_seconds BIGINT NOT NULL DEFAULT 600,
 client_id VARCHAR(256),
 client_secret VARCHAR(2048),
 access_token_validity_seconds BIGINT,
 refresh_token_validity_seconds BIGINT,
 application_type VARCHAR(256),   
          -- why is this not an enum or foreign key?
 client_name VARCHAR(256),
 token_endpoint_auth_method VARCHAR(256),
 subject_type VARCHAR(256),
 request_object_signing_alg VARCHAR(256),
 user_info_signed_response_alg VARCHAR(256),   
          -- is this really a single value per client?
 user_info_encrypted_response_alg VARCHAR(256), 
 user_info_encrypted_response_enc VARCHAR(256), 
 id_token_signed_response_alg VARCHAR(256),
 id_token_encrypted_response_alg VARCHAR(256),
 id_token_encrypted_response_enc VARCHAR(256),
 token_endpoint_auth_signing_alg VARCHAR(256),
 default_max_age BIGINT,
 require_auth_time BOOLEAN,
 created_at TIMESTAMP NULL,   
             -- why is NULL allowed?
 unique(client_id)
);

CREATE TABLE IF NOT EXISTS client_uris (
   client_details_id BIGINT REFERENCES client_uris(id),
   role ENUM (
         'logo',
         'policy',
         'client',
         'tos',
         'jwks',
         'sector_identifier',
         'initiate_login',
         'post_logout_redirect') NOT NULL,
   uri TINYTEXT,
   UNIQUE(client_details_id, role)
);

Upvotes: 2

Ashutosh SIngh
Ashutosh SIngh

Reputation: 1019

The total size of all fields in the table is more than the limit, 65535 bytes, that's why you are getting this error.

You should use text type instead of varchar for long strings. Replace all varchar with text, and it should work.

http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

Upvotes: 2

Related Questions