1.8.4 archive.php - SQLSTATE[42S22]: Column not found: 1054 Unknown column '-1' in 'field list'

Support & Bugs
1

Hi all,

after upgrading from a perfectly working 1.8.3 to 1.8.4, the archiving is now broken.

Error message:

ERROR: 10 total errors during this script execution, please investigate and try and fix these errors. First error was: Got invalid response from API request: https:///piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=1&period=day&date=last52&format=php&token_auth=&trigger=archivephp. Response was ‘a:2:{s:6:“result”;s:5:“error”;s:7:“message”;s:75:“SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘-1’ in ‘field list’”;}’

1.8.3 was working fine, the database upgrade to 1.8.4 was also running without errors (was about 1 hour of runtime). The live visitor stats are still working but the archiving is broken.

I deleted all archive tables, reuploaded the piwik files and did the database upgrade a second time to be sure that all is working fine but the problem still exists.

More information:

PHP 5.3.14 (x64)
MySQL 5.5.25a
log_link_visit_action: ~ 40million rows
log_action: ~13 million rows
log_visit: ~ 17 million rows
websites configured: 12

No settings have been changed manualy before and after the upgrade to 1.8.4

What causes this problem? It seems that piwik is still collecting data but I can’t see it because archiving is not working.

Thanks a lot!

2

It looks like a bug!

Are you using some third party plugins in your piwik?

Can you apply the patch:


