ERROR 1366 (HY000): Incorrect integer value: '' for column 'config_device_type'

[jacobweber]

I'm upgrading Piwik from 2.17.1 to 3.2.1. It's using PHP 5.6.29 and MySQL 5.7.18.

During the upgrade, it failed when it ran the following query:

ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visit_goal_buyer` TINYINT(1) NULL, MODIFY COLUMN `visit_goal_converted` TINYINT(1) NULL, MODIFY COLUMN `visitor_days_since_first` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `visitor_days_since_order` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `visitor_returning` TINYINT(1) NULL, MODIFY COLUMN `visitor_count_visits` INT(11) UNSIGNED NOT NULL, MODIFY COLUMN `visit_entry_idaction_name` INTEGER(10) UNSIGNED NULL, MODIFY COLUMN `visit_exit_idaction_name` INTEGER(10) UNSIGNED NULL, MODIFY COLUMN `visit_exit_idaction_url` INTEGER(10) UNSIGNED NULL DEFAULT 0, MODIFY COLUMN `visit_total_actions` INT(11) UNSIGNED NULL, ADD COLUMN `visit_total_interactions` SMALLINT UNSIGNED DEFAULT 0, MODIFY COLUMN `visit_total_searches` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `referer_url` TEXT NULL, MODIFY COLUMN `location_browser_lang` VARCHAR(20) NULL, MODIFY COLUMN `config_browser_engine` VARCHAR(10) NULL, MODIFY COLUMN `config_browser_name` VARCHAR(10) NULL, MODIFY COLUMN `config_browser_version` VARCHAR(20) NULL, MODIFY COLUMN `config_device_brand` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, MODIFY COLUMN `config_device_model` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, MODIFY COLUMN `config_device_type` TINYINT( 100 ) NULL DEFAULT NULL, MODIFY COLUMN `config_os` CHAR(3) NULL, MODIFY COLUMN `visit_total_events` INT(11) UNSIGNED NULL, MODIFY COLUMN `visitor_localtime` TIME NULL, MODIFY COLUMN `visitor_days_since_last` SMALLINT(5) UNSIGNED NULL, MODIFY COLUMN `config_resolution` VARCHAR(18) NULL, MODIFY COLUMN `config_cookie` TINYINT(1) NULL, MODIFY COLUMN `config_director` TINYINT(1) NULL, MODIFY COLUMN `config_flash` TINYINT(1) NULL, MODIFY COLUMN `config_gears` TINYINT(1) NULL, MODIFY COLUMN `config_java` TINYINT(1) NULL, MODIFY COLUMN `config_pdf` TINYINT(1) NULL, MODIFY COLUMN `config_quicktime` TINYINT(1) NULL, MODIFY COLUMN `config_realplayer` TINYINT(1) NULL, MODIFY COLUMN `config_silverlight` TINYINT(1) NULL, MODIFY COLUMN `config_windowsmedia` TINYINT(1) NULL, MODIFY COLUMN `visit_total_time` INT(11) UNSIGNED NOT NULL, MODIFY COLUMN `location_country` CHAR(3) NULL, MODIFY COLUMN `location_latitude` decimal(9, 6) DEFAULT NULL, MODIFY COLUMN `location_longitude` decimal(9, 6) DEFAULT NULL;

The error is:

ERROR 1366 (HY000): Incorrect integer value: '' for column 'config_device_type' at row 6963868

This is apparently because there are many rows in this column with empty-string values. I don't know whether it's safe/correct to convert the empty-string values to null before re-running this query.

[sgiehl]

Converting empty values to NULL should be good solution

[vbudithi]

There is no other way to get a solution except changing all the empty values to NULL or '0'.

[sgiehl]

As we didn't have any further reports about that in the past year, I'll close this issue now.