saving the word "joint" in an enum type (mysql) with yii

Archives Bug Discussions
1

Hi Qiang,

this is something very strange that made me crazy for about 3 hours :frowning:

To reproduce it:

  • mysql 5.1.30 and 5.0.77

  • create a table with a field of type enum and declare a value for that enum with the word "joint".

  • create model and crud for the table

  • use both a activeTextField or activeDropDownList (tried both) for the enum field

  • try to save a record (update, create it is the same).

you will see that it will save a blank value. (there isn't any difference if the field can be null or not null, anyway I have it not null and that with a required validator in the model).

I can see in controller just an instruction before (and also after) the save method that in the $model->attribute there is the correct value. But on database…nothing.

Obviously using phpmyadmin i can save correctly the value inside it.

please help me because it is quite important…:-S

2

What is the SQL generated by AR? And what is the parameter value bound the SQL statement?

3

in which way can I see the sql generated?

I don't understand the second question… in the textfield i write "joint", in the activeDropDownList i create an array ('joint'=>'joint'), and i select it.

If i try to write in the enum declaration "join" or 'joind' or 'any other words' everything works correctly.

4

In you app config, add a CWebLogRoute (do not set category and level options).

5

Maybe that’s the new drug prevention feature of MySQL. ;) (Couldn’t resist, sorry…)

6

ahahah it’s the very first thing I thought… ;D but it is related to welding technics in this case.

How can i see the generated sql?

7

I'm sorry. I have read only the last post

8

ok: that's the query



Executing SQL: UPDATE `asme_wdt_joint` SET `swj_pk`=:yp0, `swj_wps`=:yp1,


`swj_pqr`=:yp2, `swj_welding`=:yp3, `swj_joint_type`=:yp4,


`swj_backing`=:yp5, `swj_backing_material`=:yp6,


`swj_backing_material_type`=:yp7, `swj_diameter`=:yp8,


`swj_diameter_joint`=:yp9, `swj_joint_drawing`=:yp10, `swj_quote`=:yp11


WHERE `asme_wdt_joint`.`swj_pk`=31

the field is swj_joint_type. Can this be helpful?

9

You also need to inspect the parameters that are bound. Try to add this to your db configuration:

<?php


'db' => array(


    ...


    'enableParamLogging' => true,


    ..


),
10

i have never seen something like this before.

look at the parameter :yp4.

This is the query generated with the enum with this values:

'Butt welding','Butt welding on pipe'



Executing SQL: UPDATE `asme_wdt_joint` SET `swj_pk`=:yp0, `swj_wps`=:yp1,


`swj_pqr`=:yp2, `swj_welding`=:yp3, `swj_joint_type`=:yp4,


`swj_backing`=:yp5, `swj_backing_material`=:yp6,


`swj_backing_material_type`=:yp7, `swj_diameter`=:yp8,


`swj_diameter_joint`=:yp9, `swj_joint_drawing`=:yp10, `swj_quote`=:yp11


WHERE `asme_wdt_joint`.`swj_pk`=31. Bind with parameter :yp0=31, :yp1=NULL,


:yp2=2, :yp3=NULL, :yp4='Butt welding', :yp5=0, :yp6=NULL, :yp7='nonfusing


metal', :yp8=NULL, :yp9=NULL, :yp10=NULL, :yp11=NULL

and it works.

If I add another value:

'Butt welding','Butt welding on pipe','Pipe angle joint'

where there is the word "joint" (I have tried the single word) this is the query:



Executing SQL: UPDATE `asme_wdt_joint` SET `swj_pk`=:yp0, `swj_wps`=:yp1,


`swj_pqr`=:yp2, `swj_welding`=:yp3, `swj_joint_type`=:yp4,


`swj_backing`=:yp5, `swj_backing_material`=:yp6,


`swj_backing_material_type`=:yp7, `swj_diameter`=:yp8,


`swj_diameter_joint`=:yp9, `swj_joint_drawing`=:yp10, `swj_quote`=:yp11


WHERE `asme_wdt_joint`.`swj_pk`=31. Bind with parameter :yp0=31, :yp1=NULL,


:yp2=2, :yp3=NULL, :yp4=0, :yp5=0, :yp6=NULL, :yp7='nonfusing metal',


:yp8=NULL, :yp9=NULL, :yp10=NULL, :yp11=NULL

the :yp4 is 0!!! 0 is not a valid value and then it saves the blank value…

:frowning:

11

Could you execute the following SQL in both cases and show what the result is?

SHOW CREATE TABLE tablename;

12

with some values with the word "joint" inside



