Unit test regressions #1303

On r2098

Bamboo reporting:

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key<br/><b>TEST INITIALIZATION FAILED!
13-Apr-2010 10:10:20         [exec] Zend_Db_Statement_Exception: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key in /mnt/bamboo-agent/build-dir/PIWIK-TRUNK/build/libs/Zend/Db/Statement/Pdo.php on line 234
13-Apr-2010 10:10:20         [exec]
13-Apr-2010 10:10:20         [exec] Call Stack:
13-Apr-2010 10:10:20         [exec]     0.0027      65400   1. {main}() /mnt/bamboo-agent/build-dir/PIWIK-TRUNK/build/tests/all_tests.php:0
13-Apr-2010 10:10:20         [exec]     0.1305    8981592   2. TestSuite->run() /mnt/bamboo-agent/build-dir/PIWIK-TRUNK/build/tests/all_tests.php:54
13-Apr-2010 10:10:20         [exec]     0.1312    8981860   3. TestSuite->run() /mnt/bamboo-agent/build-dir/PIWIK-TRUNK/build/tests/simpletest/test_case.php:598
13-Apr-2010 10:10:20         [exec]     0.1312    8982508   4. Test_Database->__construct() /mnt/bamboo-agent/build-dir/PIWIK-TRUNK/build/tests/simpletest/test_case.php:594

Running unit tests locally:

Exception: ../tests/core/ReleaseCheckList.test.php -> Test_Piwik_ReleaseCheckList -> test_piwikTrackerDebugIsOff -> Unexpected PHP error [Constant PIWIK_ENABLE_TRACKING already defined] severity [E_NOTICE] in [/home/apang/work/piwik/dev/trunk/piwik.php line 19]

Looking at the upgrade script, we're going to get a 1075 error on this:

    'ALTER TABLE ' . Piwik::prefixTable('log_visit') . '  DROP PRIMARY KEY;' => false,
    'ALTER TABLE ' . Piwik::prefixTable('log_visit') . ' ADD PRIMARY KEY (idsite, idvisit) ;' => false,

From http://dev.mysql.com/doc/refman/5.4/en/innodb-restrictions.html
For an AUTO_INCREMENT column, you must always define an index for the table, and that index must contain just the AUTO_INCREMENT column. In MyISAM tables, the AUTO_INCREMENT column may be part of a multi-column index.

(In [2099]) fixes #1303 - quick fix until the Installer allows storage engine selection

I'm not sure about this issue, as it works fine on my mysql: the idvisit is auto incremented separately for each idsite. The queries ran fine on the demo test database which is now running trunk.

are there potential problems?

The first Bamboo error is typical when the default storage engine is Innodb. Another scenario is when users try to convert their database from MyISAM to Innodb.

The 1075 error in comment:1 occurs when updating a database that's already using the Innodb storage engine.

ah! nice catch by bamboo there. I found a thread: http://www.mail-archive.com/mysql@lists.mysql.com/msg63181.html

I think some users are using Innodb so it makes sense to try and stay compatible with this. The downside is an additional INDEX which will slow down tracker, but this is the only solution for now to have good performance in Live! widget.