Error: (1064, "You have an error in your SQL syntax; ...)

[SWS-5007]

Hi, When I run this alembic upgrade head command, I am getting this error below.

... ... File "C:\Users\Home\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error err.raise_mysql_exception(self._data) File "C:\Users\Home\AppData\Local\Programs\Python\Python310\lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (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 'JSON) AS value\n LIMIT 1\n ) ON DUPLIC' at line 20") [SQL: CREATE DEFINER=root@%% PROCEDURE etl_range(block_start INT(11), block_end INT(11), update_status INT(1)) BEGIN

GLOBAL SETTINGS

            SET @block_start = `block_start`;
            SET @block_end = `block_end`;
            SET @update_status = `update_status`;

            ### CALL OTHER STORED PROCEDURES ###
            CALL `etl_codec_block_timestamp`(`block_start`,`block_end`,`update_status`);

            # CALL `stored_procedure_02`();
            # CALL `stored_procedure_03`();

            ### UPDATE STATUS TABLE ###
            IF @update_status = 1 THEN
                INSERT INTO `harvester_status` (`key`,`description`,`value`)(
                    SELECT
                        'PROCESS_ETL' AS    `key`,
                        'Max blocknumber of etl process' AS `description`,
                        CAST(@block_end AS JSON) AS `value`
                    LIMIT 1
                ) ON DUPLICATE KEY UPDATE
                    `description` = VALUES(`description`),
                    `value` = VALUES(`value`)
                ;
            END IF;
        END
                    ]

(Background on this error at: https://sqlalche.me/e/14/f405)

Here is my docker-compose.yml file.

version: '3.2'

services:

  harvester:
    build: .
    image: polkascan/harvester
    restart: unless-stopped
    entrypoint: /usr/src/start.sh
    ports:
      - '9620:9616'
    environment:
      - DB_CONNECTION=mysql+pymysql://root@mysql:3306/polkascan?charset=utf8mb4
      - SUBSTRATE_RPC_URL=wss://sheartoken.com
#      - SUBSTRATE_RPC_URL=ws://host.docker.internal:9944
      - NODE_TYPE=archive
      - SUBSTRATE_SS58_FORMAT=42
    depends_on:
      - mysql
      - substrate-node

  substrate-node:
    image: arjanz/substrate-node-template:latest
    volumes:
      - 'substrate-node:/substrate'
    ports:
      - '9944:9944'
      - '9933:9933'
    command: --dev --ws-external --rpc-cors=all --rpc-external --rpc-methods=Unsafe --no-telemetry --no-prometheus --pruning=archive

  mysql:
    image: mysql:latest
    volumes:
      - 'mysql-data:/var/lib/mysql'
    ports:
      - '33061:3306'
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=polkascan

volumes:
  substrate-node:
  mysql-data:

How to solve this error? Please help me.

[arjanz]

Looks like a Windows line-ending issue (\n in stead of \r\n\), you will have to convert this somehow

[arjanz]

Looks like a Windows line-ending issue (\n in stead of \r\n\), you will have to convert this somehow

See https://docs.github.com/en/get-started/getting-started-with-git/configuring-git-to-handle-line-endings

[SWS-5007]

I tried as your recommended doc, but there is same error above.

[arjanz]

I see you are using MariaDB, which has slightly different features or format than MySQL. The JSON syntax works on MySQL, but is different on MariaDB. See also https://mariadb.org/making-mariadb-understand-mysql-json/

[SWS-5007]

What I have done is only to clone this repo, https://github.com/polkascan/harvester and follows the readme step by step. I didn't change any codebase too. So I can't see why This error says I am using MariaDB.

[arjanz]

What I have done is only to clone this repo, https://github.com/polkascan/harvester and follows the readme step by step. I didn't change any codebase too. So I can't see why This error says I am using MariaDB.

I assumed you used an external DB, because I saw You have an error in your SQL syntax; check the manual that corresponds to your **MariaDB** server version in your logs above. So you are using the Docker MySQL image?

[SWS-5007]

No, I am testing this hasvester on my local, so I am using the Local MySQL database with Xampp Server.

[arjanz]

Then that is not a MySQL on your local machine, but MariaDB.

[SWS-5007]

So, you mean, I need to change the this code, https://github.com/polkascan/harvester/blob/main/db/versions/861750430061_etl_procedures.py with MariaDB Query Format?

Currently, I am getting Syntax Errors only in this file.

[arjanz]

It means it doesn't work on MariaDB because of earlier mentioned reasons:

I see you are using MariaDB, which has slightly different features or format than MySQL. The JSON syntax works on MySQL, but is different on MariaDB. See also https://mariadb.org/making-mariadb-understand-mysql-json/

Unless you follow the steps described in the link I provided

[arjanz]

The impression I get that this is a bit out of your comfort-zone, so I would strongly recommend using the Docker version.