SQL error 1366 after upgrade from 1.1.2 to 1.1.4

Yii 1.1 General Discussion
1

Hi,

I’m quite new to Yii, and until now I didn’t have to post anything because I could manage my problems by myself, however, I falled into a strange problem after upgrade to the newest version. I just can’t add new records. The old version works fine, but I wish my Yii framework was up to date :)

Here is the log:

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: ‘’ for column ‘col3’ at row 1

Source File

D:\Internet\www\Apache\htdocs\yii-1.1.2.r2086\framework\db\CDbCommand.php(234)

00222: if($this->_connection->enableProfiling)

00223: Yii::endProfile(‘system.db.CDbCommand.execute(’.$this->getText().’)’,‘system.db.CDbCommand.execute’);

00224:

00225: return $n;

00226: }

00227: catch(Exception $e)

00228: {

00229: if($this->_connection->enableProfiling)

00230: Yii::endProfile(‘system.db.CDbCommand.execute(’.$this->getText().’)’,‘system.db.CDbCommand.execute’);

00231: Yii::log('Error in executing SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,‘system.db.CDbCommand’);

00232: $errorInfo = $e instanceof PDOException ? $e->errorInfo : null;

00233:

00234: throw new CDbException(Yii::t(‘yii’,‘CDbCommand failed to execute the SQL statement: {error}’,

00235: array(’{error}’=>$e->getMessage())),(int)$e->getCode(),$errorInfo);

00236: }

00237: }

00238:

00239: /**

00240: * Executes the SQL statement and returns query result.

00241: * This method is for executing an SQL query that returns result set.

00242: * @param array input parameters (name=>value) for the SQL execution. This is an alternative

00243: * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing

00244: * them in this way can improve the performance. Note that you pass parameters in this way,

00245: * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.

00246: * binding methods and the input parameters this way can improve the performance.

I know this error comes from MySQL server that works in a strict mode, but Yii 1.1.2 works fine with it.

Any ideas?

2

Upgrading from 1.1.2 to 1.1.4…hm did you follow Yii best practises:

It doesn’t sound like Yii problem can you post some related code for further analysis.

3

Hi, thanks for reply.

Actually, I don’t know what piece of code would be apropriate for analysys.

Because I’m in progress with developing my webpage, I didn’t make a genuine upgrade - I downloaded and unpacked yii 1.1.4 into a new folder and then copied apps folder into and deleted all content of my application assets folder.

Is there anything I could miss?

Barbq

4

  • backup your assets folder

  • delete your assets folder

  • reload application

  • see if it got fixed

…just brainstorming

5

looks like your problem is very similar to this one if not identical http://bugs.php.net/bug.php?id=44707 please have a look and let me know what you think

6

nope, doesn’t work :mellow:

7

see my latest comment

8

Seems to be VERY similar to my problem. But, if it’s a PDO bug, then why Yii 1.1.2 works fine on the same php version?

The object is an ActiveRecord type, so binding parameters is hidden deeply in Yii. What can I do to fix this?

9

have you been able to isolate which query breaks your app? Paste it here so we can see it. May be do some type checking of the values that you pass to the query…

10

Here you are:

Error in executing SQL: UPDATE score_schema SET id=:yp0, name=:yp1,

col1=:yp2, col2=:yp3, col3=:yp4, col4=:yp5, col5=:yp6,

col6=:yp7, col7=:yp8, col8=:yp9, col9=:yp10, col10=:yp11,

used_tables=:yp12, des1=:yp13, des2=:yp14, des3=:yp15,

des4=:yp16, des5=:yp17, des6=:yp18, des7=:yp19, des8=:yp20,

des9=:yp21, des10=:yp22, type=:yp23, created_by=:yp24,

created_at=NOW(), description=:yp25, is_active=:yp26 WHERE

score_schema.id=‘10’

Only col1 cannot be null, the rest can be. So in my example 3 cols are set, and the query raise error on col4 which is empty:

exception ‘CDbException’ with message 'CDbCommand failed to execute the SQL

statement: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: ‘’

for column ‘col4’ at row 1’

11

can you paste your code for the query. Also did you try to debug each value that is being assigned to each field to see if there is anything fishy?

12

I used Gii to generate model and honestly I don’t know how to check it…

13

On the first post the error says col3, on post #10 error says col4 so you changed something?

One thing I could think of is that colX is numeric but Yii to represent numeric types uses string… so in strict mode could be the problem that inserting empty string gives this error… check the value of $model->colX to see if that is the case

If that is the case you can solve this with a ‘default’ rule like


array('colX','default','setOnEmpty'=>true,'value'=>0)

This way Yii will set the attribute colX to 0 if it’s empty

14

Thanks for reply,

I made a check if this is about empty column, not just col3. Yes your hint solved the problem, but I used ‘value’=null, because I wanted null. I had a rule ‘allowEmpty’=>true and thought it is enough… Actually, where can I find more details about options for rules?

Anyway, thanks a llllot! You too, Bettor!:)

BarBQ