Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hello,

I would appreciate any assistance I can receive.

While following the instructions provided in chapter 17 to create the users table for the forum2 database (page 553), I had encountered the following error:

 CREATE TABLE users (    
   -> user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,    
   -> lang_id TINYINT UNSIGNED NOT NULL,    
   -> time_zone VARCHAR(30) NOT NULL,    
   -> username VARCHAR(30) NOT NULL,    
   -> pass VARCHAR(255) NOT NULL,    
   -> email VARCHAR(60) NOT NULL,    
   -> PRIMARY KEY (user_id),    
   -> UNIQUE (username),    
   -> UNIQUE (email),    
   -> INDEX login (username, pass)    
   -> );

 

Error message:

#1071 - Specified key was too long; max key length is 1000 bytes

 

Some additional information:

Currently using paid hosting.

My database character set is:  utf8mb4 

My database collation is:  utf8mb4_unicode_ci

All tables use: InnoDB.

 

I would like to please know how this error can be resolved to continue proceeding with the chapter.

 

Enjoying the book!

Many thanks,

MF.

 

 

 

Edited by mainframe
Link to post
Share on other sites

Thanks for the nice words. Yeesh on this error message, though! That command creates four keys (or indexes): on user_id, on username, on email, and on the combination of username and pass. None of those seems to be 100 bytes! My hunch is it's the login key that's causing the problem. Try removing that from the command (and delete the comma after `UNIQUE (email)` and try it again. 

  • Like 1
Link to post
Share on other sites

Thank you Larry for your help.

Your solution worked perfectly in the interim!

After changing the storage engine to MyISAM and attempting other workarounds, the problem still incurred.

Later I realised for a specified key to be too long, the charset used in the client must be consuming too many bytes and could therefore not be UTF8.  

The problem was due to my accidental omission of:

CHARSET UTF8;

I would have expected unusual characters to be stored in the database if the charset were wrong however this was an error thrown before any database population could have taken place.

Cheers and thanks again,

MF

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...