Data truncation with SELECT...INSERT

[Foamcow]

I'm trying to copy a whole load of rows from one table to another.
The tables should be identical since the same SQL was used to create them both.

Here's the SELECT...INSERT I'm using

insert into jobarchive (
id,consultantId,branchId,reference,postDate,location,type,timeType,title,description,status,salaryFrom,salaryPre,salaryTo,salaryPeriod,salaryOptions,views,archive,lastUpdate,created
) 
SELECT 
id,consultantId,branchId,reference,postDate,location,type,timeType,title,description,status,salaryFrom,salaryPre,salaryTo,salaryPeriod,salaryOptions,views,archive,lastUpdate,created 
FROM jobs WHERE archive="yes"

But when this runs I get the following error

Data truncation: Data truncated fro column 'timeType' at row 92.

timeType is an enum column
enum('Full Time','Part Time')

none of the rows being moved have a value different to either option.
Another enum column in the table seems to copy over perfectly well.

Help!? Any idea what is going on here?

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[Foamcow]

No probs...

timeType was empty for some of the rows.

Fixed it now by refreshing my test data with something more up to date.


<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[guelphdad]

you have just discovered one of the reasons that ENUM isn't all that great.

 

[Foamcow]

It is in this case. At least for me.
It shouldn't be possible to have it blank. It was only blank in some rows because I'd been breaking things. When I went back to my original test data it worked fine.

In the real application that column cannot be blank unless things have gone horribly wrong - in which case that will be the least of my worries

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[guelphdad]

you can also insert whatever value you feel like in an ENUM. if you declare your ENUM as YES and NO you can insert WHATEVER into it as well. mind you it will get converted. check the mysql manual, it even warns about unpredictability of using ENUM. in short, avoid it.