Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unexpected readonly mode with specific string in query #194

Open
JanMikes opened this issue Sep 6, 2023 · 3 comments
Open

Unexpected readonly mode with specific string in query #194

JanMikes opened this issue Sep 6, 2023 · 3 comments

Comments

@JanMikes
Copy link

JanMikes commented Sep 6, 2023

Hi! I have experienced exceptions during inserts. It was weird, because out of approx 1B inserts, there were only few that failed on this exception:

In Statement.php line 172:
DatabaseException:  default: Cannot execute query in readonly mode. (READONLY)

After some debugging i was able to create minimal query to replicate the problem:

INSERT INTO `log` (`response_body`)  VALUES  ('{a:b}')

TLDR: the query will match regexp in \ClickHouseDB\Query\Query::isUseInUrlBindingsParams()

public function isUseInUrlBindingsParams():bool
    {
        //  'query=select {p1:UInt8} + {p2:UInt8}' -F "param_p1=3" -F "param_p2=4"
        return preg_match('#{[\w+]+:[\w+()]+}#',$this->sql);
    }

Then it removes the readonly: 0 default value - readonly=1 will be added to the database connection URL.

Screenshot 2023-09-06 at 23 05 40 Screenshot 2023-09-06 at 23 06 18

How to replicate:

/** @var \ClickHouseDB\Client $clickhouse */
$clickhouse->insert(
    'api_log',
    [['{a:b}']],
    ['response_body'],
);
CREATE TABLE IF NOT EXISTS api_log (
    response_body String NOT NULL
) ENGINE = MergeTree()

Expected behaviour - readonly: 0 in the URL (just changed value from {a:b} to {a: b}

Screenshot 2023-09-06 at 23 28 11

Working workaround - adds space after : in the whole query if it matches the regexp:

preg_replace('#{([\w+]+):([\w+()]+)}#', '{$1: $2}', $value);

Is there any way, other than modify the string value inserting? Thank you

@simPod
Copy link
Contributor

simPod commented Sep 28, 2023

@JanMikes you can try https://github.com/simPod/PhpClickHouseClient since there's no magic between usercode and issuing request so it might suite you better.

@andrelec1
Copy link

Same here

image

MakarMS pushed a commit to MakarMS/phpClickHouse that referenced this issue Jan 16, 2024
isublimity added a commit that referenced this issue Jan 16, 2024
fix(#202): Fix converting boolean when inserting into int and fix(#194): Fix unexpected readonly mode with specific string in query
@MakarMS
Copy link

MakarMS commented Jan 16, 2024

A fix has been made

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants