1.3 Migration Question re:NULL and Defaults

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

1.3 Migration Question re:NULL and Defaults

MikeK-2
I am porting a 1.2.0.5875 app to 1.3 in preparation for moving to 2.X. I have several fields (tax, amount, fee) in an order record all with NULL set to "No", each is a decimal(6,2) field, and each has default value of 0.00

The following code worked fine and dandy in the 1.2 system and in the event nothing was specified by the user for tax, amount, or fee, they were properly set to 0.00 in the DB:

$this->cleanUpFields(); //WILL REMOVE AS THIS IS DEPRECATED
$this->Order->create();
if ($this->Order->save($this->data)) {

In my 1.3 migration build I am getting 'tax' cannot be NULL and I see in the SQL insert op that indeed it is being set to NULL, and thus MySQL chokes on it.

Why did it operate correctly in the old 1.2 system and what should I do to correct this? It is pervasive across numerous classes and I want to get it right the first time.

Is this perhaps a difference in the way the form helper is handling initialization of the fields? Or somewhere in the model? I did not need to specify validation for these fields as used back in 1.2 etc.

Thanks in advance!

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

John Andersen
Can I assume that you are working on a copy of the original database?

If so, please verify that a default value is specified for that column. Kindly provide us with the definition of it.

It is quite alright for CakePHP to provide a NULL for the column on an insert, MySql should then set the value to the default one.

Enjoy, John

On Tuesday, 30 December 2014 20:18:03 UTC+2, MikeK wrote:
I am porting a 1.2.0.5875 app to 1.3 in preparation for moving to 2.X. I have several fields (tax, amount, fee) in an order record all with NULL set to "No", each is a decimal(6,2) field, and each has default value of 0.00

The following code worked fine and dandy in the 1.2 system and in the event nothing was specified by the user for tax, amount, or fee, they were properly set to 0.00 in the DB:

$this->cleanUpFields(); //WILL REMOVE AS THIS IS DEPRECATED
$this->Order->create();
if ($this->Order->save($this->data)) {

In my 1.3 migration build I am getting 'tax' cannot be NULL and I see in the SQL insert op that indeed it is being set to NULL, and thus MySQL chokes on it.

Why did it operate correctly in the old 1.2 system and what should I do to correct this? It is pervasive across numerous classes and I want to get it right the first time.

Is this perhaps a difference in the way the form helper is handling initialization of the fields? Or somewhere in the model? I did not need to specify validation for these fields as used back in 1.2 etc.

Thanks in advance!

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

MikeK-2

John-

Thank you for the reply. Yes indeed I am working on a copy of the DB - in fact I have cloned the entire domain including the DB etc, so I am able to compare things as well as test tweaks in the DB. Yes a default value of 0.00 is specified for the 'column' . The column definitions are:

column=amount   decimal(6,2) Null=No Default=0.00 all other fields are blank (extra, collation, attributes, etc)

other columns are decimal(4,2) but otherwise the same.

I am not sure if under the 1.2.0.5875 prebeta the original project utilized whether CakePHP is performing an insert with a NULL for the unset field and MySQL is taking care of the conversion to the default, or whether CakePHP itself performs the conversion based on schema prior to insert request.

It has always been my understanding that MySQL should handle it.

I have also purged all relevant tmp files, cache files etc.

I do have the ability to operate a copy of the old code and the migrated code simultaneously on the copy of the DB for testing purposes.

I have confirmed that using the same backup DB and same version of MySQL (same server) that cakephp 1.3.20 and 1.2.0.5875 are having different results on the same save operation at the controller level.

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

MikeK-2
In reply to this post by John Andersen
I also ran the legacy code with some SQL log dumping and it seems to me the old code did NOT attempt an INSERT with the values of the unset fields set to NULL - apparently it only attempted to INSERT the fields that were set, at least according to the lastQuery() dump.

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

MikeK-2
FYI the host is running MYSQL 5.0.96-log and PHP 5.3.24

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

John Andersen
In reply to this post by MikeK-2
I just checked MySQL and it does fail when I try to insert a NULL into a Non-Null column. So it is only when there is no value for the column that MySQL will use the default value instead.

So when CakePHP uses NULL for that column, MySQL will fail the insert operation. How does the save statement look like? What fields are in the data array that you try to save?

Enjoy, John

On Tuesday, 30 December 2014 22:50:11 UTC+2, MikeK wrote:
I also ran the legacy code with some SQL log dumping and it seems to me the old code did NOT attempt an INSERT with the values of the unset fields set to NULL - apparently it only attempted to INSERT the fields that were set, at least according to the lastQuery() dump.

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

MikeK-2
Thanks again, yes this is what I am seeing and is consistent with the idea that the old code did not submit a NULL field.

As notated above the "save" statement in the controller has no parms other than $this->data - when the field has nothing assigned to it cakePHP 1.3.20 sets it to NULL - although this exact same thing occurs under the old 1.2 code, in 1.2 the field set to NULL is not part of the INSERT operation, whereas in 1.3 it is.

So the old app code that essentially has no parm validation on the form does not work in 1.3. Funny thing is I guess they were relying on mySQL to convert the NULL to the default when what they were really relying on was cakePHP recognizing the NULL and stripping that column from the INSERT request thus allowing the default in the DB to set the default value.

I guess what is now needed is to set your own default value - I wonder if there is an easy way to do this in the model somewhere?

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: 1.3 Migration Question re:NULL and Defaults

John Andersen
You could probably use the BeforeSave method to modify the values, so as to replace 'NULL' with null or remove the field entirely in the array.
Enjoy, John
[snip]

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP

---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/d/optout.