Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
jacobweber opened this issue Feb 4, 2018 · 3 comments
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@jacobweber
Copy link

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
Copy link
Member

sgiehl commented Feb 4, 2018

Converting empty values to NULL should be good solution

@vbudithi
Copy link

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

@Findus23 Findus23 added the Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. label May 24, 2020
@sgiehl
Copy link
Member

sgiehl commented Nov 13, 2022

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

@sgiehl sgiehl closed this as completed Nov 13, 2022
@sgiehl sgiehl added answered For when a question was asked and we referred to forum or answered it. and removed Potential Bug Something that might be a bug, but needs validation and confirmation it can be reproduced. labels Nov 13, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Development

No branches or pull requests

4 participants