Incorrect integer 'idsite'

After an upgrade from 1.7.1 to 1.8 i’m receiving the following error for several websites. The only website that is not giving this error is the ‘main’ website i added during the installation of 1.7.1.

Any suggestions?

SQLSTATE[HY000]: General error: 1366 Incorrect integer value: ‘’ for column ‘idsite’ at row 1

Go to Piwik
Login
Backtrace:

#0 C:\piwik\core\ArchiveProcessing.php(691): Zend_Db_Adapter_Pdo_Abstract->exec(‘INSERT INTO pk_…’)
#1 C:\piwik\core\ArchiveProcessing.php(533): Piwik_ArchiveProcessing->loadNextIdarchive()
#2 C:\piwik\core\ArchiveProcessing.php(472): Piwik_ArchiveProcessing->initCompute()
#3 C:\piwik\core\Archive\Single.php(226): Piwik_ArchiveProcessing->launchArchiving()
#4 C:\piwik\core\Archive\Array\IndexedByDate.php(90): Piwik_Archive_Single->prepareArchive()
#5 C:\piwik\plugins\VisitsSummary\API.php(88): Piwik_Archive_Array_IndexedByDate->getDataTableFromNumeric(Array)
#6 [internal function]: Piwik_VisitsSummary_API->get(‘2’, ‘day’, ‘2012-05-02,2012…’, false, ‘nb_visits’)
#7 C:\piwik\core\API\Proxy.php(190): call_user_func_array(Array, Array)
#8 C:\piwik\plugins\API\API.php(986): Piwik_API_Proxy->call(‘Piwik_VisitsSum…’, ‘get’, Array)
#9 [internal function]: Piwik_API_API->get(‘2’, ‘day’, ‘2012-05-02,2012…’, false, ‘nb_visits’)
#10 C:\piwik\core\API\Proxy.php(190): call_user_func_array(Array, Array)
#11 C:\piwik\core\API\Request.php(128): Piwik_API_Proxy->call(‘Piwik_API_API’, ‘get’, Array)
#12 C:\piwik\core\ViewDataTable.php(421): Piwik_API_Request->process()
#13 C:\piwik\core\ViewDataTable\GenerateGraphData\ChartEvolution.php(109): Piwik_ViewDataTable->loadDataTableFromAPI()
#14 C:\piwik\core\ViewDataTable\GenerateGraphData.php(181): Piwik_ViewDataTable_GenerateGraphData_ChartEvolution->loadDataTableFromAPI()
#15 C:\piwik\core\Controller.php(247): Piwik_ViewDataTable_GenerateGraphData->main()
#16 C:\piwik\plugins\VisitsSummary\Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins(‘VisitsSummary’, ‘getEvolutionGra…’, Array, Array, ‘Dit is een over…’)
#17 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#18 C:\piwik\core\FrontController.php(138): call_user_func_array(Array, Array)
#19 C:\piwik\core\FrontController.php(159): Piwik_FrontController->dispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#20 C:\piwik\core\ViewDataTable\GenerateGraphHTML.php(170): Piwik_FrontController->fetchDispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#21 C:\piwik\core\ViewDataTable\GenerateGraphHTML.php(125): Piwik_ViewDataTable_GenerateGraphHTML->getGraphData()
#22 C:\piwik\core\ViewDataTable\GenerateGraphHTML.php(109): Piwik_ViewDataTable_GenerateGraphHTML->buildView()
#23 C:\piwik\core\Controller.php(247): Piwik_ViewDataTable_GenerateGraphHTML->main()
#24 C:\piwik\plugins\VisitsSummary\Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins(‘VisitsSummary’, ‘getEvolutionGra…’, Array, Array, ‘Dit is een over…’)
#25 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#26 C:\piwik\core\FrontController.php(138): call_user_func_array(Array, Array)
#27 C:\piwik\index.php(53): Piwik_FrontController->dispatch()
#28 {main}

Thanks for the report, we didn’t catch this bug because we don’t run with the sam version of mysql

please try replacing your core/archiveprocessing.php file by thi one wher i’ve tried to fix the issue: http://dev.piwik.org/svn/trunk/core/ArchiveProcessing.php

but because I cant replicate i’m not sure the fix works. can you please confirm ASAP if this works for you? Thanks :slight_smile:

Hi Matt,

Thanks for the quick response. I tried the new file but now i’m getting an incorrect date error.

The executed query giving this error:


INSERT INTO pk_archive_numeric_2012_05 SELECT ifnull(max(idarchive),0)+1, 'locked_66a635bf73858f86902a34d146ddc0a2',2, '','',0,'',0 FROM pk_archive_numeric_2012_05 as tb1 

And if you’re changing the file, place the $locked variable outside the quotes ;).


SQLSTATE[HY000]: General error: 1292 Incorrect date value: '' for column 'date1' at row 1

