Every Widget Generates SQLSTATE Column not found Error

[superscotty19]

TYPO3 6.2LTS, Downloaded piwikintegration from github today. Couldn't get any data -> traced it to PHP error that plugins were acting up with "Column Not Found" errors. Deactivated plugins so as to get Piwik to show a useful dashboard. Now the problem is, every widget on the dashboard (except donate ;-) ) throws a "Column Not Found" Error. If I try to bypass the widgets and go directly to a spot, say, Visitors, I get "Oops… there was a problem during the request. Maybe the server had a temporary issue, or maybe you requested a report with too much data. Please try again. If this error occurs repeatedly please contact your Piwik administrator for assistance."

[kaystrobach]

@superscotty19 please specify the following versions:

• php
• TYPO3
• piwik

Thanks a lot

Hopefully they have not changed the internal API so heavily again :(

[superscotty19]

Thanks for the fast reply! I think they have :-(

I'm going to try to disable as many plugins as possible and report back what I find. In the meantime, I can enable Diagnostics and see that I have PHP: 5.4.41 TYPO3: 6.2.12LTS Piwik: 2.13.1 Piwikintegration: 4.0.0

[superscotty19]

Update: As expected, deactivating a bunch of plugins was useless. I now only have the following active plugins: Actions (Core) Contents (Core) Dashboard (Core) DevicesDetection (Core) Diagnostics (Core) ImageGraph (Core) Insights (Core) MultiSites (Core) Overlay (Core) PrivacyManager (Core) Provider (Core) Referrers (Core) Resolution (Core) ScheduledReports (Core) SEO (Core) Transitions (Core) TYPO3Login (v2.13.1) TYPO3Widgets (v2.13.1) UserCountry (Core) UserCountryMap (Core) UserLanguage (Core) VisitFrequency (Core) VisitorInterest (Core) VisitsSummary (Core) VisitTime (Core) ... but every widget on the dashboard still gives "Column not found", and if I want to add a widget, the preview is "Column not found" as well.

[superscotty19]

Update (no solution):

• Tried turning on maintenance mode (as per http://piwik.org/faq/how-to/#faq_111 )
• Investigated forcing re-upgrade (as per http://piwik.org/faq/how-to-update/faq_179/ ), but the "version_core" value of table "user_piwikintegration_option" was already set to 2.13.1

[superscotty19]

Update (closer, but still no solution): The specific errors in the install are thrice: 1.) browser_lang 2.) DeviceDetection Plugin 3.) 'log_link_visit_action.custom_var_v5' (as seen in my initial attachment)

Solutions, I have 2/3:

1.) ALTER TABLE user_piwikintegration_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL; 2.) ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_os_version VARCHAR( 100 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_device_type VARCHAR( 100 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_device_brand VARCHAR( 100 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_device_model VARCHAR( 100 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_os VARCHAR( 100 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_browser_version VARCHAR( 20 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_browser_name VARCHAR( 100 ) DEFAULT NULL; ALTER TABLE user_piwikintegration_log_visit ADD COLUMN config_browser_engine VARCHAR( 100 ) DEFAULT NULL;

3.) ???

We figure out (3), we have this solved.

[superscotty19]

* SOLUTION *

Ignore the SQL statements above; here is the final solution for TYPO3 6.2LTS + piwikintegration +:piwik 2.13.1

1.) In a shell window, turn on PHP PDO and download piwikintegration: nano php.ini ;# required for Piwik extension=pdo.so extension=pdo_mysql.so wget https://github.com/kaystrobach/TYPO3.piwikintegration/archive/master.zip unzip master mv TYPO3... piwikintegration

2.) In TYPO3 Extension Manager: install piwik and piwikintegration include piwikintegration in TS template click statistics -> piwik will download (and most likely cause a 500 error)

Back to shell window: cd public_html/typo/typo3conf/ chmod 755 piwik

3.) TYPO3 -> Statistics (Piwik)

a.) DO NOT CLICK UPGRADE piwik -> Go to PuTTY to perform update by typing: php /path/to/typo/typo3conf/piwik/piwik/console core:update

You will get an error like it couldn't find "os_device" or something. So, go to PHPmyAdmin and:

    ALTER TABLE `user_piwikintegration_log_visit`
    ADD COLUMN `location_browser_lang` VARCHAR(20) NOT NULL;

ALTER TABLE `user_piwikintegration_log_visit` 
    ADD COLUMN `config_os_version` VARCHAR( 100 ) DEFAULT NULL,
    ADD COLUMN `config_device_type` VARCHAR( 100 ) DEFAULT NULL,
    ADD COLUMN `config_device_brand` VARCHAR( 100 ) DEFAULT NULL,
    ADD COLUMN `config_device_model` VARCHAR( 100 ) DEFAULT NULL,
    ADD COLUMN `config_os` VARCHAR( 100 ) DEFAULT NULL,
    ADD COLUMN `config_browser_version` VARCHAR( 20 ) DEFAULT NULL,
    ADD COLUMN `config_browser_name` VARCHAR( 100 ) DEFAULT NULL,
    ADD COLUMN `config_browser_engine` VARCHAR( 100 ) DEFAULT NULL;

ALTER TABLE `user_piwikintegration_log_link_visit_action` 
    ADD COLUMN `custom_var_k1` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v1` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k2` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v2` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k3` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v3` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k4` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v4` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k5` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v5` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `idaction_event_category` INTEGER(10) UNSIGNED,
        ADD COLUMN `idaction_event_action` INTEGER(10) UNSIGNED;

ALTER TABLE `user_piwikintegration_log_visit` 
    ADD COLUMN `custom_var_k1` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v1` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k2` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v2` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k3` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v3` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k4` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v4` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_k5` VARCHAR(50) DEFAULT NULL,
        ADD COLUMN `custom_var_v5` VARCHAR(50) DEFAULT NULL;

ALTER TABLE `user_piwikintegration_log_conversion` 
    ADD COLUMN `revenue` FLOAT DEFAULT NULL,
    ADD COLUMN `revenue_subtotal` FLOAT DEFAULT NULL,
    ADD COLUMN `revenue_tax` FLOAT DEFAULT NULL,
    ADD COLUMN `revenue_shipping` FLOAT DEFAULT NULL,
    ADD COLUMN `revenue_discount` FLOAT DEFAULT NULL;

b.) Admin -> Plugins -> Only run these plugins: Actions (Core) Contents (Core) Dashboard (Core) DevicesDetection (Core) Diagnostics (Core) ImageGraph (Core) Insights (Core) MultiSites (Core) Overlay (Core) PrivacyManager (Core) Provider (Core) Referrers (Core) Resolution (Core) ScheduledReports (Core) SEO (Core) Transitions (Core) TYPO3Login (v2.13.1) TYPO3Widgets (v2.13.1) UserCountry (Core) UserCountryMap (Core) UserLanguage (Core) VisitFrequency (Core) VisitorInterest (Core) VisitsSummary (Core) VisitTime (Core)

Debugging.) nano /public_html/php.ini log_errors = On error_log = /path/to/public_html/php_errors.log -> run a site, watch error log grow!

With that, piwikintegration 4.0.0 works with Piwik 2.13.1 :-)

Don't ask me for my references, because that's a thread in itself :-P . I just went error by error finding the missing column, looking up what it should be (varchar, float, etc.) and adding it to appropriate column.

[kaystrobach]

Thanks a lot for investigating!

so it was a broken update script? because of missing PDO? sounds scary