Error importing v1 Database

[MMetze]

Describe the bug
Migration of DB in Kimai2 0.6
Following the migration howto on https://v2.kimai.org/documentation/upgrade-kimai-v1/
When running the import, following errors occures:

An exception occurred while executing 'SELECT value FROM `kimai_configuration` WHERE option = ?' with params ["version"]:
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 'option = 'version'' at line 1

To Reproduce
Steps to reproduce the behavior:

1. running the import on local server

Expected behavior
Migrating the v1 database to my v2 installation

Server (please complete the following information):

• OS: Ubuntu 16.04.5 LTS
• mySQL: 5.7.24

Additional context
I manually installed php7.1, on 16.04.5 LTS.

I got executed without error when I changed the lines 324 and 332 in KimaiImporterCommand.php to

where('\'option\' = \':option\'') 

(Escaped ')
Still no luck, then the result is empty and the script exits with
[ERROR] Import can only performed from an up-to-date Kimai version:
Needs at least 1.0.1 but found

The manual query

SELECT `value` FROM kimai_configuration WHERE `option`=`value`;

also has an empty result.

[kevinpapst]

The manual query should be:

SELECT `value` FROM kimai_configuration WHERE `option`="version";

But could you please test if it works when you replace:

            ->where('option = :option')
            ->setParameter(':option', 'xxx')

to

            ->where('option = :option')
            ->setParameter('option', 'xxx')

Probably the : within the setParameter() call is wrong.

[MMetze]

Thanks for posting the correct manual query string. That one returns the actual version field fine.
Changing the script to your suggestions throws the following error:

In AbstractMySQLDriver.php line 99:
  An exception occurred while executing 'SELECT value FROM `kimai_configuration` WHERE option = ?' with params ["version"]:

  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 'option = 'version'' at line 1

[kevinpapst]

I guess I found the problem now. The column "option" is a reserved name in MySQL but I am testing with MariaDB, which can somehow ignore that.
The column needs to be quoted, can you replace the complete code block with this:

    protected function checkDatabaseVersion(SymfonyStyle $io, $requiredVersion, $requiredRevision)
    {
        $optionColumn = $this->connection->quoteIdentifier('option');
        $qb = $this->connection->createQueryBuilder();

        $version = $this->connection->createQueryBuilder()
            ->select('value')
            ->from($this->connection->quoteIdentifier($this->dbPrefix . 'configuration'))
            ->where($qb->expr()->eq($optionColumn, ':option'))
            ->setParameter('option', 'version')
            ->execute()
            ->fetchColumn();

        $revision = $this->connection->createQueryBuilder()
            ->select('value')
            ->from($this->connection->quoteIdentifier($this->dbPrefix . 'configuration'))
            ->where($qb->expr()->eq($optionColumn, ':option'))
            ->setParameter('option', 'revision')
            ->execute()
            ->fetchColumn();
...

[MMetze]

Works like a charm. Thank you for your quick support!

[kevinpapst]

Thanks for your feedback, I'll close the ticket after I fixed it in master.

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. If you use Kimai on a daily basis, please consider donating to support further development of Kimai.