Backtrace:

#0 C:\sites3\Netpoint\piwik\core\ArchiveProcessing.php(695): Zend_Db_Adapter_Pdo_Abstract->exec('INSERT INTO pk_...')
#1 C:\sites3\Netpoint\piwik\core\ArchiveProcessing.php(533): Piwik_ArchiveProcessing->loadNextIdarchive()
#2 C:\sites3\Netpoint\piwik\core\ArchiveProcessing.php(472): Piwik_ArchiveProcessing->initCompute()
#3 C:\sites3\Netpoint\piwik\core\Archive\Single.php(226): Piwik_ArchiveProcessing->launchArchiving()
#4 C:\sites3\Netpoint\piwik\core\Archive\Array\IndexedByDate.php(90): Piwik_Archive_Single->prepareArchive()
#5 C:\sites3\Netpoint\piwik\plugins\VisitsSummary\API.php(88): Piwik_Archive_Array_IndexedByDate->getDataTableFromNumeric(Array)
#6 [internal function]: Piwik_VisitsSummary_API->get('2', 'day', '2012-05-02,2012...', false, 'nb_visits')
#7 C:\sites3\Netpoint\piwik\core\API\Proxy.php(190): call_user_func_array(Array, Array)
#8 C:\sites3\Netpoint\piwik\plugins\API\API.php(986): Piwik_API_Proxy->call('Piwik_VisitsSum...', 'get', Array)
#9 [internal function]: Piwik_API_API->get('2', 'day', '2012-05-02,2012...', false, 'nb_visits')
#10 C:\sites3\Netpoint\piwik\core\API\Proxy.php(190): call_user_func_array(Array, Array)
#11 C:\sites3\Netpoint\piwik\core\API\Request.php(128): Piwik_API_Proxy->call('Piwik_API_API', 'get', Array)
#12 C:\sites3\Netpoint\piwik\core\ViewDataTable.php(421): Piwik_API_Request->process()
#13 C:\sites3\Netpoint\piwik\core\ViewDataTable\GenerateGraphData\ChartEvolution.php(109): Piwik_ViewDataTable->loadDataTableFromAPI()
#14 C:\sites3\Netpoint\piwik\core\ViewDataTable\GenerateGraphData.php(181): Piwik_ViewDataTable_GenerateGraphData_ChartEvolution->loadDataTableFromAPI()
#15 C:\sites3\Netpoint\piwik\core\Controller.php(247): Piwik_ViewDataTable_GenerateGraphData->main()
#16 C:\sites3\Netpoint\piwik\plugins\VisitsSummary\Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins('VisitsSummary', 'getEvolutionGra...', Array, Array, 'Dit is een over...')
#17 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#18 C:\sites3\Netpoint\piwik\core\FrontController.php(138): call_user_func_array(Array, Array)
#19 C:\sites3\Netpoint\piwik\core\FrontController.php(159): Piwik_FrontController->dispatch('VisitsSummary', 'getEvolutionGra...', Array)
#20 C:\sites3\Netpoint\piwik\core\ViewDataTable\GenerateGraphHTML.php(170): Piwik_FrontController->fetchDispatch('VisitsSummary', 'getEvolutionGra...', Array)
#21 C:\sites3\Netpoint\piwik\core\ViewDataTable\GenerateGraphHTML.php(125): Piwik_ViewDataTable_GenerateGraphHTML->getGraphData()
#22 C:\sites3\Netpoint\piwik\core\ViewDataTable\GenerateGraphHTML.php(109): Piwik_ViewDataTable_GenerateGraphHTML->buildView()
#23 C:\sites3\Netpoint\piwik\core\Controller.php(247): Piwik_ViewDataTable_GenerateGraphHTML->main()
#24 C:\sites3\Netpoint\piwik\plugins\VisitsSummary\Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins('VisitsSummary', 'getEvolutionGra...', Array, Array, 'Dit is een over...')
#25 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#26 C:\sites3\Netpoint\piwik\core\FrontController.php(138): call_user_func_array(Array, Array)
#27 C:\sites3\Netpoint\piwik\index.php(53): Piwik_FrontController->dispatch()
#28 {main}

I have changed the code to this:


		$db->exec("INSERT INTO $table "
					." SELECT ifnull(max(idarchive),0)+1, '".$locked."',".(int)$this->idsite.", '".$this->startDatetimeUTC."','".$this->endDatetimeUTC."',0,'',0 "
					." FROM $table as tb1");

It now gives an error ‘SQLSTATE[HY000]: General error: 1292 Incorrect datetime value: ‘’ for column ‘ts_archived’ at row 1’ but i’m not sure what date to use in that field.

OK please try this one: http://dev.piwik.org/trac/changeset/6442

Thanks this fixed the problem!

I was really thinking the wrong way…