0
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?

Blisskarthik
  • 1,246
  • 8
  • 20
user125583
  • 11
  • 1
  • 3
  • Possible this will help you: http://stackoverflow.com/questions/22637733/mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te – Vartlok Jul 22 '14 at 11:06
  • 2
    Bear in mind that it depends on the character set: 256 characters of utf-8 storage could need 768 bytes to store. For example, your statement fails for me with `DEFAULT CHARACTER SET utf8` specified, but succeeds with `DEFAULT CHARACTER SET latin1` (Since MySQL 4.1, column lengths for VARCHAR are characters, not bytes.) – Matt Gibson Jul 22 '14 at 11:14
  • Note that as you seem to be storing URLs, you might be able to work around the problem by specifically storing the URL columns with a "smaller" character set—perhaps even straight ASCII—as Unicode characters in URLs aren't that well-supported, and those are your biggest columns. – Matt Gibson Jul 22 '14 at 11:23

3 Answers3

2

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

Ashutosh SIngh
  • 931
  • 3
  • 13
  • 26
2

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)
);
symcbean
  • 47,736
  • 6
  • 59
  • 94
0
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;

Sarvesh Sawant
  • 1