Found mysql two issues in new dal.py
First issues
mysql VARCHAR length should be 0 to 255,but web2py default varchar value was
set to length = 512,if some forgot to declare varchar length he will get
error message key too long.
Second issue
After creating FK in the model
if we declare
db.define_table('dogs',
Field('teamname'),primarykey=['teamname'],migrate=False
)
db.define_table('users',
Field('name'),
Field('team',db.dogs)
)
db.users.name.requires = IS_NOT_EMPTY()
db.users.team.requires = IS_IN_DB(db,'dogs.teamname','dogs.teamname')
sql :
CREATE TABLE users(
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(100),
team id, INDEX team__idx (team), FOREIGN KEY (team) REFERENCES
dogs(teamname),
PRIMARY KEY(id)
) ENGINE=InnoDB CHARACTER SET utf8;
Here teamname was set to string and team in the user table goes interger
,then it throws error 1005 can't create table users
'reference': 'INT, INDEX %(field_name)s__idx (%(field_name)s),
FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON DELETE
%(on_delete_action)s',
as wrokaround i have changed the dal file to
'reference': 'VARCHAR(100), INDEX %(field_name)s__idx
(%(field_name)s), FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON
DELETE %(on_delete_action)s',
then it works.
