phpClickHouse icon indicating copy to clipboard operation
phpClickHouse copied to clipboard

Fix write method format

Open den-kuz opened this issue 1 year ago • 1 comments

This PR https://github.com/smi2/phpClickHouse/pull/190 brings breaking changes.

First of all, we can not execute CREATE, DROP, ALTER without ON CLUSTER. Cause CREATE TABLE blabla ... FORMAT JSON will produce syntax error exception Second, this was very bad solution. 0 === strpos? What if query will have comment in start?

My PR fixes this issue, but breaks old code. But i think this is much better solution than the current one

den-kuz avatar Mar 14 '24 23:03 den-kuz

Hi, @isublimity can you check this PR? There was a partial fix for this problem in PR #216, and queries without the ON CLUSTER statement are executed normally now. However, there are still errors when trying to execute CREATE, DROP, and ALTER queries with the ON CLUSTER statement, because not all of them support FORMAT. For example, it is not currently possible to execute CREATE USER, DROP USER, and similar queries.

Lanatel avatar Jul 17 '24 12:07 Lanatel

Hi,

The first issues occured when we ugpraded from 1.5.1 -> 1.5.3 We've upgraded to 1.6.0 and the issue still occurs (for now we fully downgraded to 1.5.1 again, it's breaking our production now) So we're trying to create a user like this (where $this->client = ClickHouseDB\Client)

return $this->client->write(
            'CREATE USER OR REPLACE {user} ON CLUSTER special_cluster IDENTIFIED WITH sha256_password BY \'{password}\'',
            [
                'user' => $user,
                'password' => $password
            ],
            true
        );

But this results into syntax error, because the code is adding FORMAT JSON at the end of the query And based on the CH documentation, you can't use FORMAT JOSN on these kind of operations -- https://clickhouse.com/docs/en/sql-reference/statements/create/user

  [ClickHouseDB\Exception\DatabaseException (62)]                                                                                                                                          
  Syntax error: failed at position 140 ('FORMAT'): FORMAT JSON. Expected one of: VALID UNTIL, HOST, SETTINGS, DEFAULT ROLE, GRANTEES, DEFAULT DATABASE, IN, end of query. (SYNTAX_ERROR)   
  IN:CREATE USER OR REPLACE customer_db ON CLUSTER special_cluster IDENTIFIED WITH sha256_password BY 'xxx' FORMAT JSON      

Also the fix in https://github.com/smi2/phpClickHouse/pull/216 , shouldn't that have been !== false?

     if (strpos($sql, 'ON CLUSTER') === false) {
            return $this->getRequestWrite($query);
        }

aalwash avatar Jan 30 '25 10:01 aalwash