Skip to content

Form validation strange behavior, SQLSTATE[23000] error #6126

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
nikolaykovenko opened this issue Nov 20, 2014 · 6 comments
Closed

Form validation strange behavior, SQLSTATE[23000] error #6126

nikolaykovenko opened this issue Nov 20, 2014 · 6 comments
Assignees
@cebe
Labels
status:to be verified Needs to be reproduced and validated.

Comments

@nikolaykovenko
Copy link

Hello.

I have simple mysql table:

CREATE TABLE IF NOT EXISTS `categories` (
`id` int(10) unsigned NOT NULL,
  `rate` int(11) NOT NULL DEFAULT '0',
  `static` varchar(255) NOT NULL,
  `caption` varchar(255) NOT NULL,
  `skills_matrix` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=14 ;

And default generated by CRUD Model, Controller and Views without any changes. Model has these validation rules:

/**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['rate', 'skills_matrix'], 'integer'],
            [['static', 'caption'], 'required'],
            [['static', 'caption'], 'string', 'max' => 255],
            [['static'], 'unique']
        ];
    }

When I'm trying to create new category item without filling rate field, Yii throws exception:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'rate' cannot be null
The SQL being executed was: INSERT INTO `categories` (`rate`, `static`, `caption`) VALUES (NULL, '123', '222')

Screenshot

I think this is very strange. Do the rate field should init default value before saving to DB, don't it?
@samdark samdark added the status:to be verified Needs to be reproduced and validated. label Nov 20, 2014
@cebe cebe added type:docs Documentation and removed status:to be verified Needs to be reproduced and validated. labels Nov 20, 2014
@cebe cebe added this to the 2.0.1 milestone Nov 20, 2014
@cebe cebe self-assigned this Nov 20, 2014
cebe added a commit that referenced this issue Nov 20, 2014
@cebe
improved documentation about loadDefaultValues()
db56384 
issue #6126
@cebe
Copy link
Member

cebe commented Nov 20, 2014

  1. Yii does not load default values itself, you have to call loadDefaultValues() explicitly. I updated the docs about it a bit.
  2. The error seems strange to me, normally the default value should be inserted by the DBMS when you insert null... is the same thing happening with skills_matrix?

@cebe cebe added status:to be verified Needs to be reproduced and validated. and removed type:docs Documentation labels Nov 20, 2014
@cebe cebe modified the milestones: 2.0.x, 2.0.1 Nov 20, 2014
@nikolaykovenko
Copy link
Author

Yes, as you can see here - https://yadi.sk/i/629sBUhWcq9bc

I tried to exec the query directly with PMA and it was the same error occured - https://yadi.sk/i/FNvJs11GcqAMf

But then I tried the same query with two rows of parameters - https://yadi.sk/i/qdT1TsEJcqADV and it was made without errors

normally the default value should be inserted by the DBMS when you insert null

Are you sure this is correct for mysql? I can't find this in mysql docs, just the "default" keyword. So the query

INSERT INTO `categories` (`rate`, `static`, `caption`, `skills_matrix`) VALUES 
(10, 'test', 'test', default);

passes success - https://yadi.sk/i/jxRblRi4cqDEw

I thought maybe sql_mode has wrong value,but it's default (empty value).

@samdark
Copy link
Member

samdark commented Nov 20, 2014

It is sql_mode indeed. If it's strict any insertion of incompatible data results in error. If it's not, MySQL tries to convert it somehow (mostly results in wrong data).

@nikolaykovenko
Copy link
Author

Yeah, it's true. But empty sql_mode value is not strict, is it?

My sql_mode value:
https://yadi.sk/i/Jdaq6brEcqF2T
https://yadi.sk/i/SgJ4BLOTcqF4R

@samdark
Copy link
Member

samdark commented Nov 20, 2014

It shouldn't be but it may depend on the OS package.

@dynasource
Copy link
Member

out of the yii scope. If you want to ensure a attribute has a specific value, you should set a default value by Yii http://www.yiiframework.com/doc-2.0/guide-tutorial-core-validators.html#default

@cebe cebe removed this from the 2.0.x milestone Nov 3, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@cebe cebe

Labels
status:to be verified Needs to be reproduced and validated.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@samdark @dynasource @cebe @nikolaykovenko