Error: Data isn't compatible with new type

2 posts by 2 authors in: Forums > CMS Builder
Last Post: about an hour ago (RSS)

Hit this on the latest version. Created a field as a Text Box by mistake, then went to switch its type to Checkbox in the field editor. The save fails with a MySQL type-compatibility error.

Steps to reproduce

In a section editor, add a new field and set the type to Text Box.
Save. (The column gets created across every existing record in the table.)
Edit that same field and change the type to Checkbox.
Save → error.

Error returned

Error: Data in `my_field` isn't compatible with new type:
tinyint unsigned NOT NULL DEFAULT 0

Please modify your data or column type and try again.

Query: ALTER TABLE `cms_example` CHANGE COLUMN `my_field` `my_field`
       tinyint unsigned NOT NULL DEFAULT 0
in [path]/cmsb/lib/Fields/BaseField.php:510  [Admin View]

Backtrace (CMS paths only)

#0 Script started        cmsb/admin.php:0
#1 require()             cmsb/admin.php:82
#2 include()             cmsb/lib/menus/default/actionHandler.php:65
#3 require()             cmsb/lib/menus/database/actionHandler.php:72
#4 require()             cmsb/lib/menus/database/editField.php:8
#5 submitFormViaAjax()   cmsb/lib/menus/database/editField_functions.php:179
...

Thanks,

Kenny H

By Dave - about an hour ago

Hi Kenny,

The issue is that a checkbox column doesn't support the same data types as a text box. A text box stores NULL by default (or an empty string once you've saved content), but a checkbox only supports 1 or 0, never NULL. It's the same even if the text box had real content in it, since MySQL can't map arbitrary text to a number.

If the field is empty, the easiest fix is to just erase it and create the checkbox fresh. Or you can convert the existing values to 0 first:

UPDATE `your_table` SET `my_field` = 0 WHERE `my_field` IS NULL OR `my_field` = '';

Once the data in the column matches what a checkbox supports (in MySQL: tinyint unsigned NOT NULL), it should let you switch the type with no problem.

Hope that helps! Let me know any questions.

Dave Edis - Senior Developer
interactivetools.com