Last Visit Syntax error on V1.6

Support & Bugs
1

Hi everyone,

I installed and configured version 1.6 yesterday, but the last Visit graph widget shows the following error. Visits by Server Time and the Visitors overview also do something similar. I’ve been reading through the forum but I’ve only seen you speak about this error for versions prior to 1.6. I’ve checked the times in the config and the PHP ini, but they’re the same.

Since I installed, there was a moment where I got it to work after making a change and restarting. Unfortunately, It’s reverted back to the error.

Any help would be appreciated, Thanks!

Here is the outputted text.

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 ‘����=<[�{zR���\ � ,Mo�e��Teu���㾣aj��Ԯ�1T�mAv�<�u�Z߱�i1zW�j�q[�];�’ at line 4

Go to Piwik
Login
Backtrace:

#0 C:\Inetpub\wwwroot\piwikinst\libs\Zend\Db\Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\Inetpub\wwwroot\piwikinst\core\Db\Adapter\Pdo\Mysql.php(207): Zend_Db_Statement->execute(Array)
#2 C:\Inetpub\wwwroot\piwikinst\core\PluginsFunctions\Sql.php(45): Piwik_Db_Adapter_Pdo_Mysql->query(‘INSERT IGNORE??..’, Array)
#3 C:\Inetpub\wwwroot\piwikinst\core\PluginsFunctions\Sql.php(90): Piwik_Sql::query(‘INSERT IGNORE??..’, Array)
#4 C:\Inetpub\wwwroot\piwikinst\core\Piwik.php(2381): Piwik_Query(‘INSERT IGNORE??..’, Array)
#5 C:\Inetpub\wwwroot\piwikinst\core\Piwik.php(2357): Piwik::tableInsertBatchIterate(‘piwik_archive_b…’, Array, Array)
#6 C:\Inetpub\wwwroot\piwikinst\core\ArchiveProcessing.php(724): Piwik::tableInsertBatch(‘piwik_archive_b…’, Array, Array)
#7 C:\Inetpub\wwwroot\piwikinst\core\ArchiveProcessing.php(670): Piwik_ArchiveProcessing->insertBulkRecords(Array)
#8 C:\Inetpub\wwwroot\piwikinst\plugins\Actions\Actions.php(474): Piwik_ArchiveProcessing->insertBlobRecord(‘Actions_downloa…’, Array)
#9 C:\Inetpub\wwwroot\piwikinst\plugins\Actions\Actions.php(433): Piwik_Actions->archiveDayRecordInDatabase(Object(Piwik_ArchiveProcessing_Day))
#10 [internal function]: Piwik_Actions->archiveDay(Object(Piwik_Event_Notification))
#11 C:\Inetpub\wwwroot\piwikinst\libs\Event\Dispatcher.php(284): call_user_func_array(Array, Array)
#12 C:\Inetpub\wwwroot\piwikinst\core\PluginsManager.php(609): Event_Dispatcher->postNotification(Object(Piwik_Event_Notification), false, false)
#13 C:\Inetpub\wwwroot\piwikinst\core\ArchiveProcessing\Day.php(42): Piwik_PostEvent(‘ArchiveProcessi…’, Object(Piwik_ArchiveProcessing_Day))
#14 C:\Inetpub\wwwroot\piwikinst\core\ArchiveProcessing.php(440): Piwik_ArchiveProcessing_Day->compute()
#15 C:\Inetpub\wwwroot\piwikinst\core\Archive\Single.php(224): Piwik_ArchiveProcessing->launchArchiving()
#16 C:\Inetpub\wwwroot\piwikinst\core\Archive\Array\IndexedByDate.php(81): Piwik_Archive_Single->prepareArchive()
#17 C:\Inetpub\wwwroot\piwikinst\plugins\VisitsSummary\API.php(79): Piwik_Archive_Array_IndexedByDate->getDataTableFromNumeric(Array)
#18 [internal function]: Piwik_VisitsSummary_API->get(‘1’, ‘day’, ‘2011-10-04,2011…’, false, ‘nb_visits’)
#19 C:\Inetpub\wwwroot\piwikinst\core\API\Proxy.php(179): call_user_func_array(Array, Array)
#20 C:\Inetpub\wwwroot\piwikinst\core\API\Request.php(121): Piwik_API_Proxy->call(‘Piwik_VisitsSum…’, ‘get’, Array)
#21 C:\Inetpub\wwwroot\piwikinst\core\ViewDataTable.php(391): Piwik_API_Request->process()
#22 C:\Inetpub\wwwroot\piwikinst\core\ViewDataTable\GenerateGraphData\ChartEvolution.php(71): Piwik_ViewDataTable->loadDataTableFromAPI()
#23 C:\Inetpub\wwwroot\piwikinst\core\ViewDataTable\GenerateGraphData.php(98): Piwik_ViewDataTable_GenerateGraphData_ChartEvolution->loadDataTableFromAPI()
#24 C:\Inetpub\wwwroot\piwikinst\core\Controller.php(146): Piwik_ViewDataTable_GenerateGraphData->main()
#25 C:\Inetpub\wwwroot\piwikinst\plugins\VisitsSummary\Controller.php(63): Piwik_Controller->renderView(Object(Piwik_ViewDataTable_GenerateGraphData_ChartEvolution), false)
#26 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#27 C:\Inetpub\wwwroot\piwikinst\core\FrontController.php(132): call_user_func_array(Array, Array)
#28 C:\Inetpub\wwwroot\piwikinst\core\FrontController.php(153): Piwik_FrontController->dispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#29 C:\Inetpub\wwwroot\piwikinst\core\ViewDataTable\GenerateGraphHTML.php(148): Piwik_FrontController->fetchDispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#30 C:\Inetpub\wwwroot\piwikinst\core\ViewDataTable\GenerateGraphHTML.php(108): Piwik_ViewDataTable_GenerateGraphHTML->getGraphData()
#31 C:\Inetpub\wwwroot\piwikinst\core\ViewDataTable\GenerateGraphHTML.php(92): Piwik_ViewDataTable_GenerateGraphHTML->buildView()
#32 C:\Inetpub\wwwroot\piwikinst\core\Controller.php(146): Piwik_ViewDataTable_GenerateGraphHTML->main()
#33 C:\Inetpub\wwwroot\piwikinst\plugins\VisitsSummary\Controller.php(63): Piwik_Controller->renderView(Object(Piwik_ViewDataTable_GenerateGraphHTML_ChartEvolution), false)
#34 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#35 C:\Inetpub\wwwroot\piwikinst\core\FrontController.php(132): call_user_func_array(Array, Array)
#36 C:\Inetpub\wwwroot\piwikinst\index.php(53): Piwik_FrontController->dispatch()
#37 {main}

2

It seems to be an incompatility with Mysql or the PDO / php driver and Piwik data.

please apply the following patch:


Index: core/PluginsFunctions/Sql.php
===================================================================
--- core/PluginsFunctions/Sql.php	(revision 5398)
+++ core/PluginsFunctions/Sql.php	(working copy)
@@ -42,7 +42,13 @@
 
 	static public function query($sql, $parameters = array())
 	{
-		return self::getDb()->query($sql, $parameters);
+		try {
+			return self::getDb()->query($sql, $parameters);
+		} catch(Exception $e) {
+			var_dump($sql); var_dump($parameters);
+			var_dump($e->getMessage());
+			throw $e;
+		}
 	}
 
 	static public function fetchAll($sql, $parameters = array())

Then generate the error again and post full message here. Thanks

Also, please update to latest PHP and Mysql, the problem should be fixed?

3

Hey Matt, many thanks for the quick response.

I did the file replace as you suggested. This is a strange problem, at one point I’ll see the error and then if I refresh or stop-start the server (or something), it goes away as it’s not even there right now.

This morning, it showed the error on the last external websites widget, and not on the last visit graph and after I did a stop start, it’s no longer on the last external websites widget.

These are my versions. Are they that far off?
php: 5.3.02.0
mysql: 5.4.1-beta-community

On the other hand, I did copy the returned error text yesterday, as you requested.

string(146) “INSERT IGNORE INTO piwik_archive_blob_2011_11 (idarchive,idsite,date1,date2,period,ts_archived,name,value) VALUES (?,?,?,?,?,?,?,?)” array(8) { [0]=> int(11) [1]=> string(1) “1” [2]=> string(10) “2011-11-03” [3]=> string(10) “2011-11-03” [4]=> int(1) [5]=> string(19) “2011-11-03 20:36:12” [6]=> string(21) “Actions_actions_url_1” [7]=> string(385) “x���_o�0ſK|۠�,1{٫��_M�.@��j���(C���6�rN~�{)��>&f�L�X���}���-�B��,6���z�Wߺ�t�R� �SA3yO%�x�m���-b�������h�a�2��(�K�X(���p���օ������mE��E��F�b+#�h�˫���Sp(5��q����*e�.����HD+�F�����C�u�Ie��,�yV؅�:PC�]�~�:h ��) V�@�u��Z������AN]4@8����ݟe��O��’ziq�c�~�_���L��a����X��㡥 wܡ�)�5�o�8�#t% �b=xL�NУ��;��/$n" } string(276) “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 ‘ziq�c�~�_���L��a����X��㡥wܡ�)�5�o�8�#t% �b=xL�NУ��;��/$n’)’ at line 4” string(146) “INSERT IGNORE INTO piwik_archive_blob_2011_11 (idarchive,idsite,date1,date2,period,ts_archived,name,value) VALUES (?,?,?,?,?,?,?,?)” array(8) { [0]=> int(11) [1]=> string(1) “1” [2]=> string(10) “2011-11-03” [3]=> string(10) “2011-11-03” [4]=> int(1) [5]=> string(19) “2011-11-03 20:36:12” [6]=> string(21) “Actions_actions_url_2” [7]=> string(209) “x�����0��ew��@w��x’e!��B�� ���@$�mڦ���Cp����#���I7: �X��,�yC�A[�D�bXG����agD� �B���[��’��;��qm!L ����ȣ앸!I��JSU7qDUr�f�桐a��熽��T�wx���_1 �E�a��I�y���������"�D}�a�xCձe" } string(282) "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 'e!��B\0� ���@$�mڦ���Cp����#�\0�I7:\r�X��,�yC�A[�D�bXG����agD�\r�B���[' at line 4" string(146) "INSERT IGNORE INTO piwik_archive_blob_2011_11 (idarchive,idsite,date1,date2,period,ts_archived,name,value) VALUES (?,?,?,?,?,?,?,?)" array(8) { [0]=> int(11) [1]=> string(1) "1" [2]=> string(10) "2011-11-03" [3]=> string(10) "2011-11-03" [4]=> int(1) [5]=> string(19) "2011-11-03 20:36:12" [6]=> string(21) "Actions_actions_url_3" [7]=> string(180) "x���=�0���� m-���Ƹ����H�5���S���]��}�P�0U’d{���MS�ӕJ��K�2�A��8����R�-�0�”$�~ӯ��(�<�[��$�4���>}T³��� X{�0I���L�k;O�[“� uT�|����@�����w��9_��| ��yæ� �^t8” } string(282) “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 'd{���MS�ӕJ��K�2�A��8����R�-�0�”$�~ӯ��(�<�[��$�4��\Z�>}T³��� X{�0I�’ at line 4" string(146) “INSERT IGNORE INTO piwik_archive_blob_2011_11 (idarchive,idsite,date1,date2,period,ts_archived,name,value) VALUES (?,?,?,?,?,?,?,?)” array(8) { [0]=> int(11) [1]=> string(1) “1” [2]=> string(10) “2011-11-03” [3]=> string(10) “2011-11-03” [4]=> int(1) [5]=> string(19) “2011-11-03 20:36:12” [6]=> string(20) “Actions_downloads_13” [7]=> string(248) "x���Ak1����^c6��N�z+�E�x�Y;��Y�)k���Je�=xx�0��Ǘ �p�01�J�����Xմ^�N��c�El�AЗ�E� j��&@����s����h��s�w�e�LJ:c!��TT��kC�2-KsJS��j߾w�3[���m���ATt��͜z�’�M"���}�>��@�Lj�kC���@Z�ݵpdO����k�#[�ie��K�!���c�ӿ~���z�5�|Qis�ϓ” } string(282) “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 ‘�M"���}�>��@�Lj�kC���@Z�ݵpdO����k�#[�ie��K�!���c�ӿ~���z�5\0|Qis�ϓ’ at line 4”

4

These are my versions. Are they that far off?
Yes pretty old. If you update it should fix it hopefully?

5

Hey Matt,

Strangely enough, I’ve not seen any problems in a while (haven’t upgraded). I’m gonna watch it for a bit and let you know if anything comes up.

Thanks again for your help