--- core/DataTable/Renderer/Xml.php	(revision 7082)
+++ core/DataTable/Renderer/Xml.php	(revision )
@@ -42,7 +42,7 @@
 	{
 		$this->renderHeader();
 
-		$exceptionMessage = self::renderHtmlEntities($this->exception->getMessage());
+		$exceptionMessage = self::renderHtmlEntities($this->exception->getMessage() . $this->exception->getTraceAsString());

Then, go to the URL generating the error, which should now display the strack trace - what is it?

3

Hi Matt,

thanks for looking into this! I needed to patch the core/DataTable/Renderer/Php.php file.

Here’s the slightly formated and anonymized output:

/usr/local/bin/php //piwik/misc/cron/archive.php --url=https:///piwik/

ERROR: Got invalid response from API request:
https:///piwik/index.php?module=API&method=VisitsSummary.getVisits&idSite=1&period=day&date=last52&format=php&token_auth=&trigger=archivephp.
Response was
’a:2:{s:6:“result”;s:5:“error”;s:7:“message”;s:2788:“SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘-1’ in ‘field list’
#0 //piwik/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->execute(Array)
#1 //piwik/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 //piwik/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’???SELECT???..’, Array)
#3 //piwik/core/Db/Adapter/Pdo/Mysql.php(220): Zend_Db_Adapter_Pdo_Abstract->query(’???SELECT???..’, Array)
#4 //piwik/plugins/Actions/Actions.php(767): Piwik_Db_Adapter_Pdo_Mysql->query(’???SELECT???..’, Array)
#5 //piwik/plugins/Actions/Actions.php(574): Piwik_Actions->archiveDayQueryProcess(‘log_action.name…’, Array, 'log_link_visit…’, ’12 DESC, name…’, ‘log_action.idac…’, ‘idaction_url’, Object(Piwik_ArchiveProcessing_Day), Object(Piwik_RankingQuery))
#6 [internal function]: Piwik_Actions->archiveDay(Object(Piwik_Event_Notification))
#7 //piwik/libs/Event/Dispatcher.php(284): call_user_func_array(Array, Array)
#8 //piwik/core/PluginsManager.php(668): Event_Dispatcher->postNotification(Object(Piwik_Event_Notification), false, false)
#9 //piwik/core/ArchiveProcessing/Day.php(45): Piwik_PostEvent(‘ArchiveProcessi…’, Object(Piwik_ArchiveProcessing_Day))
#10 //piwik/core/ArchiveProcessing.php(498): Piwik_ArchiveProcessing_Day->compute()
#11 //piwik/core/Archive/Single.php(226): Piwik_ArchiveProcessing->launchArchiving()
#12 //piwik/core/Archive/Single.php(256): Piwik_Archive_Single->prepareArchive()
#13 //piwik/core/Archive/Single.php(447): Piwik_Archive_Single->get(‘nb_visits’, ‘numeric’)
#14 //piwik/core/Archive/Array.php(93): Piwik_Archive_Single->getNumeric(‘nb_visits’)
#15 //piwik/plugins/VisitsSummary/API.php(114): Piwik_Archive_Array->getNumeric(‘nb_visits’)
#16 //piwik/plugins/VisitsSummary/API.php(120): Piwik_VisitsSummary_API->getNumeric(‘1’, ‘day’, ‘last52’, false, ‘nb_visits’)
#17 [internal function]: Piwik_VisitsSummary_API->getVisits(‘1’, ‘day’, ‘last52’, false)
#18 //piwik/core/API/Proxy.php(190): call_user_func_array(Array, Array)
#19 //piwik/core/API/Request.php(128): Piwik_API_Proxy->call(‘Piwik_VisitsSum…’, ‘getVisits’, Array)
#20 //piwik/plugins/API/Controller.php(27): Piwik_API_Request->process()
#21 [internal function]: Piwik_API_Controller->index()
#22 //piwik/core/FrontController.php(138): call_user_func_array(Array, Array)
#23 //piwik/index.php(53): Piwik_FrontController->dispatch()
#24 {main}”;}’

The only plugin i was using some weeks ago was the GeoIP plugin. It’s disabled now but this doesn’t change anything on the above error :frowning: The rest is pure piwik 1.8.4

4

Hi Matt,

I’ve got the same problem. I use MySQL 5.0.91 and PHP 5.2.6.
There are no additional plugins installed. Only the ones of the main version.

Do you need any further informations?

5

That is really surprising.

  • Can you please upgrade Mysql to a more recent version, I think this could be a mysql bug
  • Otherwise please upgrade Piwik to latest beta: 301 Moved Permanently

If you still experience this bug after the 2 updates, post here, I will follow up as this would be definitely a new bug!

6

I upgraded MySQL to 5.5.28 (latest) and Piwik to 1.9-b8 (there were no database changes or other changes while running CoreUpdater).

archive.php is still failing with above errors, some archive_blob and archive_numeric tables are created (2011_01, 2011_11, 2012_03, 2012_08, 2012_09, 2012_10).
The same errors appear when i let piwik update its stats from the browser.

Is there anything else for me to post that can help tracking down this problem?

7

in Actions.php(767) can you do var_dump($query) to print out the full query used? we can see if the query is different from what is expected. Thanks

8

Hi Matt,

as of I’m on piwik 1.9-b8 now, i patched plugins/Actions/Archiving.php (394) and got this query:

SELECT

                            CASE
                                    WHEN counter = 50001 THEN "-1" 
                                    ELSE `idaction`
                            END AS `idaction`
                    , 
                            CASE
                                    WHEN counter = 50001 THEN "-1" 
                                    ELSE `name`
                            END AS `name`

                            , `url_prefix`, `1`, sum(`12`) AS `12`, sum(`2`) AS `2`, `type`
                    FROM ( 
                    SELECT
                            `idaction`, `name`,

                    CASE
                            WHEN `type` = 1 AND @counter1 = 50001 THEN 50001
                            WHEN `type` = 1 THEN @counter1:=@counter1+1
                            WHEN `type` = 2 AND @counter2 = 50001 THEN 50001
                            WHEN `type` = 2 THEN @counter2:=@counter2+1
                            WHEN `type` = 3 AND @counter3 = 50001 THEN 50001
                            WHEN `type` = 3 THEN @counter3:=@counter3+1
                            WHEN `type` = 4 AND @counter4 = 50001 THEN 50001
                            WHEN `type` = 4 THEN @counter4:=@counter4+1
                            ELSE 0
                    END
             AS counter
                            , `url_prefix`, `1`, `12`, `2`, `type`
                    FROM
                            ( SELECT @counter1:=0 ) initCounter1, ( SELECT @counter2:=0 ) initCounter2, ( SELECT @counter3:=0 ) initCounter3, ( SELECT @counter4:=0 ) initCounter4, 
                            ( 
                    SELECT
                            log_action.name,
                            log_action.type,
                            log_action.idaction,
                            log_action.url_prefix,
                            count(distinct log_link_visit_action.idvisit) as `2`,
                            count(distinct log_link_visit_action.idvisitor) as `1`,
                            count(*) as `12`
                    FROM
                            log_link_visit_action AS log_link_visit_action
                            LEFT JOIN log_action AS log_action ON log_link_visit_action.idaction_url = log_action.idaction
                    WHERE
                            log_link_visit_action.server_time >= ?
                            AND log_link_visit_action.server_time <= ?
                            AND log_link_visit_action.idsite = ?
                            AND log_link_visit_action.idaction_url IS NOT NULL
                    GROUP BY
                            log_action.idaction
                    ORDER BY
                            `12` DESC, name ASC ) actualQuery
             ) AS withCounter
                    GROUP BY counter, `type`
9

  • I would recommend to update Mysql. what version are you using? I bet it’s an old one, since this looks like a mysql bug. EDIT: re-read your post, wow I’m really suprised at this error in your recent version of mysql. Le’ts find out why I hope.

  • can you try the patch?


--- core/RankingQuery.php	(revision 7136)
+++ core/RankingQuery.php	(revision )
@@ -288,7 +288,7 @@
 		{
 			$labelColumnsOthersSwitch[] = "
 				CASE
-					WHEN counter = $limit THEN \"{$this->othersLabelValue}\" 
+					WHEN counter = $limit THEN '{$this->othersLabelValue}'
 					ELSE `$column`
 				END AS `$column`
 			";
10

Great, now it works for me.

After changing to single quotes the archiving works without errors.

Thank you matt

11

Hi Matt,

after changing the line it also works here! Thanks a lot!

There needs to be some tunes in how piwik is archiving data. Even that i have “max_allowed_packet = 512M” in my my.cnf file, after 2 hours i get “SQLSTATE[HY000]: General error: 2006 MySQL server has gone away”. Piwik was not yet done with Site 1 for a complete archive rewriting.

I’ll retry it with 1GB packet size.

12

Hi Matt,

I had a similar issue when I upgraded from 1.8.3 to 1.8.4.
I’m using no extension, no nothing, just the plain product as shipped.
My config:

  • PHP: 5.2.17
  • MySQL: 5.0.95-log

I’ve been going through the various steps you suggested above, incl. migrating to 1.9-b13, but this hasn’t helped me out so far.

After patching core/DataTable/Renderer/Xml.php, I can see the following in the first widget (top left) of the default homepage:

::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_action.url_prefix’ in ‘field list’

Backtrace:

#0 /piwik/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->execute(Array)
#1 /piwik/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /piwik/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’???SELECT???..’, Array)
#3 /piwik/core/Db/Adapter/Pdo/Mysql.php(220): Zend_Db_Adapter_Pdo_Abstract->query(’???SELECT???..’, Array)
#4 /piwik/plugins/Actions/Archiving.php(394): Piwik_Db_Adapter_Pdo_Mysql->query(’???SELECT???..’, Array)
#5 /piwik/plugins/Actions/Archiving.php(136): Piwik_Actions_Archiving->archiveDayQueryProcess(‘log_action.name…’, Array, 'log_link_visit…’, ’12 DESC, name…’, ‘log_action.idac…’, ‘idaction_url’, Object(Piwik_ArchiveProcessing_Day), Object(Piwik_RankingQuery))
#6 /piwik/plugins/Actions/Archiving.php(85): Piwik_Actions_Archiving->archiveDayActions(Object(Piwik_ArchiveProcessing_Day), ‘50000’)
#7 /piwik/plugins/Actions/Actions.php(450): Piwik_Actions_Archiving->archiveDay(Object(Piwik_ArchiveProcessing_Day))
#8 [internal function]: Piwik_Actions->archiveDay(Object(Piwik_Event_Notification))
#9 /piwik/libs/Event/Dispatcher.php(284): call_user_func_array(Array, Array)
#10 /piwik/core/PluginsManager.php(668): Event_Dispatcher->postNotification(Object(Piwik_Event_Notification), false, false)
#11 /piwik/core/ArchiveProcessing/Day.php(45): Piwik_PostEvent(‘ArchiveProcessi…’, Object(Piwik_ArchiveProcessing_Day))
#12 /piwik/core/ArchiveProcessing.php(498): Piwik_ArchiveProcessing_Day->compute()
#13 /piwik/core/Archive/Single.php(236): Piwik_ArchiveProcessing->launchArchiving()
#14 /piwik/core/Archive/Single.php(266): Piwik_Archive_Single->prepareArchive()
#15 /piwik/core/Archive/Single.php(462): Piwik_Archive_Single->get(‘nb_visits’, ‘numeric’)
#16 /piwik/core/ArchiveProcessing/Period.php(95): Piwik_Archive_Single->getNumeric(‘nb_visits’)
#17 /piwik/core/ArchiveProcessing/Period.php(49): Piwik_ArchiveProcessing_Period->archiveNumericValuesGeneral(Array, ‘sum’)
#18 /piwik/core/ArchiveProcessing/Period.php(302): Piwik_ArchiveProcessing_Period->archiveNumericValuesSum(Array)
#19 /piwik/core/ArchiveProcessing/Period.php(269): Piwik_ArchiveProcessing_Period->isThereSomeVisits()
#20 /piwik/core/ArchiveProcessing.php(498): Piwik_ArchiveProcessing_Period->compute()
#21 /piwik/core/Archive/Single.php(236): Piwik_ArchiveProcessing->launchArchiving()
#22 /piwik/core/Archive/Array/IndexedByDate.php(90): Piwik_Archive_Single->prepareArchive()
#23 /piwik/plugins/VisitsSummary/API.php(88): Piwik_Archive_Array_IndexedByDate->getDataTableFromNumeric(Array)
#24 [internal function]: Piwik_VisitsSummary_API->get(‘1’, ‘week’, ‘2012-04-16,2012…’, false, ‘nb_visits’)
#25 /piwik/core/API/Proxy.php(190): call_user_func_array(Array, Array)
#26 /piwik/plugins/API/API.php(1045): Piwik_API_Proxy->call(‘Piwik_VisitsSum…’, ‘get’, Array)
#27 [internal function]: Piwik_API_API->get(‘1’, ‘week’, ‘2012-04-16,2012…’, false, ‘nb_visits’)
#28 /piwik/core/API/Proxy.php(190): call_user_func_array(Array, Array)
#29 /piwik/core/API/Request.php(128): Piwik_API_Proxy->call(‘Piwik_API_API’, ‘get’, Array)
#30 /piwik/core/ViewDataTable.php(425): Piwik_API_Request->process()
#31 /piwik/core/ViewDataTable/GenerateGraphData/ChartEvolution.php(109): Piwik_ViewDataTable->loadDataTableFromAPI()
#32 /piwik/core/ViewDataTable/GenerateGraphData.php(181): Piwik_ViewDataTable_GenerateGraphData_ChartEvolution->loadDataTableFromAPI()
#33 /piwik/core/Controller.php(236): Piwik_ViewDataTable_GenerateGraphData->main()
#34 /piwik/plugins/VisitsSummary/Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins(‘VisitsSummary’, ‘getEvolutionGra…’, Array, Array, ‘Ceci est un ape…’)
#35 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#36 /piwik/core/FrontController.php(138): call_user_func_array(Array, Array)
#37 /piwik/core/FrontController.php(160): Piwik_FrontController->dispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#38 /piwik/core/ViewDataTable/GenerateGraphHTML.php(187): Piwik_FrontController->fetchDispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#39 /piwik/core/ViewDataTable/GenerateGraphHTML.php(141): Piwik_ViewDataTable_GenerateGraphHTML->getGraphData()
#40 /piwik/core/ViewDataTable/GenerateGraphHTML.php(125): Piwik_ViewDataTable_GenerateGraphHTML->buildView()
#41 /piwik/core/Controller.php(236): Piwik_ViewDataTable_GenerateGraphHTML->main()
#42 /piwik/plugins/VisitsSummary/Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins(‘VisitsSummary’, ‘getEvolutionGra…’, Array, Array, ‘Ceci est un ape…’)
#43 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#44 /piwik/core/FrontController.php(138): call_user_func_array(Array, Array)
#45 /piwik/index.php(53): Piwik_FrontController->dispatch()
#46 {main}
::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::~::

Note - The patch on RankingQuery.php doesn’t work for me, as it generates a syntax error, probably because of the old version of MySQL I have:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN counter = 50001 THEN ‘-1’ ’ at line 4

Being dependent on my host, I have no means to upgrade MySQL.

Anything you can think of to help me?
Thanks in advance.

13

GFC, the error


SQLSTATE[42S22]: Column not found: 1054 Unknown column 'log_action.url_prefix' in 'field list'

Means that, the update that was creating this field, didn’t work well/. Which version were you updating from?

you can try to run the query:


UPDATE piwik_option SET value='1.8.3' WHERE name='version_core'

This will tell piwik to re-run the update script from 1.8.3 upwards

14

Thanks Matt, but it didn’t work (same error). I was actually upgrading from 1.8.3 to 1.8.4 when I first had the problem.

I’ve installed the latest 1.9 and now I’m getting the err msg reported in this other thread:
http://forum.piwik.org/read.php?2,95058

I see that you’re recommending running that same UPDATE query. But that doesn’t help either.

I’m not sure I fully understand mvandek’s recos to delete 1.9 files and restore back the 1.8 ones. Any better step by step explanation for the dummies?

Thanks!

15

It should work, are you sure the updates files are correctly uploaded in core/Updates/1.9-bXXX.php ?

16

Hi Matt,

with current piwik 1.9 i still need to patch core/RankingQuery.php as mentioned above. Will there be an official fix in a future release?

17

Ok, can you please create a ticket for the bug, and mention that the patch worked for you? and the patch itself. we’ll fix it. Thakns!

18

The ticket is created: archive.php - SQLSTATE[42S22]: Column not found: 1054 Unknown column '-1' in 'field list' · Issue #3453 · matomo-org/matomo · GitHub

Meanwhile i found another SQL problem, this time in the new Transitions plugin. I added this to the ticket too.

19

Hi again Matt,
Just to let you know I’m fine now.
Not sure what was wrong, but I was trying to restore to 1.8.4 before upgrading again to 1.9 when actually I needed to get back to 1.8.3 and start again from there.
Nice to pick advices from various posts here around.
Best thoughts for 2.0!

20

The bug was fixed and it will be in next version coming soon!