MySQL Error Message after instalation (version 0.4.1)

Support & Bugs
1

Hi,

I’ve recently installed piwik on a test environment to test piwik has a analytics software.

Installation went smooth, no errors what so ever.

Pushed the javascript code into the website pages (jsp in this case) and, again, all everything works ok (dumping http headers shows requests to the tracker url).

However, when going into the web environment to check the stats, i always get the following database error message (taken form the log files):

"0" "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 ')\n                                                                         AND name IN ( 'nb_visits' )' at line 3" "/usr/local/piwik/libs/Zend/Db/Statement/Pdo.php" "227" "#0 /usr/local/piwik/libs/Zend/Db/Statement.php(298): Zend_Db_Statement_Pdo->_execute(Array)\n#1 /usr/local/piwik/libs/Zend/Db/Adapter/Abstract.php(269): Zend_Db_Statement->execute(Array)\n#2 /usr/local/piwik/libs/Zend/Db/Adapter/Pdo/Abstract.php(206): Zend_Db_Adapter_Abstract->query('SELECT value, n...', Array)\n#3 /usr/local/piwik/libs/Zend/Db/Adapter/Abstract.php(476): Zend_Db_Adapter_Pdo_Abstract->query('SELECT value, n...', Array)\n#4 /usr/local/piwik/core/Archive/Array/IndexedByDate.php(86): Zend_Db_Adapter_Abstract->fetchAll('SELECT value, n...')\n#5 /usr/local/piwik/plugins/VisitsSummary/API.php(53): Piwik_Archive_Array_IndexedByDate->getDataTableFromNumeric(Array)\n#6 [internal function]: Piwik_VisitsSummary_API->get('1', 'day', '2009-06-23,2009...', Array)\n#7 /usr/local/piwik/core/API/Proxy.php(141): call_user_func_array(Array, Array)\n#8 /usr/local/piwik/core/API/Request.php(113): Piwik_API_Proxy->call('Piwik_VisitsSum...', 'get', Array)\n#9 /usr/local/piwik/core/ViewDataTable.php(348): Piwik_API_Request->process()\n#10 /usr/local/piwik/core/ViewDataTable/GenerateGraphData.php(94): Piwik_ViewDataTable->loadDataTableFromAPI()\n#11 /usr/local/piwik/core/Controller.php(109): Piwik_ViewDataTable_GenerateGraphData->main()\n#12 /usr/local/piwik/plugins/VisitsSummary/Controller.php(30): Piwik_Controller->renderView(Object(Piwik_ViewDataTable_GenerateGraphData_ChartEvolution), false)\n#13 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()\n#14 /usr/local/piwik/core/FrontController.php(119): call_user_func_array(Array, Array)\n#15 /usr/local/piwik/index.php(58): Piwik_FrontController->dispatch()\n#16 {main}" "2009-07-22T10:19:43-05:00"

Doing “show tables” in the database shows:

| piwik_access                  | 
| piwik_archive_blob_2009_07    | 
| piwik_archive_numeric_2009_07 | 
| piwik_goal                    | 
| piwik_log_action              | 
| piwik_log_conversion          | 
| piwik_log_link_visit_action   | 
| piwik_log_profiling           | 
| piwik_log_visit               | 
| piwik_logger_api_call         | 
| piwik_logger_error            | 
| piwik_logger_exception        | 
| piwik_logger_message          | 
| piwik_option                  | 
| piwik_site                    | 
| piwik_site_url                | 
| piwik_user                    | 
| piwik_user_dashboard          | 
| piwik_user_language           | 
+-------------------------------+

MySQL Server - Version 5.0
PHP/PHP-PDO - Version 5.2.4
Piwik - Version 0.4.1

Don’t know what to do to fix this.

Can anyone help me out with this?

Thanks,
Bruno

2

In core/Archive/Array/IndexedByDate.php, apply the changeset from:

http://dev.piwik.org/trac/changeset/1268

3

I’ve applied your patch but still reports the same error.

IndexedByDate.php now looks like:

               foreach($queries as $table => $aIds)
                {
                        if(!count($aIds))
                        {
                                continue;
                        }

                        $inIds = implode(', ', $aIds);
                        $sql = "SELECT value, name, UNIX_TIMESTAMP(date1) as timestamp
                                                                        FROM $table
                                                                        WHERE idarchive IN ( $inIds )
                                                                                AND name IN ( $inNames )";
                        $values = $db->fetchAll($sql);
                        foreach($values as $value)
                        {
                                $arrayValues[$value['timestamp']][$value['name']] = (float)$value['value'];
                        }
                }

A bit more investigation shows that the offending query is:

SELECT value, name, UNIX_TIMESTAMP(date1) as timestamp FROM piwik_archive_numeric_2009_07 WHERE idarchive IN (  ) AND name IN ( 'nb_visits' );

Has you can see there is nothing inside idarchive to be selected and this returns a syntax error in mysql.

We believe this blank “idarchive” is due to database being in GMT timezone and the server running piwik being in PDT (-8h) timezone.

We can change database timezone or server timezone to match. (which would probably fix this issue)

However, our goal is to use Piwik (in a central stats server) to provide stats for different websites (running on different servers). Those websites will be running in different timezones, then the main piwik server.

Piwik doesn’t seem able to handle a different timezone per website.
If there a way to configure piwik to handle this?

Thanks.

4

Yes, Piwik currently requires that PHP and MySQL be configured to the same timezone. (Usually this manifests as a lag in dashboard reporting.)

There is an open ticket to address this issue. If you’d like to help work on this, please see:

http://dev.piwik.org/trac/ticket/56

5

Thanks for the update.

Unfortunately we don’t posses any knowledge that would help with this issue.

The issue (ticket) is already opened for 19 months. Which doesn’t seem likely it will be fixed any time soon.

Unfortunately (again) this issue is a blocker for us, to use Piwik, due to our requirements.
However, will keep an eye on it and maybe in the future, when this issue gets fixed, we can consider piwik again.

Thanks for helping.
Keep up to good work (developers & community).

Regards.