Can't install - MariaDB, PHP7.4 Error

[Kapsonfire-DE]

[2021-06-16 09:09:34] ALERT: Rebuild database fault: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'lead (id VARCHAR(24) NOT NULL COLLATE utf8mb4_unicode_ci, deleted TINYINT(...

Webserver: | Apache/2.4.38 (Debian)
PHP-Version: | 7.4.20
MySQL-Server-Version: | 5.5.5-10.3.27-MariaDB-0+deb10u1

[Kapsonfire-DE]

[tmachyshyn]

Did this error occur during the installation or rebuild?

[Kapsonfire-DE]

On Installation. Fresh new database

[Kapsonfire-DE]

CREATE TABLE lead (id VARCHAR(24) NOT NULL COLLATE utf8mb4_unicode_ci, deleted TINYINT(1) DEFAULT '0' COLLATE utf8mb4_unicode_ci, salutation_name VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, first_name VARCHAR(100) DEFAULT '' COLLATE utf8mb4_unicode_ci, last_name VARCHAR(100) DEFAULT '' COLLATE utf8mb4_unicode_ci, title VARCHAR(100) DEFAULT NULL COLLATE utf8mb4_unicode_ci, status VARCHAR(255) DEFAULT 'New' COLLATE utf8mb4_unicode_ci, source VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, industry VARCHAR(255) DEFAULT '' COLLATE utf8mb4_unicode_ci, opportunity_amount DOUBLE PRECISION DEFAULT NULL COLLATE utf8mb4_unicode_ci, website VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, address_street VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, address_city VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, address_state VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, address_country VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, address_postal_code VARCHAR(40) DEFAULT NULL COLLATE utf8mb4_unicode_ci, do_not_call TINYINT(1) DEFAULT '0' NOT NULL COLLATE utf8mb4_unicode_ci, description MEDIUMTEXT DEFAULT NULL COLLATE utf8mb4_unicode_ci, converted_at DATETIME DEFAULT NULL COLLATE utf8mb4_unicode_ci, created_at DATETIME DEFAULT NULL COLLATE utf8mb4_unicode_ci, modified_at DATETIME DEFAULT NULL COLLATE utf8mb4_unicode_ci, account_name VARCHAR(255) DEFAULT NULL COLLATE utf8mb4_unicode_ci, middle_name VARCHAR(100) DEFAULT NULL COLLATE utf8mb4_unicode_ci, opportunity_amount_currency VARCHAR(6) DEFAULT NULL COLLATE utf8mb4_unicode_ci, created_by_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, modified_by_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, assigned_user_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, campaign_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, created_account_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, created_contact_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, created_opportunity_id VARCHAR(24) DEFAULT NULL COLLATE utf8mb4_unicode_ci, INDEX IDX_FIRST_NAME (first_name, deleted), INDEX IDX_NAME (first_name, last_name), INDEX IDX_STATUS (status, deleted), INDEX IDX_CREATED_AT (created_at, deleted), INDEX IDX_CREATED_AT_STATUS (created_at, status), UNIQUE INDEX UNIQ_CREATED_AT_ID (created_at, id), INDEX IDX_ASSIGNED_USER (assigned_user_id, deleted), INDEX IDX_ASSIGNED_USER_STATUS (assigned_user_id, status), INDEX IDX_CREATED_BY_ID (created_by_id), INDEX IDX_MODIFIED_BY_ID (modified_by_id), INDEX IDX_ASSIGNED_USER_ID (assigned_user_id), INDEX IDX_CAMPAIGN_ID (campaign_id), INDEX IDX_CREATED_ACCOUNT_ID (created_account_id), INDEX IDX_CREATED_CONTACT_ID (created_contact_id), INDEX IDX_CREATED_OPPORTUNITY_ID (created_opportunity_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB

this query fails, activated general log for it

[Kapsonfire-DE]

lead is reserved keyword, putting it into backticks works. soo the database manager should put column and table names always in backticks

[yurikuzn]

The query is generated by Doctrine DBAL I believe.

[Kapsonfire-DE]

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

reserved since 8.0.2

[Kapsonfire-DE]

Seems to be related to:
doctrine/dbal#3152

[yurikuzn]

MySQL 8.0.2 is released 4 years ago. We've never encountered this issue before. Tested on MySQL 8 numerous times.

[Kapsonfire-DE]

I can just say, this is the query espocrm tries to execute which fails and its because of LEAD is a reserved keyword.

[yurikuzn]

What EspoCRM version?

[Kapsonfire-DE]

I just downloaded today

[Kapsonfire-DE]

6.1.7

[yurikuzn]

We investigated a bit. LEAD is not reserved word in MariaDB 10.3. We can use this word unescaped in queries. It looks like you have MySQL 8.0, but it's recognized as MariaDB. Weird.

[Kapsonfire-DE]

Nope, I have MariaDB for sure
Its default for Debian Repo
just right from ssh
2021-06-17 16:30:25 0 [Note] mysqld (mysqld 10.3.27-MariaDB-0+deb10u1)

[tmachyshyn]

What is the version of your Debian OS?

[Kapsonfire-DE]

root@web1:~# lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 10 (buster)
Release: 10
Codename: buster

Operating System: Debian GNU/Linux 10 (buster)
Kernel: Linux 4.19.0-14-amd64
Architecture: x86-64

[tmachyshyn]

Thanks.
We will try to reproduce this bug on Debian 10.

[tmachyshyn]

We have installed EspoCRM on the Debian 10 from the standard repository. All works like a charm.
Perhaps, your hosting provider made some changes to the Debian distribution.