I’m totally new to this software, but I’m an experienced user of software/computers etc. I think the installation procedure was very easy. And it all seemed to work fine, when I’ve logged in. But when I tested (put your js-code on a website and visited that website), and then logged in to the control panel I got this message in ALL gadgets:
“Illegal mix of collationsSQLSTATE[HY000]: General error: 1271 Illegal mix of collations for operation ‘concat’”
This seems to be an issue with some general SQL-statement in your code. (But first after one visit has been made)
I’ve tested with diffrent languages but it doesn’t seem to matter. (I get the error-code anyway). I’ve attached a screenshot, so you might be able to understand the problem easier.
I also get this error when I click on menu "websites"
Notice: Undefined index: debugTrackVisitsInsidePiwikUI in C:\www\stats\piwik\tmp\templates_c%%1D^1D5^1D598A14%%piwik_tag.tpl.php on line 3
Yes, it’s true that Mysql has ISO-8859-1 as default characterset.
As I understand I have to replace the code:
$db = Zend_Db::factory($config->database->adapter, $dbInfos);
$db->getConnection();
// see [http://framework.zend.com/issues/browse/ZF-1398](http://framework.zend.com/issues/browse/ZF-1398)
$db->getConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$db->getConnection()->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
Zend_Db_Table::setDefaultAdapter($db);
$db->resetConfigArray(); // we don't want this information to appear in the logs
in file core/piwik.php with your code? (About row 1212) (in function createDatabaseObject())
I didn’t get this to work. In fact it got even worse, the error is shown before any gadget is shown when this replacement has been done.
Ok If I understund you right, the static function createDatabaseObject should look like this:
static public function createDatabaseObject( $dbInfos = null )
{
$config = Zend_Registry::get('config');
if(is_null($dbInfos))
{
$dbInfos = $config->database->toArray();
}
$dbInfos['profiler'] = $config->Debug->enable_sql_profiler;
$db = null;
Piwik_PostEvent('Reporting.createDatabase', $db);
if(is_null($db))
{
if($config->database->adapter == 'PDO_MYSQL')
{
$dbInfos['driver_options'] = array(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
}
$db = Zend_Db::factory($config->database->adapter, $dbInfos);
$db->getConnection();
// see [http://framework.zend.com/issues/browse/ZF-1398](http://framework.zend.com/issues/browse/ZF-1398)
$db->getConnection()->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
$db->getConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$db->getConnection()->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
Zend_Db_Table::setDefaultAdapter($db);
$db->resetConfigArray(); // we don't want this information to appear in the logs
}
Zend_Registry::set('db', $db);
}
It seems like we’re closer to the solution now. There are still a illegal mix of collations but only at “Last visits” graph gadget. A file is attached…
Above is when I show today.
But now I noticed when I change interval to week or month and there is no data, then the same problem with “Illegal mix of collations” appears as it did from the beginning. A file is attached for this also.
You’ll then need to access your MySQL database directly using either the shell (aka command line) ‘mysql’ command or GUI, and delete all your archive tables. These are named archive_blob_* and archive_numeric_*. These will be recreated with the proper charset & collation.
You’ll then need to access your MySQL database directly using either the shell (aka command line) ‘mysql’ command or GUI, and delete all your archive tables. These are named archive_blob_* and archive_numeric_*. These will be recreated with the proper charset & collation.
Examples:
SHOW TABLES LIKE ‘%archive_%’;
DROP TABLE piwik_archive_numeric_2009_07;[/quote]
I tested your above solution, and now we are back to were we were from the beginning ):
Now I have digged further and I see that the proper charset & collation is correct on every single table in the piwik-database.
So it might be another problem?
I’m using MySQL 3.23. Is that an issue? The table-engine is MyISAM. Can that be it?
I’ve attached a file… the configuration for a table in the piwik-database.
Piwik requires MySQL 4.1 or greater. So, yeah, that could be an issue…
The last thing to try is to drop the database and delete your config.ini.php. Modify the CREATE DATABASE query (in Piwik.php) appending DEFAULT CHARACTER SET utf8. Then go thru the Piwik installation screens again.
I would try this with and without the earlier code snippets to see if any make or break it.
[quote=vipsoft @ Aug 4 2009, 12:19 PM]Piwik requires MySQL 4.1 or greater. So, yeah, that could be an issue…
The last thing to try is to drop the database and delete your config.ini.php. Modify the CREATE DATABASE query (in Piwik.php) appending DEFAULT CHARACTER SET utf8. Then go thru the Piwik installation screens again.
I would try this with and without the earlier code snippets to see if any make or break it.[/quote]
Ok It wasn’t clear to me that Piwik requires MySQL 4.1… It should also been flagged for in the installation-process (I only saw that MySQL was required). I’ll try the above first and come back to you. Thanks anyway for great support!
[quote=vipsoft @ Aug 4 2009, 12:19 PM]Piwik requires MySQL 4.1 or greater. So, yeah, that could be an issue…
The last thing to try is to drop the database and delete your config.ini.php. Modify the CREATE DATABASE query (in Piwik.php) appending DEFAULT CHARACTER SET utf8. Then go thru the Piwik installation screens again.
I would try this with and without the earlier code snippets to see if any make or break it.[/quote]
It doesn’t work. It seems to be an issue with the version of mysql. I’ll have to wait for piwik-usage until our Mysql is upgraded.
[quote=vipsoft @ Aug 5 2009, 04:05 AM]What does your MySQL database return for this query:
SELECT VERSION();
I’m curious how you got past the MySQL version check in the installer. (It should have displayed an error, e.g.,
Error while trying to connect to the MySQL database:
Your MySQL version is 3.0.23 but Piwik requires at least 4.1.
[/quote]
Aaaah. Please forgive me! I look at the db-table and it stood that the default engine from mysql 3.23 is MyIsam was default, but now I’ve checked the actual version of the MySQL server is 4.1.7-nt. So if you have such a check, it probably works
But that means, I’m back and messing up your brains with a lot of stupid questions… style_emoticons/<#EMO_DIR#>/rolleyes.gif
I’ve tested with setting charset to utf8 and collation to utf8_swedish_ci. (I’ve also tested with collation utf8_general_ci). Which collation is prefered in piwik? (I know the utf8 is prefered as charset…) Which storage-engine is prefered? (I tested now with innodb and from the beginning it was db-engine MyISAM)
[quote=vipsoft @ Aug 5 2009, 02:23 PM]We should be able to use the default collation with utf8. MyISAM is ok.
I took another look and there’s a bug in my cut & paste editing of that patch.
Let’s step through this again.
drop the piwik database
remove piwik/config/config.ini.php
delete files in piwik/tmp/*
apply the patch below (there might be some contextual differences because we’ve been working on other things)
walk through the Piwik installation again
--- Piwik.php 2009-08-04 23:51:07.000000000 -0400
+++ Piwik.php.new 2009-08-05 10:10:09.000000000 -0400
@@ -1204,23 +1204,24 @@
}
static public function createDatabase( $dbName = null )
{
if(is_null($dbName))
{
$dbName = Zend_Registry::get('config')->database->dbname;
}
- Piwik_Query("CREATE DATABASE IF NOT EXISTS ".$dbName);
+ Piwik_Query('CREATE DATABASE IF NOT EXISTS ' . $dbName
+ . ' DEFAULT CHARACTER SET utf8');
}
static public function dropDatabase()
{
$dbName = Zend_Registry::get('config')->database->dbname;
Piwik_Query("DROP DATABASE IF EXISTS " . $dbName);
}
static public function createDatabaseObject( $dbInfos = null )
{
$config = Zend_Registry::get('config');
if(is_null($dbInfos))
{
@@ -1234,16 +1235,20 @@
if(is_null($db))
{
if($dbInfos['port'][0] == '/')
{
$dbInfos['unix_socket'] = $dbInfos['port'];
unset($dbInfos['host']);
unset($dbInfos['port']);
}
+ if($config->database->adapter == 'PDO_MYSQL')
+ {
+ $dbInfos['driver_options'] = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'");
+ }
$db = Zend_Db::factory($config->database->adapter, $dbInfos);
$db->getConnection();
// see [http://framework.zend.com/issues/browse/ZF-1398](http://framework.zend.com/issues/browse/ZF-1398)
$db->getConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$db->getConnection()->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
Zend_Db_Table::setDefaultAdapter($db);
$db->resetConfigArray(); // we don't want this information to appear in the logs
}
[/quote]
Hello…
It went slightly worse… When I tried to do the installation again, I got this error. "Error while trying to connect tohe MySQL -database. No entry is registered for key ‘db’. See attached screenshot.
I switched back to my “old” code, and I got past that screen. I switched back to your code… I got this error… see attached screenshot.
It seems like the database-object doesn’t get registrered correctly…
Hm… Why can’t I attach more files in this forum? (It stands "No entry is registered for key ‘db’ and a lot of backtrace… core\pluginsFunctions\sql.php: Zend_Registry::get(‘db’) and so on…
