Creating database schema throws exceptions

[Richigeht]

Describe the bug
Creating database schema throws exceptions.

To Reproduce
Steps to reproduce the behavior:

1. Run: bin/console doctrine:schema:create

Expected behavior
Database to be created successfully.

Using PHP7.2 and mySQL 10.5

Additional context
`In ToolsException.php line 34:

Schema-Tool failed with Error 'An exception occurred while executing 'CREAT
E TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARC
HAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NU
LL, address TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL,
calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, re
nderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX
UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb
4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
oo long; max key length is 767 bytes' while executing DDL: CREATE TABLE kim
ai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NO
T NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address
TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator
VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARC
HAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CF
E95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE u
tf8mb4_unicode_ci ENGINE = InnoDB

In AbstractMySQLDriver.php line 126:

An exception occurred while executing 'CREATE TABLE kimai2_invoice_template
s (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHA
R(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL,
due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NUL
L, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, pa
yment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), P
RIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci EN
GINE = InnoDB':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
oo long; max key length is 767 bytes

In PDOConnection.php line 109:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
oo long; max key length is 767 bytes

In PDOConnection.php line 107:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t
oo long; max key length is 767 bytes

doctrine:schema:create [--dump-sql] [--em [EM]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--]
`

[kevinpapst]

It can be solved by setting the MySQL config innodb_large_prefix=1, see:
#285 (comment)

And I will change the definition, the key doesn't need to be so long.

[kevinpapst]

Thanks for reporting @Richigeht
I never experienced this, seems this is fixed in newer database versions.
But I added a PR that should fix the issue, as it was reported by other users as well.
Could you give it a try and let me know how it works?

[kevinpapst]

Merged to master, just pull and retry. You might have to drop you schema first.

[Richigeht]

Hi, still get an error though:

`17:09:53 ERROR [console] Error thrown while running command "doctrine:schema:create". Message: "Schema-Tool failed with Error 'An exception occurred while executing 'CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':\n\nSQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes' while executing DDL: CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB" ["exception" => Doctrine\ORM\Tools\ToolsException { …},"command" => "doctrine:schema:create","message" => """ Schema-Tool failed with Error 'An exception occurred while executing 'CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':\n \n SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes' while executing DDL: CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB """] []

In ToolsException.php line 34:

Schema-Tool failed with Error 'An exception occurred while executing 'CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, add
ress TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95
E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes' while executing DDL: CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL, due_days INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NU
LL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB

In AbstractMySQLDriver.php line 126:

An exception occurred while executing 'CREATE TABLE kimai2_invoice_templates (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, company VARCHAR(255) NOT NULL, address TEXT DEFAULT NULL, due_day
s INT NOT NULL, vat INT DEFAULT NULL, calculator VARCHAR(20) NOT NULL, number_generator VARCHAR(20) NOT NULL, renderer VARCHAR(20) NOT NULL, payment_terms TEXT DEFAULT NULL, UNIQUE INDEX UNIQ_1626CFE95E237E06 (name), PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

In PDOConnection.php line 109:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

In PDOConnection.php line 107:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

`

As I understand your fix should prevent needing the mySQL option to be set, correct? Thanks.

[kevinpapst]

Correct. But you are still using the old version. The error says name VARCHAR(255) where I changed it to 60 character name VARCHAR(255).

[Richigeht]

Odd! I did fetch and pull to get the latest version and it said I am on latest code/up with Master. Will try again tomorrow. Mit freundlichen Grüßen / Best regards Richi Von meinem iPhone gesendet

…

Am 24.09.2018 um 20:55 schrieb Kevin Papst ***@***.***>: Correct. But you are still using the old version. The error says name VARCHAR(255) where I changed it to 60 character name VARCHAR(255). — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

[kevinpapst]

Pretty sure not, its 60 character in master: https://github.com/kevinpapst/kimai2/blob/master/src/Entity/InvoiceTemplate.php#L40
Probably the old data is cached, try to execute bin/console cache:clear first.

[Richigeht]

I did download again a copy of master but now get:
PHP Warning: require(/var/www/kimai2/bin/../vendor/autoload.php): failed to open stream: No such file or directory in /var/www/kimai2/bin/console on line 12
PHP Fatal error: require(): Failed opening required '/var/www/kimai2/bin/../vendor/autoload.php' (include_path='.:/usr/share/php') in /var/www/kimai2/bin/console on line 12

[kevinpapst]

Follow the documentation and do a composer install

[fedibelkacem]

I confirm resolution of this issue/bug :-)
Thank you Richi & Kevin.

[kevinpapst]

Thanks for your feedback @fedibelkacem 👍

[Richigeht]

Ok got it to work finally 👍

[kevinpapst]

Glad that it works finally.

[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.