CREATE TABLE `asme_wdt_joint` (


 `swj_pk` int(11) NOT NULL AUTO_INCREMENT,


 `swj_wps` int(11) DEFAULT NULL,


 `swj_pqr` int(11) DEFAULT NULL,


 `swj_welding` int(11) DEFAULT NULL,


 `swj_joint_type` enum('Butt welding','Butt welding on pipe','Pipe angle joint','T like joint','Angle welding') NOT NULL,


 `swj_backing` tinyint(1) DEFAULT '0',


 `swj_backing_material` varchar(128) DEFAULT NULL,


 `swj_backing_material_type` enum('metal','nonfusing metal','nonmetallic','other') DEFAULT NULL,


 `swj_diameter` float DEFAULT NULL,


 `swj_diameter_joint` float DEFAULT NULL,


 `swj_joint_drawing` int(11) DEFAULT NULL,


 `swj_quote` varchar(128) DEFAULT NULL,


 PRIMARY KEY (`swj_pk`),


 KEY `swj_wps` (`swj_wps`),


 KEY `swj_pqr` (`swj_pqr`),


 KEY `swj_joint_drawing` (`swj_joint_drawing`),


 KEY `swj_welding` (`swj_welding`),


 KEY `swj_joint_type` (`swj_joint_type`),


 CONSTRAINT `asme_wdt_joint_ibfk_4` FOREIGN KEY (`swj_wps`) REFERENCES `asme_wps` (`wps_pk`) ON DELETE CASCADE ON UPDATE CASCADE,


 CONSTRAINT `asme_wdt_joint_ibfk_7` FOREIGN KEY (`swj_pqr`) REFERENCES `asme_pqr` (`pqr_pk`) ON DELETE CASCADE ON UPDATE CASCADE,


 CONSTRAINT `asme_wdt_joint_ibfk_8` FOREIGN KEY (`swj_joint_drawing`) REFERENCES `joint_drawings` (`drw_pk`) ON DELETE SET NULL ON UPDATE CASCADE,


 CONSTRAINT `asme_wdt_joint_ibfk_9` FOREIGN KEY (`swj_welding`) REFERENCES `asme_weldings` (`wld_pk`) ON DELETE CASCADE ON UPDATE CASCADE


) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

without



CREATE TABLE `asme_wdt_joint` (


 `swj_pk` int(11) NOT NULL AUTO_INCREMENT,


 `swj_wps` int(11) DEFAULT NULL,


 `swj_pqr` int(11) DEFAULT NULL,


 `swj_welding` int(11) DEFAULT NULL,


 `swj_joint_type` enum('Butt welding','Butt welding on pipe') NOT NULL,


 `swj_backing` tinyint(1) DEFAULT '0',


 `swj_backing_material` varchar(128) DEFAULT NULL,


 `swj_backing_material_type` enum('metal','nonfusing metal','nonmetallic','other') DEFAULT NULL,


 `swj_diameter` float DEFAULT NULL,


 `swj_diameter_joint` float DEFAULT NULL,


 `swj_joint_drawing` int(11) DEFAULT NULL,


 `swj_quote` varchar(128) DEFAULT NULL,


 PRIMARY KEY (`swj_pk`),


 KEY `swj_wps` (`swj_wps`),


 KEY `swj_pqr` (`swj_pqr`),


 KEY `swj_joint_drawing` (`swj_joint_drawing`),


 KEY `swj_welding` (`swj_welding`),


 KEY `swj_joint_type` (`swj_joint_type`),


 CONSTRAINT `asme_wdt_joint_ibfk_4` FOREIGN KEY (`swj_wps`) REFERENCES `asme_wps` (`wps_pk`) ON DELETE CASCADE ON UPDATE CASCADE,


 CONSTRAINT `asme_wdt_joint_ibfk_7` FOREIGN KEY (`swj_pqr`) REFERENCES `asme_pqr` (`pqr_pk`) ON DELETE CASCADE ON UPDATE CASCADE,


 CONSTRAINT `asme_wdt_joint_ibfk_8` FOREIGN KEY (`swj_joint_drawing`) REFERENCES `joint_drawings` (`drw_pk`) ON DELETE SET NULL ON UPDATE CASCADE,


 CONSTRAINT `asme_wdt_joint_ibfk_9` FOREIGN KEY (`swj_welding`) REFERENCES `asme_weldings` (`wld_pk`) ON DELETE CASCADE ON UPDATE CASCADE


) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

with phpmyadmin I can save everything correctly.

13

Sorry, need more information. Could you execute the following SQL and show the result here? Thanks.

SHOW COLUMNS FROM tablename;

14

attached the 2 printscreen with the result of the query requested with and without "joint"

15

Thanks. I just fixed this bug. It is caused by incorrectly typecasting enum-typed column values by AR.

16

come on qiang!

you just have to say that you have disabled drug protection.

;D ;D ;D